SELECT these records when Table A[LIST ] = Table B[LIST]

[tomdengle]

I'm trying to match companies based on countries in which they operate. If Company A operates in "Australia, Germany", I'd like the list of other (multiple) companies that also operate in "Australia, Germany".

A select statement matching single values SELECT(Customers[Customer],([Industry] = [_THISROW].[Industry])) - easy.

I cannot get a Table A[Virtual List] = [_THISROW].[Virtual List] to work. 

Likewise, I cannot store the list (as text) and get it to work. LIST(Table A[TEXT]) = [_THISROW].[Virtual List]. 

I'm sure I've tried [LIST as TEXT] = [LIST as TEXT] and also failed. What am I missing?

[MultiTech]

References my friend... too many people aren't doing references in their apps.

#ReferencesAreEssential

• Create a table for countries
• In your Companies table, change the Company column to a REF (to the Countries table)

On your Countries table (when looking at a country record in your app) you'll now see all the [Related Companies] for that country.

Anytime you need that list of companies (for say a dropdown) you can simply dereference that list.

[Country_Link].[Related Companies]

https://support.google.com/appsheet/answer/10106510?hl=en

https://support.google.com/appsheet/answer/10107396?hl=en

[MultiTech]

Same can be said for your "Industry" as well:

Create an industry table, and then you'll see a [Related Companies] for each industry record.  😉

[tomdengle]

Thank you Multitech! I also struggle with writing posts like this...the "companies" sit in two tables, so I'm looking for:

SELECT these records when Prospects[LIST ] = Customers[LIST]. So if prospect is in "Australia, Germany", I'd like the list of customers in "Australia, Germany". 

I am using REF_ROWS elsewhere; not sure it's a fit here...