Forum Academy Marketplace Showcase Pricing Features

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?

users provide the formula when they sign up. they give me a spreadsheet.

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


Premium Bubble Plug-Ins

thanks @shot - 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”.

Hi @brad.h

It sounds like a new thing called “formula” could help you.

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

@brad.h Please review the following hypothetical way to approach this:

Database
image

An option set called “formula” with 3 options:
image

3 users each with one different type of formula
image

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:

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: https://bubble.io/page?type=page&name=dynamic_formula&id=dynamicformula&tab=tabs-2

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

@brad.h

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