'If' formula help!

we currently work out stage payments depending on contract type ('supply and fit' or 'supply only') we have a new contract type 'New 2023 Terms' which will mean that the second stage payment will either be 40% if contract type is 'supply and fit' or 45% if contract type is 'New 2023 Terms'.

the field we are looking to amend is 40% Suggested Amount - we want this to check the contract type field and depending whether it says 'supply and fit' or 'new 2023 terms' work out 40% value or 45% value respectively.

we have the code for it to work out the 40% based on 'supply and fit' but do not know what we should add to make it depend on that cell and change the formula as and when required.

can anyone help with this (the formula also makes the value on the suggested field change to zero if the client has paid the up to or over the amount up to that payment stage, to signify to our team that the client has nothing outstanding at that payment stage)

ifElse(
equal(round($pr_total_paid_c,2),
round(multiply($pr_payment_gross_value_c,.1),2)),
multiply($pr_payment_gross_value_c,.3),
ifElse(
greaterThan(
$pr_total_paid_c,
multiply($pr_payment_gross_value_c,.1)
)
,"0",
ifElse(
greaterThan($pr_total_paid_c,multiply($pr_payment_gross_value_c,0)),
subtract(multiply($pr_payment_gross_value_c,.4),$pr_total_paid_c),
multiply($pr_payment_gross_value_c,.3)
)

)

)

Parents

0 Chris Raffle over 1 year ago

Hi Penny,

Based on your description, I believe the 40% calculation is happening in lines 36 to 42 of the code below:

ifElse(
    equal(
        round(
                $pr_total_paid_c,
                2
            ),
        round(
            multiply(
                $pr_payment_gross_value_c,
                .1
            ),
            2
        )
    ),
    multiply(
        $pr_payment_gross_value_c,
        .3
    ),
    ifElse(
        greaterThan(
            $pr_total_paid_c,
            multiply(
                $pr_payment_gross_value_c,
                .1
            )
        ),
        "0",
        ifElse(
            greaterThan(
                $pr_total_paid_c,
                multiply(
                    $pr_payment_gross_value_c,
                    0
                )
            ),
            subtract(
                multiply(
                    $pr_payment_gross_value_c,
                    .4
                ),
                $pr_total_paid_c
            ),
            multiply(
                $pr_payment_gross_value_c,
                .3
            )
        )
    )
)

If that is accurate, then you would want to add a condition at that spot to check the contract type. It would replace lines 36 - 42 from above and use lines 36 - 55 below:

ifElse(
    equal(
        round(
                $pr_total_paid_c,
                2
            ),
        round(
            multiply(
                $pr_payment_gross_value_c,
                .1
            ),
            2
        )
    ),
    multiply(
        $pr_payment_gross_value_c,
        .3
    ),
    ifElse(
        greaterThan(
            $pr_total_paid_c,
            multiply(
                $pr_payment_gross_value_c,
                .1
            )
        ),
        "0",
        ifElse(
            greaterThan(
                $pr_total_paid_c,
                multiply(
                    $pr_payment_gross_value_c,
                    0
                )
            ),
            ifElse(
                equal(
                    $contract_status_field,
                    "Supply and Fit"
                ),
                subtract(
                    multiply(
                        $pr_payment_gross_value_c,
                        .4
                    ),
                    $pr_total_paid_c
                ),
                subtract(
                    multiply(
                        $pr_payment_gross_value_c,
                        .45
                    ),
                    $pr_total_paid_c
                )
            ),
            multiply(
                $pr_payment_gross_value_c,
                .3
            )
        )
    )
)

Lines 38 and 39 should be changed to detail the proper field name for your contract type and the database value for 'supply and fit' respectively. With that condition, it will calculate the 40% amount if the contract type is 'supply and fit' or 45% for anything else (assuming you only have 2 contract types currently).

Let me know if you have any questions or if I misinterpreted something!

Chris

Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

Reply