How to sum up multi-criteria

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

How to do that?

  • 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

1 Like

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?

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.

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

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:


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?

  • 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”

1 Like

Thank you. It works.


This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.