Suggested Values SELECT() showing too many rows

[Wally_Young]

I have a form where I'm wanting to show a list of available vendors based on an expense type selected. The form is associated with an Expenses table, in the table there are multiple rows with expenses, and the Expenses table includes the Expense Type (Enum, base type Text) and Vendor (Enum, base type Text). When the user selects the Expense Type on the form, I'm wanting the list of available vendors to be displayed for the Vendors to show all vendors associated with the Expense Type selected, but also allow a new vendor to be added if it's not in the list. My Suggested Values has the following:

SELECT(Expenses[Vendor], TEXT([Expense Type]) = TEXT([_THISROW].[Expense Type]),TRUE)

This returns the vendors associated with the selected Expense Type on the form, but after it displays the associated vendors, it then shows all vendors in the Expense table.

When I include the following in the Valid If, it works the way I'm expecting but it's not allowing the user to add a new vendor:

IN( [_THIS], SELECT(Expenses[Vendor], [_THISROW].[Expense Type] = [Expense Type]))

What am I missing or doing wrong? Thanks in advance!

[MultiTech · Accepted solution]

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

#ReferencesAreEssential

• Create a table for Expense Types
• In your Vendors table, change the [Expense Type] column to a REF

On your Expense Types table (when looking at an Expense Type record in your app) you'll now see all the [Related Vendors] for that Expense Type.

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

[Expense Type].[Related Vendors]

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

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

[Wally_Young]

Thanks! Always forgetting to do this (original code/columns were all Enum with hard coded values, and already had an Expense Types table just neglected to use it properly).

FYI I don’t currently have a Vendors table, all the vendors are currently stored in the Expenses table. Are you recommending to also create a Vendors table as well as the Expense Types table?

To clarify the Suggested Values, when I try entering [Expense Type].[Related Vendors], it's giving me an error "Column Name 'Vendor' in Schema 'Expenses_Schema' of Column Type 'Enum' has invalid 'Suggested Values' of '=[Expense Type].[Related Vendors]) '. The type of the Suggested Values does not match the column type.

I've changed the Expenses.Expense Type column to a Ref that references [Expense Types] table. And confirmed in the Expense Types table there is a virtual column [Related Vendors] that is a List type with formula REF_ROWS("Expenses", "Vendor").

[MultiTech]

Yes make a separate vendors table, and reference connect your vendor column
in the other tables to that one.

When you set it up like that that's when you'll get the reverse reference
on your vendor table of everything else, and then when you're on one of
those other tables you can use the reference to get those lists on the
vendor table.

🤪 (A little crazy with the back and forth there, lol)

Best
Matt

[SkrOYC]

Just curious @Wally_Young 

Why are you using Text() there?
TEXT() - AppSheet Help

[Wally_Young]

I think it was desperation trying to get it to work, thinking it was returning a reference or a list so I was trying to ensure it was comparing text values… I see it’s not needed at this point…