0

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.)

5 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    You can follow Alain Fontaine advice in this forum.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi William

    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

    Regards John

      • Independent Developer
      • WILLIAM_PORTER
      • 2 yrs ago
      • Reported - view

      John Halls Thanks, John. I understand your approach. I have done things like this before and I expected the answer would be something like this. Thanks also for your code. It's useful for me to see code snippets like the one you offered.

      But I took the advice of @fred and used Alain Fontaine's advice. Worked fine and was quite easy — easier than I thought it would be.

      . 

      For benefit of anybody else in this situation, here are the basic steps. I was playing with two source tables in FileMaker, Firms (i.e. law firms) and Employees. The 'primary' field in Firms was 'Firm'. 

      1. I confirmed the values in the Firm field of the Firms table were unique. I was sure they would be, but this wasn't inevitable. The tables in the source app are related on unique key values that I do NOT want to import into Ninox and in theory there could be two different records in that table, referring to two different law firms, but with the same name. Anyway, confirmed that the names were unique. Then exported this table. Did NOT export the firm's primary key field. 
      2. Exported from the FileMaker Employees table and included in the export a column that grabbed the name of the related firm. Did not export either the ID of the record in Employees (the primary key) OR the FirmID value in the same table (the foreign key). The keys are irrelevant to Ninox. 
      3. Imported both CSV files into Ninox, creating new tables named Firms and Employees.
      4. In Ninox now, edited the Employees table, changing the name of the Firm field to FirmNameImported.
      5. Also in Employees table, added a table reference to the Firms table. Named this reference "Firm". Employees now had three fields: EmployeeName, FirmNameImported, and Firm.
      6. Switched to the Employees table view showing all records, clicked on the gear and selected Update Multiple Records.
      7. Configured the Update to target the Firm field, using Alain Fontaine's formula, as adapted below. Performed the update.
      8. Finally, after confirming everything worked, I deleted the field FirmNameImported from the Employees table (since it's no longer needed).

      Here's the code I used in step 7: 

      let xfirm := FirmNameImported;
first(select Firms [Firm = xfirm])

      And it worked! There was one thing that had me scratching my head. In the preview screen of the Update process, it looked like what was going to be moved into the Firm field in Employees was the ID number of the connected record in Firms. But that's not what happened. When I finished the update, the Firm column contained names that matched those in the FirmNameImported column. 

      .

      In my notes above I focused entirely on the fields that matter. In real life, the Firms table has a bunch of other fields (contact name, address, etc) and the Employees table does, too. 

      I did this only as a test and I only imported data from these two tables. When I come back and convert an entire database (with say, a dozen or more related tables) I'll think a bit about how to adapt the procedure above to the more complex data sets, but I think it will basically be the same thing over and over again, always working from the perspective of the child or "many" table.

      THANKS AGAIN y'all!

      William

    • Fred
    • 2 yrs ago
    • Reported - view

    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.

    • Independent Developer
    • WILLIAM_PORTER
    • 2 yrs ago
    • Reported - view
    Fred said:
    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. 

    William

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 5Replies
  • 227Views
  • 3 Following