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,
[Solved] Macro to integrate database queries
[Solved] Macro to integrate database queries
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
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Macro to integrate database queries
I'm not sure what you mean by
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
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
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
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.Invoicing_and_Cloning_with_SubForms.odb
a macro-free demo of passing multiple fields through SubForm links
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Macro to integrate database queries
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.UnklDonald418 wrote:I'm not sure what you mean byall 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.
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