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

[andrew59]

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 viewtopic.php?f=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.

[Villeroy]

viewtopic.php?t=88516&p=416210#p416210

[andrew59]

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.

[Villeroy]

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

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

[andrew59]

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.

[Villeroy]

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.

[andrew59]

All clear now. Mark the question SOLVED.
Thanks for Your time and the great job with OO!