Tables sync
Hello everyone, I have the following problem:
I have a Table A with X and Y fields and a Table B with links of X and Y fields.
If I choose a value on the link X on Table B then the link to Y field will return the value from field Y of Table A. The problem is that if I change the value form Y field on Table A, the link to Y field on Table B WILL NOT change. What options do I have for this synchronisation?????
24 replies
-
I can’t tell if your “links of X and Y” are regular fields or reference fields.
Ninox does linking of records differently than other DB apps. It links at the record level.
So in Table B you would create a reference field to Table A. You can all this reference field X if you want. In the show field part of the reference field you can put field X in it so it will show the actual value of field X from Table A.
Then create a formula field and put this in the formula (assuming you renamed your reference field to “X”):
X.YThen this will show the value of Y from Table A.
-
OK I got that, but after all this procedure, will the reference to value Y be change, after I change the actual value from Y field on Table A???? Let me tell you exactly what this is:
Table A has names (field X) and dates (field Y). So is Table B (but with more input and more fields). So far I have managed to make the reference and whenever I put in a name in Table B (reference to X), the link to Y field is auto filled with the value from Y field of Table A.
After that, I will have to change some values on field Y (dates) in Table A. How will I change the links to field Y on table B automatically???
-
pirinisz said:
After that, I will have to change some values on field Y (dates) in Table A. How will I change the links to field Y on table B automatically???If you set it up, what was your experience?
If you set it up like I recommended then when you make changes to field Y in Table A it will show up in Table B. Since field Y in Table B is just a formula field showing you the value of field Y in Table A.
I would recommend to not duplicate data in your DBs as much as possible. Making links and using formula fields is the best way to keep data in sync and makes data entry easier for you.
-
Yes it's done! But I noticed that I have to close and reopen NINOX in order to synchronise, right? Thank you very much Fred !
-
But only for the new records... damn, I have to start all over again!!!!
I'll try it step by step using your method!!
-
pirinisz said:
But I noticed that I have to close and reopen NINOX in order to synchronise, right?Which version are you using, cloud or MacOS app?
-
MacOS app!!
Could you please explain to me with more details the following, according to the values I gave you?
"So in Table B you would create a reference field to Table A. You can all this reference field X if you want. In the show field part of the reference field you can put field X in it so it will show the actual value of field X from Table A."
-
There is one more thing I have to tell you: In the date field of Table A, I added in the "trigger after update" the following code: if today() > 'date' then
'date' := date(year(today()) + 1, month('date'), day('date'))
end -
So as you realise the values of the Date field are auto changed every day, so I need their references to table B be changed accordingly and automatically!!
-
pirinisz said:
Could you please explain to me with more details the following, according to the values I gave you?In Table B, open Edit Fields.
On the right side of the window is a section called Table Reference. Click on that, then drag Table A into the Fields column. You can rename it or leave it Table A.
Then click on the reference field you just created, then click on More Options. Click in the blank space under Show data as. Then you can add the Name field.
-
pirinisz said:
I added in the "trigger after update" the following code: if today() > 'date' then 'date' := date(year(today()) + 1, month('date'), day('date')) endNinox has pretty nice date data handler properties. You can rewrite the above code to:
if today() > 'date' then 'date' := 'date' + 365 endYou can also do the following:
if today() > 'date' then 'date' := date(year(today()), month('date') + 12, day('date')) endNinox will move the date field up 1 year as well. Or change it to 24 and it will jump two years.
-
Here's an example: On table A I have the following record
Name: Fred
Date: 22/11/2022
On Table B I have 2 references (one for name and one for date). Using this:
"There is one more thing I have to tell you: In the date field of Table A, I added in the "trigger after update" the following code: if today() > 'date' then
'date' := date(year(today()) + 1, month('date'), day('date'))
end"I managed to insert on table B the name "Fred" (reference field) and it will auto fill the date field (reference field) with 22/11/2022.
Tomorrow, on Table A the date field will change to 22/11/2023, but the reference to Table B will stay the same, 22/11/2022!!! How can I change that???
-
pirinisz said:
OK but I don't get to see the date field this way....I thought you were going to use a formula field?
Another way is, you can add the date to the Show data as formula:
Name + " - " + 'date'Now you don't need another field.
-
pirinisz said:
On Table B I have 2 references (one for name and one for date)Are your two reference fields pointing to Table A?
-
Sorry man, the date field is actually a simple date field, not a reference.
-
pirinisz said:
Sorry man, the date field is actually a simple date field, not a reference.Refer back to my original reply about creating a formula field as your Date field for Table B.
-
pirinisz said:
Yes but in a formula there is no grading by dateI have no idea what "no grading" by date means?
Since the date field in Table A is a date field. Ninox will treat the data as date data in Table B and you will be able to do normal date functions on it. As well as add it to your table view and be able to sort by date, etc.
-
IT WORKED!!! THE DATE IS ACTUALLY SYNCED IN ALL RECORDS!!! THANK YOU SO MUCH!!!
Content aside
- Status Answered
-
1
Likes
- 2 yrs agoLast active
- 24Replies
- 119Views
-
2
Following