Filemaker lookup field alternative

Creating tables and queries
Post Reply
Rounak
Posts: 5
Joined: Sat May 11, 2013 7:09 am

Filemaker lookup field alternative

Post by Rounak »

Three Tables:
I am writing the fields in Table.FieldName format below.

1a. Customer.ID
1b. Customer.Name

2a. Invoice.ID
2b. Invoice.Customer Name
2c. Invoice.Customer ID

3a. Invoice Details.ID
3b. Invoice Details.Invoice ID

1a, 2a, 3a are all primary fields.

Relationship one to many between:
1. Customer ID.ID ->Invoice.Customer ID
2. Invoice.ID -> Invoice Details.Invoice ID

Now, in Filemaker this was the way to use these tables:
Create a new Invoice (i.e. create a new record using a Form based on Invoice Table).
Enter a value in the field Invoice.Customer Name (a combo box populates it with values from Customer.Name)
I have been able to reach upto here in Base.

This is the part where I need help:
In Filemaker, the Invoice.Customer ID field used to be setup as a lookup field to auto-retrieve the Customer ID number from the Customer Table when an entry was made in Invoice.Customer Name. The obvious purpose of doing this is to link the Invoice and Customer record. I do not know how I am supposed to get this done in Base. Hope I am clear. Kindly let me know if I am not. Thank you for your time.

[PS: crosspost:mailing list global.libreoffice.org (I have no idea how to access that post and so I could not paste the link)]
LibreOffice 4.0.3.3 on Mac OS 10.7
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: filemaker lookup field alternative

Post by F3K Total »

Hi,
take a listbox, use as listsource SQL like this:

Code: Select all

Select "Name","ID" from "Customer"
The first ("Name") is visible, the second ("ID") is written to the bound column (Invoice.Customer ID)
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
Rounak
Posts: 5
Joined: Sat May 11, 2013 7:09 am

Re: filemaker lookup field alternative

Post by Rounak »

Works, thank you. I hope my method of relating tables in this manner is correct.
LibreOffice 4.0.3.3 on Mac OS 10.7
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: filemaker lookup field alternative

Post by F3K Total »

Hi,
Rounak wrote:I hope my method of relating tables in this manner is correct.
It is! I would leave 2b. Invoice.Customer Name away from the Invoice table, it's already defined in table Customer.
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
Tom6
Posts: 4
Joined: Thu Apr 14, 2011 12:19 pm

Re: filemaker lookup field alternative

Post by Tom6 »

Hi :)
Is it better to do Forms and Reports and stuff off Queries rather than directly off Tables? I thought this was a general 'rule' (really just a guideline) about databases in general? Is it relevant to Base too?

Also is the internal back-end safe or is it better to export the tables to an external back-end such as MySql/MariaDB or PostgreSQL? I have heard of people suffering data loss from using the internal back-end and having to restore from back-ups quite frequently.
Regard from
Tom :)
Tom
OpenOffice 3.3.0 on Ubuntu 10.04 & Ubuntu 10.10
Rounak
Posts: 5
Joined: Sat May 11, 2013 7:09 am

Re: filemaker lookup field alternative

Post by Rounak »

while at the topic of using back-ends, kindly please also clarify this:
I am a single user who is going to use my Invoice Database on a single computer. Why does it become necessary for a user like me to know about MySql/PostgreSQL, ODBC, JDBC or whatever? Are they methods to store data?
LibreOffice 4.0.3.3 on Mac OS 10.7
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: filemaker lookup field alternative

Post by F3K Total »

Hi Rounak,
Rounak wrote:Are they methods to store data?
No, they are Databaseengines and Databaseconnectors
You have to know, that Base, as delivered, is a Frontend with embedded HSQL Database Engine.
While you're working, the Database is build up in you RAM, nowhere else. If you have a crash, mostly all data since begin of the session, maybe a whole days work, is lost.
As you say you're using it as single-user, in my oppinion the most simple way to avoid that dataloss is to create a Split-Version in filemode.
With that method, you can build up your DB as embedded version. If all Tables, Forms, Querys and Reports are as you like, you swap to Split-Version in filemode.
There are lots of tutorials e.g. here (Porting an "embedded database" from the all-inclusive database file (.odb) to a robust "split database" folder)
R
EDIT: You will see no difference between embedded and split version, except that you have an additional folder, where your databasefiles are stored.
The advantage is, that in that moment where you save a record, it will be written to your disk. I've tried it by saving a record, then terminated soffice.bin with the task manager. Restarted the Database and found the last changed record correctly.
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
Tom6
Posts: 4
Joined: Thu Apr 14, 2011 12:19 pm

