I'll start by saying I think this should be simple but I've gone around in circles for a while and can't sort it out.

I have a table of event information which includes addresses so I would like to be able to choose the locale (city, town or suburb) and populate the state and postcode from a separate postcodes table. However there are some duplicate town names and a variety of suburbs with the same postcode. When I create a value list of locales, only the first shows and hence only the looked up values for that record will be populated.

I'm pretty sure a join table is what I need but I've tried to construct this a couple of times but can't get it. I'm convinced it  has an easy solution but it escapes me at the moment so any help is appreciated.

PostCodes.fmp12

I would suggest you consider choosing the locale from a card window or a portal. Then you can show all records, with all fields that are relevant to the selection.

With a value list you would need to select the name of the town first, then choose a state from a 2nd value list showing only related records (i.e. records with the selected name). Or construct a calculation field that combines the name and the state and use this as the second field for the value list - which would actually select the ID. This is assuming there are no two towns with the same name in the same state.

Added:
I just looked at your file again and I see that the above assumption is not true. So the question is how do you intend to select the correct postcode when the selected town, in the selected state, has more than one?

 

Edited March 27, 20223 yr by comment

postal codes are complex. Consider an address verification api, like https://www.smarty.com/products/apis/international-street-api 

Thanks for your advice. I will try to follow comments suggestion though in reality, I could achieve what I need by stripping down the Postcodes database to only include those close to home, which is where the majority of addresses to be used will occur. This feels a little like cheating though so I will try to do it properly.

Quote

Consider an address verification api, like https://www.smarty.com/products/apis/international-street-api 

It's just for a small personal project that I will use so I didn't want any expenses but I will keep it in mind.

Thanks again - I thought there was a simpler way of doing this.