Read data in external table based on email in current table

[aminsaleh]

Hello there

I have a table called  (Stats Ca)

(stats ca) contains coordinator email and seller email as seen below:

one of the column of (stats ca) table is called (Return Qty) in red arrow above, and it reads the return qty number from another table called (Order Details)

Here is the expression:

"🔙Return: "&SUM(SELECT(Order Details[Return Qty],
AND([Status]<>"Processing",[Status]<>"Canceled/NA",ISNOTBLANK([Status]),ISNOTBLANK([Accountant]))))

The question is: How can I edit above expression to only get the return qty for specific seller email and/or coordinator email. 

Note: same seller and coordinator email are available in both tables. (Stats Ca) and (Order Details)

Thanks indeed

[dbaum]

SUM(SELECT(Order Details[Return Qty],
AND([Status]<>"Processing",[Status]<>"Canceled/NA",ISNOTBLANK([Status]),ISNOTBLANK([Accountant]), [Email] = [_THISROW].[Email])))

[aminsaleh]

Hi @dbaum  

I've just tried it but hasn't worked ... maybe I have ambiguity when descried the issue.

If you do not mind I sent (PM) you my app editor link to have a close look.  

First table: Sats ca

coloumn: Return Qty

outer table: Order Details

[aminsaleh]

Hi @dbaum 

I've solved the main issue and now I am able to pull exact number of returns for specific email.

but the new problem is when I I select "All" means all sellers emails, it shows zero return while It should shows total returns for all emails.

►Expression

CONCATENATE("🔙Returns: "&SUM(SELECT(Order Details[Return Qty], AND([Status]<>"Processing",[Status]<>"Canceled/NA",ISNOTBLANK([Status]),ISNOTBLANK([Accountant]),[Seller]=[_THISROW].[Seller]))))

►Explanation of the expression from Appsheet:

Note: This expression could impact performance. The concatenation of all these text values ( ....The concatenation of all these text values ( ........"🔙Returns: " ........SUM( ................The list of values of column 'Return Qty' ................from rows of table 'Order Details' ................where this condition is true: (ALL these statements are true: ....................1: (The value of column 'Status') is not equal to ("Processing") ....................2: (The value of column 'Status') is not equal to ("Canceled/NA") ....................3: This statement is false: ........................(The value of column 'Status') is empty ....................4: This statement is false: ........................(The value of column 'Accountant') is empty ....................5: (The value of column 'Seller') is equal to (The value of 'Seller' from the row referenced by 'ID')))

[dbaum]

SUM(SELECT(Order Details[Return Qty], AND([Status]<>"Processing",[Status]<>"Canceled/NA",ISNOTBLANK([Status]),ISNOTBLANK([Accountant]), IN([_THISROW].[Seller], LIST([Seller], "All"))))))

[aminsaleh]

@dbaum 

Thank you so much, yes it works perfecty

Just one more question plz

If also I want to add "Coordinator" to the in function and also list all Coordinators just like what you did for seller. But combine them (seller and coordinator) all in one expression. I've tried by repeating the in function last line and replaced seller word with coordinator but got an error.

[dbaum · Accepted solution]

The following may be what you're requesting.

OR(IN([_THISROW].[Seller], LIST([Seller], "All")), IN([_THISROW].[Coordinator], LIST([Coordinator], "All")))

[aminsaleh]

Hi @dbaum 

sorry for being late

Thank you very much

but (OR) did not work for me

I used (And) instead and it works nicely