How to sum up multi-criteria

[paiboolk]

I have transaction looks like above picture and would like to sum up and display as below picture.

How to do that?

[Darren_Murphy]

• Create a template column that joins the Item & Location
• Create a multiple relation that matches that template column to itself
• Add two rollup columns that take a sum through that relation; one for Qty In and one for Qty Out

[paiboolk]

Thank you @Darren_Murphy . It works. Anyway, it missed the third step to add lookup value from that relation.
Anyway, I still don’t get the idea why to create a multiple relation to match itself?

[Darren_Murphy]

What would you want a Lookup for?
A lookup will only give you the associated value with the first of each set, which I assumed is not what you want?
Matching each set to itself allows the rollups to give the correct result.

Edit: Actually, the result of a lookup would depend on whether the relation is single or multiple. A lookup through a single relation would give you the first value, a lookup through a multiple relation would give you an array of all values. Either way, it’s not what you need.

[paiboolk]

Relation cannot roll up number. Hence, I have to use lookup after relation every time. Is it right? I am not sure.

[Darren_Murphy]

No, you don’t need any Lookups.
Just make sure your relation is a multiple relation, and then use two rollup columns.
One for Qty In, and one for Qty Out.
Like this:

[paiboolk]

Dear @Darren_Murphy ,
The solution you advice me works very well. Thank you so much.
I face a new problem. After display on the table, some records are have the same matched item as in attached picture. How to screen them out?

[Darren_Murphy]

• Add a RowID column to the table (if you don’t already have one)
• Add a Single Value column. This should fetch the first RowID via the multiple relation that you created earlier
• Now add an if-then-else column:
  – If RowID is Single Value RowID, then true

Use that if-then-else column as a filter - “where if-then-else is checked”

[paiboolk]

Thank you. It works.