Switching from Filemaker to Base

Discuss the database features

Switching from Filemaker to Base

Postby Rounak » Sat May 11, 2013 9:39 am

I am exhausted and thoroughly frustrated searching the internet and not able to find any answers. I have browsed through lot of articles, videos etc most of them fairly basic and others not directly providing me with what I am looking for. Instead of wasting my time doing something non-tech, I decided to make this post which may avoid the frustrations of some future user switching from Filemaker to Base. I could not find any switchers guide to move to Base from Filemaker. Here, in this post, I am attempting to find out the manner of creating a database in Base.

I have created a single invoicing, accounting database in Filemaker which I have been using for over a year (i.e. I am no expert, but I am fairly comfortable using Filemaker). I have no knowledge of Access or any other such software.

I will lay down the sample steps (steps necessary for my database but can be thought of as general steps) required to create a database in Filemaker and perhaps some one here can show me the way these steps are performed in Base so that other Filemaker users can find it easy to switch.

1. Create Tables

2. Fields can be of type "Calculation", "Lookup", "Summary" etc among other types. Calculation fields are used to perform calculations using fields from the same table or different tables. Summary fields are capable of (among other things) summing the values of fields from the same table and displaying them in each record as running total etc. Lookup fields work based on the relationships created to retrieve values from fields in other tables. (The inability to find a workaround for these three fields is probably my biggest problem, currently, though I am prepared for many other hurdles to come. "Queries", huh?).

3. Create One-to-Many Relationships by joining tables with a primary key (any field present in the one-side table) and the corresponding foreign key in the many-side table. (In Filemaker, there is no such key as primary key. You can join any fields). For one-to-many relationships, a unique key is necessary on the one-side in Filemaker.

4. Create Layout (referred to as "Form" in Base). You edit the type of field to "Drop-down list", "Combo-box" etc. unlike Base where some controls have to be dragged, populated with lists and then values copied to the field in question. I don't know what happens to the existing fields that were supposed to be Combo-boxes and to which the values have been now copied.

5. Create portals (called sub-form in Base). I have no idea how to customize (changing fields to Combo-Boxes etc) the portal fields in Base (All I see is the column names).

6. Writing Scripts (not any particular language) but Filemaker's own syntax. I do not know what scripting language must I know for Base. (I see SQL sometimes and at other times I see something like Visual Basic (something I saw an expert Drew B write somewhere in a really large post)).

7. Reporting.........get to that later. Let me have something working and useful first.

There is no such thing as "Queries" in Filemaker. Though I have seen a few videos showing how queries are created and executed to display the result, I do not know how to use the values or results generated by the queries. For example, if a query multiplies two fields from the same table, how must I display the result in the "Form" everytime a user enters newer values for the two fields. I guess a the learning curve in Base is all about Queries, SQL etc more than anything else, but what do I know?

I am determined to learn Base, but fast. :D
LibreOffice on Mac OS 10.7
Posts: 5
Joined: Sat May 11, 2013 7:09 am

Re: Switching from Filemaker to Base

Postby RPG » Sat May 11, 2013 11:26 am


Start reading in the tutorials of this forum. I think special the tutorials of Villeroy and DACM can be real helpful. Continue in using you old program until you understand how OOo-base is working. I have no answer on your question but I think they will be answered when you read tutorials.

When you search on the internet search only on this forum and add author names to the search. You can also search only in a database forum.

There are two sub forums for Base one for Tutorials and one for examples you can find there a lot of information

More to read
I think a good starting point can maybe this [Tutorial] Forms in OpenOffice.org Base. You can find there also links to other tutorials. Not all tutorials are even important for you

This tutorial of Arineckaig describes what you can do with the Navigation toolbar and with the navigation control.
LibreOffice on openSUSE Leap 15
Posts: 2192
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Switching from Filemaker to Base

Postby MTP » Sat May 11, 2013 1:50 pm

2. The field types you describe in Filemaker can be replicated in Base by using SQL. To a very limited extent, in Base the query "design view" and the various wizards can shield you from having to write SQL. At the level you are at, it sounds like you will actually need to learn SQL to get done what you want.

3. Queries are just a package for SQL. If you want to pull data just for a single field in a form, it's a matter of personal preference whether you put the SQL separately in a query or if you put it directly in the field. For a complicated SQL expression, the wide open space in the query editor lets you see the entire expression, vs. having it crammed inside the little SQL box in the "properties" window of a formfield. For certain uses of subform slaving, putting the SQL directly in the field can reduce the need for "refresh" buttons or macros. If the same dataset is going to be used in multiple forms (or perhaps if you want to export it to, say, Calc), it's generally better to have it in a query, so if you edit the query both (or all) forms are updated.

4. Have you found the "Relationships" view in Base? Tools -> Relationships, click the little icon of a table with a plus sign to add tables, then drag and drop to create relationships. Sometimes it matters which direction you drag, so if you get an error, try creating the relationship the other way.

5. Are you using the form navigator? The icon in the shortcut bar is a table with a little compass in the upper right hand corner. Fifth from the left (or top) in the "Form Design" toolbar. That is how you create and manage forms and subforms. As far as changing a field from one type to another (say a text box to a combo box), right click on the field and choose "replace with" and then choose what type you want. Although in Base, list boxes are typically much more useful than combo boxes (from reading the forums, I've gathered that combo boxes in other frontends do what list boxes do in Base).

6. For working within the Base graphical user interface, making forms, creating queries, SQL is the language you want. If you run up against a limitation of the GUI, or are advanced enough you just want it to look slicker, then you may want to learn to use macros. Macros from the message boards are generally written in StarBasic, though Base also accepts macros in Java, BeanShell, and Python.

For the question you asked at the end, the specific situation you described may not be best solved with a query. Within your form, go to the control of the field where you want the multiple displayed. Right-click on the field and choose "control". (Or, in the form navigator, right-click on the field and choose "properties". Gets you the same popup.) Choose the tab "data". Under "type of list contents" choose "sql". Under "list content" type
Code: Select all   Expand viewCollapse view
SELECT "a" * "b" FROM "Table"
. You will need a button in the field with the "Action" of "Refresh form". After the user enters new data, they will have to press the button to see the updated multiple. (It is possible to eliminate the need for the button with a macro.)

If you did want to use a query for that purpose, when you create the form, you would have the form (or subform) datasource be the query instead of the table. Then in your fields, you would have the option to select the calculated field as the field datasource.

I hope at least some of that made sense - you had a very general post and I wasn't sure what specific questions you were hoping to have answered.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests