0

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

53replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

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

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

    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

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

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

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    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

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

    When did the reference fields appear in your tables? Are they the result of your code? Because I am dealing with a situation where I have already set up a few tables and made some references between them and other tables elsewhere in the database, but then discovered that I want to create a new connection between two tables for a field that is currently just a text field in each. Is that the case when you start, and then your code creates the reference between the two? This is my starting point:

    City Original

    State Original

    In any case, when I try to run the code exactly as you have it above, I get the "Field not found: State at line 6, column 11" message for the last line of code.

    Console

    If it is the case that you start at exactly the same point as me, and use the same code, and get it to work, I'm definitely stumped. I'm running the Mac Desktop version of Ninox, synced through iCloud if that makes any difference.

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

    So, I went in and created a new field in the State table, which is the "relationship" of "from City." I then tried your code, and it ran successfully, filling the new relationship field with the data from the StateID field. I think I misunderstood the steps to make this all happen, and what was going on in the background. This is what I did:

    Starting point: two tables exist (full of data) with fields that are not linked but contain data to link.

    1) create a new "to parent" relationship field in the child table

    2) set the "show as" in the relationship field to fieldname from the parent table

    3) create a button in the parent table with a script:

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

    4) run the code

    5) In the Child table, delete the original non-linked field, which now contains a duplicate set of data to the new linked column.

     

    The only circumstances where this doesn't seem like it works well, are if for some reason, you have multiple tables that need to link the same field.

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    You beat me to it. The code doesn't create the Reference field, it creates the link and, as you figured out, you add the Reference field from "Edit fields...".

     

    I haven't tried linking multiple tables to the same field, but you can certainly try it. Just update the code accordingly. :)

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

    Thanks again for your help. Ninox is really well designed, but there is definitely a learning curve. Its way more power than I initially thought, but it has some differences from other databases and spreadsheet/table systems that have caught me off guard. I feel like writing a "newbie" cheat sheet where I put all my initial errors in one place so the next person in line can skip a few steps

    Like
    • Mconneen
    • Mconneen
    • 2 yrs ago
    • Reported - view

    @Ben.. While not all "relational theory" goes out the window.. You do have to rethink some approaches based on the platform. 

     

    Good luck.. and Happy Ninox-ing. 

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

    True! I find Ninox extremely pleasant to use, and there is a great deal of overlap with other models, but the idea of creating the relationship itself as a field is one that is important to wrap one's head around before designing the database in Ninox. I have to confess that for the past few days I have been bumping up against different versions of what is kind of the same problem: I want to make the fields as a certain type in both tables, THEN define the relationship between them. Now (I hope) I have understood finally that you create the data field in one table, then every other table links to that without creating an equivalent "text" or "number" field first.

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

    update to my "5 steps" above... the code does not appear to work if you create the relationship from the child table. I just re-ran my test and it gave me the same "can't find field" in line 6 of the code. Having clearly succeeded once, I started to wonder which step I got wrong. On reflection, I am pretty certain I innitiated the relationship field via the parent table, and to test this, I deleted my relationship field created from the child table, and created one from the parent table, without changing my text at all. When I put the code in to the button, there was no error, and when I clicked, it made the connection perfectly. I can't imagine why that is the case, but I figured I'd just put my bug out there for the next person.

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

    And, just to be explicit now about the steps I did to make it work:

    Starting point: two tables exist (full of data) with fields that are not linked but contain data to link.

    1) create a new "to child" relationship field in the parent table

    2) create a button in the parent table with a script:

    let parentId := (select parent).Id;
    let parentField := (select parent).field;
    let parentCt := count(parentId);
    for i in range(0, parentCt) do
    for j in (select child)[field = item(parentField, i)] do
    j.(parent := item(parentId, i))
    end
    end

    3) run the code

    4) [optional] In the Child table, delete the original non-linked field, which now contains a duplicate set of data to the new linked column.

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    Here is another version that will link two tables (1:N). Note, this only works in the Mac app and, I assume, the iPad app. I have tried it in the browser version and it does not work. This version will link records with null or empty fields.

     

    let myTable1 := (select Table1);
    let i := 0;
    for r in select Table1 do
    for j in select Table2 where Text = item(myTable1, i).Text do
    j.(Table1 := item(myTable1, i).number(Id))
    end;
    i := i + 1
    end

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    This was one of those facepalm realizations...

     

    for oneRec in select OneTable do
    for manyRec in select ManyTable where Text = oneRec.Text do
    manyRec.(OneTable := oneRec.Id)
    end
    end

     

    It works in both the browser and Mac app versions. In the line...

     

    manyRec.(OneTable := oneRec.Id)

     

    OneTable is the Reference in ManyTable.

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    @Ben, I didn't have a problem creating a Reference from the "child" or "many" table. I did find that if I created the Reference from the "parent" or "one" table it would reverse the (1 : N) and that's not how you want it to work. If you have multiple "child" table that you want to link to the "parent" table you can modify the code to do that.

     

    for oneRec in select OneTable do
    for manyRec1 in select ManyTable1 where Text = oneRec.Text do
    manyRec1.(OneTableRef := oneRec.Id)
    end;
    for manyRec2 in select ManyTable2 where Text = oneRec.Text do
    manyRec2.(OneTableRef := oneRec.Id)
    end
    end

     

    Hope that helps.

    Like
  • Hi Sean

    Like otherd above I need to do this and have tried various examples from the above but am also getting the same field not found error.

    I’m using the cloud version and dearly would like to get my imported table linked as have over 7000 records to link.

    Would you mind clarifying from the above what script was successful.

    As one of the options gave m no error and run but did nothing !

    So to be clear..

    My Parent table is called ‘Customers’ (the ONE Table)

    My Child table (import) is called ‘OldDocs’ ( the many Table)

    ‘Olddocs’ (ONE) is related to main table ‘Customers’ (Many)

    and both tables have a ‘CustID’ field (text field) and all records are populated

    ie ‘customers’ record has CustID (eg “MEL501”)

    ‘OldDocs’ has many forms that have a unique sequence number job1 , job 2 etc but also have common field CustID with “MEL501”)

    So there could be say 35 Olddoc that need to link to 1 main customer form/table

     

    I’ve tried it with the script on the main and or child table and in buttons but alsa geeting soemthing wrong

    If you could give me a definitive script fro cloud version that will do the job and confirm where to put the script

    that would be a great help.

    Kind regards

     

    Mel

    Like
    • Sean
    • Sean
    • 1 yr ago
    • Reported - view

    Hi Mel, Here is the code I would use...

     

    for oneRec in select OneTable do
    for manyRec in select ManyTable where Text = oneRec.Text do
    manyRec.(OneTableReference := oneRec.Id)
    end
    end

     

    There is a LinkTables database in the Webinar EN 2020 team where you can see how it works. The code goes in a button. Also, you might need to put it inside do as server.

    Like
  • Hi Sean

    Okay thats vrillinat will give this a go

    After I“ve finished wtahcing the Trump/Biden election affair (Even us Brits do look at whats going on :-)

    Kind regards

    Mel

    Like
    • Sean
    • Sean
    • 1 yr ago
    • Reported - view

    I“m afraid our politics have lost its entertainment value for me. 😏

     

    Best regards,

    Sean

    Like
  • Appologies to all for the terrible typing !!!!

    Had a massive motorbike accident recently and whilst still on the mend am having to type with just one finger and using the wrong hand !

    Mel.

    Like
  • sorry but what is do as server all about? 

    Like
    • Sean
    • Sean
    • 1 yr ago
    • Reported - view

    You can find information about do as server here...

     

    https://ninox.com/en/manual/api/http-calls

    Like
  • Sean

    i think the links as well as piccture uploading are now broken

    Like
    • Sean
    • Sean
    • 1 yr ago
    • Reported - view

    Mel, yeah, it“s definitely looking like amateur hour here at Ninox. I guess they”re incapable of being motivated by either pride or embarrassment.

    Like
  • I wanted the luxury of doing this on custmer by customer basis

    so that i could see the records being linked before moving onto next customer

    so Button sits on main form “Customers” - ie the one in the link!

    and the many table to link is “OldRecords”

    The relation ship link is done before running the button.

    CustID is the field in both forms that has the common data ie IAN501 IAN502 etc

    script in button

    let myTable1 := Id;
    let myText := Custid;
    for i in (select “OldRecords”)[CustID = myText] do
    i.(Customers := myTable1)
    end

    Very grateful for Seans excellent help with this !

    also Sean gave other useful examples how to do this in one pass for all records !!!

    the links don“t appear to be working on forum at th emoment but more info can be had from here

    https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/is-it-possible-to-link-automatically-via-a-function-fields-in-two-tables-that-contain-the-same-text-5c6aaf80e850ad238982b496

     

    Mel

    Like
Like Follow
  • 3 mths agoLast active
  • 53Replies
  • 14664Views
  • 2 Following