Details about this thread
  • State Not Answered
  • +1 person also asked this people also asked this
  • Replies 6 replies
  • Subscribers 308 subscribers
  • Views 805 views
  • Users 0 members are here
Related content from around SugarClub

Formula help

Penny Whitlock

Hi 

I am currently trying to get this field to calculate 10% of gross value if the net value is under £50,000.

if the net value is over £50,000 then I need the field to equal £5,000 plus net value x (VAT rate/100) 

i.e. if VAT rate is 20 (20% vat) and the net value is over £50,000 then the 10% payment amount should be £6,000 (net value of £5,000 plus £1,000 VAT)

if the VAT rate is 0 and the net value is over £50,000 then the 10% payment amount should be £5,000 (net value of £5,000 plus £0 VAT)

this is what i have so far: 

ifElse(
greaterThan(
$pr_net_value_c,
"50000"),
add("5000",(multiply($pr_net_value_c,(divide($pr_payment_rate_c,100))
ifElse(
subtract(multiply($pr_payment_gross_value_c,.1),$pr_total_paid_c)
)

thanks so much for any help in advance

Parents
  • 0

    Hi  ,

    If I'm understanding your requirements correctly, then the formula should be:

    ifElse(
    greaterThan(
        $pr_net_value_c,
        50000
    ),
    add(
        5000,
        multiply(
            $pr_net_value_c,
            divide(
                $pr_payment_rate_c,
                100
            )
        )
    ),
    multiply(
        $pr_payment_gross_value_c,
        .1
    )
)

    Your requirements state that if the value is under 50,000 pounds, then the field should have 10% of the gross value. I'm not clear why your original formula is attempting to subtract pr_total_paid_c from 10% of the pr_payment_gross_value_c field. I am assuming pr_payment_gross_value_c is the gross value, so I removed the subtraction element to only provide the 10% value of that field to match your stated requirement.

    Also, your original formula had two extra opening parentheses and not enough closing parentheses. I find it easiest to write out formulas with tab indents like above to easily track when parentheses are needed.

    Chris

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

Reply
  • 0

    Hi  ,

    If I'm understanding your requirements correctly, then the formula should be:

    ifElse(
    greaterThan(
        $pr_net_value_c,
        50000
    ),
    add(
        5000,
        multiply(
            $pr_net_value_c,
            divide(
                $pr_payment_rate_c,
                100
            )
        )
    ),
    multiply(
        $pr_payment_gross_value_c,
        .1
    )
)

    Your requirements state that if the value is under 50,000 pounds, then the field should have 10% of the gross value. I'm not clear why your original formula is attempting to subtract pr_total_paid_c from 10% of the pr_payment_gross_value_c field. I am assuming pr_payment_gross_value_c is the gross value, so I removed the subtraction element to only provide the 10% value of that field to match your stated requirement.

    Also, your original formula had two extra opening parentheses and not enough closing parentheses. I find it easiest to write out formulas with tab indents like above to easily track when parentheses are needed.

    Chris

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

Children