Dynamically changing the formula based on the values of a column

Question

Hi All, I am trying to create a workflow based on a logic and I am stuck. There are three columns, Number 1, Number 2 and Operator. The Number 1 and Number 2 contains values which are numeric and the Operator contains operators(<,>,<=,>=). Based on the value of the operator in each row, I want to create a flag(new column) which will tell me if the operator for that row is placed correctly. Example is listed below: Number 1Number 2OperatorFlag56T3434<=T

jdunkerley79 · Accepted Answer

Given the limited list of operators I would suggest a formula of; Switch([Operator], NULL(), "<", [Number 1] < [Number 2], ">", [Number 1] > [Number 2], "<=", [Number 1] <= [Number 2], ">=", [Number 1] >= [Number 2] ) If you do want to do a dynamic formula then the CReW macro pack maintained by @MarqueeCrew as a macro to do this. You need to build the expression and then it will build the column for you.

MarqueeCrew · Accepted Answer

@Zaid, @jdunkerley79 is right, but his formula already takes enough effort without the addition of a new tool. At Inspire in 2019 I may be training on CReW macros and would include a Dynamic Formula lesson. Here I will provide you with a different STATIC formula that gets you what you need. IF [Number 1] < [Number 2] AND [Operator] = "<" OR [Number 1] > [Number 2] AND [Operator] = ">" OR [Number 1] = [Number 2] AND [Operator] = "=" OR [Number 1] <= [Number 2] AND [Operator] = "<=" OR [Number 1] >= [Number 2] AND [Operator] = ">=" THEN 'T' ELSE 'F' ENDIF James provided you a solution that assumes that your flag is boolean ('True' and 'False'). If you output to a string, you get -1 for True and 0 for False. His formula also defaults to Null if the operator isn't one of the listed values (that is an improvement/feature over my expression). The benefit of my expression is that it is easy to read and it gets you to the "T" or "F" output that you requested. In a subsequent formula, you can update the flag as: IF Operator IN ('<', '>', '<=', '>=', '=') THEN [FLAG] ELSE Null() ENDIF While typing that second expression I realized that I had included an = operator. That may be an added bonus. Cheers, Mark