Scenario Overview

In some situations you may have Invoices where there are multiple Invoice Product line items which are for the same Product but may have different attributes. For this business scenario the requirement is to automatically create a product summary view for each Product -  there is to be only one Invoice Product line per Product in the summary, so the quantity, cost price, selling price, tax and extended amount are to be summed for ‘like’ products.

In essence, it is required that each invoice have a custom sub-grid that shows only one entry of each Product . If there are multiple entries of the same products in the invoice, it is required that they been combined into one summarized version.

Note : For this sample, a custom entity called Invoice Product Summary needs to be created.  It is required to have the following fields:
 
new_name                                        Type : Single line of text 
new_productlookup                        Type : Lookup  
new_extendedamount                     Type : Single line of text 
new_quantity                                    Type : Floating Point Number  
new_sellingpriceperunit                  Type : Currency  
new_tax                                             Type : Currency   
new_invoiceid                                   Type : Lookup   


  

North52 Decision Suite Solution

The North52 Decision Suite solution works like this,

• A formula of type Proccess Genie is created on the Invoice entity
• The Source Entity is set to Invoice
• A fetch XML query is run that collects and groups together all Invoice Products related to the Invoice entity
•  A record is created for the custom entity Invoice Product Summary using the information brought back by the fetch XML  

North52 Decision Suite Steps

The following set of steps outline how to create this Formula:

• Create a new formula of type Process Genie
• Set the Source Entity to 'Invoice'
• Set the Mode to 'Server Side'
• Copy and paste the formula provided below into the formula editor 
• Click Save
• Click on the Fetch-Xml tab
• Click on the plus "+" sign to add a new Fetch Xml Query
• Give it the name 'Invoice Product Aggregate'
• Copy and paste in the XML query from below - see 'Invioice Product Aggregate'
• Take note of the formula shortcode in the top right hand corner.
• Click Save and Close 

N52 Workflow Steps

• Navigate to Settings > Processes
• Click New to create a new process
• Give it the name InvoiceProductSummary
• For Category, select Workflow
• For Entity, select Invoice
• Click OK
• Uncheck Record is Created
• Check As on-demand Prcoess
• Click Add Step
• Select North52 > N52 Process Genie
• Click Set Properties
• For the Value in Formula Shortcode, enter the Formula Shortcode located in the top right of the formula editor
• Click Save and Close
• You are now ready to run the workflow on the Invoice entity

Formula

ForEachRecord(

           FindRecordsFD('Invoice Product Aggregate','true',SetParams([invoice.salesorderid])), 

                CreateRecord('new_invoiceproductsummary',
                          SetAttribute('new_name', CurrentRecord('productid_name') ),
                          SetAttributeLookup('new_productlookup', 
                                             'product',
                                             CurrentRecord('productid_guid') ),
                          SetAttribute('new_extendedamount', CurrentRecord('extendedamount_sum') ),
                          SetAttribute('new_quantity', CurrentRecord('quantity_sum') ),
                          SetAttribute('new_sellingpriceperunit', CurrentRecord('priceperunit_max') ),
                          SetAttribute('new_tax', CurrentRecord('tax_sum')),
                          SetAttributeLookup('new_invoiceid','invoice', [invoice.invoiceid] )

  )
)

Invoice Product Aggregate

<fetch version="1.0" aggregate="true" >
  <entity name="salesorderdetail" >
    <attribute name="productid" alias="productid_guid" groupby="true" />
    <attribute name="productidname" alias="productid_name" groupby="true" />
    <attribute name="priceperunit" alias="priceperunit_max" aggregate="max" />
    <attribute name="quantity" alias="quantity_sum" aggregate="sum" />
    <attribute name="extendedamount" alias="extendedamount_sum" aggregate="sum" />
    <filter type="and" >
      <condition attribute="productid" operator="not-null" />
     <condition attribute="salesorderid" operator="eq"  value="{0}" />
    </filter>
  </entity>
</fetch>

Wizard - FindRecordsFD

Please see below the wizard you can use to create the FindRecordsFD() function call used in this formula. 

Note you will need to create the Fetch Xml - Invoice Product Aggregate beforehand. You will also need to go to the Source tab to find the Parameters.