[Solved] Macro to display SQL result in Calc

Discuss the database features

[Solved] Macro to display SQL result in Calc

Postby Tireur » Thu Apr 27, 2017 9:47 am

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   Expand viewCollapse view
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
Tireur
 
Posts: 7
Joined: Thu Oct 29, 2015 10:52 pm

Re: Macro to display SQL result in Calc

Postby Villeroy » Thu Apr 27, 2017 12:49 pm

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 91 times
PowerFilter.odb
Embedded hsqldb to be registered as "PowerFilter"
(54.73 KiB) Downloaded 85 times
Last edited by Villeroy on Sun Jun 11, 2017 12:30 pm, edited 1 time in total.
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: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to display SQL result in Calc

Postby Tireur » Thu Apr 27, 2017 2:17 pm

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
Tireur
 
Posts: 7
Joined: Thu Oct 29, 2015 10:52 pm

Re: Macro to display SQL result in Calc

Postby Villeroy » Thu Apr 27, 2017 2:43 pm

Register your database if it is not registered yet.

Tools>SQL...
Code: Select all   Expand viewCollapse view
CREATE TABLE "Filter"(FID INT PRIMARY KEY, ID1 INT);
INSERT INTO "Filter" VALUES(1,Null);


Query qFilterRow1:
Code: Select all   Expand viewCollapse view
SELECT * FROM "Filter" WHERE "FID"=1


Query qFiltered:
Code: Select all   Expand viewCollapse view
SELECT "table".* FROM "table", "Filter" WHERE "Filter"."FID"=1 AND ("table"."ID"="Filter"."ID1" OR "Filter"."ID1" IS NULL)


Query qListBox:
Code: Select all   Expand viewCollapse view
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.
Navi_OKButton.png
Macro button on separate dummy form.
Navi_OKButton.png (11.04 KiB) Viewed 2076 times
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: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to display SQL result in Calc

Postby Villeroy » Thu Apr 27, 2017 6:04 pm

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   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to display SQL result in Calc

Postby Tireur » Fri Apr 28, 2017 2:47 pm

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
Tireur
 
Posts: 7
Joined: Thu Oct 29, 2015 10:52 pm

Re: Macro to display SQL result in Calc

Postby Villeroy » Fri Apr 28, 2017 5:05 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby ouchd'greyt » Mon Jul 17, 2017 11:22 am

Is it possible to search in database table using textbox not listbox in calc??
OpenOffice 4.1.2 on Windows 7
ouchd'greyt
 
Posts: 28
Joined: Wed Jun 28, 2017 4:41 am

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

Postby Villeroy » Mon Jul 17, 2017 3:41 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re:Macro to display SQL result in Calc

Postby ouchd'greyt » Wed Jul 19, 2017 3:55 am

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
ouchd'greyt
 
Posts: 28
Joined: Wed Jun 28, 2017 4:41 am


Return to Base

Who is online

Users browsing this forum: No registered users and 6 guests