Aggregate condition based on a summed row

  • 1
  • Question
  • Updated 2 years ago
  • Answered
Is it possible with an aggregate model to create a condition for the summed qty of a field to the qty of a field in another model?

For example.

Model 1(Basic) has one row with an item that has 4 units

Model 2 (Agg) is pulling all items from the object that the summed qty is equal or greater to the qty in model 1. There could be multiple rows.
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb

Posted 2 years ago

  • 1
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
Can you elaborate a little more, with perhaps an example with dummy labels like oranges/apples, etc. to help us understand the end result you're after? It sounds like something that may be possible, and might need to make use of additional UI-only fields to make sure the sums are in the place and format needed.
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
Of course!

For inventory purposes we need to do blind counts of the inventory. We have built a process for the count to be done but now we need a process to reconcile difference between what is in inventory and what was counted blindly.

We have an inventory position object that stores the locations of all inventory. Each inventory position can have a qty of more than 1. 

So for our example we will count oranges.

The system says we have 4 oranges in the NY Warehouse. When we count the actual amount of oranges in the NY Warehouse we only have 1.

So I need to move 3 oranges to the Missing Warehouse. Here is the problem:

I need to go through all the inventory positions and sum the qty to 3 and then move those inventory position(s) to a different warehouse. There most likely will be more than one record. 

There could be one inventory position that has a qty of 2, another one that has a qty of 2. So I need to see these records and then move the first inventory position to the missing warehouse. Then split the second inventory position by reducing the qty to 1 and create a new inventory position for qty of 1.

The creating and splitting can be achieved through actions. The problem is pulling the inventory positions that sum the number I need.

Does this make a bit more sense?
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
Thanks Raymond. I thought of that first but didn't think (perhaps mistakenly) it would fit my needs because it deals with the count and I need something that deals with the sum.
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
That particular example is for count but I think you could modify the having statement for sum. Based on my conversations with The community, the only way to condition based on aggregated field results is "Having".
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
This sounds promising. I didn't realize that I could modify the having statement for sum. I will dig through the community and see if I can find some information on it. Thanks!
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Yes, in that thread I linked to, Zach gives the xml for a having statement. You should be able to use that to just change the field to your target field and "Count" to "Sum". I used this for a project where I needed to filter results by an aggregated count field. I just modified Zach's template To my needs and pasted it in to XML where he indicated. Worked like a charm!