[SOLVED] Search data between today and 30 days previous

Hi all,

I can’t figure out why this conditional is red text.

The default for this text shows the invoice’s Due-date, and the conditional should show the words ‘Upon Receipt’ When the Due-date is today’s date.

The invoice is created inside a group on the index page (single page app) and the User selects date choices from a popup. Then, the save button sends the popup data to the DB (see below) before navigating to a new page “preview-html” to preview the invoice.

Your conditional needs to say when something is true. All you’re saying here is to take the due date and add 0 days to it. There is nothing to prove as true or false.

Try saying when Current Page Invoice’s Due-date is Current Time/Date

2 Likes

It’s red because it’s not complete (it doesn’t make sense as it is)…

A conditional has to evaluate to either yes or no. Currently your conditional evaluates to a date/time (although even that doesn’t make sense - you’ve got a date plus zero days, which is the same as just the date on it’s own).

In any case, you need to finish the expression so that the output is a yes/no (true or false).

1 Like

You should change the 0 with the number of max days to pay the invoice, let’s say 14 and then continue the expression by comparing it to the current date.

Thanks, I have tried everything I can think of but I always see red error text. What I want to say is When Current page's Invoice's Due-date is Current date/time then show ‘Upon Receipt’ .

For more context, here is the popup Data source for Current date/time. If a user choses this date, then the Current date/time shows, which is fine, but it looks better on the invoice to simply say 'Upon Receipt’.

And for added context, here is what the Data source shows if a user selects payment due 7 days from Current date/time.

The max days has to be 0 because what I want to say is When Current page's Invoice's Due-date is Current date/time then show the text ‘Upon Receipt’ . Since the option to refer to Current date/time is not available, I am trying to refer to Day +0 (ie. today).

I just want an option for the user to choose ‘Upon receipt’ which doesn’t need to be a date/time at all, it could be text. But, I can’t even do that!

If that’s what you want to say, then say it - that’s a perfectly valid expression for a conditional (it’s either true or it’s false).

But I doubt that really is what you want to say, as that will only be true for a single millisecond…

I’d guess that what you’re really trying to do is show the alternative text if the due date falls on today (or presumably any date prior to today as well). In which case you can simply use when due date < current date time: +(days) 1: rounded to Day

I can’t complete this expression as I am being asked to format it as text or number, both end up with red text.

Screenshot 2022-01-28 190726

I can’t, there is simply no option to select ‘Current date/time’. So, I am doing something very wrong.

I’ve even tried just having text, so the user can literally just choose a text input. But when I head over to the html-invoice page and try and show this result as a conditional I am unable to refer to the text input.

Try it the other way around (Bubble has some strange limitations with the way you can write conditionals and expand on certain expressions).

But do it backwards and it should work just fine.

i.e. current date/time +(days) 1: rounded down to Day > parent group's Invoice's Due Date

It works fine for me.

1 Like

That worked! I was really impressed with this part > parent group's Invoice's Due Date - I would never has thought of that solution. Thanks very much, I tried for best part of 2 days on this crazy little problem!

1 Like

@adamhholmes I would like to search for data from the last 30 days but the only options I see forcurrent date/time +(days) has the ‘+’ sign, I can’t see how to search for -(days)?

Just put -30 as the value.

1 Like

This didn’t work but I think the issue is not with the line current date/time +(days) -30but rather with something else. I did a test to show ‘invoices Paid’ for today (current date/time)

When a user marks an invoice as Paid, they click a button and I’ve added ‘Paid-date’ as a field in the DB.

We can see this works:

Now, all I need to do is show that amount on my website. I search for Status = Paid and current date/time but the text box is empty when viewed in Preview? The other two text fields work perfectly, the only difference is they are not limited to today so they don’t have a Paid-date filter.

I did a test to show ‘invoices Paid’ for today (current date/time )

‘Current date/time’ is not synonymous with ‘Today’. I think you’re misunderstanding the concept of a ‘Date’ in Bubble.

Dates are a timestamp - the precise number of milliseconds elapsed since 1st Jan 1970 00:00:00 UTC.

Current date/time means ‘Now’, as in this very millisecond.

When you store a date using current date/time you’re storing that precise timestamp at the moment the workflow action runs.

When you’re searching for a Thing’s date using current date/time you’re searching for dates that match the precise timestamp that the search is being made.

There are 86.4 million milliseconds in a day - so trying to match a Date (a stored timestamp) with the current date/time (the exact millisecond that the search is being done) and expecting it to find anything is (for all intents and purposes) never going to return what you’re looking for.

You need instead to search for invoices who’s paid date (a timestamp in milliseconds) falls within the current days milliseconds - i.e. it is an equal or larger number than the timestamp of midnight this morning, AND a smaller number than the timestamp of midnight tonight (although in your specific case, assuming an invoice can never be paid in the future, you don’t really need the upper limit if you’re only ever searching for Today. If you’re searching for any day in the past then you will need the upper limit).

Thank you for taking the time to explain so clearly. I understand what you’re saying and I have updated the search to look for invoices with a Paid-date of current date/time (the current millisecond) all the way back 30 days, but still it returns no results?

but still it returns no results?

No, it won’t return any results as you’re still asking it to check a single timestamp (unless there happens to be an invoice that was paid at the precise millisecond of the date you’ve told it to search for - i.e. 30 days prior to the moment the search was made).

image

If you want it to return invoices paid within the last 30 days you need to tell it to return results with a paid-date which is larger than the specified date (i.e. paid-date > current date/time +(days)-30

1 Like

Ah thankyou that worked, thanks for sticking with me. For some reason this was very unintuitive.

I would have expected current date/time -30 daysto mean any data between this exact millisecond and 30 days ago. But, I see now that it means this exact millisecond minus exactly 30 days (which would be an exact millisecond).

The fact that we need to put a larger than sign is confusing because I am search the past 30 days, something isn’t clicking for me but I guess I know a little more than I did yesterday! Thanks again :slight_smile: