Search constrained by relationship between fields

Hi,
I wonder if anyone knows if this can be done with Bubble…

Assume you have a table with two date fields: Due_Date and Completion_Date.

II want to construct a Search to count all the records where Completion_Date > Due_Date
In this case, the restrictions relate two fields of that same table.

Is this possible?

I know I can solve the problem by creating a workflow on a list, passing all the records in my table, and using a temporary table to hold the sum of the number of records that meet the condition, but I wanted to find the result just by doing a search.

Many thanks in advance,

Alex

1 Like

Someone here gave me some good advice a while back which was that you should calculate and store anything you’ll need as soon as possible. That way you can just search for it instead of having to do logic in the search.

So, in this case, just calculate whether or not completion > due and put it into a Y/N field whenever one of the dates changes.

If you need to initialize it you can run a batch edit one time to do all the calculations and store the result in the Y/N field. From then on it should take care of itself.

1 Like

I understand what @blueback09 is saying about calculating and storing values BUT, it’s not really good database practice. Good for convenience of accessing the data. NOT good for database integrity. As the program/app evolves, it’s easy to overlook the extra steps needed to keep such derived data accurate.

In “real” database programming, it would be a simple thing to select based on a comparison of two fields in the row. I don’t see how this is done in Bubble. In a search constraint, Bubble lets you refer to a field in the thing on the left side of the constraint statement but not on the right.

It is odd to me that Bubble does allow you to select another field from the same thing for the right side of the comparison. I’d guess you can do a search to get the same record but I couldn’t immediately see how to do it.

2 Likes

Yeah, but Bubble has strong constraints on how you can use its database. Your screenshot demonstrates one of those constraints. Since you can’t do much calculation or boolean logic inside a search, it’s better to pre-calculate those steps and store them so the search is simpler.

1 Like

Understood.
There’s a lot to love about Bubble. Picking it up after letting 30 years of programming skill get rusty, I’m amazed at what I’ve been able to build in just two weeks part time. Still, I’m seeing some frustrating limitations based on past database and procedural language programming.
Thanks for the dialog.

1 Like

Same. I’m trying to setup a second server where I can duplicate my database and run real analytics/charting. I thought I’d be able to hack bubble into a good enough solution but its limitations on repetition, recursion, and batch processing mean that it’s great right up until you need something more than CRUD. Awesome for CRUD. I can build a CRUD app in a few hours and make it live to the whole world, but I can’t run a repeat loop in real time or use queries on the database.

1 Like

image

Simple in “irreal” database programming too :slight_smile:

4 Likes

Nigel,

I’m trying to understand the context of your screenshot. It looks like you’re directly responding to @alex5’s question. You’re getting the count of things matching the criteria.
This is great! Thank you.

I went in a slightly different direction, with the intent of filling a repeating group with the things that meet the criteria. Can the same :filtered and Advanced: method be used to select what fills the repeating group? How do you get the Advanced: prefix in the List filter dialog?

Sorry if these are lame questions. I just don’t have time to dig into the Bubble editor right now to see if I can find these options.

Yes, was responding to Alex. I think !

Advanced filter is at the bottom of the filter dialog.

And yes, you can fill an rg this way.

1 Like

Thank you. I have so much to learn.

Thank you @blueback09, @laurence and @NigelG for your answers.

I agree with Lawrence’s point about not having unnecessary fields in a table. (I hate doing that, and I already have to do it to sort by fields that are actually “things”), so Nigel’s suggestion is right on point.

That said, I also like @blueback09’s advice of precalculating what you need in advance. This will certainly reduce the query time, especially considering that constraining through filtering is less efficient than doing so through the search.

I’ll play a bit with both but have the feeling that, in this particular use case, the pre-calculation approach may work better.

Many thanks once again !

Alex

I am lucky I that when I first started with Bubble it was just an input box and a big button (am joking !).

There is a lot to learn, and it is surprisingly deep, although if you are used to thinking in a certain way (my database and programming experience is also from quite a while back) it can be unsettling.

Certainly some things that are not quite there yet at the moment, but they will come.

2 Likes

Thanks, Nigel.

On another topic, I can use some help learning what I need to learn to get the right UI effect. Since I don’t want this thread to go off topic, can you take a look at the thread on drag-drop elements.

Thanks.

Hi Nigel, your tip made my day. Thank you so much

1 Like

Thank you :slight_smile: