Details about this thread
  • State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 6 replies
  • Subscribers 313 subscribers
  • Views 1218 views
  • Users 0 members are here
Related content from around SugarClub

How to combine formulas - Help with correction

Penny Whitlock

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
  • 0 Sugar Employee Badge

    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: 

    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
            )
        ),

        // The second ifElse is properly structured but missing a closing parenthesis.
        
        ifElse(
            greaterthan($pr_total_paid_c, 0) &&
            equal($pr_project_type_c, "supply and fit"),
            subtract(
                multiply($pr_payment_gross_value_c, 0.9), // This is the "then" part of the third ifElse.
                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
                )
            )
            // The third ifElse is properly structured but missing a closing parenthesis.
        )
    )
)



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

    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
            )
        ),
        ifElse(
            and(
                greaterthan($pr_total_paid_c, 0),
                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
                )
            )
        )
    )
)


    Cheers, 

    André

  • 0 in reply to Andre Serrazina

    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')

Reply Children
  • 0 Sugar Employee Badge in reply to Penny Whitlock

    Hello Penny, 

    I believe I understand what's the problem, when using a IfElse() I only specify one result (and not the else) I have the same error: 

    Having another look at your formula, I believe you are missing the else values for the third ifElse

    This means, the value that you want to set when the condition bellow is not met: 

    and(
    greaterthan($pr_total_paid_c, 0),
    equal($pr_project_type_c, "supply and fit")
    )

    As a test, you could try to set the else value to "0" to check if this is the problem. 

    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
            )
        ),
        ifElse(
            and(
                greaterThan($pr_total_paid_c, 0),
                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
                )
            ),0 
        )
    )
)