I was able to create my first Workato Recipe creating invoices in Quickbooks Online from Salesforce. However, we need to group the Opportunity Line Items by Product Family and then subtotal the line totals. Then these line totals would be created as Quickbooks Invoice Line Items. For example, if we have an Opportunity with 10 line items, using Products from only 3 Product Families, we ultimately need to create a single Quickbooks Invoice with 3 line items that include the Product Family and Subtotaled Amounts. Is this possible? Could someone point me in the right direction?
Hi Becky, recipes are highly customizable on Workato so that is certainly possible. Since you do not want the entire Salesforce Opportunity Product Line Items to be synced over to QuickBooks Invoice line items, you will have to create a new list to contain the subset that you wish to sync over.
Example: There are 5 opportunity line items below but there are only 3 unique products that should be synced over.
Step 1: If you have more than one opportunity line item, create a temporary list with size = no. of unique lines to be synced over to be used for iterating over.
Opportunity Product is a list data pill from step output of "Get related opportunity product list in Salesforce"
Name refers to the API name of the Opportunity Product Name -- you can check the step output in your Job Report to find out what is the API name for the field you wish to map
.pluck retrieves the specified 'Name' values from the Opportunity Product list in an array data type
.uniq returns the unique 'Name' values
.length returns the final number of items
Learn more about common formulas in formula mode here: https://support.workato.com/support/solutions/articles/1000173152-formula-mode-list-of-common-formulas-operators-and-how-to-use-them
In this example, a list of size = 3 will be created.
Step 2: Iterate through the temporary list created using a Repeat Action
Step 3: Create subset "Opportunity Products by Group (Subtotal)" list using Append item to list action. Define the fields you need - in this example, only the "Product Group" and "Product Group Subtotal" were defined.
List name and List item field names are defined by the user.
The green part returns you the array of unique Opportunity Product Names.
The red part uses "Index" (from Foreach step output) to tell Workato exactly which value (Opportunity Product Name) in the array should be appended to the list at each iteration.
Product Group Subtotal:
Blue and green parts: The combined use of .where and .pluck retrieves a filtered array whereby "Opportunity Product Name" is equal to the product name specified at each iteration of the Repeat Action.
Red part: .pluck retrieves the "Total Price" values for the filtered array. .sum calculates the sum of the "Total Price" values to return the Subtotal amount for each Opportunity Product.
End result: The list of 5 items is reduced to 3 items with the subtotal for each unique product name calculated. After you have created the list containing the subset of line items you wish to sync over, you can map this list in the appropriate source list field in Quickbooks Create Invoice action. You can refer to this forum thread for more information: https://support.workato.com/discussions/topics/1000084673
Send us a ticket, we will try our best to assist you with your problem