Formula help

Hi Penny Whitlock 

I'm a bit confused here....

Here is what I got:

• Gross Value : $pr_payment_gross_value_c
• Net Value : $pr_net_value_c
• VAT Rate : $pr_payment_rate_c

This one I didn't get:

• $pr_total_paid_c : What is this?

According to your specification, the formula should be something like that:

ifElse(
    greaterThan(50000,$pr_net_value_c),
    multiply($pr_payment_gross_value_c,0.1),
    add(5000,multiply($pr_net_value_c,(divide($pr_payment_rate_c,100))))
)

However, the calculation from your examples doesn't seem to fit;

Perhaps we may need a clear/more detailed requirements.

Hi Penny Whitlock ,

If I'm understanding your requirements correctly, then the formula should be:

ifElse(
    greaterThan(
        $pr_net_value_c,
        50000
    ),
    add(
        5000,
        multiply(
            $pr_net_value_c,
            divide(
                $pr_payment_rate_c,
                100
            )
        )
    ),
    multiply(
        $pr_payment_gross_value_c,
        .1
    )
)

Your requirements state that if the value is under 50,000 pounds, then the field should have 10% of the gross value. I'm not clear why your original formula is attempting to subtract pr_total_paid_c from 10% of the pr_payment_gross_value_c field. I am assuming pr_payment_gross_value_c is the gross value, so I removed the subtraction element to only provide the 10% value of that field to match your stated requirement.

Also, your original formula had two extra opening parentheses and not enough closing parentheses. I find it easiest to write out formulas with tab indents like above to easily track when parentheses are needed.

Chris

Hi Chris

thanks for your help - i am getting this error

greaterThan: The parameter at index 1 must be of type number

Im getting an error: no open parathesis found  :( 

thanks so much for your help, i have tried to amend slightly and cant get it to accept it

i have had to add some code to the start so that the field turns to 0 when a payment is received but cant get the parenthesis working again - i cannot see for the life of me where the missing one would be! 

ifElse(
greaterThan(
$pr_total_paid_c,
0
)
,"0",)
ifElse(
greaterThan(
$pr_net_value_c,50000),
add(5000,(multiply(5000,(divide($pr_payment_rate_c,100)))
))
),
multiply(
$pr_payment_gross_value_c,
.1
)

Hi Penny Whitlock ,

Your formula contained a few errors. Here is your formula in an easier to read format with comments on why it will not validate:

ifElse(
    greaterThan(
        $pr_total_paid_c,
        0
    ),
    "0",
) // This is a final closing parentheses and is placed too early. You cannot have any additional formulas after this parentheses
ifElse(
    greaterThan(
        $pr_net_value_c,
        50000
    ),
    add(
        5000,
        (multiply( // The opening parentheses before multiply is not necessary
            5000,
            (divide( // The opening parentheses before divide is not necessary
                $pr_payment_rate_c,
                100
            ))
        ))
    )
), // This is another final closing parentheses
multiply(
    $pr_payment_gross_value_c,
    .1
)

Here is how the formula should look. I also took your prior comment into account about the greaterThan formula. This means the field pr_total_paid_c was not created as a numeric field type. You need to cast that field to the expected format for the formula to function by using number():

ifElse(
    greaterThan(
        $pr_total_paid_c,
        0
    ),
    "0",
    ifElse(
        greaterThan(
            number(
                $pr_net_value_c
            ),
            50000
        ),
        add(
            5000,
            multiply( 
                5000,
                divide( 
                    $pr_payment_rate_c,
                    100
                )
            )
        ),
        multiply(
            $pr_payment_gross_value_c,
            .1
        )
    )
)

Chris