Forums - Thread Details
  • State Verified Answer
  • Replies 6 replies
  • Subscribers 319 subscribers
  • Views 1008 views
  • Users 0 members are here
Related

Why does this cause my instance of Sugar to crash?

Howard Jennings

The formula saves without any errors. But when I save the field I get an error that crashed my instance. It's on demand, not on my server. This is my little formula. All the fields are correct and the syntax appears to be fine too. It looks worse han it is. It's basically the same calculation with 2 main options occ or vac and 8 options p1, p2, p3 etc. 

ifElse(equal($policy_status_c,"Cancelled"),"0.00",
ifElse(equal($occ_status_c,"Occupied"),

ifElse(equal($bi_program_no_c,"1"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p1_com_occ_t_c")),
divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p1_com_occ_t_c")),12)),
ifElse(equal($premium_freq_c,"Annual"),
currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p1_res_occ_t_c")),
divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p1_res_occ_t_c")),12)
)),
ifElse(equal($bi_program_no_c,"2"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p2_com_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p2_com_occ_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p2_res_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p2_res_occ_t_c")),12),$add_windstorm_cost_c)
)),
ifElse(equal($bi_program_no_c,"3"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p3_com_occ_t_c")),
divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p3_com_occ_t_c")),12)),
ifElse(equal($premium_freq_c,"Annual"),
currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p3_res_occ_t_c")),
divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p3_res_occ_t_c")),12)
)),

ifElse(equal($bi_program_no_c,"4"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p4_com_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p4_com_occ_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p4_res_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p4_res_occ_t_c")),12),$add_windstorm_cost_c)
)),

ifElse(equal($bi_program_no_c,"5"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p5_com_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p5_com_occ_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p5_res_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p5_res_occ_t_c")),12),$add_windstorm_cost_c)
)),

ifElse(equal($bi_program_no_c,"6"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p6_com_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p6_com_occ_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p6_res_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p6_res_occ_t_c")),12),$add_windstorm_cost_c)
)),
ifElse(equal($bi_program_no_c,"7"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p7_com_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p7_com_occ_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p7_res_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p7_res_occ_t_c")),12),$add_windstorm_cost_c)
)),

ifElse(equal($bi_program_no_c,"8"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p8_com_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p8_com_occ_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p8_res_occ_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p8_res_occ_t_c")),12),$add_windstorm_cost_c)
)),

"next bit"),
)))))))
,
ifElse(equal($bi_program_no_c,"1"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p1_com_vac_t_c")),
divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p1_com_vac_t_c")),12)),
ifElse(equal($premium_freq_c,"Annual"),
currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p1_res_vac_t_c")),
divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p1_res_vac_t_c")),12)
)),
ifElse(equal($bi_program_no_c,"2"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p2_com_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p2_com_vac_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p2_res_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p2_res_vac_t_c")),12),$add_windstorm_cost_c)
)),
ifElse(equal($bi_program_no_c,"3"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p3_com_vac_t_c")),
divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p3_com_vac_t_c")),12)),
ifElse(equal($premium_freq_c,"Annual"),
currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p3_res_vac_t_c")),
divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p3_res_vac_t_c")),12)
)),

ifElse(equal($bi_program_no_c,"4"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p4_com_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p4_com_vac_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p4_res_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p4_res_vac_t_c")),12),$add_windstorm_cost_c)
)),

ifElse(equal($bi_program_no_c,"5"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p5_com_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p5_com_vac_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p5_res_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p5_res_vac_t_c")),12),$add_windstorm_cost_c)
)),

ifElse(equal($bi_program_no_c,"6"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p6_com_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p6_com_vac_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p6_res_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p6_res_vac_t_c")),12),$add_windstorm_cost_c)
)),

ifElse(equal($bi_program_no_c,"7"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p7_com_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p7_com_vac_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p7_res_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p7_res_vac_t_c")),12),$add_windstorm_cost_c)
)),

ifElse(equal($bi_program_no_c,"8"),
ifElse(equal($property_type_c,"Commercial"),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p8_com_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p8_com_vac_t_c")),12),$add_windstorm_cost_c)),
ifElse(equal($premium_freq_c,"Annual"),
add(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p8_res_vac_t_c")),$add_windstorm_cost_c),
add(divide(currencyMultiply($sum_insured_c,related($r_rates_bi_buildings_insurance_1,"p8_res_vac_t_c")),12),$add_windstorm_cost_c)
)),

""),

)))))))
)
)

