[Solved] Query based on form fields

Creating and using forms

[Solved] Query based on form fields

Postby TondaBucek » Wed May 20, 2020 9:30 am

Hi, I want to do in my opinion easy task, but it drives me crazy.
I have a simple table with DATE column. The only think I want to do is make form, in which user select to dates in date input field and the form displays all records from the table between this two dates.

I created this sql which works fine: SELECT * FROM "MyTable" WHERE "Date" >= {D '2020-05-15' } AND "Date" <= {D '2020-05-16' }

But instead of fixed dates I want it replace by user input. So I thought that user will select the dates in form and I attach somehow this sql to push button. But I don't know how.
I don't insist on using the push button', but it must be somehow controlled from the form, not from oo Base filtres etc.
Anybody any idea, pls?
Thanks
Last edited by robleyd on Fri May 22, 2020 12:08 pm, edited 2 times in total.
Reason: Tagged [Solved]
OpenOffice 4, Windows
TondaBucek
 
Posts: 3
Joined: Wed May 20, 2020 9:20 am

Re: Query based on form fields

Postby Villeroy » Wed May 20, 2020 11:34 am

menu:Tools>SQL....
Code: Select all   Expand viewCollapse view
CREATE TABLE "Filter"(ID INT PRIMARY KEY, DT1 AS DATE, DT2 AS DATE);
INSERT INTO "Filter" VALUES(0, NULL, NULL);

menu:View>Refresh Tables.
-------------------------------------------------
Bind a form to SQL statement:
Code: Select all   Expand viewCollapse view
SELECT * FROM "Filter" WHERE ID=0

Important: On the form's data tab disable everything except "Allow Modifications" (no toolbar, no tabbing into records other than the one with ID=0)
Add 2 date controls for the from-date DT1 and the until-date DT2 with optional input ("Input Required" = No)
-------------------------------------------------
Bind a subform to this statement:
Code: Select all   Expand viewCollapse view
SELECT "MyTable".*
FROM "MyTable"
WHERE ("Date">= :pDT1 OR :pDT1 IS NULL) AND ("Date"<= :pDT2 OR :pDT2 IS NULL)

Add a push button with Action = Refresh Form" to the subform and design the subform as you like.
------------------------------------------------
Now you can enter one, two or no dates into the filter form and refresh the subform which is filtered by the parameter query.

You may create another subform based on an aggregation query like this:
Code: Select all   Expand viewCollapse view
SELECT AVG("Amount") AS "Average", MIN("Amount") AS "Minimum", MAX("Amount") AS "Maximum"
FROM "MyTable", "Filter"
WHERE "Filter"."ID"=0 AND ("MyTable"."Date">= "Filter"."DT1" OR "Filter"."DT1" IS NULL)
  AND ("MyTable"."Date"<= "Filter"."DT2" OR "Filter"."DT2" IS NULL)


You may create a report based on this query and the report will use the same filter that has been set in the filter form
Code: Select all   Expand viewCollapse view
SELECT "MyTable".*
FROM "MyTable", "Filter"
WHERE "Filter"."ID"=0 AND ("MyTable"."Date">= "Filter"."DT1" OR "Filter"."DT1" IS NULL)
  AND ("MyTable"."Date"<= "Filter"."DT2" OR "Filter"."DT2" IS NULL)

---------------------------------------------
For another criteria set, just add a row to "Filter" and use another row ID.
For more filter criteria add more columns to "Filter" with optional input.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query based on form fields

Postby TondaBucek » Fri May 22, 2020 11:29 am

Thank you very much. Some stepts were not clear for me because I am new to OO Base. But I found LibreOffice serial on YouTube and realized thet LibreOffice is almost the same as OpenOffice. So with help of that tutorial and your description and SQLs I managed my task. Thanks :)
The LibreOffice tutorial which helped me a lot: https://www.youtube.com/playlist?list=P ... 4CYHMr4wmV
OpenOffice 4, Windows
TondaBucek
 
Posts: 3
Joined: Wed May 20, 2020 9:20 am

Re: Query based on form fields

Postby Villeroy » Fri May 22, 2020 12:10 pm

The SQL statements (CREATE, INSERT, SELECT) are easier to communicate and to execute than step-by-step instructions clicking through the graphical user interface. SQL is not Base specific. The same (or similar) statements work with any other relational database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Query based on form fields

Postby TondaBucek » Fri May 22, 2020 1:23 pm

Yes, the SQL is clear to me :) But I didn't know e.g. how to add a button to sub-form since it is GUI related. But that was explained in the tutorial. So with your help I was able to realized what I really need to learn and found out and finished what i needed. Thanks :)
OpenOffice 4, Windows
TondaBucek
 
Posts: 3
Joined: Wed May 20, 2020 9:20 am


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests