Ponderated calculation in databse

Hello community. I need help to understand how I could execute a calculation from database data.

Let’s assume I have a table with “Main Topics” and a table with “Detailed Items”.

  • Main Topics has a text field for name and a number field as a ponderation. Let’s say we have 3 records in this Main Topics Table. Each with different ponderation values.
  • Detailed Topics has a text Field for description, a Main Topic as a reference and a ponderation as well.
  • A Main Topic can have up to 6 Detailed Items.
  • A Users table has a field which is a LIST of Detailed Items. The number of items in this list lay vary from 0 to n

I want to calculate an agregated value of a User’s overall score based on the items in the LIST of Detailed Items.

I would know how to do it in CSharp or such languages as a function, but here i wonder how I could do so in Bubble ? Javascript function ? Grouped Items ? Do Search embedding Group by ?

Not easy…

Ok, assuming you mean some kind of weighted arithmetic mean, then you’ll probably need to break your calculation down into steps

Create a field to do your calculation in steps, you might need to be using do a search for:sum * your calculated previous field.

If none of this is close, then maybe go into more detail on what PONDERATE means for the layman and you’ll get more assistance

You are right, what I call Ponderation is “Weight”.
And I also forgot to say that each record for Detailed Topic has a Value Field
Let’s be a bit clearer with example :
Main Topic topic contains the following 2 records :

  • Name = “Topic 1”, Weight = 0.4
  • Name=“Topic 2”, Weight = 0.6

Detailed topic table contains the follwoing 4 records :

  • Name = “Detail 1”, Weight= 0.25, Main Topic = Topic 1, Value = 4
  • Name = “Detail 2”, Weight= 0.10, Main Topic = Topic 1, Value = 2
  • Name=“Detail 3”, Weight = 0.45, Main Topic = Topic 2, Value = 6
  • Name= “Detail 4”, Weight = 0.33, Main Topic = Topic 2, Value = 3

The result I want to get by dynamic calculation would be : 2.694

Details for Topic 1 are Detail1 and Detail2
Detail 1 : 0.25 x 4 = 1
Detail2 : 0.10 x 2 = 0.2
Detail1 + Detail2 = 1.2
Weighted value for Topic 1 = 1.2 x 0.4 = 0.48

Details for Topic 2 are Detail3 and Detail4
Detail3 : 0.45 x 6= 2.7
Detail4 : 0.33 x 3 = 0.99
Detail3 + Detail 4 = 3.69
Weighted value for Topic 2 = 3.69 x 0.6 = 2.214

Weighted value for Topic1 and Topic2 combined would be 0.48 + 2.214 = 2.694

Not easy right ?

@contact.info

Using :format as text together with @richard10 recommendation of breaking things into pieces (perhaps, using groups of type number as pieces to house the result of pre-calcs).

Here a great video by @eli showing how :format as text can be used for calculations (and within this method you can use pre-calc groups as well)

ok, Thanks for the tip. I looked at the video.
I find the method really uggly and not professional at all. It’s more like a workaround, not an elegant solution I’m looking for.
I’ll continue to investigate some intermediate calculation steps, States as list ot things like that.
Thanks

If you have the skill set to code this in JS there is no reason to try to make this happen in native Bubble. Custom plugins are your friend :slight_smile:

2 Likes

Private plugins for apps are a real blessing, particularly for Server Side Actions. They strongly promote modular design and code reuse. Being forced to break tasks down into small components that fit inside individual plugin actions is what many people spend the entirety of a computer science degree to learn.

1 Like