Good Afternoon!
I'm currently trying to determine a solution for the following scenario. In the table below, you will find that I have multiple orders, with multiple line items in each order, and dollar values in multiple columns.
OrderNo | LineItem | Qty | UnitCost | AddCost1 | AddCost2 | AddCost3 | AddCost4 | AddCost5 | TotalCost | Description |
1001 | 1 | 4 | 5.25 | 2.25 | 10.25 | 5.50 | 3.50 | 1.50 | 44 | Widget1 |
1001 | 2 | 1 | 25.50 | 1.25 | 2.25 | 3.50 | 1.00 | 2.00 | 35.50 | Widget2 |
1001 | 3 | 2 | 10 | 1.00 | 12.00 | 5.00 | 1.00 | 1.00 | 40 | Widget3 |
1002 | 1 | 1 | 50 | 0 | 15 | 0 | 0 | 0 | 65 | Widget4 |
1003 | 1 | 1 | 125 | 3 | 15 | 0 | 0 | 2 | 145 | Widget5 |
After running through the workflow, I need the table to look like this:
OrderNo | LineItem | Qty | UnitCost | AddCost1 | AddCost2 | AddCost3 | AddCost4 | AddCost5 | TotalCost | Description |
1001 | 1 | 4 | 5.25 | 2.25 | 10.25 | 5.50 | 3.50 | 1.50 | 44 | Widget1 |
1001 | 2 | 1 | 25.50 | 1.25 | 2.25 | 3.50 | 1.00 | 2.00 | 35.50 | Widget2 |
1001 | 3 | 2 | 10 | 1.00 | 12.00 | 5.00 | 1.00 | 1.00 | 40 | Widget3 |
1001 | 4 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 53 | AddedCost |
1002 | 1 | 1 | 50 | 0 | 15 | 0 | 0 | 0 | 65 | Widget4 |
1002 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 15 | AddedCost |
1003 | 1 | 1 | 125 | 3 | 15 | 0 | 0 | 2 | 145 | Widget5 |
1003 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 20 | AddedCost |
The amount of orders and line items vary. I need for the order number to always match the order in which is it summing up and the line item to increment the next value, based on the order number and line item numbers. Any help is greatly appreciated.
Solved! Go to Solution.
Hi @scsh4,
Can you clarify how do you amend a total cost value?
The rest of the logic is ready for you
The output:
I'm not sure that's performing as I expect it to. The quantity should always be 1 on the newly created line item, and the total cost should be the sum of the AddedCost1 through AddedCost5 of all line items on the order number.
Hi @scsh4,
My bad 😀
I believe this does exactly what I need it to! Thanks for the super quick help, @messi007!