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

Ninox Profile

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.

Ninox Profile

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.

Ninox Profile

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

Ninox Profile

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

Ninox Profile

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

Ninox Profile

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.

Ninox Profile

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.

Ninox Profile

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.

Ninox Profile

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

Ninox Profile

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.

Reply