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)
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))
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.
Thank you for your replies
I will try and post results
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 !
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))
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.