0

I am creating a formula resource in flow to populate a pickval field dependent on a number field I keep getting the error message The formula expression is invalid: Syntax error. Missing ')'

but i'm not sure where the the ) should go or if I have just messed up the formula altogether?

IF( 
    {!$Record.NumberOfEmployees} >= 1 && 49 <= ISPICKVAL( {!$Record.Tier_Rating__c} "Tier 4"), 
    IF( 
        {!$Record.NumberOfEmployees} >= 50 && 99 <= ISPICKVAL( {!$Record.Tier_Rating__c} "Tier 3" ), 
        IF( 
            {!$Record.NumberOfEmployees} >= 100 && 249 <= ISPICKVAL( {!$Record.Tier_Rating__c} "Tier 2"), 
            IF( 
                {!$Record.NumberOfEmployees} >= 250, ISPICKVAL( {!$Record.Tier_Rating__c} "Tier 1", 
                null
            ) 
        ) 
    ) 
) 
)

Any help much appreciated!

1
  • 1
    When facing issues with formulas, your first step should always be to apply proper formatting. Use a new line for each sub-expression, and indent like it were code. This is usually enough to reveal simple issues like this.
    – Derek F
    Feb 21, 2023 at 22:54

2 Answers 2

0

As I said in my comments, properly formatting your formula makes it easy to see issues.

The issue

Here, you have 4 main syntax issues:

  • You aren't using commas to separate arguments to the ISPICKVAL() function
  • You don't use a close parenthesis for the final ISPICKVAL() function
  • You do not provide the third argument to the final IF() (and all 3 arguments are always required)
  • You are improperly trying to compare multiple values to a single input (this is what I assume you are trying to do with {!$Record.NumberOfEmployees} >= 1 && 49 <= and the like)

As another note, ISPICKVAL() is used to do comparisons against picklists. It does not set the value of a picklist. This counts as a semantic error (an error in the meaning of your formula rather than the form of the formula).

How to fix it

To compare a single field against multiple values, you need to use the field once per comparison

So instead of {!$Record.NumberOfEmployees} >= 1 && 49 <=
you would need {!$Record.NumberOfEmployees} >= 1 && {!$Record.NumberOfEmployees} <= 49

When you're doing a simple range comparison like this, you can safely assume that subsequent comparisons have already tested the prior comparisons.

So instead of

IF(
    {!$Record.NumberOfEmployees} >= 1 && {!$Record.NumberOfEmployees} <= 49,
    IF(
        {!$Record.NumberOfEmployees} >= 50 && {!$Record.NumberOfEmployees} <= 99,
        ...

You can assume that the number is greater or equal to 50 in the second IF(). Doing an explicit check for that is just more typing (and thus more chance to make a mistake)

IF(
    {!$Record.NumberOfEmployees} >= 1 && {!$Record.NumberOfEmployees} <= 49,
    'tier 4'
    IF(
        /* 
           To get to this point, the number of employees cannot possibly be
             <= 49, meaning it is at least (or equal to) 50.
           So there is absolutely no need to check for that condition.
        */
        {!$Record.NumberOfEmployees} <= 99,
        'tier 3',
        IF(
            /* 
               Likewise, you don't need to check if the value is >= 100.
               This is logically and mathematically guaranteed, so no check is required.
            */
            {!$Record.NumberOfEmployees} <= 249,
            /* 
               This was the last level of nested IF()
               if you get to the "else" part, then the number of employees
                 is >= 250 (no need to check that).
               Just specify tier 2 for the "then" part, and tier 1 for
                 the "else" part, and your formula should be complete.
            */
        )
    )
)

This is untested

I'm reasonably certain that you can use a formula to assign a value to a flow variable.

What I'm not so sure about is your ability to assign a picklist value using a flow variable.

It's not entirely clear to me why you need to use a picklist or a flow here in the first place. Simply making this a formula field on your target object should achieve what I assume you're trying to do.

1
  • thank so much for this it has really helped and I will go with your suggestion and use a formula field
    – Tenn
    Feb 22, 2023 at 9:38
0

You don't have any commas in your ISPICKVAL expressions.

e.g.

ISPICKVAL( {!$Record.Tier_Rating__c} "Tier 4") should be ISPICKVAL( {!$Record.Tier_Rating__c}, "Tier 4")

Also, you can't compare a number with a boolean.

How are you expecting this statement to work?

49 <= ISPICKVAL( {!$Record.Tier_Rating__c} "Tier 4")

This will result in either 49 <= true or 49 <= false

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .