0

Dynamic choice field to autofill text field with values from unrelated table

Hi all,

 

I’ve searched the forum but haven’t found any specific thread so I start this new one.

I always used FileMaker Pro (never been a pro user, anyway) and just moved to Ninox so bear with me  and let’s see if I can explain clearly enough my needs.

 

I have two unrelated tables;

PEOPLE (Id, FirstName, Last Name, Municipality, Province, Zipcode)

MUNICIPALITIES (Id, Municipality, Province, Zipcode)

(In both tables there are more fields, but others are unimportant for the result I aim to).

MUNICIPALITIES is a table static in time (no need at all for updating the records or adding new records), so it’s just a support table to get data from. For that reason I don’t really need to build a relationship to it, unless creating a relationship with PEOPLE is the only way to solve my problem.

 

I’ve set people.municipality to be a combobox dynamic choice field, showing the values from municipalities.municipality.

What I want is that, once I’ve made my choice in people.municipality, the fields people.province and people.zipcode get autofilled with the correspondent values from municipalities table. I made it work using the following code in a formula field for municipality.province:

 

let x := text(municipality);

(select municipalities where municipality = x).province

 

While this may be ok for province (not ideal though), I need something different for zipcode, because once the field is filled, I might have the need to change the value and that is not possible if I use a formula field.

It would be perfect to use a text field for people.zipcode and fill it using a trigger on update in people.municipality field, but I don’t have a clue about what formula to use in there.

Also, it would be even better if both province and zipcode fields could be filled by the same trigger in municipality.

Thanks for any suggestion!

4 replies

null
    • simon
    • 2 yrs ago
    • Reported - view

    Hi Gianluca – welcome to Ninox!

    As I see it you would probably be better with something like this:

    PEOPLE (FirstName, Last Name, Zipcode)
    MUNICIPALITY (Municipality, Province)

    The PEOPLE table will have a link to MUNICIPALITY from where the value can be selected (or added if is doesn't exist). The Zipcode is added separately to each record in PEOPLE as a text file – as you pointed out, these are different for each person. If you wanted to call the Zipcode from a linked table, make a separate one for ZIPCODE. Difficult to know without knowing exactly how Zipcodes work for you. 

    You could achieve this with a version using a dynamic choice field, but there are advantages to linking the tables.

    Hope this helps!

    Simon

    • Gianluca
    • 2 yrs ago
    • Reported - view

    Thank you Simon,

    I'll give it a try the way you suggested and see if it works for me. I'll post later the result.

    • Gianluca
    • 2 yrs ago
    • Answer
    • Reported - view

    simon  tried your way and it worked (of course). Thank you!

    I also got a reply from support and they suggested me a way to do it with unrelated tables, so i leave it here in case someone else finds it useful (to use as trigger on update in municipality field)

    let t := number(Municipality);

    Zipcode := first((select Municipalities)[ID = t].Zipcode);

    Province := first((select Municipalities)[ID = t].Province)

    • simon
    • 2 yrs ago
    • Reported - view

    Good to hear this – glad you're getting there. In some ways it's a matter of personal preference which way you achieve this, see what works best for you. However, in terms of good database practice I like to avoid recreating the same data in multiple places. Consider above if you discover, for example, a spelling mistake, or make another change to one of the Province fields,  in the linked table method this would automatically be reflected. In the trigger on update method, it wouldn't. Best wishes.

Login to reply

Content aside

  • 2 yrs agoLast active
  • 4Replies
  • 337Views
  • 2 Following