Average cost IF criteria is true

Need help Database
1

I haven’t actually started with Bubble yet, but I’m wanting to see if it can do what I need before I jump in.

Is there a way that I can have a column in my database return an average of all rows that meet various criteria?

For example:

My rows are items, and each item has a size, a cost, and a True/False boolean field.

I want the average column to show the average cost of all items with the same size that are also TRUE in the boolean column. I also need it to ignore any items that do not have a cost entered yet.

Is it possible to do this?

2

Here’s an easy way to get that average in Bubble:

What is possible

You can calculate the average on-the-fly anywhere you need it (text box, repeating-group cell, backend workflow, etc.) with a single dynamic expression:

Do a search for Items
   (constraints: 
       Size   = Current Item's Size
       In-stock? = yes          ← your boolean
       Cost    is not empty)
's Cost :average
  • Constraints: add as many as you like; Bubble only keeps rows that match every one.
  • :average operator: turns the list of numbers into one numeric value. (Bubble Docs)
  • Empty / null costs are filtered out by the Cost is not empty constraint, so they won’t skew the result.

If you need the figure for each size, put a Repeating Group on the page, set its data source to
Search for Items :filtered (In-stock? = yes and Cost is not empty) :grouped by Size
and inside the cell show Current grouping's Cost :average.

What isn’t built in

Bubble’s database doesn’t have “calculated fields,” so you can’t store a live-updating average in a column automatically. If you really need the value saved (for performance, reporting, etc.), schedule a backend trigger to recalc the number whenever an Item is created/updated and write it to a numeric field like Average cost for this Size.

I hope that help you! :slight_smile:

1 Like
3

Awesome! Looks like Bubble may be the solution I’ve been looking for. Thanks!

4

This won’t achieve what the OP is asking (you can’t self-reference items in a Bubble search, and displaying the average of ALL item’s Costs is not what’s being asked here).

Tell ChatGPT it’s wrong.

@joshuatuckr

The only way to acheive this in Bubble is as follows:

You’ll need 2 Repeating groups (or any list data that accepts Groupings as a data source).

RG 1.

Search for Items where the Cost is NOT EMPTY and the Boolean is TRUE

Grouped by Size with an aggregation of Avg of the Cost field.

Then, in a second RG (the one you’ll use to display the items):

Search for Items where the Cost is NOT Empty and the Boolean is TRUE

Then in the RG Row, where you need to display the AVG you need to refer to the First RGs list of Groupings, filtered where the Size is the Size of the Current Cell (of the second RG): first item AVG.

Note: this kind of filtering can ONLY be done client-side and so this is not suitable for large amounts of data.

1 Like