Adding new record IDs from partly existing IDs

[ShadeDaddy]

Hi Community,

I need to get Target_order_of_sale(output) from Current_Order_of_sale. As you can see if there is value in "current.." column then that value should go in output, and if not it should get value depending on "current" and "class" priority(by that I mean that A is highest and b2 lowest priority). 

I was trying multiple ways to solve it , but cannot find a correct solution.

Help!!!!

Best

[TonyA]

I was going to ask how you are assigning priorities, but I think I see it.

[ShadeDaddy]

i need to always look for each product(3records each product). First is to put value from "current_order_of_sale". And if it is null adding value by "class", value needs to be different then in "current_order_of_sale" for the same product. So that I get distinct RecordIDs on each Product. My head is going to explode. 🙂

[TonyA · Accepted solution]

The only way I could figure out how to do this was with a pretty complex multrow formula. Another option would be to use a crosstab and join to add the current values for each product for all classes to each line and then use an equally complex standard formula.

My solution is attached.

It is important that all rows for each product are grouped and the classes are sorted in ascending order.

[ShadeDaddy]

@TonyA you are the King!!!!

It works!

That is fantastic!