[Solved] Macro to display SQL result in Calc

Discuss the database features
Post Reply
Tireur
Posts: 7
Joined: Thu Oct 29, 2015 10:52 pm

[Solved] Macro to display SQL result in Calc

Post by Tireur »

Hi there,
I think this one shouldn't be so difficult to solve, but I am total beginner.
I have currently a database connected to Calc and it works just fine. I have the database registered and I use the link between them. Now I have created simple sql query:

Code: Select all

SELECT * FROM "table" WHERE "ID" = ?
The query works just like I want, I have to enter "ID" to display display the result I wish. The problém is, that when I link the query into Calc (Using F4 for data sources), the dialog box to enter ID opens up, I fill the ID in and the results is displayed, but I refresh the whole spreadsheet a lot (using macro controled by button) and every time I refresh the spreadsheet, the dialog box to enter ID opens up again. Simply, my goal is to have a button, which would trigger the SQL query only by clicking it to display the result in a specifie Calc cell range.

Is is possible? Thank you very much in advance
Last edited by Tireur on Tue May 09, 2017 8:14 pm, edited 1 time in total.
Openoffice 3.4.1. on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to display SQL result in Calc

Post by Villeroy »

Call menu:Tools>Options>Base>Databases and register PowerFilter.odb as "PowerFilter".
Store PowerFilter.ods in a trusted directory. It contains a one-line macro.
The first sheet has a filtering criteria form with a filtered and input form on the first sheet. This one works without any macro.
The second sheet has a filtering criteria form with a filtered report on sheet cells. The OK button calls a macro to refresh the import range.
Both filter sets ignore missing criteria. See queries "qFiltered" and "qFiltered_Form".

Search this forum for "power filtering". It is the preferred way to apply complex filter criteria with no macro.
A "power filter form" is linked to a single criteria row in a separate table.
The linked criteria form stores filter criteria in its dedicated criteria row.
The user must not delete that row nor tab into another row therefore the form's row set is limited to the single row without allowing insertion/deletion of rows with navigation bar hidden.
In order to get an editable record set, the input form uses a record set that is based on a single table "Data" with parameters substituted by the parent form.
The report simply merges all table data with filter record #2 and dumps them on the sheet. See [Tutorial] Using registered datasources in Calc

Edit
For a completely macro free version you can move the report sheet's OK button to the filtering form, remove the button's macro event and assign action "Save".
Usage: Edit the filter criteria and click OK. Then click any cell in the import range and call menu:Data>Refresh

Edit 2017-06-11: Added a pivot table to the spreadsheet.
Attachments
PowerFilter.ods
Input form and report for "PowerFilter.odb". Contains a one-line macro.
(36.95 KiB) Downloaded 921 times
PowerFilter.odb
Embedded hsqldb to be registered as "PowerFilter"
(54.73 KiB) Downloaded 842 times
Last edited by Villeroy on Tue May 15, 2018 10:18 am, edited 2 times in total.
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
Tireur
Posts: 7
Joined: Thu Oct 29, 2015 10:52 pm

Re: Macro to display SQL result in Calc

Post by Tireur »

Thank you very much, Villeroy, your reply is quite complex. Your example works great. Unfortunately for me, it's too difficult. I don't need so many filtering conditions and can't simplify it on my own.

In my case, I have one simple table containing all the data. Every row has unique ID. I would just need one listbox to choose the "ID", click OK to get the report from data row matching the ID to defined cell range. I don't need editable form.
I hope I explained it correctly. I appreaciate your help very much, thanks :)
Openoffice 3.4.1. on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to display SQL result in Calc

Post by Villeroy »

Register your database if it is not registered yet.

Tools>SQL...

Code: Select all

CREATE TABLE "Filter"(FID INT PRIMARY KEY, ID1 INT);
INSERT INTO "Filter" VALUES(1,Null);
Query qFilterRow1:

Code: Select all

SELECT * FROM "Filter" WHERE "FID"=1
Query qFiltered:

Code: Select all

