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

 

 

C

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

Ninox Profile

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

Ninox Profile

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.

Ninox Profile

Thank you for your replies

I will try and post results

N

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

Ninox Profile

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

Ninox Profile

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.

Ninox Profile

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" ...

Ninox Profile

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.

Reply