Conditional Dropdown - Multiple Values
I have a table named Organizations.
I have some conditional dropdowns for fields detailing where these organizations are based, using two other tables: Country and City.
However, our dataset is rather large, and some of the Organizations are present in multiple Countries or multiple Cities (or both).
Thus, as I understand conditional dropdowns cannot use multi-select fields, a traditional conditional dropdown does not quite suit my needs.
Of the solutions I can think of, all have downsides.
1) Instead of having Organizations as the child table in the dropdown relationships, I could use a join table instead, so that Organizations has a many-to-many relationship with Cities, and each organization can be listed with multiple countries and/or cities if needed.
Downside
I won't be able to sort and filter my Organizations table easily by city and country, as it will be the parent table of where the data is actually stored. Given the size of my dataset, easy filtering and analysis of the Countries and Cities involved is important for us.
Or is there a way around this?
2) I could create a second or third conditional dropdown, in order to add these second or third locations.
Downside
Also difficult to filter and analyse data, and fiddly to enter data.
3) I could create a 'Multiple Locations' value option in the Countries and/or Cities, and then have a new field in my Organizations table to detail this supplementary information.
Downside
One set of values would be divided among different fields.
Question:
Does anyone have any suggested alternatives to the options above?
Or solutions to the downsides identified?
Any advice enormously appreciated!
I have some conditional dropdowns for fields detailing where these organizations are based, using two other tables: Country and City.
However, our dataset is rather large, and some of the Organizations are present in multiple Countries or multiple Cities (or both).
Thus, as I understand conditional dropdowns cannot use multi-select fields, a traditional conditional dropdown does not quite suit my needs.
Of the solutions I can think of, all have downsides.
1) Instead of having Organizations as the child table in the dropdown relationships, I could use a join table instead, so that Organizations has a many-to-many relationship with Cities, and each organization can be listed with multiple countries and/or cities if needed.
Downside
I won't be able to sort and filter my Organizations table easily by city and country, as it will be the parent table of where the data is actually stored. Given the size of my dataset, easy filtering and analysis of the Countries and Cities involved is important for us.
Or is there a way around this?
2) I could create a second or third conditional dropdown, in order to add these second or third locations.
Downside
Also difficult to filter and analyse data, and fiddly to enter data.
3) I could create a 'Multiple Locations' value option in the Countries and/or Cities, and then have a new field in my Organizations table to detail this supplementary information.
Downside
One set of values would be divided among different fields.
Question:
Does anyone have any suggested alternatives to the options above?
Or solutions to the downsides identified?
Any advice enormously appreciated!