0

Link records in 2 tables

Hi, I have two tables: Table 1 is called "Checks" and table 2 is called "Customers".
The relationship between the two tables is N to 1. The two tables share a field called "codclt". How can I automatically link all the records of the two tables in one operation?

8 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

    Please see this thread...

     

    https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/is-it-possible-to-link-automatically-via-a-function-fields-in-two-tables-that-contain-the-same-text-5c6aaf80e850ad238982b496

     

    The button with the script needs to be placed on the 1 table in order to link the N table.

    • Andrea.1
    • 4 yrs ago
    • Reported - view

     

    it works but after linking the records it remains in a loop, blocking the web pages.

    I have more than 5000 record in table 2..

    • Andrea.1
    • 4 yrs ago
    • Reported - view

    Another problem..the connections were all made but randomly! although the two tables share the same text fields with the same content, the links are all incorrect. Why?

    • Sean
    • 4 yrs ago
    • Reported - view

    Hello, I would first like to point out that I am not a Ninox employee. Secondly, if you read the entire thread you will see that others have sucessfully used this approach to link their tables. I haven't seen your code or your database so I can't possibly explain why you have gotten the results you have.

     

    If you want to post your code or temporarily invite me to your team, I will look at it. I can say that looping a large number of records in the browser version of Ninox is very, very slow and I would recommend getting the Mac app if you have a Mac.

    • Sean
    • 4 yrs ago
    • Reported - view

    Two more points. One, if you have duplicates in your 1 table, the linking will attach the N table records to the last duplicate of the 1 table based on Id sort order. Two, if there are any null values in your 1 table Text field it will link the N table records incorrectly. I'm working on this.

    • Sean
    • 4 yrs ago
    • Reported - view

    Per support, sometimes code that works in the Mac app will not work in the browser version.

    • Andrea.1
    • 4 yrs ago
    • Reported - view

    this is the code:

    let myTable1Id := (select 'Decreti Ingiuntivi').ID;
    let myTable1Text := (select 'Decreti Ingiuntivi').CodiceCliente;
    myTable1Text := unique(myTable1Text);
    let myTable1Cnt := count(myTable1Id);
    for i in range(0, myTable1Cnt) do
    for j in (select Indagini)[CodiceCliente = item(myTable1Text, i)] do
    j.('Decreti Ingiuntivi' := item(myTable1Id, i))
    end
    end

    the code links the records of the two tables in an absolutely random way, despite the fact that the "CodiceCliente" field (including number in both tables) contains the same values. The anomaly also appears on the Mac app. In both tables, the "CodiceCliente" field is enhanced and there are no records with this empty field.

    • Sean
    • 4 yrs ago
    • Reported - view

    Screen Shot 2020-03-17 at 2.22.28 PM

     

    Your code uses the unique() function so that will cause a problem. Is the CodiceCliente field a text field? What do you mean by, ""CodiceCliente" field is enhanced"? I have posted an updated version of the code in thread link above and you are welcome to try it.

Login to reply

Content aside

  • 4 yrs agoLast active
  • 8Replies
  • 1669Views