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
[Solved] Query based on form fields
-
- Posts: 3
- Joined: Wed May 20, 2020 9:20 am
[Solved] Query based on form fields
Last edited by robleyd on Fri May 22, 2020 12:08 pm, edited 2 times in total.
Reason: Tagged [Solved]
Reason: Tagged [Solved]
OpenOffice 4, Windows
Re: Query based on form fields
menu:Tools>SQL....
menu:View>Refresh Tables.
-------------------------------------------------
Bind a form to SQL statement:
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:
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:
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
---------------------------------------------
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.
Code: Select all
CREATE TABLE "Filter"(ID INT PRIMARY KEY, DT1 AS DATE, DT2 AS DATE);
INSERT INTO "Filter" VALUES(0, NULL, NULL);
-------------------------------------------------
Bind a form to SQL statement:
Code: Select all
SELECT * FROM "Filter" WHERE 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
SELECT "MyTable".*
FROM "MyTable"
WHERE ("Date">= :pDT1 OR :pDT1 IS NULL) AND ("Date"<= :pDT2 OR :pDT2 IS NULL)
------------------------------------------------
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
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)
Code: Select all
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 3
- Joined: Wed May 20, 2020 9:20 am
Re: Query based on form fields
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
The LibreOffice tutorial which helped me a lot: https://www.youtube.com/playlist?list=P ... 4CYHMr4wmV
OpenOffice 4, Windows
Re: Query based on form fields
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 3
- Joined: Wed May 20, 2020 9:20 am
Re: [Solved] Query based on form fields
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