How can I filter out "Yes" in another table as result

[LawrenceMa]

Hello, 

I am writing a formula as below which is working perfectly:

ANY(SELECT(DataTable[BoothNo], ([_RowNumber]=MAX(SELECT(DataTable[_ROWNUMBER]
,[_THISROW].[VendorNo] = [VendorNo])))))

However, I want to rewrite it to get DataTable[BoothNo] with lastest row and with same [VendorNo] and without "Yes" in DataTable[BoothCancelled] as below:

ANY(SELECT(DataTable[BoothNo], ([_RowNumber]=MAX(SELECT(DataTable[_ROWNUMBER]
and(DataTable[BoothCancelled]!="Yes"
,[_THISROW].[VendorNo] = [VendorNo]))))))

I got warning "Unable to find function 'DATATABLE[_ROWNUMBER] AND'" from above formula.

How can I rewrite above formula?  Thanks

 @Steve @WillowMobileSys @SkrOYC @Suvrutt_Gurjar @dbaum 

[WillowMobileSys · Accepted solution]

First,  we have recently discovered some inconsistencies in how the "Yes" identifier is used in Yes/No columns.  If [BoothCancelled] is indeed a Yes/No column then I recommend replacing "Yes" with TRUE - these are interchangeable or at least expected to be interchangeable.

For NOT EQUAL you will want to use the "<>" operator instead of "!=".  If you replace that also then the expression should be fine.

[Luis_Rodriguez_]

---

@LawrenceMa wrote:

ANY(SELECT(DataTable[BoothNo], ([_RowNumber]=MAX(SELECT(DataTable[_ROWNUMBER] and(DataTable[BoothCancelled]!="Yes" ,[_THISROW].[VendorNo] = [VendorNo]))))))

---

I think you miss a coma "," before 'and' beside what  @WillowMobileSys  had expose. Appsheet is reading 'DATATABLE[_ROWNUMBER] AND' as a function.

[WillowMobileSys]

Yes, absolutely!  Good catch!

[Steve]

Expressions: The Essentials - AppSheet Help