Products with Custom Pricing Formulas

Need help App Organization
1

Hello everyone! I’m facing a problem while developing an application and could really use some help.

I own a visual communication company, and each product has a list of materials that make it up. To calculate the final price of the product, I need to calculate the cost of each material. However, each material has different variables and unique formulas for calculating its final cost.

For example, the ABC PANEL is composed of ADHESIVE X and PLATE Y.

To calculate the cost of ADHESIVE X for this panel, I need to know the HEIGHT and WIDTH of the panel, and the formula is:
HEIGHT * WIDTH * AdhesiveCost

To calculate the cost of PLATE Y, I need the HEIGHT, WIDTH, and also the DEPTH, and the formula is:
((HEIGHT * WIDTH) + DEPTH) * PlateCost

I have a product database where, during product registration, I link (using another data table) all the necessary variables for calculating the materials of the product (which are diverse, so I cannot standardize the variables. In the example above: HEIGHT, WIDTH, and DEPTH). I also link (using another data table) all the materials that make up the product, each with its respective formula. In the formulas, I use the variable names so that I can later replace them with the variable values provided by the user when creating a quote.

When quoting, after the user selects the ABC PANEL, the product configuration popup displays two tables (or repeating groups): one with the list of all materials that make up the product, and another with the list of all variables needed to calculate the product price, so the user can fill in the value of each variable for that specific quote. Here’s a mockup of this configuration screen during the quote creation process:

Mockup
Mockup895×379 47.8 KB

I managed to make this work for a single material outside a repeating group using the Orchestra plugin:

  1. I fetch the material’s formula.
  2. Loop through the rows in the repeating group of variables.
  3. Replace the variable names with the values provided by the user for each row.
  4. With this, I get the final formula, and I use Math.js to calculate the value.

Since I don’t know which of all the product’s variables are present in a specific material’s formula (for example, the DEPTH variable is only used in the formula for PLATE Y), I have to loop through all the variable rows.

However, for the example above, with multiple materials in a repeating group, I need to:

  • For each material, fetch its formula.
  • Loop through the variables, replacing them to finalize the formula.
  • Then calculate the cost.

I tried using a Maestro element outside everything, activating Musicians inside each material row. These would, in turn, activate another Maestro inside the row to trigger the variables’ Musicians. But it didn’t work!

I’m having trouble planning this part of the application. I’m not sure if I’m on the right track or if there’s a better way to achieve this. I haven’t developed much of the app yet (especially this part), as I’m trying to design the functionality first to build it correctly. I tested the Orchestra plugin with the scenario of just one material and multiple variables, and it works perfectly in that case. The challenge is to repeat this process for multiple materials inside a repeating group rather than just a single material on the page.

I’m not necessarily looking for a solution, but I’d like to know if you’re aware of any applications with similar functionality that I could analyze, or if you have any ideas on how I can approach this. I’ve searched a lot in the community, tutorials, and online but haven’t found anything that addresses this specific issue or an app with similar functionality to use as a reference.

Thank you so much!!

2

The product calc sounds quite involved. You’re likely much better sending it to a backend workflow to process there.

How I’d structure it:

  1. backend api workflow to “calc” the order
  2. separate custom events to handle each type of calculation (can input values and output result)
  3. use “api workflow” and pass the list of products into it and then calc each item and minus it from the list, when all calced then run a custom event to sum the totals, add taxes etc
  4. write the calc back to the datas
  5. if the calc takes time to process I’d also add a progress/updater for the front end and a loader there to entertain the user

A better way would be to calc the product when it is added to the order, but it sounds like product calcs are dependent on other products details, and I guess users can add and remove products during order process which would change how products calculate - forcing the calc to run multiple times (each time a product changes on the order). In that case the best way would be to calc once the user has finalized the order options rather than for each product add/remove.

Now you could do all this on the front end but that gets very tricky when you try to iterate over a list on the front end and if they user interrupts the iteration then things get messy quickly. Do it on the backend and it’ll be easier and you can call it from anywhere in the app.