[Solved]Pass parameters query from form&place output in Calc

Creating and using forms

[Solved]Pass parameters query from form&place output in Calc

Postby andrew59 » Mon Sep 11, 2017 10:00 pm

Hi! I am a fan of OO and use 4.1.3 on all Win10 64 bit CPU in our company.
1. I set a direct connection to MariaDB from within Calc and retrieve quickly data to spreadsheet for further processing.
2. Great help, but to change query values one needs to edit the queries. I figured out how to set parameters in the query (like :Date1 :SKU2 etc.) and manually ascribe values to them.
3. Great functionality, but quite clumsy if parameters are numerous and just one of them changes often. I read about passing values to query parameters using FORM. The tutorials and the perfect example on https://forum.openoffice.org/en/forum/v ... 13&t=41465 made this technology clear.

Before going into FORM details I would like to ask if after passing values from a FORM to query parameters, the query output can be placed in Calc spreadsheet rather than in the FORM itself (or in subform)?
I need the retrieved data from DB placed into a spreadsheet to process it there and the Writer format of FORM is not appropriate. The fiddling to export data from DB interface (I have one) & import in Calc spreadsheet is what I save using direct link to DB from within Calc, so export from FORM/import into Calc is not an option.
Please, explain how to do it if possible, could not find info in the forum.
Last edited by RoryOF on Wed Sep 13, 2017 5:12 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
OpenOffice 4.1 on Windows Home 10 64 bit www.microasu.com
User avatar
andrew59
 
Posts: 9
Joined: Tue Sep 20, 2016 9:19 am

Re: Pass parameters to query from form, place output in Calc

Postby Villeroy » Mon Sep 11, 2017 10:39 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24568
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pass parameters to query from form, place output in Calc

Postby andrew59 » Tue Sep 12, 2017 8:54 pm

Thanks! Second page named "Report" in the ods file is exactly what I want.

It works fine with the sample data, but I cannot understand how values are passed from the controls in the FilterForm to the query.
1. On page1 "Input form" of the ods file in DataForm properties there is: Link master/slave fields where form controls and query parameters are linked (D1 D2 INT1 INT2 --> paramD1 paramD2 paramP paramC)
2. On page2 "Report" there are no Link master/slave fields in FilterForm properties (as expected) and I do not know how to select DataForm there. Or I should look for the links list in another place?

I deleted the links on first page and query refresh requested manual input, but query on second page still works fine. So there should be no parameter values transfer between page1 and page2 of the ods file.
OpenOffice 4.1 on Windows Home 10 64 bit www.microasu.com
User avatar
andrew59
 
Posts: 9
Joined: Tue Sep 20, 2016 9:19 am

Re: Pass parameters to query from form, place output in Calc

Postby Villeroy » Tue Sep 12, 2017 9:13 pm

The criteria form stores the criteria values in a distinct record of the filter table.
The parameter query for the editable subform is necessary because record sets are only editable when they come from a single table (qFiltered_Form: SELECT ... FROM "One_Table" WHERE :paramA = "A")
The record set of a report does not need to be editable. Therefore you can join the data table, the detail tables and the filter table.

Query qFiltered does that:
Code: Select all   Expand viewCollapse view
SELECT "D"."D" AS "Date", "P"."N" AS "Person Name", "C"."N" AS "Category", "D"."V" AS "Value" <--- various fields from 3 tables
FROM "Categories" AS "C", "Data" AS "D", "Persons" AS "P", "Filter" AS "F" <--- 4 Tables
WHERE "C"."ID" = "D"."CID" AND "P"."ID" = "D"."PID"   <--- the relation to categories and persons
AND "F"."FID" = 2  <--- the single record with the relevant filter criteria
AND ( "D"."PID" = "F"."INT1" OR "F"."INT1" IS NULL ) <--- filtered person if any
AND ( "D"."CID" = "F"."INT2" OR "F"."INT2" IS NULL )  <--- filtered category if any
AND ( "D"."D" >= "F"."D1" OR "F"."D1" IS NULL )  <--- filtered from date if any
AND ( "D"."D" <= "F"."D2" OR "F"."D2" IS NULL )  <--- filtered until date if any
ORDER BY "Date" DESC
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24568
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pass parameters to query from form, place output in Calc

Postby andrew59 » Wed Sep 13, 2017 10:49 am

It is getting brighter after Your expalanation, thanks.
I gather that values, input in the control fields of the filter form on page2, are stored in a separate table (Filter as F for short) in the data base, that is how values are served to queries. The queries should be modified to use these values from Filter table to return the needed data in OO spreadsheet.

If this insight is true, the DB account in OO should have INSERT and other "write" permissions over the DB, not just SELECT as it is at the moment. And a proprietary table (Filter) should be created and appended to the DB. It is not a part of the DB as masterminded by the DB designers.

These considerations are not a tragedy, just for the record. I am using MariaDB on a server accessed on WAN through VPN tunnel.
OpenOffice 4.1 on Windows Home 10 64 bit www.microasu.com
User avatar
andrew59
 
Posts: 9
Joined: Tue Sep 20, 2016 9:19 am

Re: Pass parameters to query from form, place output in Calc

Postby Villeroy » Wed Sep 13, 2017 11:03 am

andrew59 wrote:If this insight is true, the DB account in OO should have INSERT and other "write" permissions over the DB, not just SELECT as it is at the moment. And a proprietary table (Filter) should be created and appended to the DB. It is not a part of the DB as masterminded by the DB designers.

Yes and no. Embedded forms can not access multiple databases because they have no data source property. However forms in stand-alone documents can have parent forms and subforms in different databases. You can create another tiny database in HSQL or dBase only for the filter criteria. Then bind the filtering form to your personal criteria DB and the filtered form to the actual data DB.
However, this is not easily possible with external import ranges or pivot tables on spreadsheets. We can not SELECT ... FROM "Database A"."Filter", "Database B".Data
This is where a small macro could help to substitute a parameter query with values read from elsewhere.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24568
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SOLVED Pass parameters query from form&place output in C

Postby andrew59 » Wed Sep 13, 2017 5:07 pm

All clear now. Mark the question SOLVED.
Thanks for Your time and the great job with OO!
OpenOffice 4.1 on Windows Home 10 64 bit www.microasu.com
User avatar
andrew59
 
Posts: 9
Joined: Tue Sep 20, 2016 9:19 am


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests