Display data based on camparing two fields from same table

[tsmakrakis]

I have one table called BAGS with three fields:

BAGS

1. Bag Name
2. TotalStock
3. Monthly Sales

I am trying to display data to a repeating group to show all the bags where:

TotalStock < Monthly Sales

Currently the search function does not let me compare these two fileds.

Also how do I filter results if I want to do:

TotalStock < 2 x Monthly Sales

So to display all the bags whose stock number is smaller than twice their monthly sales?

[adamhholmes]

2 ways that I’m aware of…

The first is to use an advanced filter on the returned search results. That way you can easily filter the results by This BAGS's TotalStock < This BAGS's Monthly Sales.

Note that, unlike search constraints, filtering data is done client-side, so if the dataset is large it might not be good performance wise. But if you narrow your initial search as much as possible, and use Privacy rules to limit the data, it may not be too much of an issue.

The second way would be to add an additional field on the BAGS datatype to record the difference between the total stock and monthly sales, and then you can simply search for items where that field value is negative. It will be much faster (especially with a lot of data) but will require some extra workflow steps to set the data.

[gazinhio]

You can’t do field-to-field comparisons on constraints. As Adam suggested use an Advanced filter if you know your data set is never going to get very large. Advanced filters work client-side so every record in the table that’s not limited by a constraint is retrieved and filtered client-side which is very inefficient on anything other than maximum couple hundred records.

It’s been my experience with Bubble that it’s best to optimise for reads (searches) over writes which often means you prioritise performance over clean database design Therefore de-normalising the database to have duplicated or calculated fields that help optimise speed is the way to go. Therefore I would echo Adam in having a single field that contains the calculation and have a constraint that simply checks for < 0, > 0 etc. You’ll have to update that field whenever necessary but you’ll likely be updating it a lot less often than you’re querying it.

[tsmakrakis]

Thank you already done the extra field but I am fascinating by the advanced filter, where about do I set this if I want to add that filter?

[keith]

It’s part of the expression builder. Click in your expression (Search for BAG) and select the :filter operator. Then you can select the Advanced option, which lets you write a filter expression that will let you examine the properties of each item.

[tsmakrakis]

Ok great found it, I guess I can see now the advanced filter being more slow than adding that extra field as the search brings many more results that then get subsequently filtered where with the extra filed the results come straight away and they do not need to get further filtered…