'If' formula help!

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

Hi Chris

Thanks so much this is fab! We have 3 contract types - how do i make the last part of the calculation specific to the other contract type called "Supply and Fit 2023 Terms" this will need to calculate the balance payment up to 45% of the contract value 

Hi Penny,

That depends. How do you want the calculation to behave if the third contract type is selected?

Chris

we have managed to make it work as the other contract type works with different cells so there is only 2 contract types for the cells in question. 

we need the same thing for 'up to 90% payment stage' which relates to 50% and 55% of the contract value - i have tried to emulate the above but havent got anywhere :( 

could you explain the code or maybe correct this one for us please? 

ifElse(
equal(round($pr_total_paid_c,2),
round(multiply($pr_payment_gross_value_c,.4),2)),
multiply($pr_payment_gross_value_c,.5),

ifElse(
greaterThan(
$pr_total_paid_c,
multiply($pr_payment_gross_value_c,.5)
)
,"0",
ifElse(
greaterThan($pr_total_paid_c,multiply($pr_payment_gross_value_c,.1)),
subtract(multiply($pr_payment_gross_value_c,.9),$pr_total_paid_c),
multiply($pr_payment_gross_value_c,.5)
)

)
)

Hi Penny Whitlock,

I need more detail to help in adjusting the new formula.

• Do you mean this formula should also be based on the contract type field? If so, am I correct in assuming that if the contract type is 'Supply and Fit' then the calculation should be 50% of the contract value and if the contract type is 'Supply and Fit 2023 Terms' then the calculation should be 55% of the contract value?
• Am I correct in assuming in that this calculation should only take place when the total paid is less than 10% of the contract value?

Chris

• Do you mean this formula should also be based on the contract type field? If so, am I correct in assuming that if the contract type is 'Supply and Fit' then the calculation should be 50% of the contract value and if the contract type is 'Supply and Fit 2023 Terms' then the calculation should be 55% of the contract value?

- yes but it should also take into account if any of the previous stage payments (10% and up to 40%/45%) have been made less than their 'suggested amount' as this stage payment will need to be up to 90%/95% of the contract value.

i.e. if someone pays £10 short on the payment beforehand, this needs to roll over to this stage payment. (as a payment is entered on any particular stage, it will reduce the suggested amount to zero and roll over the remainder to the next stage payment (or reduce the next stage if an overpayment is made) - so im assuming the best calculation would be to take into account 'total amount paid'?

• Am I correct in assuming in that this calculation should only take place when the total paid is less than 10% of the contract value?

The 'suggested amount' per payment stage is needed as soon as the contract becomes live so we can confirm to the client their stage payments and they are there as reference for our team (this works at the moment), but they should recalculate as payments are made. so when 40%/45% suggested cell turns to zero (this will be because the 40%/45% payment has been made and accepted as payment for that stage even with potential variance) 'suggested 90%/95%' should recalculate how much is required to be paid to take the 'total paid' up to 90%/95%.

Hope this makes sense!

Thanks for all your help so far, i have replied below

Hi Penny Whitlock,

I'm sorry but this has gotten more complex than I have time to assist. Hopefully someone else can provide you further guidance here, or it may be worthwhile to seek a Sugar partner in your region to engage in a formal consultation to ensure you get the results you seek. 

Chris