[Tutorial] Standalone Forms / Switchboard

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

[Tutorial] Standalone Forms / Switchboard

Post by DACM »

A common question involves opening Base Forms and Reports directly, without exposing the Base application interface (GUI) to the end-user, and perhaps utilizing a push-button interface or "switchboard" Form/Report launcher.

Enter "standalone" Forms and Reports. There's a distinction between "standalone" and "embedded" Forms and Reports. We normally work with embedded Forms and Reports in Base, which are essentially Writer documents stored within the Base container file (.odb). Opening an embedded document requires that we open Base, thereby exposing the Base GUI. But standalone Forms or Reports bypass the GUI, effectively hiding this interface from the end-user. Base still plays a role because a "registered" .odb file is necessary to connect to the data source. But this connection duty is performed transparently for standalone documents. There are some limitations. While Base Forms may be exported as standalone files or created anew in Writer (.odt) or Calc (.ods), Base Reports have no standalone equivalent. But we do have some standalone reporting options available.

Some background:
  • The official Switchboard Extension (manual) for Base uses the default "embedded database" configuration to provide push-button access to Forms and Reports stored in any number of "registered" Base files (.odb). In my experience this product works well, even when using the Switchboard Extension with external databases such as HSQLDB 2.x, H2, MySQL, PostgreSQL, etc. But unfortunately, this approach exposes the Base GUI to the end-user. "DB Doc Shortcut" is designed to remedy this shortcoming by opening embedded Forms and Reports from desktop shortcuts, but this feature doesn't seem to function properly (?) with newer versions of Base.

    Otherwise, macros expose the Base GUI, defeating our purposes here. But that hasn't stopped developers from using macros to open embedded Forms and Reports using Push Buttons or even a List Box. They also use macros to open Forms filtered to a specific record(s), but that's a little off-topic here. And one point of caution: macros can inadvertently circumvent database connection requirements, so care must be taken especially when opening Forms using the 'Open Document' event at Base startup.
The Solution:
  • We can avoid macros, extensions, and exposing the Base interface -- all together -- when using stand-alone Office documents to host Forms, Reports, or a switchboard. You'll find Writer and Calc documents particularly well-suited in these roles -- perhaps best when viewed full screen. Writer is a natural choice for Forms because we can export our existing Base Forms as standalone Writer files (.odt). Calc takes it to the next level with a tabbed interface supporting both Form Controls and certain tools useful in generating Reports. Tying it all together, we can open any Writer or Calc file directly (perhaps using shortcut files), to include a central switchboard document. Database access is handled seamlessly by "registered" Base .odb files (one file per database) serving as universal "data-sources" across your AOO/LibO installation.

    Now, while Forms retain their full-function as standalone documents (actually gain access to multiple data sources from one document), standalone Reports exhibit some limitations. Actually, the definition of a Report is difficult to nail-down in Base. Reports can involve lists (Base), charting (Calc) and mail-merge functions (Writer). In addition, Form Controls such as a Table Control (table-grid) can be used to display/print interactive Reports. Lets examine each of these Reporting options in light of our objectives.
    • You're probably aware that embedded Base Reports are adept at lists, and even grouped lists when using the Report Builder extension. Unfortunately, standalone Reports don't support these lists or the Report Builder extension. Some workarounds include:
      • A standalone Writer Form with a Table Control (grid) and other interactive Form Controls. Most Form Controls can be set to non-printable using their "Printable" property. This can be useful when making an interactive Report suitable for printing.
      • Perhaps try-out or modify DB Doc Shortcut to access embedded Reports (which do support lists), without exposing the Base GUI.
      • Or simply open/expose the Base GUI as necessary to access these list-oriented Reports embedded within a Base .odb file. We can open a Base .odb file directly using a Push Button on a standalone document or switchboard using the button's built-in Action: Open document property while also adding the (.odb) document path to the button's URL property.
    • Otherwise, standalone Writer documents are limited to mail-merge functions (form-letters, mailing labels, business cards, envelopes, etc.). But once a mail-merge template is setup, it can be opened anytime and printed with the latest data.
    • Calc spreadsheets offer advanced computations, charted ranges and pivot-tables from various data-sources (seamlessly through Base .odb files).
    So with some limitations and/or workarounds in mind, we can build a standalone switchboard with push-button access to Forms and Reports, while avoiding macros, extensions and the Base GUI.

    Lastly, I should clarify that Base provides both seamless and managed access to various data sources. 'Seamless access' here infers an automated process which includes loading the database driver/engine automatically in the background for a completely seamless end-user experience. Seamless data-sources include: CSV files, spreadsheets, dBase flat-files, and selected relational databases such as HSQLDB, H2, Derby and SQLite. The default "embedded database" configuration is also seamless, but this configuration risks data corruption, so it's best to convert these files to a proper 'split database' configuration, which can remain seamless. This 'seamless' experience contrasts with 'managed' access such as we encounter with a full-scale database engine. These full-scale engines require separate start-up and shutdown -- although access is seamless for the end-user once the database-server (RDBMS) is started. Base provides managed access to several full-scale databases engines including: PostgreSQL, MySQL, MariaDB, Firebird, Oracle, MS SQL Server, etc.
