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

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

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

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

I created two tables the way you described and ran the first code you posted and they linked without a problem...

 

 

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

 

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:

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.

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.

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.

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. :)

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

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

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.

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.

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.

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

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.

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

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

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.

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

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

 

Best regards,

Sean

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.

sorry but what is do as server all about? 

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

 

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

Sean

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

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.

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