Text from database into frame / textbox

Creating and using forms
Post Reply
schsch
Posts: 8
Joined: Mon Sep 25, 2017 11:31 am

Text from database into frame / textbox

Post by schsch »

Hi there,

I have some writer documents for several templates. I use a SQl-Database for handling my projects data, such as project-code, projectadress, ...
Therefore I need the selected entry for a project on every page of the writer documents.
But it is not possible to insert fields in the header section in writer.

Now I m trying to get the text from the selected database entry into a frame or textbox, because that can be inserted in the headersection.
Can somebody help me to put the text per makro into the frame?
thx
OpenOffice 4.1.3. on macOS Sierra 10.12.6
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text from database into frame / textbox

Post by Villeroy »

schsch wrote:But it is not possible to insert fields in the header section in writer.
Why not? You can insert anything into the header. However, this has nothing to do with forms. Add database fields to your templates. menu:Insert>Fields>Database
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
schsch
Posts: 8
Joined: Mon Sep 25, 2017 11:31 am

Re: Text from database into frame / textbox

Post by schsch »

because when you do that, the field is empty and stays empty. I thought that is a bug, but someone said this is normal because database things cant be in the header ;(
OpenOffice 4.1.3. on macOS Sierra 10.12.6
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text from database into frame / textbox

Post by Villeroy »

I took a serial letter from another topic, added some dummy text so it spreads over 2 pages and copied one of the fields into the header. When you print that serial letter to a file, the header shows the field value for each item on both pages.
The data source is "Bibliography" which is the dBase demo shipped with every office.
Attachments
Serial_Biblio_w_header.odt
Mail merge field in header
(15.17 KiB) Downloaded 205 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
schsch
Posts: 8
Joined: Mon Sep 25, 2017 11:31 am

Re: Text from database into frame / textbox

Post by schsch »

thank you Villeroy!
The Problem is, that if you have many database rows its very unhandly and for the other people in our small company its hard to remeber the steps for selecting the right project. At the moment it works very fine by selecting a project from a drop-down list and all the recommanded informations are listet in the fields.
I just need to "tell" the framebox1 that there should be the text1 from my databse.
OpenOffice 4.1.3. on macOS Sierra 10.12.6
schsch
Posts: 8
Joined: Mon Sep 25, 2017 11:31 am

Re: Text from database into frame / textbox

Post by schsch »

and I found a code in the german OO Forum, but I cannot apply this to a database connection.
http://oooforum.de/viewtopic.php?t=49207
OpenOffice 4.1.3. on macOS Sierra 10.12.6
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text from database into frame / textbox

Post by Villeroy »

and then there is https://extensions.openoffice.org/en/pr ... rt-builder
Forms are made for database input.
Reports, stand-alone documents and templates can be used for database output into some printable layout.

Let the user select the project from the drop-down and click a hyperlink(-button) which opens the right text document (or template) with the right layout, database fields and printer settings.
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
schsch
Posts: 8
Joined: Mon Sep 25, 2017 11:31 am

Re: Text from database into frame / textbox

Post by schsch »

Yeah I already have this nice Report builder, but since a few days its broken and I cannot create a form with it.
Is it there possible to export a form to an normal *.ods file?
Can somebody make a test if its possible to put fields in the header section of every page and export this to a normal writer doc?
OpenOffice 4.1.3. on macOS Sierra 10.12.6
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text from database into frame / textbox

Post by Villeroy »

A form consists of form controls for input. Forms are attached to documents because nobody developed another type of canvas for input forms and documents is all what this applications is about. Forms are used without any page layout ("web view") on a computer screen. The printability of this canvas is not of any importance although nothing prevents you to turn an input form into a printed report.

Reports are printable documents filled with database data for output. We have the old style reports and the report builder reports that are embedded in a Base document. And we have stand-alone mail merge documents (form letters, serial letters) which can be seen as reports as well. And we may also use spreadsheets and pivot tables on spreadsheets for reporting.
The report builder has separate sections for a report's body, header and footer section. Serial letters can have database fields in the header and footer. But they don't accept form fields as I found out by now thanks to this topic.
Fix your report builder installation or install LibreOffice which comes with a pre-installed report builder.
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
schsch
Posts: 8
Joined: Mon Sep 25, 2017 11:31 am

Re: Text from database into frame / textbox

Post by schsch »

OK I ve installed LibreOffice now and now I can work with the report builder. Nice!
Its possible to drag a field into the header section there, but its not possible to save/export the report as a odt file for my users.
OpenOffice 4.1.3. on macOS Sierra 10.12.6
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text from database into frame / textbox

Post by Villeroy »

I know. The whole embedded database concept can be frustrating when you want to open forms and reports as stand-alone documents from some folder or through hyperlinks. The report definition can't be saved as a stand-alone document. You can only save the resulting report with the static values at run time.

Notice that the embedding Base document can be different for different co-workers. Each co-worker can have a document with only the forms and reports he actually needs. You can also add folders to the collections of forms and reports. Put the forms/reports for everybody in the root folder, rarely userd forms for setup and maintainance in their own folder and one folder for each co-worker's tools.

You can't open embedded objects by means of hyperlinks. However, it is possible to open an embedded report with a little help from a macro.

There is also a switchboard extension: https://extensions.openoffice.org/en/pr ... witchboard

Stand-alone reports with spreadsheets and with serial letters are easy to implement, including any filtering form controls that do not appear on the print page.
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
schsch
Posts: 8
Joined: Mon Sep 25, 2017 11:31 am

Re: Text from database into frame / textbox

Post by schsch »

OK,
is there somebody who can help me by creating a makro that inserts the database text in a frame object?
I have a makro "select listbox" that select the entry of the database so that me users can select them by a dropdownlist.
Is the right way to write a script that gives the textbox the entry or do I have to give the textbox a code that "reads" the entry?
Sorry I m very at the beginning of makros in OOB.
OpenOffice 4.1.3. on macOS Sierra 10.12.6
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Text from database into frame / textbox

Post by RoryOF »

There is a complete set of Listbox manipulation macros included in all releases of OpenOffice; these can be accessed via /Tools /Macros /Orqanize Macros /OpenOffice Basic /OpenOffice Macros /Tools /Listbox [Path is under Preferences for Macs]; at the same level are other macros which may repay investigation (see macro collection Misc for database relevant macros).

There is little (read: no?) documentation for these that I have found; in the past, using these, I was able to load a listbox from a text file and allow the user return a selection from it. Be prepared for a steep learning curve.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Post Reply