Hi all,
I am trying to introduce another part to my formula (enclosed as New Workflow 1) whereby:
if IsNull(Cat1) and IsNull(Cat2) Then
Check Category against Other1
Check Category against Other2
Check Category against Other3
Check Category against Other4
If Match Then Yes
Else No
For example on screenshot provided(Image): I would expect Row 8, 10, 13 and 14 to come back as Yes. Row 4 seems to be correct as doesnt match.
Any help please. I have been struggling to get the next part of the IF to be added to my existing IF statement.
Thanks
Solved! Go to Solution.
Hi @B_Y_ID8
Try this formula:
IF IsNull([Category])
THEN 'No'
ELSEIF [Category] IN ([Cat1],[Cat2],[Other1],[Other2],[Other3],[Other4])
THEN 'Yes'
ELSEIF ([Cat1]IN('DD') AND [Category]!='DD') || ([Cat2]IN('DD') AND [Category]!='DD')
THEN 'DD Error'
ELSE 'No'
ENDIF
This lets the Category field find any match across the columns specified.
If this solves your issue please mark the answer as correct, if not let me know!
Thanks!
Phil
Hi Phil,
Thanks for the response - it nearly does - apologies but I probably missed an extra bit out.
If content is already in Cat1 or Cat2 then it should not check Other1, Other2, Other3 and Other4. It should determine if Category matches either Cat1 or Cat2 and output Match Yes/No/DD Error.
Only if Cat1 and Cat2 are empty then the check of Category against Other1, Other2, Other3 and Other4 should take place and then output Match Yes/No.
That is the reason why I put: If isnull(cat1) and isnull(cat2) then check. (but I wasnt clear enough to begin with - my fault)
Hope this makes sense.
Thanks
This should work for you.
IF IsNull([Category])
THEN 'No'
ELSEIF [Category] IN ([Cat1],[Cat2])
THEN 'Yes'
ELSEIF 'DD' IN ([Cat1],[Cat2]) AND [Category]!='DD'
THEN 'DD Error'
ELSEIF IsNull([Cat1]) AND IsNull([Cat2]) AND [Category] IN ([Other1],[Other2],[Other3],[Other4])
THEN 'Yes'
ELSE 'No'
ENDIF
I modified the formula to only look if for Cat1 and Cat2 are null and after all the other operations first run.
If this solves your issue please mark the answer as correct, if not let me know!
Thanks!
Phil
Hi Phil,
On the whole - Yes the solution has worked. Thank you.
I may need to check though as whether I need to apply as a logic - an example below:
Category Cat1 Cat2
AA DD FF
= DD Error (which I would expect)
Category Cat1 Cat2
AA AA DD
= Output shows as Yes (as it matches Category and Cat1) however I need to check if Cat2 would overrule and output should be DD Error.
At the moment I am happy with the solution and will mark as solved but if I do need to apply that logic would I be able to come back to you?
Thanks for your help - much appreciated.
Hi @B_Y_ID8
Modify the elseif like below. I am changing the order.
IF IsNull([Category])
THEN 'No'
ELSEIF 'DD' IN ([Cat1],[Cat2]) AND [Category]!='DD'
THEN 'DD Error'
ELSEIF [Category] IN ([Cat1],[Cat2])
THEN 'Yes'
ELSEIF IsNull([Cat1]) AND IsNull([Cat2]) AND [Category] IN ([Other1],[Other2],[Other3],[Other4])
THEN 'Yes'
ELSE 'No'
ENDIF
Please check and let me know. Priority is changed when order is changed it first come first serve
In the previous IF
ELSEIF [Category] IN ([Cat1],[Cat2])
THEN 'Yes'
was checked first in your second case it was true hence it didnt check for DD error
Hope this helps 🙂
If this post helps you please mark it as solution. And give a like if you dont mind 😀👍
Hi atcodedog05,
Yes I have changed the IF sequence and it covers the scenario if Cat2 would overrule as mentioned and output a DD error.
Im not entirely sure which logic will be applied yet - however many thanks for giving the solution to the 2nd option.
Im not sure if I am able to accept both Yours and Maskell-Rascal solutions so that you both get credit. I have given Likes to the both of you though. Im hoping to be fair so when I find out which logic will be applied I will then accept that as the solution.
Again thank you to both of you!
Hi @B_Y_ID8
You will be able to mark both posts as solution
And do please check and let you know does it work or how can we help you further🙂
Hi atcodedog05
Apologies for not posting the response. I have accepted both as solutions however in the end the chosen logic was what Maskell-Rascal wrote.
Many thanks