Importing Linked Data
This chapter shows how linked data can be imported into related tables.
Ninox builds table links solely based on the Ninox-own keys. As a key Ninox uses the No. column. The values of this column are assigned automatically and can not be changed. It is therefore not possible to change this column during the import.
Suppose you link a table “Invoices” to another table “Customers”. Then Ninox creates in the background in the table “Invoices” a field for the No. of customer.
Note, if you have previous experience with other databases: SQL, this would be equal roughly to ALTER TABLE “Invoices” ADD COLUMN “Customers” FOREIGN KEY REFERENCES customer (No).
To fill properly Invoices.Customer when importing this field, you need to give to the Import Wizard information enabling Ninox to identify the right customer to build up the relation.
The procedure to import relational data is illustrated by the example Invoices => customers. “Customers” is the main table and “Invoices” the detail table. (A customer can have multiple invoices.)
- Preparing the data to be imported
- Creating the tables in Ninox
- Import in the main table
- Import into the detail table
1. Preparing the data to be imported
The data must be in two csv files with the following exemplary structure:
customers.csv — customer No.; first name; surname
Invoices.csv — customer No.; invoice number; date; amount
It is important that there is a common key field – in this case, the customer number. The other fields are for illustration only.
2. Creating the tables in Ninox
Create analogous to CSV files two tables in Ninox:
- Customer No (text)
- First name (text)
- Name (Text)
- Customer No (reference to the Customer table)
- Invoice number (text)
- Date (date)
- Amount (number)
Note that the table “Invoices” doesn’t have a field for the customer number, instead a link to the Customers table.
3. Import into the main table
Import the Customers.csv without special settings into the customers table.
4. Import into the detail table
Import the Invoices.csv into the invoices table.
In the field assignment, you specify the following:
[Customer No] — [customers (customer number)] (update all)
Through this assignment, you tell the Import Wizards- to look up the customer number from the Invoices.csv in the customer table to associate the right customers.