SELECT "table".* FROM "table", "Filter" WHERE "Filter"."FID"=1 AND ("table"."ID"="Filter"."ID1" OR "Filter"."ID1" IS NULL)
Query qListBox:

Code: Select all

SELECT "some_name"AS "Visible Field", "ID" FROM "table" ORDER BY "Visible Field"
Add a list box to the spreadsheet.
Form Properties: Link the form to query qFilterRow1 of your registered database, disallow deletion, disallow insertion, hide navigation bar.
Control Properties: Link the list box to query qListBox, linked field is "ID1" of the underlying filter table, bound field is 1 (second field, 0 is the visible one).
Add an OK button with action "Save"

Get the data source window (F4 in OpenOffice, Ctrl+Shift+F4 in LibreOffice).
Browse to the queries of your registered database and drag the icon "qFiltered" into your sheet.

##############################################################################################

Usage: Select an item from the list box, OK, click any cell in the list and call menu:Data>Refresh. You may define a shortcut or toolbar button instead of the menu command, however the cell cursor needs to be within or adjacent to the import range.

###############################################################################################

For the refresh-by-macro solution:
Get the form navigator and add a dummy form with no binding.
Move the OK button to that form and remove its action property.
Assign the execute event to the one line macro ThisComponent.DatabaseRanges.getByName("Import1").refresh where "Import1" is the name of the wanted database range.
Macro button on separate dummy form.
Macro button on separate dummy form.
Navi_OKButton.png (11.04 KiB) Viewed 13805 times
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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to display SQL result in Calc

Post by Villeroy »

Most simple solution without filter table, input form nor macro code:

Register your database if it is not registered yet.

Query qParamFilter:

Code: Select all

SELECT "table".* FROM "table" WHERE :Filter_Value ="table"."ID"
The query in your initial posting might work as well.

Get the data source window (F4 in OpenOffice, Ctrl+Shift+F4 in LibreOffice).
Browse to the queries of your registered database and drag the icon "qParamFilter" into your sheet.

You will be prompted for the filter value before the filtered record set is dumped into the sheet.

##############################################################################################

Usage: Click any cell in the list and call menu:Data>Refresh and enter the filter value. You may define a shortcut or toolbar button instead of the menu command, however the cell cursor needs to be within or adjacent to the import range.
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
Tireur
Posts: 7
Joined: Thu Oct 29, 2015 10:52 pm

Re: Macro to display SQL result in Calc

Post by Tireur »

Villeroy, thank you so much, you're the best :) :bravo:

I followed your instructions, I had to change it a bit to fit into my project, but finally it works :) Thanks to you, I think I understand the code a bit more again.

Your second more simple solution results in the same problem that I wrote about in the main post, I refresh the spreadsheet a lot using a macro (working on different sheets of this document) and I am prompted for the filter value every time (even when I don't want to use it).
Openoffice 3.4.1. on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to display SQL result in Calc

Post by Villeroy »

Thank you for taking the time to implement this solution. I can tell you that a macro with the same functionality would be far more complicated. This solution uses an input form to store the parameter(s) in a database table rather than trying to read parameters from spreadsheet cells. Keeping all data and data processing in the database and dumping the final result into the spreadsheet is easier to build and more consistent.
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
ouchd'greyt
Posts: 28
Joined: Wed Jun 28, 2017 4:41 am

Re: [Solved] Macro to display SQL result in Calc

Post by ouchd'greyt »

Is it possible to search in database table using textbox not listbox in calc??
OpenOffice 4.1.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Macro to display SQL result in Calc

Post by Villeroy »

ouchd'greyt wrote:Is it possible to search in database table using textbox not listbox in calc??
Did you try?
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
ouchd'greyt
Posts: 28
Joined: Wed Jun 28, 2017 4:41 am

Re:Macro to display SQL result in Calc

Post by ouchd'greyt »

yes i've tried and still trying, but there's a lot of things i didn't get,
when i'm updating the form properties of a listbox on "Content type" and "Content" after updating the other form properties like the table and the textbox also updated.
i'm doing this cal, but when i checked your sample its different.

Please Help
OpenOffice 4.1.2 on Windows 7
Post Reply