0

Lookup records in another database?

I am planning two databases. I want to have a lookup field in the first database which displays records from a table in the second such that the user can select that record and the field is filled in with the external value. I would want only "active" records to be listed as lookup values and when they become "inactive" they would not be included in the lookup field's values. In other words I want the values to be dynamic.

I understand that there is no way this is possible using Ninox's features. How about using the API? Zapier? Has anyone tried something like this and been able to do it? If so, what was your experience? 

5 replies

null
    • Choices_Software_Dean
    • 4 yrs ago
    • Reported - view

    Per the docs, you could do a API GET with filter:

    GET https://api.ninoxdb.de/v1/teams/67mm9vc324bM7x/databases/nk5xt24oixj4/tables/A/records?filters={}

    or, if the record set is not too large, you could bring all the records over and set a filter on the "Active" column.

    • Mconneen
    • 4 yrs ago
    • Reported - view

    @Jen

    First.. Which platform are you working on?  Ninox Cloud?  Mac App?  iPad?    

    Why two databases?   There are use cases that it DOES make sense.. but I am just curious as most use cases can be solved using role based access to show / hide tables, fields, rows, etc..  

    Let us assume that there is a valid use case for the two separate databases....   Tell me more about the "look up field" in database 1.   

    Is this a synced logical key value from Database 2 that is in a table in Database 1? 

    Is this a simple text field in Database 1 that will be used as the filter? 

    How volital are the values in Database 2?  Meaning .. how often are you adding / removing / deactivating them? 

    The eventual solution is as @Westy suggests..  You can look up the value on another Ninox Cloud database using the Ninox API.  You have to understand a bit about the syntax.. but it is not that difficult.    One hint.. do NOT use the "ID" field for the rows in Database 2.  row IDs can actually change as a result of the sync process between devices... so it is a better practice to manage your own unique key value. 

    • Watch These Kids Bloom, Inc.
    • Jen
    • 4 yrs ago
    • Reported - view

    Thanks, @Mconneen!  I am using Ninox Cloud. Two databases because they are about two different things (kids at an orphanage vs. donors to the nonprofit that helps manage the orphanage) with the set of users being potentially pretty different. Creating 2 databases seemed easier than a complicated security system. The only link between the two is that I want to show the list of donors as a "pick list" in a drop-down (choice field) in the kids table under the field "sponsor". Where sometimes kids are sponsored by a donor. I only want to show "active" donors in the pick list. That is, donors where the yes/no field "inactive" is "no".  I don't know how volital the list of donors will be. I'd guess somewhat volital.  

    I'm new to Ninox, so the API thing sounds a little intimidating but I'm game to try it with the help of the API weekly webinar.  Any hints would be appreciated. For example, where do you put the syntax? (Newby question!)

    Thanks!

    • blackie
    • 4 yrs ago
    • Reported - view

    Another option to lookup records in another DB may be to "share" a veiw of the info you want to share. If you share JSON it is pretty easy to consume that data in another database.

    Screen Shot 2019-10-07 at 7.07.55 AM

    • Mconneen
    • 4 yrs ago
    • Reported - view

    @Jen, 

    I guess the question is... Do you want to add logic to the Readable / Show Only .. to determine if the user is an "Orphan DB" user vs a "Donor DB" user ... or do you want to add logic to push / pull data between two databases?   Assuming the later, to satisfy the requirement of 

                     "I want to show the list of donors as a "pick list" in a drop-down (choice field) ".. 

    I would add logic to the Donor database .... 

    1.  On create / update .. push the donor information into the Orphan database via a POST.

    see https://ninoxdb.de/en/manual/api/rest

    2.  Add a "button" to "delete" a Donor.. as there is no "on delete trigger".. and within this button... also send a DELETE request to the Orphan database. 

    Within the Orphan database..  add logic to link the donor to the orphan. 

Content aside

  • 4 yrs agoLast active
  • 5Replies
  • 3349Views