Update value of one product with another

[PN2527]

Hi,

As shown in the attached file, I need to update the Unit Cost of one material with another. The row number is not fixed and may change with the addition of another material over the period. 

Can we replace/ update values based on the material?

[DavidP · Accepted solution]

It doesn't look like there is a clear rule or pattern that a formula can be based on for Unit Cost. In order to write a formula, you have to be able to put the logic into words.

For instance, it looks like Description plays a role, so perhaps you can use a Sort tool to sort the rows by Description so that Desc2 - Actual comes after Desc2. Then you can possibly create a multi-row formula for Unit Cost such as:

if Left([Description],5)=Left([Row-1:Description],5) then [Row-1:Unit Cost]

elseif [Quantity]!=0 then [Amount]/[Quantity]

else 0 endif

This formula still does not include logic for Desc3, so that would have to be built in.

To preserve your original row order, you can introduce a recordID at the start and then after the calculations are finished, sort by RecordID again.

Hope this helps.

[deviseetharaman · Accepted solution]

Hi @PN2527 

Based on the provided data I have compiled a formula for get the unit cost. Please checkout the attached workflow.