Expression Help

[Jonathan_S]

Hey Guys,

Little lost,

Im trying to build an inventory App.

Contains the following components.

1. Vendors.
2. Catagories.
3. Items.

I have a form regarding the inventory and As the user fills out the form, I only want the items to show up in the enum based on the Vendor and Catagorie they selected.

This is the formula I am using in the Valid If field of my Item field.

SELECT(Type[Item Name],  
And(Type[Catagory].[Catagory] = [_THISROW].[Catagory],
Type[Vendor].[Vendor] =[_THISROW].[Vendor]), FALSE)

It currently returns Blank. If I don’t have this expression it Contains all types from all vendors and categories.

[WillowMobileSys · Accepted solution]

Jonathan_S:

SELECT(Type[Item Name], And(Type[Catagory].[Catagory] = [_THISROW].[Catagory], Type[Vendor].[Vendor] =[_THISROW].[Vendor]), FALSE)

You are close! The expression is actually simpler:

SELECT(Type[Item Name],  
       And( [Catagory] = [_THISROW].[Catagory], 
            [Vendor] = [_THISROW].[Vendor]))

The SELECT automatically knows context so knows that Catagory and Vendor are in the Type table. [_THISROW] is only needed because the columns in the current table have the same name as what was used in the Type table (perfectly ok). But had your row columns been different names [_THISROW] could have been removed.

[Jonathan_S]

Wow,

Working perfectly.

I am getting Triangles beside each option in the list for all options. What could this mean?

See below

[WillowMobileSys]

In your SELECT you should actually use the KEY column from your Type table. Then in the AppSheet table editor set the “Item Name” column as the Label. AppSheet will record the Key in the data but use the Label for display.

[Jonathan_S]

Wow,

Ok, So can you explain your thinking behind this? The triangles are gone but why?

[WillowMobileSys]

The Valid_IF is actually determining if the values in the list returned are valid values from the table used in the SELECT(). AppSheet does this in a Ref column by using the Key column in the referenced table. When you specify another column in the SELECT(), none of those values are keys so AppSheet doesn’t find a match and marks them as INVALID.

[Bahbus]

In even simpler terms… Since the column is marked Ref pointing to another table, AppSheet expects the column to have Key values from the other table, so as to successfully “link” them. No Key values means no “link”. No “link” when AppSheet expects one means yellow triangle of doom.

[Bellave_Jayaram]

Delete these prefixes :
Type[Catagory]. and Type[Vendor].