1

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

68 replies

null
    • conneenmichael
    • 5 yrs ago
    • Reported - view

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

    • Sean
    • 5 yrs ago
    • Reported - view

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

    • Sean
    • 5 yrs ago
    • Reported - view

    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.

    • Ioannis
    • 5 yrs ago
    • Reported - view

    Thank you for your replies

    I will try and post results

    • nickrobin
    • 5 yrs ago
    • Reported - view

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

    • Pierre_COLLE
    • 5 yrs ago
    • Reported - view

    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

    • Sean
    • 5 yrs ago
    • Reported - view

    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.

    • Pierre_COLLE
    • 5 yrs ago
    • Reported - view

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

    • Sean
    • 5 yrs ago
    • Reported - view

    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.

    • Pierre_COLLE
    • 5 yrs ago
    • Reported - view

    Wunderbarr !! Thanks ;)

    • Sean
    • 5 yrs ago
    • Reported - view

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

    • cyan_silver
    • 4 yrs ago
    • Reported - view

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

    • Sean
    • 4 yrs ago
    • Reported - view

    Have you tried linking the tables one at a time?

    • cyan_silver
    • 4 yrs ago
    • Reported - view

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

    • Sean
    • 4 yrs ago
    • Reported - view

    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.

    • Sean
    • 4 yrs ago
    • Reported - view

    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.

    • cyan_silver
    • 4 yrs ago
    • Reported - view

    Thank you , Sean.

    • cyan_silver
    • 4 yrs ago
    • Reported - view

    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)

    • Sean
    • 4 yrs ago
    • Reported - view

    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

    • Sean
    • 4 yrs ago
    • Reported - view

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

    • Sean
    • 4 yrs ago
    • Reported - view

    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

    • cyan_silver
    • 4 yrs ago
    • Reported - view

    Thank you very much , I will try it.

    • Halio
    • Halio
    • 4 yrs ago
    • Reported - view

    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?

    • Sean
    • 4 yrs ago
    • Reported - view

    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

    • Ben.2
    • 4 yrs ago
    • Reported - view

    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

Content aside

  • 1 Likes
  • 10 mths agoLast active
  • 68Replies
  • 15678Views
  • 7 Following