Comparing Conditional Statements

[avilla34]

Hello,

I am trying to look at 2 columns. The SCR number is the common identifier between both columns. Some SCR numbers are assigned a different supplier type (it could be a Finished Products & Sterilizers, Direct Material and Indirect Material). I would like to assign an SCR number the worse kind of supplier the order of supplier from worse to best is: Finished Products & Sterilizers, Direct Material, and Indirect Material. In the example workflow, the SCR number is 24 and the type of suppliers assigned to ir are Finished Products & Sterilizers and Direct Material. For this specific example, I would like to look at the supplier type which is column EM DM ID, look through the types and assign it to be a Finished Products & Sterilizer since that is the worse type of supplier. In the workflow, I have attempted an if statement in the formula which has given an error. I have attempted different if statements and they all return the same error. Can someone assist with the issue?

Thanks!

[Luke_C]

Hi @avilla34 

You need to name your field. Click the dropdown and select add column and name the new field.

I also think you can simplify your formula to: 

IF [SCR number] = 6 and Contains([EM DM ID], "Finished Products & Sterilizers")
THEN "Finished Products & Sterilizers"
ELSE [EM DM ID] ENDIF

[danilang]

hi @avilla34 

Can you provide a sample input file as well as your workflow?

Thanks

Dan

[avilla34]

Thanks Luke. That makes so much sense. I am having issues assigning it to the correct supplier type. It is not assigning a finished supplier to all types when it has the same SCR Number. i will inlcude a worfklow for reference. How can I have it look at all of the suppliers associated with one SCR number and then have replace the supplier type with the worse case. Ie if it has direct, finished, and indirect, I would like for it to replace all with finished since that is the worse case.

[danilang]

Hi @avilla34 

Since a Formula tool only has access to one record at a time and you want to find the worse type for all the records, you need to summarize your records using SCR as a key, find the max supplier type and then assign this type to all your original records.

Here I used a Supplier Type/Priority mapping table to order the types.  Then it's a matter of assigning priorities to all your original records, finding the max per SCR and then assigning the related Supplier Type to all your original records.  Here I created an new IR field to show the difference between the inputs and outputs

Dan