[Solved] Help with filter condition

Creating and using forms
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] Help with filter condition

Post by gkick »

Hi,

Am a bit stuck with the following:

I have a maintenance table based form. Among other fields there is a boolean complete field and a DueDate (date)
field.

Once a record is flagged as completed, I clone the record and update the DueDate of the new record to today's date plus days of maintenance cycle, then refresh the form.
All works fine. The form is filtered to show only records where complete is false.

Now I want to show the new record within 90 days prior to today's date and struck two problems. First the filter logic, which I can not get my head around the first part still being

( "tbl_m_Tickets"."complete" = FALSE )

and the second part something like this... Duedate > DATEADD( 'day', 90, CURDATE() )

not withstanding that the second expression maybe wrong I don t seem to be able to use it in the forms filter property.

So, once I get the syntax right, do I need to run the filter via macro on form open or should I change the forms datasource to a query ?

Appreciate your thoughts, thank you
Last edited by gkick on Tue Oct 27, 2020 4:29 pm, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help with filter condition

Post by Villeroy »

SELECT ...
FROM ...
WHERE DATEDIFF('day', CURDATE(), "Duedate") < 90 [assuming that "Duedate" is in the future]
-----------
WHERE DATEDIFF('day', CURDATE(), "Duedate") < -90 [assuming that "Duedate" is in the past]
or
WHERE DATEDIFF('day', "Duedate", CURDATE()) < 90 [assuming that "Duedate" is in the past]
--------------------------------
In order to get your head around functions, display the calculation results and play with the arguments.
SELECT *, DATEDIFF(...) AS "ddiff" FROM "TBL"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Help with filter condition

Post by gkick »

Thanks you Herr Villeroy, much appreciated
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Post Reply