VALID_IF DROPDOWNS FROM A LIST IN A EXTERNAL ...

[Andrea_Sala]

VALID_IF DROPDOWNS FROM A LIST IN A EXTERNAL SPREADSHEET

Hello! I have a list of value in an external spreadsheet saved on my Drive. It is a 5.000> rows spreadsheet and 100 columns.

With VALID_IF constrains it is possibile to limit the user input to a single value choosen from that external SPREADSHEET.

Do you think is going to be slow? Are there any workaround?

More info: The spreadsheet is created with an IMPORTHTML expression that grabs the data from the Italian National statistical Institute (ISTAT) and it is automatically updated when data changes… Fortunately, data changes rarely (only once or twice per year).

[Aleksi_Alkio]

Yes it’s possible to limit the list. The only question could be… how do you want to limit the data?

[Andrea_Sala]

Enum type (dropdown)

[Aleksi_Alkio]

No… I mean how do you need to filter the data with the formula? Is there only one condition to check or something else?

[Andrea_Sala]

Sorry…

Yes, if possible, my best scenario will be this one:

1. SPREADSHEET COL A = Italian Regions ( only 20 repeated) COL B = Italian provinces (only 80 repeated) COL C=

Italian cities (5.000> unique values)

2. USER User have to input 3 values: REGION, PROVINCE, CITY

3. FILTER Filter will be like:

1° If user inputs REGION = “Lombardy”, then filter only the province in Lombardy.

2° if user input PROVINCE = “Milan”, then filter only the cities in the Milan Area.

3° User choose only between more or less 200 unique values (instead of the whole 5.000>)

Thanks

[Aleksi_Alkio]

So… basically you are looking for a dependent dropdown. You can do that with the simple fomulas in Valid_If like TableName[Region], TableName[Province] and TableName[City].

[Andrea_Sala]

Yes, exactly! Thank you very much.

So it is not a problem if data are not in my app sheet source? Or I am obliged to add a new source (the external spreadsheet)?

[Andrea_Sala]

Sorry, I’ll try to be as clear as possible…

I am considering to set a validation mechanism on user imputed addresses (not mandatory, but nice to have), but at the same time I’m afraid of a worsening of performance in terms of speed …

I have 2 spreadsheet: n.1 = my actual data source in Appsheet n.2 = italian addresses

I can choose between 3 alternatives options:

1. Add in Appsheet a new table (Address) from a new source (Spreadsheet n.2)

2. ImportRange all the data from Spreasheet 2 into a new table in Spreadsheet 1 in order to manage only 1 unique data source inside Appsheet;

3. Create a VALID_IF with expression builder constrain using the url of SPREADSHEET 2. (is it actually possible? And how?). This solution should not require to add any new tables to Spreadsheet 1.

In terms of speed, which one you’ll suggest?

Huge thanks for your helpfulness.

[Aleksi_Alkio]

The data needs to be in your app. Otherwise you can’t read it.

[Aleksi_Alkio]

One solution is to use security filter. You could use usersettings for that purpose. User opens the usersettings and choose the region and province… then it will sync the app and now you have 200 addresses in your device.

When the user opens the app without usersettings, it won’t bring any data to the device.