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

"Functions" do not manipulate data.   You can do this with a view.. or with a trigger. 

let myTable1 := Id;
let myText := Text;
for i in (select Table2)[Text = myText] do
i.(Table1 := myTable1)
end

The code in my previous post is for a record level link. If the field of the selected record in table 1 matches any of the records with the compared field in table 2, the records will be linked. I put the code in a button on table 1. The following code is for a table level link and I put the code in a button on table 1...

let myTable1Id := (select Table1).Id;
let myTable1Text := (select Table1).Text;
myTable1Text := unique(myTable1Text);
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

This will link all of the records in table 1 with matching records in table 2. Just modify the table and field names to match yours.

<a href="www.facebook.com">FB</a>

Hello, 

I need to link 2 existing tables : x records of table "SITES" (=offices, local adress etc.) with 1 record of table "ENTITIES" (=companies)

massively if possible, if not with a button

In my data tree model they're like SITES->ENTITIES

They both countain a field with the Company unique number (SIREN in France) : E.SIREN and S.SIREN

How can I do to create a link ? I tried the formula written above but my coding level is poor :/

PS : I manually linked some (a lot, indeed) so I need a formula with a chek like "only if this SITE record is linked to 0  ENTITIES record"

Thx a lot !

Pierre

I'm not sure what distinction you are making here... "massively if possible, if not with a button". The code you enter into the button formula is for a massive update, the button is just how you initiate it.

The formula was written for a one to many relationship and yours is many to one so you would put the button on ENTITIES and ENTITIES would replace "Table1"; SITES would replace "Table2" in the formula.

I didn't find it necessary to test for already linked records on my small dataset and testing might actually slow it down.

Thank you Sean. 

So I tried with 

let ENTITESId := (select ENTITES).Id;
let ENTITESText := (select ENTITES).'E.SIREN';
ENTITESText := unique(ENTITESText);
let ENTITESCnt := count(ENTITESId);
for i in range(0, ENTITESCnt) do
for j in (select SITES)['S.SIREN' = item(ENTITESText, i)] do
j.(ENTITES := item(ENTITESId, i))
end
end

but it said : "cette formule peut ne pas modifier les données" ...

Where did you put the formula? The button needs to be placed on the ENTITIES form and the formula goes in the button.

The error you got seems similar to one you would get from a formula field.

Wunderbarr !! Thanks ;)

You're welcome. I'm glad you got it working Pierre. :)

I would like to ask more than two tables, what should I do? thank you.

Have you tried linking the tables one at a time?

Excuses me, I don't understand this. If there is an image description or coding method, I am a newcomer to coding.

From the 4th post in this thread...

let myTable1Id := (select Table1).Id;
let myTable1Text := (select Table1).Text;
myTable1Text := unique(myTable1Text);
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

Without information like table names and field names I can't really do much more than give you the generic code above which you would attach to a button. Table1 is the "1" table and Table2 is the "N" table.

Additionally, you would replace the generic names Table1 and Table2 with your respective table names. You would also replace "Text" with field names you are using to link the tables.

Thank you , Sean.

let myTable1 := Id;
let myText := Text;
for i in (select Table2)[Text = myText] do
i.(Table1 := myTable1)
end

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

Excuse me, What is wrong with this code?(Can't connect the same text)

Just to be clear, the first code will only link the records in Table10 where the Text field matches the Text field in Table1 of the currently selected record in Table1.

I'm not sure why it's not working for you. I created a database with Table1 and Table10 and added records A01002F through A06002F and it worked using the code you posted. The only way I can duplicate the results you are getting is the record in Table10 was previously linked to the record in Table1. The code won't update the link if the data changes. You would have to unlink the record and run the code again.

If you have a lot of records that are linked, but don't match you can use this code in a button to unlink all the records...

for i in select Table10 do
i.(Table1 := 0)
end

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

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.

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?

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

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:

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: