1

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

null
    • Fred
    • 1 yr ago
    • Reported - view

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

    Then this will show the value of Y from Table A.

    • pirinisz
    • 1 yr ago
    • Reported - view

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

    • Fred
    • 1 yr ago
    • Reported - view
    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.

    • pirinisz
    • 1 yr ago
    • Reported - view

    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 !

    • pirinisz
    • 1 yr ago
    • Reported - view

    But only for the new records... damn, I have to start all over again!!!!

    I'll try it step by step using your method!!

    • Fred
    • 1 yr ago
    • Reported - view
    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?

    • pirinisz
    • 1 yr ago
    • Reported - view

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

    • pirinisz
    • 1 yr ago
    • Reported - view

    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

    • pirinisz
    • 1 yr ago
    • Reported - view

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

    • Fred
    • 1 yr ago
    • Reported - view
    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
      • 1 yr ago
      • Reported - view

       OK but I don't get to see the date field this way....

    • Fred
    • 1 yr ago
    • Reported - view
    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')) end

     Ninox has pretty nice date data handler properties. You can rewrite the above code to:

    if today() > 'date' then
    'date' := 'date' + 365
    end
    

    You can also do the following:

    if today() > 'date' then
    'date' := date(year(today()), month('date') + 12, day('date'))
    end
    

    Ninox will move the date field up 1 year as well. Or change it to 24 and it will jump two years.

    • pirinisz
    • 1 yr ago
    • Reported - view

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

    • Fred
    • 1 yr ago
    • Reported - view
    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
      • 1 yr ago
      • Reported - view

      Fred This one won't work for me because I need to have all those records in ascending date order

    • Fred
    • 1 yr ago
    • Reported - view
    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?

      • pirinisz
      • 1 yr ago
      • Reported - view

      Fred NOOOOO

    • pirinisz
    • 1 yr ago
    • Reported - view

    Sorry man, the date field is actually a simple date field, not a reference.

    • Fred
    • 1 yr ago
    • Reported - view
    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
      • 1 yr ago
      • Reported - view

      Fred Yes but in a formula there is no grading by date

    • Fred
    • 1 yr ago
    • Reported - view
    pirinisz said:
    Yes but in a formula there is no grading by date

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

      • pirinisz
      • 1 yr ago
      • Reported - view

      Fred yeah "no grading" means "sort by". Ok I'll give it a try and let you know! Thank you so much for all your time and interest.

    • pirinisz
    • 1 yr ago
    • Reported - view

     IT WORKED!!! THE DATE IS ACTUALLY SYNCED IN ALL RECORDS!!! THANK YOU SO MUCH!!!

      • Fred
      • 1 yr ago
      • Reported - view

      great to hear. You are on your way to master Ninox!

      Just remember to mark the post "answered".

Content aside

  • Status Answered
  • 1 Likes
  • 1 yr agoLast active
  • 24Replies
  • 94Views
  • 2 Following