Details about this thread
  • State Not Answered
  • +1 person also asked this people also asked this
  • Replies 6 replies
  • Subscribers 320 subscribers
  • Views 1275 views
  • Users 0 members are here

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:

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    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
    )
    )
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    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:

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    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
    )
    )
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    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
  • 0 in reply to Chris Raffle

    Hi Chris

    thanks for your help - i am getting this error

    greaterThan: The parameter at index 1 must be of type number

  • 0 in reply to Chris Raffle

    i have had to add some code to the start so that the field turns to 0 when a payment is received but cant get the parenthesis working again - i cannot see for the life of me where the missing one would be! 

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