Parents
  • 0

    Hi Howard,

    It looks like you are writing a switch, and a rather complex one, in a feature that was intended to handle trivial data manipulation. I recommend a different approach to your use case.

    For the aspects of this logic where you have "If value a, then b. If value c, then d, etc..." I would make a dropdown list in which the Item Name is the "if" value, and the Display Label is the "then" value, and then replace all of the embedded ifElse logic with a simpler "where Item Name, then Display Label" kind of calculation. Chris Raffle posts a great example of this idea here. 

    I hope this was helpful!

  • 0 in reply to Patrick McQueen

    Hi @Patrick McQueen

    I'm not sure how this helps

    My formula calculates an insurance premium and is basically

    MULTIPLY([sum-insured],[rate]) but [rate] has variables.

    1. Occupied [occ] or Vacant [vac]
    2. Property type = Residential [res] or Commercial [com]
    3. Program number = [p1], [p2], [p3] currently there are 8 programs
    4. Premium frequency = Annually [1] or Monthly [12]

    My logic runs

    IF is it [occ],

          then the [com] and premium frequency for each program number. then the same for [res]

          then the same for [res]

    ELSE

       exactly as above only [vac]

    I need to find a way of dynamically building the formula. Something like

    field names

    My rate fields are named pX_res_occ_t_c or pX_com_occ_t_c etc.

    p1_res_occ_t_c = 0.0043

    sum_ins = 25000 (but could be any amount)

    occupancy status = _occ or _vac

    property_type = _res or _com

    program_no = p1 or p2, or p3 etc.

    premium_freq = 1 or 12

    formula

    var occ = occupancy status

    var type = property_type

    var prog = program_no 

    var freq = premium_freq

    var field_name = CONCAT(prog,type,occ,"_t_c") // This would build a legitimate field name i.e. p1_res_occ_t_c which could then be used to calculate the premium.

    DIVIDE(MULTIPLY([sum_ins],[field_name],freq) or 25000 X 0.0043 / 1 = 107.50

    That would remove most of my IfElse statements and make it much leaner. I know I'm mixing languages, but that's the beauty of only having enough knowledge to be dangerous

  • 0 in reply to Howard Jennings

    Hi Howard,

    What do you think of having a field that pulls in the appropriate related($r_rates_bi_buildings_insurance_1,"px_xxx_occ_t_c") from the Rate record?

    For example:
    Create a field in your BI module (let's call it "px_c" for the sake of this discussion), and populate it with a formula like this:

    ifElse(equal(subStr($property_type_c,0,3),"Com"),
    ifElse(equal($bi_program_no_c,"1"),related($r_rates_bi_buildings_insurance_1,"p1_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"2"),related($r_rates_bi_buildings_insurance_1,"p2_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"3"),related($r_rates_bi_buildings_insurance_1,"p3_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"4"),related($r_rates_bi_buildings_insurance_1,"p4_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"5"),related($r_rates_bi_buildings_insurance_1,"p5_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"6"),related($r_rates_bi_buildings_insurance_1,"p6_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"7"),related($r_rates_bi_buildings_insurance_1,"p7_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"8"),related($r_rates_bi_buildings_insurance_1,"p8_com_occ_t_c"),
    0)))))))),
    ifElse(equal(subStr($property_type_c,0,3),"Res"),
    ifElse(equal($bi_program_no_c,"1"),related($r_rates_bi_buildings_insurance_1,"p1_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"2"),related($r_rates_bi_buildings_insurance_1,"p2_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"3"),related($r_rates_bi_buildings_insurance_1,"p3_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"4"),related($r_rates_bi_buildings_insurance_1,"p4_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"5"),related($r_rates_bi_buildings_insurance_1,"p5_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"6"),related($r_rates_bi_buildings_insurance_1,"p6_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"7"),related($r_rates_bi_buildings_insurance_1,"p7_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"8"),related($r_rates_bi_buildings_insurance_1,"p8_res_occ_t_c"),
    0)))))))),
    0))

    Then, in test_hazard_premium_c, you could calculate with this:

    ifElse(equal($policy_status_c,"Cancelled"),"0.00",
    ifElse(equal($occ_status_c,"Occupied"),
    ifElse(isInList($bi_program_no_c,createList(1,3)),
    ifElse(equal($premium_freq_c,"Annual"),
    currencyMultiply($sum_insured_c,$px_c),divide(currencyMultiply($sum_insured_c,$px_c),12)),
    ifElse(isInList($bi_program_no_c,createList(2,4,5,6,7,8)),
    ifElse(equal($premium_freq_c,"Annual"),
    add(currencyMultiply($sum_insured_c,$px_c),$add_windstorm_cost_c),add(divide(currencyMultiply($sum_insured_c,$px_c),12),$add_windstorm_cost_c)),
    0)),0))