Re: Filemaker lookup field alternative

Post by Tom6 »

Hi :)
It works fine as you already have it so keep going with that. However it's very important to keep regular and frequent back-ups. By backing up i just mean a copy&paste of the file, even in the same folder would do.

I've not heard of the Split-Version before and that sounds like it might prevent the problems.
Regards from
Tom :)
Tom
OpenOffice 3.3.0 on Ubuntu 10.04 & Ubuntu 10.10
Rounak
Posts: 5
Joined: Sat May 11, 2013 7:09 am

Re: Filemaker lookup field alternative

Post by Rounak »

Is it better to do Forms and Reports and stuff off Queries rather than directly off Tables? I thought this was a general 'rule' (really just a guideline) about databases in general? Is it relevant to Base too?

Also is the internal back-end safe or is it better to export the tables to an external back-end such as MySql/MariaDB or PostgreSQL? I have heard of people suffering data loss from using the internal back-end and having to restore from back-ups quite frequently.
For others who might want answers to this question too:
https://wiki.documentfoundation.org/Dev ... se_in_Base
LibreOffice 4.0.3.3 on Mac OS 10.7
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Data loss details and solutions

Post by DACM »

Tom6 wrote:Also is the internal back-end safe or is it better to export the tables to an external back-end such as MySql/MariaDB or PostgreSQL? I have heard of people suffering data loss from using the internal back-end and having to restore from back-ups quite frequently.

...I am a single user...on a single computer. Why does it become necessary for a user like me to know about MySql/PostgreSQL, ODBC, JDBC or whatever?

[later concluding...] It works fine as you already have it so keep going with that. However it's very important to keep regular and frequent back-ups.
Hmmm...it seems we need some clarifications. You're always free to choose your own back-end for Base, but rest assured that the "internal" (bundled) back-end is data-safe and often sufficient.

The data loss issue is the result of the 'embedded' file-format created by Base (known as 'embedded database' files). Some immediate remedies include:
(1) keeping an uncommon level of manual backups backups (uncommon because nobody seems to follow this advice sufficiently, including me), or
(2) place your 'embedded database' (.odb) in a folder with automatic backup and versioning (cloud backup folder or selected backup software), or
(3) extract your data files using a zip-tool in order to adopt a 'split database' configuration for proper data-durability.

As you've alluded, MySQL/PostgreSQL/Firebird and ODBC/JDBC involve a learning-curve, so they're not a realistic remedy for the average Base user. In fact, I would argue that these are not the answer for any Base user without significant prior experience with such engines, combined with a tolerance for the additional daily admin necessary to 'serve' the database. The built-in HSQLDB 1.8.0.10 engine (circa 2005) lacks some extended features, but nothing of substance offered by a full-scale engine relative to a desktop database environment is missing from the latest HSQLDB 2.x engine. This newly designed HSQLDB 2.x engine runs seamlessly with Base and is normally a drop-in upgrade for your existing 'split' database. Granted, there remains a legitimate need for full-scale alternatives as you surpass 50+ users on a network or over the internet. But at that point, you've realistically left realm of desktop front-ends like Base in favor of browser-driven, smart-client tools. These collective conclusions are the essence of FK Total's simple advice (above) to switch to a 'split database' configuration running HSQLDB in single-user 'file mode.'

