0

Linking populated tables

I am trying to migrate an existing database from Filemaker.

I have exported the records from filmmakers as a CSV document and then imported them into a new table in Ninox. So far so good. With a few minor changes, I have the tables working the way I want.

The problem comes when I try to link the tables. For example, I have a table called Clients and another called Contacts. They both have a field called "Company".

When I view a record in Clients I would like to see all the corresponding Contacts records (those with matching "Company" name).

I have tried linking Contacts as a child of Clients and this works great for new records but I can't see any of the old records.

Furthermore, sometimes one of those contacts will move to a different company that is also a client of mine. I would like to be able to edit the Contact record (change the company they work for) and have the contact record move from their old company to their new company.

Any help on how I can achieve this would be much appreciated.

Michael

2 replies

null
    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    So you have discovered that Ninox does not make links between tables by looking at the values of some data fields, but that it only uses its internal record identifiers. A consequence is that Ninox can only handle 1:N / N:1 relations directly.

    You have already build the needed relation by defining a N:1 reference field in your "Contacts" table, and verified that it works as expected for new records. You must now update that reference field, called by Ninox default "Clients", in the old records.

    While in the "Contacts" table, select "Update multiple records…" under the local "Gear" menu, choose the "Assign calculated value" for the "Clients" field, and enter the formula:

    let co := Company;
    first((select Clients)[Company = co])

    Following the procedure, you will be presented by Ninox proposal for the update. You may review it before actually doing it.

    This should build the needed relation for all records. Now what about the "Company" field in the "Contacts" table? In Filemaker, it is the base for the relation. But in Ninox, it is fully redundant, and should be removed. If you need the value of "Company" in the "Contacts" table, it is given by the expression "Clients.Company".

    In order to be able to move a contact from one client to another, using the Ninox gui, the "Contacts" table must not be a child of the "Clients" table - set "Composition" to "No".

    • Michael_Chung
    • 2 yrs ago
    • Reported - view

    Thanks Alain, I have decided to forget about moving the contact but creating the link worked perfectly

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 2Replies
  • 103Views
  • 2 Following