Reply
  • 0 in reply to Howard Jennings

    Hi Howard,

    What do you think of having a field that pulls in the appropriate related($r_rates_bi_buildings_insurance_1,"px_xxx_occ_t_c") from the Rate record?

    For example:
    Create a field in your BI module (let's call it "px_c" for the sake of this discussion), and populate it with a formula like this:

    ifElse(equal(subStr($property_type_c,0,3),"Com"),
    ifElse(equal($bi_program_no_c,"1"),related($r_rates_bi_buildings_insurance_1,"p1_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"2"),related($r_rates_bi_buildings_insurance_1,"p2_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"3"),related($r_rates_bi_buildings_insurance_1,"p3_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"4"),related($r_rates_bi_buildings_insurance_1,"p4_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"5"),related($r_rates_bi_buildings_insurance_1,"p5_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"6"),related($r_rates_bi_buildings_insurance_1,"p6_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"7"),related($r_rates_bi_buildings_insurance_1,"p7_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"8"),related($r_rates_bi_buildings_insurance_1,"p8_com_occ_t_c"),
    0)))))))),
    ifElse(equal(subStr($property_type_c,0,3),"Res"),
    ifElse(equal($bi_program_no_c,"1"),related($r_rates_bi_buildings_insurance_1,"p1_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"2"),related($r_rates_bi_buildings_insurance_1,"p2_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"3"),related($r_rates_bi_buildings_insurance_1,"p3_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"4"),related($r_rates_bi_buildings_insurance_1,"p4_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"5"),related($r_rates_bi_buildings_insurance_1,"p5_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"6"),related($r_rates_bi_buildings_insurance_1,"p6_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"7"),related($r_rates_bi_buildings_insurance_1,"p7_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"8"),related($r_rates_bi_buildings_insurance_1,"p8_res_occ_t_c"),
    0)))))))),
    0))

    Then, in test_hazard_premium_c, you could calculate with this:

    ifElse(equal($policy_status_c,"Cancelled"),"0.00",
    ifElse(equal($occ_status_c,"Occupied"),
    ifElse(isInList($bi_program_no_c,createList(1,3)),
    ifElse(equal($premium_freq_c,"Annual"),
    currencyMultiply($sum_insured_c,$px_c),divide(currencyMultiply($sum_insured_c,$px_c),12)),
    ifElse(isInList($bi_program_no_c,createList(2,4,5,6,7,8)),
    ifElse(equal($premium_freq_c,"Annual"),
    add(currencyMultiply($sum_insured_c,$px_c),$add_windstorm_cost_c),add(divide(currencyMultiply($sum_insured_c,$px_c),12),$add_windstorm_cost_c)),
    0)),0))

