auto-calculate between 2 tables if certain conditions

[starwars]

I have data with 2 tables. purchase table and sales table.
I want to create a virtual column in another table, namely the product table with the name "remaining rejected goods" with a formula.
with the condition that if there are items with the same barcode and status in the purchase table, the quantity column value will be deducted from the same barcode and status in the sales table with the status condition "rejected item".

The following are screenshots of the purchasing table and sales table. example data with barcode ZA-0068.

I tried this formula but it cannot calculate based on barcode and same status between purchase table and sales table.
please help me. cc @Roderick 

SUM(
SELECT(
Purchases[Quantity],([status] = "Rejected item").[Barcode]= [_THISROW].[Barcode]
)) -
SUM(
SELECT(
Sales[Quantity],([status] = "Rejected item").[Barcode] = [_THISROW].[Barcode]
))

[Marc_Dillon]

https://help.appsheet.com/en/articles/2347624-and

[starwars]

I tried your suggestion. and I changed it to be like this but it hasn't worked well. can you fix my formula?

SUM(
SELECT(
Purchases[Quantity],
[Barcode],AND(([status] = "Rejected item")) = [_THISROW].[Barcode]
)) -
SUM(
SELECT(
Sales[Quantity],
[Barcode],AND(([status] = "Rejected item")) = [_THISROW].[Barcode]
))

[Marc_Dillon]

That's not how you use AND(), read the article again.

[starwars]

I fixed it but it's still an error. can you fix this formula? I'm a newbie just learning and I'm already stuck 😞

SUM(
SELECT(
AND((Purchases[Quantity],
[Barcode]),(Purchases[status] = "Rejected item")) = [_THISROW].[Barcode]
))-
SUM(
SELECT(
AND((Sales[Quantity],
[Barcode]),(Sales[status] = "Rejected item")) = [_THISROW].[Barcode]
))

[Marc_Dillon]

https://community.appsheet.com/t/referencing-a-column-value-in-a-table-versus-referencing-all-column...

[starwars]

can u check this sir

SUM(
SELECT(Purchases[Quantity],
[Barcode],[Status] = "Rejected item") = [_THISROW].[Barcode]
))-
SUM(
SELECT(Sales[Quantity],
[Barcode],[Status] = "Rejected item") = [_THISROW].[Barcode]
))

i got error:

Expression 'SUM( SELECT(Purchases[Quantity], [Barcode],[Status] = "Rejected item") = [_THISROW].[Barcode] ))- SUM( SELECT(Sales[Quantity], [Barcode],[Status] = "Rejected item") = [_THISROW].[Barcode] ))' was unable to be parsed: Number of opened and closed parentheses does not match.