Alternative to IF Statements? Calculating total product price based on quantity

[Curtis]

We have solved the below with if statement columns but I wondered if there’s a better option?

The task:
User specific pricing calculator.
The user can enter a product quantity and the total price will be returned.
The products have price breaks at various quantities. These price break quantities vary per product.

Example data:
Product 1:
price breaks at the following quantities: 1, 4, 8, 25

Product 2:
price breaks at the following quantities: 1, 5, 10, 25

Product 3:
price breaks at the following quantities: 3, 6, 9, 20

There are columns for each quantity with the product price if it is relevant to that product. Each product row has the following price columns: 1, 3, 4, 5, 6, 8, 9, 10, 20, 25

Our solution was to have a user specific number column for the user to enter their product quantity for the price calculation. Then use three if statement columns for the three price break quantity types. Then have a math column to do quantity x if statement price.

Is this the best solution or are there any other options with queries or arrays etc?

[ThinhDinh]

Do they have to enter the type of product they’re buying? When you say price break, do you mean the price changes for each range? I feel like all of this can be converted into a single JavaScript column.

[Curtis]

The calculator is only accessible on product detail pages so the product is already know as a “this item” value.

Yes there is a set price per unit at each of the quantity breaks/ranges.

[Jeff_Hager]

Can you show a snippet of your data to better understand how you have it set up? I think this can maybe be done with a single IF column (otherwise definitely a javascript column like @ThinhDinh mentioned), but I want to make sure I understand how the data is structured. Is it safe to say that the 1 quantity column always has a price?

[Curtis]

No on a product which is type 3, there is no price for 1 quantity only from a quantity of 3 as below:
Product 3:
price breaks at the following quantities: 3, 6, 9, 20

See below our current solution.

The first three if columns as for each of the 3 product types. As there is some overlap with product type 1 and 2 (both of them having a price for a quantity of 1) we have separate If columns at the end to display the 1 quantity price depending on product type.

[gvalero]

Hola @Curtis

I think it can help you if I understood your scenario.

What you want to get might look like this:

The user enters the Product ID and its Qty and base on your Price breaks range, the APP returns the final price, right?

The key here is work with a list (a kind of array) to gather/link prices and their price breaks.

Your main price table must look like this one:

but the logic to catch the user data and find out the associated price based on qty should be in another table, my first image above…

The Javascript code that you need to get all this is this:

let arrQty=p2.split(",").map(Number);
let arrPrice=p3.split(",");
let price=0;

arrQty.forEach(myFunction);
function myFunction(value, index) { 
    if (p1==value || p1>value )
        price=arrPrice[index]

    if (p1> value && p1 < arrQty[index+1] )
        price=arrPrice[index]
}
return price

Let me know if you need an additional help to understand what I tried to do to you.

Saludos!