Is it possible to link automatically (via a function) fields in two tables that contain the same text?

Ninox Profile

One more point, you can use the View Layout Element which will update dynamically if the data changes.

Ninox Profile

I figured out the the way I used unique() function was causing the problem. Here's the updated formula...

 

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

 

I don't know if/when I'll try to make the unique() function work in the formula. The bottom line is if there aren't any matching records in Table2 for a record in Table1 then...

 

1) There aren't any matching records in Table2

2) The record in Table1 is a duplicate record in Table1

Thank you very much , I will try it.

Ninox Profile

Hey Sean, Thank you for the detailed help here. I am trying to link records in many different way at our school. Tapping/clicking in some scenarios is slowing things down too much so I thought something like this would make it easier:

(1) a linked field

(2) a text field 

(3) a View field

When typing in the text field, it triggers a search that shows the results in the view field and when I hit enter it selects the first match of what was typed in the text field.

Would this work? Is there a better way to link records in a faster way?

Ninox Profile

Hi Halio, You've misunderstood the purpose of this thread. The linking is for users who have separate tables, from another app usually, that they want to connect via reference. It is intended to complete the task in one pass and not be used repeatedly for filtering records.

 

If I understand you correctly, you want to setup a search field to filter records? Something like this maybe...

 

https://ninoxdb.de/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/search-dashboard-formula-help-please.-5d5b3cc388bfaf262e9df40c?post=5d5d29ba88bfaf262e9df464&page=1

 

Ninox Profile

Hi Sean, since all conversations of this topic seem to point to this code, I'm going to post my difficulty here. I've tried with a few different sets of data, but for simplicities sake, I ended up making a classic 1:n test case and trying to make it work before I apply the code and button to larger sets of data with thousands of imported records. I made two tables: "City" and "State" which I then added a few pieces of sample data to. From what I understand, the code you are using above goes in a button on the parent field (the "1" field, which in this case would be "State"), and so I attempted to do that with the following code:

let myTable1Id := (select State).Id;
let myTable1Text := (select State).StateID;
let myTable1Cnt := count(myTable1Id);
for i in range(0, myTable1Cnt) do
for j in (select City)[StateID = item(myTable1Text, i)] do
j.(State := item(myTable1Id, i))
end
end

however, it gives me the following error: "field not found "State" at line 6 column 13" which makes sense, given that "State is the name of "Table1" from your code. However, the button is clearly asking for a field. So then I put in "StateID" which is the name of the field from Table 1 that I am trying to link. The result is the following code, which the button accepts:

let myTable1Id := (select State).Id;
let myTable1Text := (select State).StateID;
let myTable1Cnt := count(myTable1Id);
for i in range(0, myTable1Cnt) do
for j in (select City)[StateID = item(myTable1Text, i)] do
j.(StateID := item(myTable1Id, i))
end
end

Before I click the button, the tables look like this:

State Table

City Table

however, when I click the button, it does not link the tables together, instead it changes the data in the "city" table to a random set of letters and numbers:

City #2

Ninox Profile

Ben, I'm going to work so I'll have to look at it later tonight 

Ninox Profile

I meant to note that if I changed the last line to

j.(select State).(StateID := item(myTable1Id, i))

then it simply places the random numbers and letters in the StateID into the corresponding column in the State Table. So I'm not sure what the last line of code requires, but it appears to not be working smoothly

Ninox Profile

Thanks Sean, and no worries about the timing. Thanks for all your helpful ideas and suggestions

Ninox Profile

I created two tables the way you described and ran the first code you posted and they linked without a problem...

 

Screen Shot 2020-02-23 at 8.51.20 PM

 

I just copied and then pasted the following into the Ninox editor...

 

let myTable1Id := (select State).Id;
let myTable1Text := (select State).StateID;
let myTable1Cnt := count(myTable1Id);
for i in range(0, myTable1Cnt) do
for j in (select City)[StateID = item(myTable1Text, i)] do
j.(State := item(myTable1Id, i))
end
end

 

The "State" table has only one user-defined field, "StateID", and the "City" table has two user-defined fields, "StateID" and "City". All the fields I defined are Text fields except for the Reference...

 

Screen Shot 2020-02-23 at 8.58.17 PM

Screen Shot 2020-02-23 at 8.58.59 PM

Screen Shot 2020-02-23 at 8.59.37 PM

Screen Shot 2020-02-23 at 8.59.56 PM

Reply