0

Linking Related Fields between two tables

I need some help with setting up a script to automatically link two fields in my database.

Table 1 "FAs" has the text field "Room"

Table 2 "Survey" has the text field "Room"

 On the FAs table in the record view, I have a sub-table where I want to have all the linked records from "Survey" display, and I want to be able to link those records based on the shared value in their separate "Room" field.

 

What is the best way to go about this?

See the below file as I am sure I am not explaining it correctly, I have manually link a few of the items in FAs to Survey so you can see what I want to accomplish.

Test.ninox

3 replies

null
    • Fred
    • 7 mths ago
    • Reported - view

    I see you have this code in a button in the FAs table:

    let currentRoom := Room;
for survey in select Survey where Room = currentRoom do
    survey.FAs.(Room := this)
end

    Line 1, you create a variable called currentRoom and put the value of Room from the current record.

    Line 2, you start a for loop that selects the table Survey where Room equals variable currentRoom.

    Line 3, you tell Ninox to set the field Room in the reference field FAs for each record in Survey to equal the record Id of the current record.

    What I think you want to happen is to set the reference field FAs to be equal to the current record. Cause right now nothing happens because:

    1) you found all records in Survey where Room = currentRoom, but all of those records have nothing set in FAs

    2) which means nothing happens when trying to set the Room field.

    You can try the following instead:

    let t := this;
for survey in select Survey where Room = t.Room do
    survey.(FAs := t)
end

    The 'this' command works best when it is put in a variable, so that is what I did in line 1. Then in line 2, I used the t variable to access the Room data from the current record. Now on line 3, you can see I can set the FAs reference field to equal the current record using the t variable again.

      • Jason.3
      • 7 mths ago
      • Reported - view

        This code worked perfectly, is there a line of code I can add that will have it complete it for all the records versus going through each one and hitting the button?

      • Fred
      • 7 mths ago
      • Reported - view

      Do you need to do this on a regular basis or just once?

      If just once then you can use the Bulk Edit and put the same code in.

      If you do this regularly then maybe you there is a way you can modify your data entry so you don't need to do this regularly.

      To answer your question, you would add a second for loop function.

      it would look something like:

      for loop1 in select FAs do
    for survey in select Survey where Room = loop1.Room do
        survey.(FAs := loop1)
    end
end

      So we are replacing the t variable with the for loop function. So now we take each record in FAs, then we do another for loop where do what we before, but as you can see we change the reference to the first loop variable, loop1. This is why I like to use loop# as my loop variables as I can know which loop I'm in.

Login to reply

Content aside

  • Status Answered
  • 7 mths agoLast active
  • 3Replies
  • 117Views
  • 2 Following