This tutorial focuses on the steps necessary to convert embedded Base Forms to standalone Writer documents, then generating a switchboard interface which utilizes push buttons to open those documents. Villeroy has mentioned/outlined this process on several occasions so here's the breakdown step-by-step...

Begin by saving your current database Forms as standalone Writer documents:
  • (1) In Base right-click each Form to Edit, then Save Copy as... > ODF Text Document (.odt).
    (2) Open each new (.odt) Writer Form and select View > Toolbars > Form Design.
    (3) Turn "on" Design Mode using the Form Design toolbar (second icon from the left).
    (4) Turn "on" Form Navigator (fifth icon from the left).
    (5) In the popup 'Form Navigator' window, right-click each Form (MainForms and SubForms) and click Properties.
    • Note: The Form Navigator window reveals the 'Forms' structure which may include any number of "MainForms" and their associated "SubForms." Each MainForm and SubForm can be linked to a separate Data source (.odb file registered within OOo/LibO), Content type (Table, Query, or SQL command), and Content (selected by Table/Query name or by manual SQL entry).
      Note: You can register your Base files (.odb) in OOo/LibO by clicking: Tools > Options... > Base > Databases
    (6) In the popup 'Form Properties' window, select the Data tab, and then proceed to select a Data source, Content type, and Content for each MainForm and SubForm in your Form Navigator window.
    • Note: don't miss Villeroy's sub-steps (6-1 thru 6-3) in the next post below which will save you some time and frustration when re-establishing the Data source when your Content consists of an existing SQL command. In fact, you'll want to use the SQL-command option over the Query option because SQL-commands are uniquely saved in the Form which enhances the portability of standalone Forms. There's also a nice tip for utilizing un-registered databases (Base .odb files), but it's generally better to utilize database registration in OOo/LibO because Data source links are hard-coded (non-relative links) in all cases (not good programming), so it's probably easier to manage the link centrally rather than for each MainForm/SubForm, as Villeroy also points-out.
    (7) Turn "off" Design Mode using the Form Design toolbar (second icon from the left) and test your Form for proper function.
    (8 ) When you're satisfied with each standalone Writer Form, click File > Save
    • Note: there's no need to save each Form "read-only" when using a Push Button to open the Form due to the associated "read-only" option of the URL. However, this option still allows the user to enter 'Design Mode' using the appropriate toolbar, so it may be best to save a more permanent "read-only" version. File > Save As... > Save with password (checkbox) > More Options... (button) > Open file read-only (checkbox). Do not specify a password. To regain the ability to edit the Writer-Form document after saving "read-only" simply Save As... another copy without the "read-only" attribute.
