0

Linking Tables

Hi,  I am moving over to Ninox from Access so that I can hopefully ditch windows all together, but I am having trouble linking my table data. 

I have multiple tables (approx 5) that I have imported, each table contains the field 'email' which is how they are currently linked in access.

I would like them all to be linked so that if I change the email on the main form all of the tables update.

How do I do this in Ninox? 

I have tried to create a button to link one of the tables using the following code, but I've gone astray somewhere.  As I am getting a 'Return type mismatch for then and else expressions at line. 7, column 25." error.

Any help would be appreciated.

let Id := (select 'Master List').Id;
let email := (select
'Master List').email;
email := unique(email);
let myTable1Cnt := count(email);
for i in range(0, myTable1Cnt) do
for j in (select 'Current Categories')[email = item(email,i)] do
j.(email:=item(email, i))
end
end

4 replies

null
    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    Contrary to some other well-known database systems, Ninox does not manage links depending on the value of some data fields - what I would call "soft links", because changing the value of a data field "automatically" updates the link. Ninox manages links by some internal mechanism - what I would call "hard links". Fortunately, it is not very difficult to make Ninox initialize its flavor of links from values in an imported table.

    First, the link must be defined. From your description, I understand that several records in the "Current Categories" table must be linked to one record in the "Master List" table, so the "Current Categories" table must include a N:1 reference to the "Master List" table. Links are always created from the "N" side. So, in the "Current Categories" table, perform the action "Create table reference" to the "Master List" table. You will see that a N:1 reference field named "Master List" has been created in the "Current Categories" table, and that a 1:N reference field named "Current Categories" has also been created in the "Master List" table. Those are the names by default of the reference fields, you may change them if you prefer more descriptive names.

    To populate the references, one must again work from the "N" side. So, in the "Current Categories" table, select the "Update multiple fields" option from the "Gear" menu. Chose the "Assign calculated value" option for the "Master List" field. Enter the formula:

    let myemail := email;
    first(select 'Master List' [email = myemail])

    and execute the update. The "first" function is needed because "select" always returns an array. A side effect is that, if several records in the "Master List" table contain the same value in the "email" field, the link will point to the first one.

    Now that the reference fields have been initialized, the "email" field in the "Current Categories" has become redundant, and should be deleted. If you need the value of "email" in that table, you may obtain it with the formula "'Master List'.email". Before deleting the field, it is probably prudent to verify that all records in "Current Categories" have a working link to a record in 'Master List'. AN easy way to look for missing links is to add, in a tabular view of the "Current Categories" table, a formula column containing "'Master List'", and to filter that column with the condition "undefined", *without* the quotes.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Well done Alain for a clear explaination of how relationships work in Ninox; it's a hard one to get across. Such a shame these excellent posts can't be 'pinned' to the top of a list for future new users to reference.

    Regards John

    • Umberto
    • 2 yrs ago
    • Reported - view

    Sono perfettamente d'accordo con Jhon. Grazie Alain!

    • Umberto
    • 2 yrs ago
    • Reported - view

    Naturalmente John.

Content aside

  • 2 yrs agoLast active
  • 4Replies
  • 551Views