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

Module: Quoted Line Item - total_amount field calculation

Paula McGuire

I am needing to advance our pricing and quoting method in our instance. We sell ERP business systems to specific industries. I currently have five different pricing methods. For example, our one pricing method is a fixed rate method in which the total is based on the amount of the product times the quantity purchased/quoted. This is working per the current calculation on the total_amount field. However, we have four other Pricing Factor Options that we are needing to configure so the total_amount field calculates the correct total. 

For instance, products that have Pricing Factor 1 will add 2950.00 to each additional quantity (past 1) that is added in addition to the base price of the item. So our item's base price is 3850.00. If the quantity on the Quoted Line Item is 1, the total_amount field will equal 3850.00. But if the quantity is equal to 2, the total_amount field should equal 6800.00 (3850.00 + 2950.00). 

Each of my products in the product catalog have the Pricing Factor Option along with the corresponding Pricing Factor Amount selected on the Product Catalog level. When the product is chosen on the quote as a quoted line item, the Pricing Factor Option on the QLI item will auto-populate with a relate logic hook calculation that will get the Pricing Factor Option as well as the Pricing Factor Amount on the QLI. The Pricing Factor Amount field on the QLI can then be used in the calculation of the total_amount when the quantity changes. 

I am needing to know how to update the total_amount field on the QLI to incorporate this pricing method/structure.

CURRENT FORMULA IN PLACE:
QLI >> Line Item Total (total_amount)
Look at calculated formula -->

ifElse(and(isNumeric(toString($quantity)), isNumeric(toString($discount_price))),
currencySubtract(
ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
ifElse(equal($discount_select, "1"),
currencyMultiply(ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)), currencyDivide($discount_amount, 100)),
ifElse(greaterThan($quantity, 0), ifElse(isNumeric(toString($discount_amount)),
ifElse(greaterThan(0, $discount_price), negate($discount_amount), $discount_amount), 0),
ifElse(isNumeric(toString($discount_amount)), negate($discount_amount), 0))
)
),
""
)

Parents
  • 0 Sugar Employee Badge

    Hi  ,

    Sorry for the delay on a reply to your query and hope I still can be of assistance.

    I believe I understood what the requirement is and found a way you could apply this pricing structure based on a couple of assumptions:

    - You are using the standard field Pricing Factor on Product Catalogue.

    - For products with what you called "fixed rate method", the Pricing Factor is 0.

    - For products with the Price Factor 1, the Pricing Factor value in the Product Catalogue is 2590

    - This applies for the rest of the Price Factors you have.

    - You are copying the Pricing Factor value from the Product Catalogue into the Pricing Factor field on the Quoted Line Item.

    Assuming the above is true, I added a new field into the Quoted Line Items module called "price_factor_c" with the following formula:


    ifElse(greaterThan($quantity,1),multiply(subtract($quantity,1),$pricing_factor),0)

    The formula explained works as follows:

    - If the quantity is bigger than 1, the field will equal to "multiply the value in Pricing Factor by (Quantity -1)". 

    - If quantity is NOT bigger then 1, then the field will equal to 0.

    - For products where no pricing factor is applied, the field will also be 0.

    After that, you would need to slightly modify the formula of the subtotal field in Quoted Line Items from:

    ifElse(
    and(isNumeric($discount_price), isNumeric($quantity)),
    ifElse(
    and(
    isNumeric($service_duration_value),
    isNumeric($catalog_service_duration_value),
    not(equal($service_duration_unit, "")),
    not(equal($catalog_service_duration_unit, ""))
    ),
    prorateValue(
    multiply($discount_price, $quantity),
    number($service_duration_value),
    $service_duration_unit,
    number($catalog_service_duration_value),
    $catalog_service_duration_unit
    ),
    multiply($discount_price, $quantity)
    ),
    0
    )

    to make it look like:

    ifElse(
    and(isNumeric($discount_price), isNumeric($quantity)),
    ifElse(
    and(
    isNumeric($service_duration_value),
    isNumeric($catalog_service_duration_value),
    not(equal($service_duration_unit, "")),
    not(equal($catalog_service_duration_unit, ""))
    ),
    prorateValue(
    add($price_factor_c,multiply($discount_price, $quantity)),
    number($service_duration_value),
    $service_duration_unit,
    number($catalog_service_duration_value),
    $catalog_service_duration_unit
    ),
    add($price_factor_c,multiply($discount_price, $quantity))
    ),
    0
    )

    What we do here is add the value we calculated on the price_factor_c field to the default "quantity times price" calculation.

    The issue with modifying the subtotal field (or the total_amount field) is that studio doesn't recognise some of the fields in the formula, which means this change will need to be applied via MLP (Module Loadable Package).

    I hope this all makes sense but let me know if you still have any questions / issues applying this logic or if I didn't get the requirement right.

    Best,

    Francesc

Reply
  • 0 Sugar Employee Badge

    Hi  ,

    Sorry for the delay on a reply to your query and hope I still can be of assistance.

    I believe I understood what the requirement is and found a way you could apply this pricing structure based on a couple of assumptions:

    - You are using the standard field Pricing Factor on Product Catalogue.

    - For products with what you called "fixed rate method", the Pricing Factor is 0.

    - For products with the Price Factor 1, the Pricing Factor value in the Product Catalogue is 2590

    - This applies for the rest of the Price Factors you have.

    - You are copying the Pricing Factor value from the Product Catalogue into the Pricing Factor field on the Quoted Line Item.

    Assuming the above is true, I added a new field into the Quoted Line Items module called "price_factor_c" with the following formula:


    ifElse(greaterThan($quantity,1),multiply(subtract($quantity,1),$pricing_factor),0)

    The formula explained works as follows:

    - If the quantity is bigger than 1, the field will equal to "multiply the value in Pricing Factor by (Quantity -1)". 

    - If quantity is NOT bigger then 1, then the field will equal to 0.

    - For products where no pricing factor is applied, the field will also be 0.

    After that, you would need to slightly modify the formula of the subtotal field in Quoted Line Items from:

    ifElse(
    and(isNumeric($discount_price), isNumeric($quantity)),
    ifElse(
    and(
    isNumeric($service_duration_value),
    isNumeric($catalog_service_duration_value),
    not(equal($service_duration_unit, "")),
    not(equal($catalog_service_duration_unit, ""))
    ),
    prorateValue(
    multiply($discount_price, $quantity),
    number($service_duration_value),
    $service_duration_unit,
    number($catalog_service_duration_value),
    $catalog_service_duration_unit
    ),
    multiply($discount_price, $quantity)
    ),
    0
    )

    to make it look like:

    ifElse(
    and(isNumeric($discount_price), isNumeric($quantity)),
    ifElse(
    and(
    isNumeric($service_duration_value),
    isNumeric($catalog_service_duration_value),
    not(equal($service_duration_unit, "")),
    not(equal($catalog_service_duration_unit, ""))
    ),
    prorateValue(
    add($price_factor_c,multiply($discount_price, $quantity)),
    number($service_duration_value),
    $service_duration_unit,
    number($catalog_service_duration_value),
    $catalog_service_duration_unit
    ),
    add($price_factor_c,multiply($discount_price, $quantity))
    ),
    0
    )

    What we do here is add the value we calculated on the price_factor_c field to the default "quantity times price" calculation.

    The issue with modifying the subtotal field (or the total_amount field) is that studio doesn't recognise some of the fields in the formula, which means this change will need to be applied via MLP (Module Loadable Package).

    I hope this all makes sense but let me know if you still have any questions / issues applying this logic or if I didn't get the requirement right.

    Best,

    Francesc

Children
No Data