Create the switchboard using a new Writer document:
  • (A) In OOo/LibO, select File > New > Text Document
    (B) View > Web Layout
    (C) Format > Page... > Background tab > As > select a Color or Graphic as desired
    (D) View > Toolbars > Form Controls
    (E) On the Form Controls toolbar, select Push Button and drag-create a Push Button on the document.
    (F) Right-click the new Push Button > Anchor > To Page as necessary
    (G) Right-click the new Push Button > Control... opens the Properties: Push Button dialog box
    (H) Scroll down to Action > Open document/web page
    (J) Now select URL > [...] (browse button) and locate the desired Writer-Form document (.odt) > Read-only (checkbox) > Open (button)
    • Note: don't miss the "Read-only" checkbox option while "Opening" this URL link.
      Note: this will create a "hard link" (including full path) to your selected Writer-Form document. To create a more flexible "relative link" to your Form document, simply edit the URL directly in the URL property text box. For instance, if all your Writer-Form documents will be saved in the same folder with your switchboard document, then delete the entire path to the (.odt) Form leaving only the Writer-Form document name. A "relative link" allows the Writer-Forms and switchboard document to be moved together (as a folder) and retain function without re-linking each document. Thus, the only "hard link" associated with your switchboard is the central 'Data source' registration (path to your .odb file) in OOo/LibO.
    (K) While you're in the Properties: Push Button dialog box, feel free to adjust the Label, Font, etc as desired
    (L) Close the dialog box and Turn "off" Design Mode (second button on the Form Controls or Form Design toolbar), and test the new Push Button making sure the associated Form opens "read-only" for immediate use.
    (M) File > Save to preserve your work so far
    (N) Repeat steps E thru M as necessary to add additional Push Buttons
    (O) And finally, File > Save As... > Save with password (checkbox) > Save (button, opens the Set Password dialog box, but skip to...) > More Options... (button) > Open file read-only (checkbox).
    • Note: Do not specify a password to open, but after clicking More Options... you'll see the option to Enter password to allow editing which can be useful (if you as the developer don't forget the password!).
      Note: To regain the ability to edit the Writer-Form document after saving "read-only" simply Save As... another copy without selecting the "read-only" attribute.
Enjoy!
Last edited by DACM on Mon Mar 28, 2016 10:35 pm, edited 88 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Opening Forms without Macros

Post by Villeroy »

DACM wrote:(6) In the popup 'Form Properties' window, select the Data tab, and then proceed to select a Data source, Content type, and Content for each MainForm and SubForm in your Form Navigator window.
Here is a screenshot of a properties window for a form to be reconnected to the database it originated from. The first property "Data Source" is new. In the old embedded context the source of data was simply the embedding database. No matter how you reconnect the form to your database, when you fill out that property the "Content" property gets lost which is confusing and annoying, paricularly when it was more than the name of a table or query.
Screenshot of form properties
Screenshot of form properties
(6.1) Copy the content of the "Content" property into the clipboard.
(6.2) The drop down lets you select the database if it is a registered one, the button under the screenshot's mouse pointer lets you pick the database file if the database is not registered.
(6.3) Paste the content back into the "Content" property.

When your database is referenced by many documents out of the database document it makes sense to register it at menuTools>Options>Base>Databases.... Then you can move or rename the database and the registration serves as a central point to adjust the connection to all documents which connect to the database by the registered name rather than file path. Furthermore you should register an odb file when you want to use database data in serial letters, pivot tables, linked import ranges within arbitrary office documents.
If you have only a small set of forms connected to that database you may decide not to register. A database that is not registered will not appear in the datasource window (F4) nor dialogs for serial letters and such.
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
f_Myeah
Posts: 8
Joined: Mon Jan 16, 2012 9:27 pm

Re: [Tutorial] Standalone Forms / Switchboard

Post by f_Myeah »

This does not work for reports - the reports are not dynamic.
OOo 3.3 WinXP
cazbym
Posts: 40
Joined: Wed Apr 15, 2009 2:52 pm

Re: [Tutorial] Standalone Forms / Switchboard

Post by cazbym »

f_Myeah wrote:This does not work for reports - the reports are not dynamic.
I believe that if you download the extension DBDocShortcuts, you can create a shortcut to your report and then paste this shortcut into the URL of your push button and it will run the report. Only (very small) downside is that the terminal window flashes up for a few seconds.
Windows 10
OOo 4.1.5
connected to
External HSQLDB 2.3.2 DB
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Standalone Forms / Switchboard

Post by Villeroy »

f_Myeah wrote:This does not work for reports - the reports are not dynamic.
Calc provides a very powerful report engine with all its calculation features, charts and pivot tables.
[Tutorial] Using registered datasources in Calc
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: [Tutorial] Standalone Forms / Switchboard

Post by Villeroy »

FreeHSQLDB v.0.3 includes a form extraction macro.
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
Post Reply