Query: Model_Lookup() to perform Aggregration. Any Advice on how to achieve this?

MWS,

Are you trying to show the Sum of ‘TOTALREQQTY’ for Glue in your left hand table?  You can make another Aggregate model that rolls up ‘TOTALREQQTY’ by Product, then do your model lookup on the group by of ‘Product’.  Just make sure you have the same conditions on this additional aggregate model as the model for your right hand table.

I don’t think there is a way to get a ‘total’ of that column from the model that groups by category and product name (the right hand table).

Thanks,

Bill

Thanks for the reply Bill. Unfortunately i dont think i can find this answer by another aggregate model as i need the grouping of product part ( ie Glue ) followed by the grouping of product to give the correct answer for TotalReqQty. I was hoping there would be some way to roll this answer up, perhaps using a snippet if required. Happy to describe in more detail the situation if anyone thinks they may have some insight. Thank you again 🙂

MWS,

You can do this with a custom render snippet on the ‘To Pack’ field to get the total you want.  I think, however, that this could be done with an additional model and a model lookup function to get the total that you want.  It looks like you can add a 3rd model to do this.

Would you post the XML for the 3 models that you refere to in your posting.  Please also describe the calculation you want to show.

Thanks,

Bill

Thank you very much for your continued assistance and time Bill! Here is a copy of the 3 models described.

The Calculation for TotalReqQty is:

Product Part, Finished Good, Demand for Finished Good ( multiple by product part required for this finished good ) = TotalReqQty of Product Part for this finished good and we basicallly continue down as the list as we go through all our raw materials and the finished goods they make.

Example from row 1: Glue is a component of SlimWall Panel 2400x600x50 mm. There is 10 Units of this finished good to be shipped and each uses 0.576 kgs of glue ( this value is hidden but in the model ). So the answer of TotalReqQty is 5.76.

ie Glue, SlimWall Panel 2400x600x50 mm, 10.00, = 5.76 ( the glue per finished good field not shown but would be 0.576 ).

Models follow:

ShipmentsLineItems - Contains the data of all finished products due to be shipped.

Product Parts - Contains the raw materials which make up each finished good. Pulls the shipment value from ShipmentLineItems to find total future demand of raw materials from products to be shipped. This is is the TotalReqQty calculation.

The final model just holds all the raw materials in inventory and pulls the demand from these aggregate models. This model may have a few redundant fields, I apologize.

Thank you Bill, I hope I made that clear enough, thank you for taking your time to look through it with me, very much appreciated 🙂

MWS,

Thanks for sharing the model XML.  I can see why another aggregate model won’t work.  You build up your total from each of the aggregate models  You don’t have any fields on the base objects that you can roll up by the product name (Glue) to get you the numbers you need for To Pack.

I think your best bet is to add a UI only field to your 3rd model for the ‘To Pack’ column.  Then run a snippet to render this UI only field.  The snippet can iterate through the other models to calculate the total for each product name (i.e. Glue).

If you don’t need something that is ‘real time’, you could setup an object in Salesforce and a batch process to update the values in this object.  The batch process could run 3-4 times a day.  Then your Skuid page would simply show the object in a table.

Thanks,

Bill