All of this said, there remains no remedy that absolves you from the personal responsibility to keep sufficient/regular backups of your critical data.
Rounak wrote:For others who might want answers to this [Tom6] question too:
https://wiki.documentfoundation.org/Dev ... se_in_Base
Now the link Rounak provided is misleading. The new and current lead developer for LibreOffice Base (Lionel Elie Mamane) has apparently settled on HSQLDB as the backend for LibreOffice, after considerable evaluation of SQLite and perhaps a glance at Firebird. Without getting into the details, SQLite creates a significant burden on core Base development due to it's lack of strong data-typing (particularly with respect to Dates and Timestamps). For the end-user, SQLite is significantly watered-down compared to HSQLDB, and this 'simplicity' does not translate into ease-of-use. On the contrary, SQLite's lack of built-in function translates into a greater reliance on end-user developed macros to replace the missing functionality; not to mention the ramifications and difficulty of data migration to another engine as the user realizes the need for proper data-typing, SQL dialect, features, and/or multi-user support. These drawbacks are practically non-existent with HSQLDB.

Firebird is mentioned in the link, as well as, a "severe performance penalty associated with the frequency of C++ to Java transitions." That statement is a bit of a "softball" (that could be hit out-of-the-park)...
  • On it's merits, Firebird is a worthy contender complete with the required embedded mode and appropriate licensing. Also, Firebird proves faster than HSQLDB when using SDBC drivers written by *Office programmers. But this is due to poor programming techniques by *Office developers, such as issuing a database 'COMPACT' command upon every write, and perhaps related to the particular JAVA/C++ translation as currently implemented (unoptimized for large data streams). The immediate fix for the end-user is to adopt a 'split database' configuration with *Office/Base in order to utilize the native JDBC driver with HSQLDB. This bypasses most of the performance degradations introduced by *Office programmers.

    And while the JDBC driver for HSQLDB is written and maintained by leading database experts, Firebird requires an in-house SDBC driver in order to meet cross-platform objectives (ease of setup and use).

    But the major drawback to adopting Firebird is the rather outdated architecture and conventions. Something as simple as an AutoValue primary key is far too difficult to establish in Firebird. When combined with the current 'embedded database' implementation, this leaves the end-user with a very cryptic database (Firebird), with no relief since third-party database managers do not support the ODB file type (unfortunately). At the current rate of development, it will be decades before Base can meet the minimal end-user needs as database manager for Firebird.

    The dubious implementation of an 'embedded database' in *Office/Base affects both HSQLDB and Firebird. In my own correspondence with LibreOffice leadership, it is clear that they need to reduce or eliminate all Java dependencies in LibO in order to ease and promote code maintenance. It's simply too difficult to attract experts in C++, Java and the *Office UNO API among today's developers. At the same time, it was clear that JDBC support is a priority and will remain in the code-base. This aligns perfectly with user-community goals/needs. While HSQLDB is written in Java, the code is independent of LibO, so future LibO development remains unaffected. This requires that we forgo the dubious 'embedded database' concept and associated SDBC driver for HSQLDB. This is a good thing. As Villeroy & I have demonstrated through macros, it's nothing to convert a legacy 'embedded database' file into a robust and portable 'split database' folder. And as we've also demonstrated, these macro-enhanced, portable, 'split' HSQL database-folders are backwards compatible, even pre-dating the existence of AOO/LibO (tested successfully with OOo 3.3.0 Portable on Windows 7 64-bit). Ultimately, it may be appropriate to pursue a 'Pack-&-Go' feature which packs a split-database folder into a single zip-file for in-transit portability purposes -- so in the end, we don't lose anything by eliminating Java code and the SDBC driver for HSQLDB from *Office.

    This end-user 'fix' would also become necessary with Firebird as well because the particular *Office/Base implementation of ODB (file type) embeds/extracts the database from the ODB file for each database session, while DELETING the extracted split-database. This introduces serious data risks. Virtually every Base user has experienced data-corruption due to the 'embedded database' implementation in *Office.
    In a perfect world, Firebird could serve as an adequate, cross-platform replacement to HSQLDB with Base. But due to the limited resources involved with the development and maintenance of an SDBC driver and adequate database manager for Firebird in Base, the most realistic solution is a 'split HSQL database' with Base. The user-community has already stepped-up to the plate in the form of a macro-enhanced (.odb) template which bypasses the Base wizards in support of portable 'split' HSQL databases. It would really be nice if core Base development would get on-board by reducing the bugs and holes in the Base GUI associated with external database support; which in this case should be simple because it involves external JDBC access to the familiar and already tightly-integrated HSQLDB engine.
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
Post Reply