Details about this thread
  • State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 6 replies
  • Subscribers 307 subscribers
  • Views 1173 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')

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

  • 0 in reply to Andre Serrazina

    Hi Andre 

    thanks for that - i have made the amends as you suggested but it seems that once Total Paid is more than 0 it will display whatever the test value we put in is for the last clause in the third else value. Do you know what could be causing this? 

    the first parameter works (if total paid is 0) but then it will not work after that Disappointed

  • +1 Sugar Employee Badge in reply to Penny Whitlock

    Hello  , 


    I created some of the fields to be able to test on my instance and it seems working for me. 

    The condition that makes it go to to the "testvalue" is when the project_type_c doesn't match the string we wrote on the formula. 

    Can you check if you are writing "supply and fit 2023" and "supply and fit" exactly how the values are stored for the records? 

    Let me know if that was the issue. 

    Cheers, 

    André

  • 0 in reply to Andre Serrazina

    Thank you so much - added a comma and tweaked terms to capitals and now it is working! 

    the system can be quite temperamental but seems to be happy for the moment - thanks so much you have really helped us out here 

Reply Children
No Data