0

how can i automatically link two fields from two tables?

I have two Excel files. I imported them into Ninox, in one database. Each Excel is one table. Each table contains approx. 1.000 records. Both tables contain one similar field, called: ParticipantID. There is a 1-n relationship between the tables. 

I created a reference between the two tables. I validated the relationship between the two tables in the data model. 

Now I want to link the two ParticipantID fields from the two tables together. Each records in table one, with the same ParticipantID should be linked to that ParticipantID in the second table. 

When this is done, I want to analyse which records on the 1 side of the relationship haven't been used.

Is there a way to automatically match these ParticipantID's??

Thanks

Jeroen

5 replies

null
    • Sean
    • 5 yrs ago
    • Reported - view

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

    • jeroen
    • 5 yrs ago
    • Reported - view

    Hi Sean, Thanks! Can you may be share an example so I can see what to change in the example code? 

    I tried: 

    let myTable1 := Id;
    let myText := 'Participant ID';
    for i in (select CLUBS)['Participant ID' = myText] do
    i.(QRcodes := myTable1))
    end

    But this doesn't work.

    thanks

    • Sean
    • 5 yrs ago
    • Reported - view

    Hi jeroen,

    You used the record level link code in your example so it only linked records in the n table that matched the current record in the 1 table assuming QRcodes is the 1 table and CLUBS is the n table. To link all records that have a match you would put this code in the formula of a Button object on the 1 table.

     

    let myTable1Id := (select QRcodes).Id;
    let myTable1Text := (select QRcodes).Text;
    myTable1Text := unique(myTable1Text);
    let myTable1Cnt := count(myTable1Id);
    for i in range(0, myTable1Cnt) do
    for j in (select CLUBS)[Text = item(myTable1Text, i)] do
    j.(QRcodes := item(myTable1Id, i))
    end
    end

    • jeroen
    • 5 yrs ago
    • Reported - view

    Hi Sean, thanks a lot! I’m going to work with this tonight. 

    In the mean time, do you know if there is a reference (some kind of manual) available anywhere that explains the programming language and syntax used by Ninox? 

    Thanks 

    • Sean
    • 5 yrs ago
    • Reported - view

    You’re welcome. Here’s the link for language reference...

     

    https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language

Login to reply

Content aside

  • 5 yrs agoLast active
  • 5Replies
  • 3368Views