Hi Experts,
I'm trying to build a flow that will calculate sales price for parent item based component prices e.g. Lamp price (150) made of a bulb (50) and the stand (100).
I've done a fair amount of play to get the data out of entities (data source - D365 Fin & Ops). For this I created arrays, filter actions and summarised the outcome by the following CSV tables:
Table 1 - Parent items
ItemIdParent |
PARENT_01 |
PARENT_02 |
Table 2 - BOMs for Parent items
ItemIdParent | BOMId |
PARENT_01 | BOM_01 |
PARENT_02 | BOM_02 |
Table 3 - BOM Lines for Parent items
BOMId | ItemIdComponent |
BOM_01 | PARENT_01_COMP_A |
BOM_01 | PARENT_01_COMP_B |
BOM_02 | PARENT_02_COMP_A |
BOM_02 | PARENT_02_COMP_B |
Table 4 - Price for BOM Lines for Parent items
ItemIdComponent | Sales Price |
PARENT_01_COMP_A | 100 |
PARENT_01_COMP_B | 50 |
PARENT_02_COMP_A | 250 |
PARENT_02_COMP_B | 150 |
Now.. based on the example above, I'd need the power automate to calculate following:
Table 5 - Calculated parent price
ItemIdParent | CalculatedPrice |
PARENT_01 | 150 |
PARENT_02 | 400 |
I'd appreciate any suggestions how to get the price calculated for parents (table 5). I can then later use that data to create records in Dataverse.
I tried to use the Union expression (in Compose data operation):
union(variables('Table4'),variables('Table3'),variables('Table2'))
I've then extracted relevant data into CSV table which looks like this:
ItemIdParent | BOMId | ItemIdComponent | SalesPriceComponent |
BOM_01 | PARENT_01_COMP_A | ||
BOM_02 | PARENT_02_COMP_A | ||
BOM_02 | PARENT_02_COMP_B | ||
PARENT_01_COMP_A | 350 | ||
PARENT_02_COMP_A | 200 | ||
PARENT_02_COMP_B | 100 | ||
PARENT_01 | BOM_01 | ||
PARENT_02 | BOM_02 |
I assume if I get the data to the format as in the example below, it will be easier to roll up parent price. I'd appreciate recommendation how to achieve that.
ItemIdParent | BOMId | ItemIdComponent | SalesPriceComponent |
PARENT_01 | BOM_01 | PARENT_01_COMP_A | 350 |
PARENT_02 | BOM_02 | PARENT_02_COMP_A | 200 |
PARENT_02 | BOM_02 | PARENT_02_COMP_B | 100 |
User | Count |
---|---|
89 | |
41 | |
22 | |
20 | |
16 |
User | Count |
---|---|
138 | |
56 | |
47 | |
36 | |
26 |