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

  • 1
  • Question
  • Updated 9 months ago
  • Answered


Sorry about the hastily drawn picture :)

The left table is a basic model that is showing a list of all my raw material inventory models.

The right table is an aggregate model with 2 layers of grouping that shows what that raw material makes as a finished good. That finished good grouping is used for a model lookup to a third aggregate model ( not shown ) that is describing the shipping demand for that finished good. The goal being that I want to know my total requirement for glue derived from all my finished good sales orders in the system.

The data here is all the correct and I am very close. My struggle is how to get the left table to show an aggregate answer for all the glue rows in the right table. The standard model lookup is finding the first value and presenting that. Is there any way to perform a model lookup loop or aggregate model lookup?

Thank you greatly for your assistance.
Photo of MWS

MWS

  • 158 Points 100 badge 2x thumb

Posted 10 months ago

  • 1
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
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
Photo of MWS

MWS

  • 158 Points 100 badge 2x thumb
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 :)
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
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
Photo of MWS

MWS

  • 158 Points 100 badge 2x thumb
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.

<model id="ShipmentLineItemsInventory" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="aggregate" sobject="Shipment_Line__c">
            <fields>
                <field id="Quantity__c" name="sumQuantityc" function="SUM"/>
                <field id="Quantity_Unfilled__c" name="sumQuantityUnfilledc" function="SUM"/>
                <field id="Quantity_Unpacked__c" name="sumQuantityUnpackedc" function="SUM"/>
                <field id="Quantity_Packed__c" name="sumQuantityPackedc" function="SUM"/>
            </fields>
            <conditions>
                <condition type="fieldvalue" value="" enclosevalueinquotes="false" field="Shipment_Date__c" state="filterableoff" inactive="true" name="Shipment_Date__c" operator="lte"/>
                <condition type="fieldvalue" value="Manufactured" enclosevalueinquotes="true" field="Project_Task__r.GMBLASERP__Product__r.AcctSeed__Inventory_Type__c"/>
                <condition type="fieldvalue" value="Dispatched" field="Shipment__r.Status__c" operator="!=" enclosevalueinquotes="true"/>
            </conditions>
            <actions/>
            <groupby method="simple">
                <field id="Project_Task__r.GMBLASERP__Product__r.Name" name="projectTaskrGMBLASERPProd"/>
            </groupby>
        </model>


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.

<model id="ProductParts" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="aggregate" sobject="AcctSeed__Product_Part__c">
            <fields>
                <field id="AcctSeed__Quantity__c" name="sumAcctSeedQuantityc" function="SUM"/>
                <field id="ShipmentsQty" uionly="true" displaytype="FORMULA" ogdisplaytype="TEXT" precision="9" scale="0" defaultvaluetype="fieldvalue" readonly="true" returntype="DOUBLE" label="ShipmentsQty">
                    <formula>VALUE(MODEL_LOOKUP("ShipmentLineItemsInventory","sumQuantityUnpackedc","projectTaskrGMBLASERPProd",{{acctSeedProductrName}}))</formula>
                </field>
                <field id="TotalReqQty" uionly="true" displaytype="FORMULA" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="9" scale="0" label="TotalReqQty">
                    <formula>{{ShipmentsQty}}*{{sumAcctSeedQuantityc}}</formula>
                </field>
            </fields>
            <conditions/>
            <actions>
                <action>
                    <actions>
                        <action type="adoptRows" sourcemodel="ProductParts" targetmodel="UIProductParts" affectedrows="all"/>
                    </actions>
                    <events>
                        <event>models.loaded</event>
                    </events>
                </action>
            </actions>
            <groupby method="rollup">
                <field id="AcctSeed__Product_Part__r.Name" name="acctSeedProductPartrName"/>
                <field id="AcctSeed__Product__r.Name" name="acctSeedProductrName"/>
            </groupby>
        </model>
    </models>

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.

<model id="InventoryManufactured" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="" sobject="AcctSeedERP__Inventory_Balance__c" orderby="">
            <fields>
                <field id="AcctSeedERP__Available_Quantity__c"/>
                <field id="AcctSeedERP__Product__c"/>
                <field id="AcctSeedERP__Product__r.Name"/>
                <field id="AcctSeedERP__Outbound_Quantity__c"/>
                <field id="AcctSeedERP__Manufacturing_Quantity__c"/>
                <field id="AcctSeedERP__Sales_Order_Quantity__c"/>
                <field id="Name"/>
                <field id="ShipmentsToPack" uionly="true" displaytype="FORMULA" ogdisplaytype="TEXT" precision="9" scale="0" defaultvaluetype="fieldvalue" readonly="true" returntype="DOUBLE" label="To Pack">
                    <formula>Value(MODEL_LOOKUP("ProductParts","TotalReqQty","acctSeedProductPartrName",{{AcctSeedERP__Product__r.Name}}))</formula>
                </field>
                <field id="Manufacturing" uionly="true" displaytype="FORMULA" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="9" scale="0" label="Manufacturing">
                    <formula>MODEL_LOOKUP("ManufacturingInventory","sumAcctSeedERPQuantityOrd","acctSeedERPManufacturedPr",{{AcctSeedERP__Product__r.Name}})</formula>
                </field>
                <field id="NetPosition" uionly="true" displaytype="FORMULA" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="9" scale="0" label="Projected">
                    <formula>({{Manufacturing}}+{{InventoryOnHand}})-{{ShipmentsToPack}}</formula>
                </field>
                <field id="AcctSeedERP__Product__r.AcctSeedERP__Safety_Stock_Quantity__c"/>
                <field id="InventoryOnHand" uionly="true" displaytype="FORMULA" label="On Hand" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="9" scale="0">
                    <formula>{{AcctSeedERP__Available_Quantity__c}}-{{ShipmentsPacked}}</formula>
                </field>
                <field id="SafetyWarning" uionly="true" displaytype="FORMULA" label="Warning" ogdisplaytype="TEXT" readonly="true" returntype="BOOLEAN">
                    <formula>IF({{NetPosition}}&lt;={{AcctSeedERP__Product__r.AcctSeedERP__Safety_Stock_Quantity__c}},true,false)</formula>
                </field>
                <field id="AcctSeedERP__Product__r.Family"/>
                <field id="AcctSeedERP__Product__r.AcctSeedERP__Lead_Time__c"/>
                <field id="AcctSeedERP__Product__r.Product_Category__c"/>
                <field id="AcctSeedERP__Product__r.AcctSeedERP__Default_Vendor__r.Name"/>
                <field id="AcctSeedERP__Product__r.AcctSeedERP__Default_Vendor__c"/>
                <field id="AcctSeedERP__Product__r.Id"/>
            </fields>
            <conditions>
                <condition type="fieldvalue" value="Raw Material" enclosevalueinquotes="true" field="AcctSeedERP__Product__r.AcctSeed__Inventory_Type__c" state="filterableon" inactive="false" name="AcctSeedERP__Product__r.AcctSeed__Inventory_Type__c"/>
                <condition type="fieldvalue" value="" enclosevalueinquotes="true" field="AcctSeedERP__Product__r.Family" state="filterableoff" inactive="true" name="AcctSeedERP__Product__r.Family"/>
            </conditions>
            <actions/>
        </model>

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 :)
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
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