How to combine formulas - Help with correction

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!

Parents
  • Hello  , 


    I was having a look and I believe you are missing some closing of parenthesis. 
    I added your code to a code window to be easier to read: 

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    ifElse(
    equal($pr_total_paid_c, 0),
    multiply($pr_payment_gross_value_c, 0.5),
    // The problems begin here:
    ifElse(
    greaterthan($pr_total_paid_c, 0) &&
    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
    )
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX



    Without testing and just closing the parenthesis I think it looks like this, can you give it a try? 

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    ifElse(
    equal($pr_total_paid_c, 0),
    multiply($pr_payment_gross_value_c, 0.5),
    ifElse(
    and(
    greaterthan($pr_total_paid_c, 0),
    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
    )
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


    Cheers, 

    André

Reply
  • Hello  , 


    I was having a look and I believe you are missing some closing of parenthesis. 
    I added your code to a code window to be easier to read: 

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    ifElse(
    equal($pr_total_paid_c, 0),
    multiply($pr_payment_gross_value_c, 0.5),
    // The problems begin here:
    ifElse(
    greaterthan($pr_total_paid_c, 0) &&
    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
    )
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX



    Without testing and just closing the parenthesis I think it looks like this, can you give it a try? 

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    ifElse(
    equal($pr_total_paid_c, 0),
    multiply($pr_payment_gross_value_c, 0.5),
    ifElse(
    and(
    greaterthan($pr_total_paid_c, 0),
    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
    )
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


    Cheers, 

    André

Children
  • Hi Andre! 

    Thanks so much for your help, i have put it in place - initially it was complaining about 'greaterthan' so i copy and pasted the term from the autogenerate function, but now i am getting this error instead - do you know what this means? 

    Cannot read properties of undefined (reading 'returnType')