Children
  • 0 in reply to Patrick McQueen

    Hi Patrick. 

    As I have to also determin if it's occupied or vacant, would the formula in "$px_c" actually need to be like this:

    ifElse(equal($property_status_c,"Occupied"),

    ifElse(equal(subStr($property_type_c,0,3),"Com"),
    ifElse(equal($bi_program_no_c,"1"),related($r_rates_bi_buildings_insurance_1,"p1_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"2"),related($r_rates_bi_buildings_insurance_1,"p2_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"3"),related($r_rates_bi_buildings_insurance_1,"p3_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"4"),related($r_rates_bi_buildings_insurance_1,"p4_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"5"),related($r_rates_bi_buildings_insurance_1,"p5_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"6"),related($r_rates_bi_buildings_insurance_1,"p6_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"7"),related($r_rates_bi_buildings_insurance_1,"p7_com_occ_t_c"),
    ifElse(equal($bi_program_no_c,"8"),related($r_rates_bi_buildings_insurance_1,"p8_com_occ_t_c"),
    0)))))))),
    ifElse(equal(subStr($property_type_c,0,3),"Res"),
    ifElse(equal($bi_program_no_c,"1"),related($r_rates_bi_buildings_insurance_1,"p1_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"2"),related($r_rates_bi_buildings_insurance_1,"p2_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"3"),related($r_rates_bi_buildings_insurance_1,"p3_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"4"),related($r_rates_bi_buildings_insurance_1,"p4_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"5"),related($r_rates_bi_buildings_insurance_1,"p5_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"6"),related($r_rates_bi_buildings_insurance_1,"p6_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"7"),related($r_rates_bi_buildings_insurance_1,"p7_res_occ_t_c"),
    ifElse(equal($bi_program_no_c,"8"),related($r_rates_bi_buildings_insurance_1,"p8_res_occ_t_c"),
    0)))))))),
    0))

    ,

    ifElse(equal(subStr($property_type_c,0,3),"Com"),
    ifElse(equal($bi_program_no_c,"1"),related($r_rates_bi_buildings_insurance_1,"p1_com_vac_t_c"),
    ifElse(equal($bi_program_no_c,"2"),related($r_rates_bi_buildings_insurance_1,"p2_com_vac_t_c"),
    ifElse(equal($bi_program_no_c,"3"),related($r_rates_bi_buildings_insurance_1,"p3_com_vac_t_c"),
    ifElse(equal($bi_program_no_c,"4"),related($r_rates_bi_buildings_insurance_1,"p4_com_vac_t_c"),
    ifElse(equal($bi_program_no_c,"5"),related($r_rates_bi_buildings_insurance_1,"p5_com_vac_t_c"),
    ifElse(equal($bi_program_no_c,"6"),related($r_rates_bi_buildings_insurance_1,"p6_com_vac_t_c"),
    ifElse(equal($bi_program_no_c,"7"),related($r_rates_bi_buildings_insurance_1,"p7_com_vac_t_c"),
    ifElse(equal($bi_program_no_c,"8"),related($r_rates_bi_buildings_insurance_1,"p8_com_vac_t_c"),
    0)))))))),
    ifElse(equal(subStr($property_type_c,0,3),"Res"),
    ifElse(equal($bi_program_no_c,"1"),related($r_rates_bi_buildings_insurance_1,"p1_res_vac_t_c"),
    ifElse(equal($bi_program_no_c,"2"),related($r_rates_bi_buildings_insurance_1,"p2_res_vac_t_c"),
    ifElse(equal($bi_program_no_c,"3"),related($r_rates_bi_buildings_insurance_1,"p3_res_vac_t_c"),
    ifElse(equal($bi_program_no_c,"4"),related($r_rates_bi_buildings_insurance_1,"p4_res_vac_t_c"),
    ifElse(equal($bi_program_no_c,"5"),related($r_rates_bi_buildings_insurance_1,"p5_res_vac_t_c"),
    ifElse(equal($bi_program_no_c,"6"),related($r_rates_bi_buildings_insurance_1,"p6_res_vac_t_c"),
    ifElse(equal($bi_program_no_c,"7"),related($r_rates_bi_buildings_insurance_1,"p7_res_vac_t_c"),
    ifElse(equal($bi_program_no_c,"8"),related($r_rates_bi_buildings_insurance_1,"p8_res_vac_t_c"),
    0)))))))),
    0))

    )

     

    HAZARD PREMIUM FIELD

    Then once I have the correct rate field the acutal calculation of the cost is

    divide(add(Multiply($sum_insured_c,px_c),$add_windstorm_cost_c),$premium_freq_c)

    $premium_freq_c returns 1 if "annual" is selected and 12 if "monthly" is selected.

    I can use the same calculation for every program, even if here is no "add_windstorm_cost", as it would just add zero

    The big job is getting the correct rate field to use in the calculation. it's all those IF's that are causing speed issues. Particularly as I need almost exactly the same again to correct field to calculate the GL cost.

    Would I be better doing this externally and updating the record using the API or with a custom module?

  • 0 in reply to Patrick McQueen

    Hi Patrick

    Just though I'd let you know I've resolved this and now have two solutions.

    1. I've split the formula in to 16 elements 2 for each 'Program' being 'Hazard' & 'GL' each returns "" if it's not the correct program. I then have a field that collects the calculated result from the correct block above. this works well with no delays
    2. Using the Sugar API and Integromat (integromat.com worth a look) Using this I am able to collect all the data from Sugar and perform just the calculation I need, without nested IF's. I have a field in Sugar called [field_name] which builds the name of the field to use in the calculation, based on the options selected. Once passed to Integromat, Integromat finds the field, does the calculation and puts the result back in the Sugar record.