Hi i am trying to figure out the 50% payment calculation that brings the account balance up to 90% or 95% depending on the payment stage beforehand, which is dictated by project type. (project type options are 'supply and fit', 'supply and fit 2023' and 'supply only')
Supply and fit 2023 - 10%, 35%, 50%, 5%
Supply and fit - 10%, 30%, 50%, 10%
Supply only - 50%, 50% (we have no issues with this set of calculations)
This calculation is for the 'suggested payment amount' field for this payment stage (50% of gross value) - once a payment is entered into 'amount paid' it should show as zero.
if the project type is "supply and fit 2023" it needs to calculate 50% of the gross value, but that should also bring the account up to 95% paid, so if there has been an earlier overpayment it would be less than 50%, in the same way if there was an underpayment it should be more than 50% that needs to display in 'suggested' field.
so i want the formula to say the below but i cant put all the strings together and make it work.
If total paid is zero, display 50% gross value
ifElse(equal($pr_total_paid_c,0),(multiply($pr_payment_gross_value_c,0.5),
if total paid is more than zero and project type equals Supply and Fit 23, then 95% of gross value minus 10% suggested, 10% amount paid, 40% amount paid, 40% suggested, 90% amount paid, additional payments 1, additional payments 2, additional payments 3, additional payments 4.
ifElse(
greaterthan($pr_total_paid_c,0)
and(
equal($pr_project_type_c,"supply and fit 2023")),
subtract(multiply($pr_payment_gross_value_c,0.95), (add($pr_10_percentage_suggested_c,$pr_40_percentage_suggested_c, $pr_10_percentage_amount_paid_c, $pr_40_percentage_amount_paid_c,$pr_90_percentage_amount_paid_c,$pr_additional_payment_1_c,$pr_additional_payment_2_c,$pr_additional_payment_3_c,$pr_additional_payment_4_c))
If the project type is 'supply and fit' and total paid is more than zero, then 90% of gross value minus 10% suggested, 10% amount paid, 40% amount paid, 40% suggested, 90% amount paid, additional payments 1, additional payments 2, additional payments 3, additional payments 4.
ifElse(
greaterthan($pr_total_paid_c,0)
and(
equal($pr_project_type_c,"supply and fit")),
subtract(multiply($pr_payment_gross_value_c,0.9), (add($pr_10_percentage_suggested_c,$pr_40_percentage_suggested_c, $pr_10_percentage_amount_paid_c, $pr_40_percentage_amount_paid_c,$pr_90_percentage_amount_paid_c,$pr_additional_payment_1_c,$pr_additional_payment_2_c,$pr_additional_payment_3_c,$pr_additional_payment_4_c))
i have combined these together as below and it doesnt work - does anyone know what i am doing wrong?
ifElse(equal($pr_total_paid_c,0),(multiply($pr_payment_gross_value_c,0.5),
ifElse(
greaterthan($pr_total_paid_c,0)
and(
equal($pr_project_type_c,"supply and fit 2023")),
subtract(multiply($pr_payment_gross_value_c,0.95),
(add($pr_10_percentage_suggested_c,$pr_40_percentage_suggested_c,
$pr_10_percentage_amount_paid_c,$pr_40_percentage_amount_paid_c,
$pr_90_percentage_amount_paid_c,$pr_additional_payment_1_c,$pr_additional_payment_2_c,
$pr_additional_payment_3_c,$pr_additional_payment_4_c)),
ifElse(
greaterthan($pr_total_paid_c,0)
and(
equal($pr_project_type_c,"supply and fit")),
subtract(multiply($pr_payment_gross_value_c,0.9),
(add($pr_10_percentage_suggested_c,$pr_40_percentage_suggested_c,
$pr_10_percentage_amount_paid_c, $pr_40_percentage_amount_paid_c,$pr_90_percentage_amount_paid_c,
$pr_additional_payment_1_c,$pr_additional_payment_2_c,$pr_additional_payment_3_c,
$pr_additional_payment_4_c))
appreciate any help!