Ignore empty constraints - No result with empty constraints

jf1 · 2023-01-09T15:29:19.700Z

Hi guys!

I´m a little confused while building a report.

I´ve got some contracts in the database.
Some of them have an “end date”, some of them have an empty “end date”.
I am trying to get all “active” ones for each month to get an overview which will be “active” in the next months.

contracts700×412 46.5 KB

Actually there is no result.
When i delete the constraint “contract end” (“Vertragsende”) I get a result.
So i am pretty sure the problem is, that there are empty “end dates”.
I thought the option “Ignore empty constraints” would count them anyway, but it does not work.

Looking forward to see where I was dumb today

Best regards

mikeloc · 2023-01-09T15:37:13.803Z

Hi there, @jf1… if I am seeing/understanding things correctly, your constraints don’t make sense. You are trying to search for things where their dates are less than or equal to the 31st of the current month and where their dates are greater than or equal to the 31st of the current month, and again, that doesn’t make sense. What things are you trying to get with that search?

Best…
Mike

jf1 · 2023-01-09T15:40:04.723Z

Hi @mikeloc !
Thanks for your reply!

These are 2 different constraints.

“contract start” → look for every contract started before or on 31st of this month.
“contract end” → look for every contract ending after or on 31st of this month.

Best regards
Jan

mikeloc · 2023-01-09T15:47:29.688Z

Oh, geez… sorry about that… I saw them as the same because I’m, well, dumb.

Are you trying to get contracts that started any time on or before the 31st of the current month and that also end any time on or after the 31st of the current month? Or are those constraints supposed to be independent of each other?

Apologies that I’m still not understanding what’s going on here… please refer to my aforementioned dumbness.

dorilama · 2023-01-09T15:50:48.159Z

Maybe the problem is that an empty date will never be bigger than a date, so that condition will exclude any record that has an empty “end date”.
Ignore empty constraint works for the value of the expression (ignore the constraint if the result of the expression is empty), not the value of the record.

jf1 · 2023-01-09T15:51:06.058Z

@mikeloc

I´ve got one date field for the start date
and
one date field for the end date
for each contract.

The “end date” is empty (because our customers love us, but thats not the point) because our the contract end is open - like we do not know yet when the customer (or us) will end the contract.

I want to see which contracts have started but not ended. In this example for this month.

jf1 · 2023-01-09T15:53:03.408Z

@dorilama thanks for your reply!

I´ve thought on something like this also.
I am not sure, but I think that bubble is using ISO8601 time, so a “date” is counted from a reference date. Maybe a date field can NOT be empty?!

I mean that would show why this is not working, but i would not be that a happy to set a random date in the future to make my report running…

mikeloc · 2023-01-09T15:56:17.742Z

Because some of your contracts have an empty end date and some of them don’t, try moving the end date constraint to an advanced filter that looks something like this?

end-date882×443 32.9 KB

The start date constraint would remain on the search, of course.

start-date873×449 30 KB

dorilama · 2023-01-09T15:56:35.134Z

An empty date in bubble is null.
null > new Date() is always false.

jf1 · 2023-01-09T16:00:32.408Z

@dorilama
I am not sure if i get that right but if I do, I have to set an end date which is anywhen in the future to make my report running?

Or do you have an idea for another solution for my needs?

((Setting a field like “contract end date available” would not work because I need to know when it ends to do my ressource planning.))

dorilama · 2023-01-09T16:06:31.003Z

You can use advanced filters like Mike suggested.

Also, if you want contracts taht have started but not ended, and you fill the end date only when the contract ends, you can just look for contracts with “end date” empty.
If “end date” can be set before the contract actually ends you can add a boolean flag to the contract to say if it’s ended or not and use it for searches. Then you can have a workflow setting it to the right value when the contract ends.

jf1 · 2023-01-09T16:18:04.397Z

@mikeloc @dorilama
Thank you both!
I´ve never used it before, but I´ll try the advanced filters now.

Best wishes
Jan

mikeloc · 2023-01-09T16:19:13.421Z

FYI… when you go to set up the advanced filter, you will find the Advanced option all the way at the bottom of the dropdown where you are adding a constraint to the filter.

jf1 · 2023-01-09T16:24:09.497Z

@mikeloc Thank you so much man!!!

I was wondering like “is this filter option in a repeating group different from a text field” …

Have a nice evening!

jf1 · 2023-01-09T16:49:35.279Z

@mikeloc
Your solution worked perfectly!

I´ve to figure out now when building sums how to break them down to days, when the contract ends inside a month - like on the 20th.
But I´ll figure that out I think

Best wishes and thank you so much!
Jan

system · 2023-03-20T15:30:03.567Z

This topic was automatically closed after 70 days. New replies are no longer allowed.