Dynamic Formulas for Calculation

I understand dynamic inputs, but how can I deliver dynamic formulas to calculate something for a user?

Each user would use a different formula to calculate.

User 1: = (input1 + input2) * input3
User 2: = input1 + input2 * input3

I want to save the formula to the DB and calculate based on current users formula.

you could try having two different hidden inputsâ€¦one with formula A and the other with formula B. Your workflow for saving to the database would have a conditionalsâ€¦only whenâ€¦so it select the correct (A or B) input to save.

1 Like

@Robert thanks for they reply! In my case, each user would have a different formula. So I would have an infinite number of formulas. I would need to create hundreds of if/then conditionsâ€¦or more.

Iâ€™m wondering if thereâ€™s a cleaner way.

Thatâ€™s a tough one. I am not sure if this is possible.

@Robert I know I can dynamically store a URL for a google sheet. I could save the equation to a google sheet, use the Google Sheets API to populate cells with inputs. But Iâ€™m not sure if I can calculate the price in google sheets and return the result to my app quickly (or at all).

Do you have any experience with something like that?

how are the users going to select their equation?

The best approach probably depends on a number of factors, a couple of which are:

• How sophisticated the math is.
• How performant it it needs to be.
• Your level of skill/comfort implementing and maintaining a given solution.

I found this plugin in the Bubble Marketplace, which might be worth a look. Donâ€™t take this as an endorsement, though, as I have no experience with it. I just remember stumbling upon it in the past.

-Steve

thanks @sudsy - I was looking at that plugin too. Hereâ€™s more detail on the questions you asked.

• the math is not crazy sophisticated. I run it through math.js now.
• it needs to be extremely reliable. its a core pricing function for a business to set sales prices on projects. I hesitate to use a plugin.
• Iâ€™m comfortable maintaining anything. Or better yet, anything is going to be easier to maintain than what I have. (I have to build a new page/ app for each user now).

How often do formulas change? Is it a one-and-done kind of thing, or will they need to be updated occasionally? And who does the updating - you or the user?

I can imagine a relatively simple approach that stores JSON with tokens representing variables to be replaced with values when the calculation is performed.

1 Like

Itâ€™s a one time setup when a new company signs up. Itâ€™s basically one and done. Maybe some edits over time.

I do the updating. They would give me a new spreadsheet and say â€śhereâ€™s the new way we calculateâ€ť.

You could create the X many formulas needed and build a new user notification system that informs the admin user (you) to assign formula 1 of 10, or 3 of 10 to the new user.

Then in the appropriate place in your app, have conditionals to use a specific (dynamic) formula depending whether the user has that formula assigned to him/her.

If there are not that many formulas then instead of a thing you could set the formula to be an option instead (using Bubbleâ€™s option sets functionality).

@cmarchan thanks for the reply. Iâ€™m not sure how I could save the formula in he database.

I could save text like: â€śX+Y*Zâ€ť and â€ś(X+Y)*Zâ€ť, but I canâ€™t fill for X, Y, & X dynamically.

Ok, so you donâ€™t need a fancy UI. If thereâ€™s a relatively small fixed number of inputs, it sounds like you could get by with:

• Saving a string representing the formula.
• A way to associate named inputs within the string with numeric values.
• A way to â€śevaluateâ€ť the formula after replacing the named inputs with the values.

I have an idea I might tinker with when I get a chance. Formulas are definitely a Bubble weak point - i.e. the UI actually gets in the way. Itâ€™d be nice to have a generalized approach to dealing with them.

1 Like

Database

An option set called â€śformulaâ€ť with 3 options:

3 users each with one different type of formula

UI with formula conditionals for each user in the text element

Summary

• Create options for the different formulas. In the above example there are 3 different formula options
• Condition the calculation of the formula depending on the userâ€™s formula option

Hope this helps!

1 Like

@cmarchan this does help! First, Iâ€™ve never used options sets. Second, this led me down a path which made me think: â€śthis is simple, why didnâ€™t this of this?â€ť. Which is exactly how I should feel.

I built exactly what you laid out and called it â€śeasy modeâ€ť. I then built a â€śhard modeâ€ť which uses more complex formulas with math.js external API. The â€śhard modeâ€ť still uses your same principle.

Under â€śhard modeâ€ť you have to click the calculate button to trigger a WF to run the math.js calculation. I built a new step in the WF for each user, and set a condition to run that step only if the users formula is XYZ.

Hereâ€™s the editor: Dynamicformula | Bubble Editor

Hereâ€™s the actual page: https://dynamicformula.bubbleapps.io/version-test/dynamic_formula?debug_mode=true

This will work for now. But itâ€™s not completely scalable. I could have a workflow with 40 steps and only 1 of the 40 steps would actually run on each workflow. It seems clunky but it will work.

1 Like

I am glad to hear that the suggestion opened up an avenue to approach the functionality that you are trying to build!

When you have many conditions I have learned that it helps to figure out a â€śprevious conditional layerâ€ť to simplify the logic by which one scenario takes place vs another. I cannot suggest how exactly to do this for your use case (I have not looked thoroughly through the editor that you kindly shared â€¦ I am swamped with to-dos).

Also, it helps me to build logic as modular as possible with the use of custom events for the different routes.

Lego-like thinking so that I can go back in an easier way to fix/change things.

@cmarchan I love it! thanks. With this advice, Iâ€™ll build the most complex calculation first and they copy and paste this one for the next calculation. This way I can delete as needed and move quickly. Thanks again!

1 Like

Excellent approach, I am working on something similar, and I did not know how to start, but know I have a clue. Tks