Selecting records based on two criteria

[garycopeland]

I'm trying to SUM 'Qty Ordered' values from one table where the 'Item' column = the 'Code' value from a different table and the Pending' column = "Y".

I have the following code that works for the first condition:
SUM(
  SELECT(
    Order History[Qty Ordered],
    [Item]= [_THISROW].[Code],
    )
  )

but when I try to add the second condition using an AND statement like this:

SUM(
  SELECT(
    Order History[Qty Ordered],
    AND(
      ([Item]= [_THISROW].[Code]),
      (Order History[Pending]="Y")
    )
  )
)

I get the following error:
Cannot compare List with Text in (Order History[Pending] = "Y")

I've tried a number of different solutions but I'm not getting anywhere.

Thanks, Gary

[Suvrutt_Gurjar · Accepted solution]

Please try 

SUM(
  SELECT(
    Order History[Qty Ordered],
    AND(
      ([Item]= [_THISROW].[Code]),
      ([Pending]="Y")
    )
  )
)

or please try the below  if [Pending] is Y/N type column

SUM(
  SELECT(
    Order History[Qty Ordered],
    AND(
      ([Item]= [_THISROW].[Code]),
      ([Pending])
    )
  )
)

However please be aware that such SELECT() or FILTER() expressions in multiple rows ( 100s or 1000s) can impact the app's sync time. Also if these expressions are in a real column those evaluate in a record only when the record is edited. In VCs those compute on each sync of the app.

If applicable, meaning if you are adding the sum just for display purpose,  you may want to instead consider the Gropu by and Sum/Count options offered by the view

Control grouping - AppSheet Help

[garycopeland]

Thanks, that works. I do only need it for display purposes so I'll explore the other options.