[Solved] Macro to integrate database queries

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
MPEcho
Posts: 99
Joined: Wed Sep 07, 2016 11:30 pm

[Solved] Macro to integrate database queries

Post by MPEcho »

I am using a Base database to manage information about projects. The documents that get filled from database queries all work - pretty much. I want to be able to have a macro that, when the document opens asks whether to fill, if the user clicks "yes" then all the fields populate automatically from multiple queries.

Why this is useful: Currently, I have several documents that rely on two, three or four queries. One with master project information, a second with team members and their assignments, a third with team leaders and resources assigned, and another with budget line items and totals. Doing this manually requires (1) open data sources, (2) select the appropriate query, (3) select all records in the table, (4) click "data to fields" to populate the data, (5) repeat 2 - 4 for all queries. It seems like this should possible to automate.

I have been searching on and off for months and no solution. Full disclosure: In spite of the signature, I am Libre Office 5.1 on Ubuntu 16.04m but am a forum refugee since the LO forum closed. But other OO macros have seemed to work with little modification so I am hoping the same will be true here.

Thanks,
Last edited by MPEcho on Sun Sep 11, 2016 1:02 am, edited 1 time in total.
Libre Office 5.1.6.2 Ubuntu 16.04
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Macro to integrate database queries

Post by UnklDonald418 »

I'm not sure what you mean by
all the fields populate automatically from multiple queries

If you are trying to display the results from these queries on a single form then maybe this tutorial on creating complex forms/subforms will help.
viewtopic.php?f=83&t=28235

If you want to copy the data from the various sources into a new record then look at this
viewtopic.php?f=100&t=56006#p246052
pay particular attention to
Invoicing_and_Cloning_with_SubForms.odb
a macro-free demo of passing multiple fields through SubForm links
Because any macro code would have to be specific to your tables and forms you aren't likely to find anything that you can make a quick copy and have it work for you. So if you find that you must use macros then I would suggest you get a copy of "Database Programming with OpenOffice.org Base & Basic" by Roberto Benitez.
http://www.baseprogramming.com/
Once you've mastered Result Sets and Row Sets you should be able to write the macro code to accomplish what you are asking
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
MPEcho
Posts: 99
Joined: Wed Sep 07, 2016 11:30 pm

Re: Macro to integrate database queries

Post by MPEcho »

UnklDonald418 wrote:I'm not sure what you mean by
all the fields populate automatically from multiple queries

If you are trying to display the results from these queries on a single form then maybe this tutorial on creating complex forms/subforms will help.
Thank you for the reply! It isn't surprising you didn't understand because "Form" and "Document" can mean so many things in this context. In my use case, EVERYTHING that I do has to result in a printable document with wordprocessor document formatting. So when I am trying to fill a "form" I meant in a writer document. I have some fairly complex Embedded Forms in Base. It is getting that information into an *.odt document easily that is the problem.

Some of the links UnklDonald418 gave are very helpful. While not quite on point, they do lead me to what appears to be the correct answer at this post:
viewtopic.php?t=40493

For the benefit of others searching: I was looking for a macro method to fill fields in a Writer Document. The problem is that using the simple writer interface (F4, select a table, drag + drop fields into the writer document) isn't really up to the complexity of what I want to do. I have done it, but useage is clunky which is what prompted the original post.

From the post above, it looks like the better choice is to get an Embedded Form working in base (using sql, subforms, etc). Then export that into a standalone Writer Document. Then change the document formatting to suit including moving elements around, etc. I will work on that and, see what I get

Thanks again for the input.
Libre Office 5.1.6.2 Ubuntu 16.04
Post Reply