Conversion into Ninox of old relational data
How would I import data into Ninox from multiple tables of a relational database built in another platform and keep records related?
I am able to get primary and foreign keys into the right records in each table, so (say) the dozen records in the old LineItems table that are linked to a given Invoice record all have that invoice record's key in a field called something like LineItems.InvoiceID. I just cannot at the moment see what to do with those values after I have imported them into Ninox.
(Please ignore all the other issues involved in conversion. I'll be converting mostly from FileMaker apps, and those apps make a lot of use of calculation fields and so on. I'll rebuild those in Ninox from scratch.)
Welcome and good to hear from another FileMaker developer. You will have no problems transitioning I am sure, and if you have any questions along the please feel free to ask.
Getting to your question I have found the best approach to be to create a table reference from the child to the parent, then create a script to loop through the parent, grab the Primary Key and then find all the child records for that key, and update the relationship
for a in select ParentTable do let k := a.PrimaryKey; for b in select ChildTable where SecondaryKey = k do b.(ParentTable := a) end end
Glad things worked out.
John's suggestion is good for a repeating task.
I figured once you imported everything, you only needed to link them once and any future records would be linked manually or by a new script.
In the Update Multiple Records window, it shows the table Id of the record from Firms that will be linked in the field Firms in the Employee table. If you only have one field in Firms called Name, then Ninox will only show that field name when displaying the link. You actually have a link to the entire record and that link can be used in many useful ways in the future. If you edit the reference field Firms, you can edit the Show As to display whatever field from the record in the linked table you want.
Anyways, welcome to Ninox and hope to hear more from you in the furture.
If you edit the reference field Firms, you can edit the Show As to display whatever field from the record in the linked table you want.
Thanks for mentioning this, Fred. Great tip that I hadn't picked up on yet!
Thanks, and thanks again to John Halls too.