Re: Need reference to filter by country

Scott_Nesbitt · 12-07-2021 10:13 AM

I am using a reference to an exchange rate table to select a country. Then I would like to use that selected country as a key into a city table, where I let the user select from cities in the country they selected. I am having a bit of trouble doing creating the filter on the country. Any suggestions?

Thanks

SkrOYC

Could you elaborate?
I didn’t get if you want that a certain field (city) depend on another one (country) or if you need to use country on two different things.

Scott_Nesbitt

Yes, I want to limit the cities available to choose based on the country they select. The countries in both the exchange and city tables match. The exchange table is just a list of countries and the exchange rate, the city table is a 1-to-many table as some countries have multiple applicable cities.

Thanks

SkrOYC

So, the link I posted above should help
In general, Valif_If or Suggested_Values with a FILTER() or List Dereference should be enough
https://help.appsheet.com/en/articles/4575708-list-dereference

Scott_Nesbitt

Hmm, I added a Valid If statement like this, and it did nothing:

EDIT: I switched the Valid If to this and it did limit it to the correct country, but it is not letting me select the city in the country:

IN( [_THIS], SELECT(Labor Data[Country], [_THISROW].[Country] = [Country]))

SkrOYC

Your column type is Ref but you are listing all the values from the “Labor Data” table and [Country] column

SkrOYC

Also, [City] is a key column?

SkrOYC

SELECT(Labor Data[City], [_THISROW].[Country] = [Country])

I guess the city column is called “City” on your Labor Data table

Scott_Nesbitt

That did it! I did not understand the logic before but now it makes perfect sense.

Thanks so much.

SkrOYC

For the record.
SELECT() takes a list of the type of data that is after the table name; in this case [City]
The list is filtered by the second argument where the rows from Labor Data should have the same [Country] as [_THISROW].[Country]
https://help.appsheet.com/en/articles/2357314-select