Scenario Overview
In this scenario our business requirement is create an order detail for each product of an order. When an order is created you need to loop over all the items in the associated price list & create an order detail record for each product. This is a typical requirement when a company sells common sets of products together.
In their product catalog they create bundles of the commonly sold products together. When the user selects a price list it is the common bundle of products that they have selected.
Then when the formula executes (Order Creation) it creates one order line item record for each product in the price list and this saves the user a lot of clicks in picking out the individual products.
Please note that Dynamics 365 does have the concept of Kits which is similar to what we are trying to achieve here but these kits only appear as one sales order line item , our method is far more flexible in that the user can easily remove unwanted line items.
Below is an example of a 'Silver Bundle' where when the Order was created the formula added in the two associated products automatically.
North52 Decision Suite Solution
The North52 Decision Suite solution works like this,
- We create a formula that triggers on the create event of an Order & it performs an action for us.
- The formula retrieves all the items on the associated price list & loops over each product
- As it loops it calls the CreateRecord function to create a Sales Order Detail record for each product
- The required fields are set on the sales order detail for each record.
North52 Decision Suite Steps
The following set of steps assumes you are just using the out of the box Dynamics 365 setup. But its easy to enhance if you have extra fields \ requirements.
- Create a new Decision Table of type 'Save - Perform Action'
- Set the Event field to 'Create'
- Set the Source Entity to 'Order'
- Set the Source Entity Property to 'Name'
- Click onto the Decision Table (if you are in Formula Editor mode, click on Commands > Toggle Editor )
- Right click A2
- Select Delete > Delete Column
- Right click A2 again
- Select Insert > Insert Action
- Do this 2 more times until you have 4 Action columns.
- Right click A2 again
- Select Set Sheet Options > Use CreateRecord
- Right click A2 again
- Select Insert > Insert ForEachRecord
- Delete the text and go to the Function tab and search for "findrecords"
- Shift Click the FindRecords function
- For Friendly Name, type "Find all Products"
- For Entity Name, select 'Price List Item'
- For Where Attribute , select 'Price List'
- For Equals Value, go to the Source tab and expand Source.
- Select 'Price List (Value)'
- For Select Attribute, select 'Product'
- Leave the other Parameters at their default.
- Click Generate
- Click on B2
- Go to the Explore tab and search for "order product"
- Expand Order Product , scroll down and select 'Order (Value)'
- Click on C2
- Go Back to the Explore tab and select 'Existing Product' (under Order Product)
- Click on D2
- Go Back to the Explore tab and select 'Quantity' (under Order Product)
- Click on E2
- Go Back to the Explore tab and select 'Unit (Value)' (under Order Product)
- Click on B4
- Go to the Source tab and search for "order"
- Select 'Order'
- Click on C4
- Paste CurrentRecord('productid')
- Click on D4
- Type 1
- Click on E4
- Go to the Function tab and search for "FindValue"
- Shift Click the FIndValue function to open the wizard
- Leave Friendly Name empty
- For Entity Name , select 'Unit'
- For Where Attribute , select 'Name'
- For Equals Value , type "Standard User (sample)"
- For Select Attribute , select 'Unit'
- Leave Default Value and No-Lock set as their defaults.
- Click Generate
- Click Save
- You are now ready to test
Wizard - FindRecords()
Please see below the wizard you can use to create the FindRecords() and FindValue() functions used in this formula.
