Yes, there is a very easy way you can do this in Five. Looking at what you are trying to do all you need to do is use the Dependent Field field on the Form Fields form.
To start with I will show you my database structure for this scenario:
- The Suburb table has a foreign relationship with the State table.
- The Location table has foreign relationships with the State and the Suburb tables.
Moving up to the form level, I have created three forms using these tables:
- States form
- Suburbs form
- Locations form
The Suburbs form becomes a page on the States form, hence, I can add a State and then add all the suburbs that belong to that state.
On my Locations form, I have the fields State and Suburb as you can see in the image below.
I have made the Suburb field dependent on the State field by selecting the StateKey field in the Dependent Field field as shown in the image below. This now makes it that the Suburb field will be dependent on the State field. This means, when a state is selected on the Locations form, only the suburbs that belong to that state will be shown.
Lets take a look in my app now. I can use the State form to add a new state and you can see I have a Suburbs page to add the suburbs for the state.
I am going to add three suburbs for the state of Adelaide.
Now we can use the Locations form to select a state. I’ll choose the state of Adelaide that I just added and you can see when I use the lookup in the Suburb field only the suburbs belonging to the state of Adelaide are show.
If I was to go back into Five and remove the Dependent Field value (StateKey) for the Suburb field as shown below, this would turn the filtering off and all suburbs would display in the lookup no matter which state was selected.
We’ll go into my app one last time just to show you that this time when I select Adelaide on the Locations form, I get all the suburbs for every state. There is no filtering applied as the dependency has not been set.
This has been done with foreign keys between the Suburb and the State tables, this is how Five works out how to filter the data automatically without code!
With the dependent field set, if someone selects a value in Field 1, in this scenario that would be they selected a state, and then they selected a suburb in Field 2, however, now they change their mind and select a different state in field 1. What will happen in this case, is the value that was selected in field 2 (Suburb) would be wiped from the field and when clicking the lookup in the field only the suburbs belonging to the newly state would be listed and the user would need to select a new suburb.