Written by Darryn Lowe | 29 January 2009
Posted in How To...

Many people seem to have sold Bento 2 short. Sure it's nothing compared to FileMaker Pro but then Bento 2 was never really aimed at that market. Bento 2 was aimed at individuals and even small businesses.

But Bento is not a feather weight either. Bento is actually capable of some pretty amazing database structures. So in this article I am going to show you how to create a relational database structure by creating a very simple invoicing database.

First we will create a new Library. A Library is the equivalent to a database in FileMaker. Bento works slightly differently to FileMaker in that it has a single file that stores the collection of Libraries. FileMaker on the other hand stores each database as a separate file. The Bento way is better in some respects because everything is in the one place and you only need to backup one file but it is messier because sooner or later your data just becomes immense and Bento will run slower with all the Libraries etc. There is a little trick that I will show you at the end of this article about that.

So let's create a Library. Lunch Bento 2 and on the left of the screen you can see the Library listing (as you see on the left here). In my list I have three Libraries created by me. These are relational databases that I created in order to keep track of my jobs that I get given at work. The next three are built-in references to Address Book and iCal.

To create a new library, click the button in the Library list section that looks like this...

This will open the Template window. For now, select the "Blank" template option and change the name of the library you are creating to "Customer Details" and then click "Choose". You will now see a screen like this...

This is the Table view and it looks pretty ugly doesn't it? It's also devoid of any fields so we need to add some. Below the Library list there is the Fields list. It may be hidden so if it is click this button...

This will show and hide the Fields in your Library. Notice the "+" icon at the bottom left? This will allow you to create a field so click on this to produce the "Create a Field" dialogue box.

The nice thing about this box is that if you use the "Create and Continue" button it will allow you to create multiple fields without having to go in and out of the screen until you're done.

In Section 1 we have the field types. For this first field we'll leave it as a "Text" field. Section 2 names our field so call it "First Name". For now we'll keep Section 3 ticked. Now click "Create and Continue".

• Repeat this process but make the name "Last Name" then once again click "Create and Continue".
  
  
• Now scroll the options within Section 1 until you can see "Phone Number". Call this field "Contact Number".
  You'll notice that Section 3 has changed. From the drop down box select "Main" and leave "Format" as "None". Click "Create and Continue".
  
  
• Choose "Address" from the list in Section1, change name to "Mailing Address", choose "Custom" from the drop down menu and change the label to "Mailing Address" and click "Create and Continue".
  
  
• Repeat this last procedure but name it "Delivery Address" and change the label to this as well. "Create and Continue".
  
  
• Choose "Email Address" from Section 1, call it "E-Mail Address", and like above change the label to "E-Mail Address". Click "Create".

Now you will be taken to the Table view again. You'll notice that it now has a spreadsheet look and feel to it with all the field headings laid out at the top. It's still ugly so we're going to change views to a form. Click on the "Untitled Form" at the top of the screen. Your main screen should look something like this:

You'll notice that this is missing all the fields that you created. Fear not for these only need to be added in. Delete the two fields shown. Make sure to click the label and not the box otherwise it will try to enter text. I normally do a 'Command-A' (Select All) then press the Delete key. Your form should be completely blank now so let's add the fields into the form.

In the Field list on the left of the screen you should now see all the fields you created. Notice that there is an "Address List "field showing. Ignore this. You'll also notice that under "Delivery Address" and "Mailing Address" there are sub-fields. Select only the "Delivery Address" and "Mailing Address" fields. These sub-fields will be copied across with each of them.

Now drag and drop the fields until you have something that looks like this...

Congratulations, you have completed your first Library. Looks a bit ugly still doesn't it? Right Mouse Click on the form anywhere that isn't a field and select "Choose Theme" from the menu and from the submenu select the "Gun Metal" theme. Your form should now look like this...

Now let's just create a record. Simply just start typing. Enter anything you want. This is your first record. To create more records click the "+" icon at the bottom of the screen.

Now we need to create another Library so repeat the procedure above with the following fields...

• Library Name: Product Details
• Text: Product Name
• Text: Product Code
• Text: Product Description
• Media: Product Image
• Currency: Product Price (ex GST)
• Pretty much leave everything as default and click "Create".

Drag the fields to the Form and have it looking something like this...

To resize fields, click on the label and you'll see drag boxes on the middle-right, bottom-right corner, and middle-bottom.

Clicking and dragging these will alter the size of the field. One good thing about this is a text box when dragged automatically becomes a memo field and any amount of data can be entered in here. Scrollbars will show up on the right when you reach the bottom of the box.

Now enter some data into the record. Just like the other Library if you want to add more records simply click the "+" at the bottom of the form...

And now we are going to implement some very powerful features of Bento, the relational field.

Create a new Library with these details:

• Library Name: Invoice
• Related Records List: Customer Details (data source = Customer Details)
• Related Records List: Product Details (data source = Product Details)
• Calculation: Total Cost (ex GST)
• Calculation: GST
• Calculation: Total

You'll notice that there is a drop-down box with a list of the Libraries that we have already created when we use the "Related Records List". This will allow the data from these Libraries to be used within the Invoice database.

The Calculation field is a little different so I need to explain this a bit. You'll notice that when you click on "Calculation" there are no options to do anything. However, when you click "Continue" there is a pretty full-on dialogue box...

First enter the name. In our case it will be called "Total Cost (ex GST)". In Section 2 scroll down until you find "Product Price (ex GST)" and select it. Now click the "Insert "➡" button and the right hand box will look like this...

This needs to be changed so click on the ▼ beside "Count" and select "Sum". Now change Section 3 to "Currency" and just leave the settings as default. Your box should look like this...

Click "Create" then repeat for the other fields.

Here's the calculations:

GST: ( Total Cost (ex GST)* 1.125) - Total Cost (ex GST)

Total: Total Cost (ex GST)* 1.125

Now create the form and it should look something like this...

I have to admit I'd really like to have the Related Records List have views that match the form rather than the ugly Table View as you can see. This isn't really an issue as we can remove some fields using this button...

...on the bottom right of each Related Records List box which will allow us to tick and untick each field we want and don't want respectively. Leave the "Customer Details" field alone but untick "Product Decription" from the "Product Details" field.

Now comes the fun part. Let's enter some customer details. Now you can actually enter the details manually using the Table View but for now we're going to select our existing customer. To do this on the bottom left of the "Customer Details" is this button:

Simply click on the field that you want entered then click "Add To List" and close this window.

Repeat this procedure for the "Product Details".

Now, if you've done everything properly the boxes for the pricing should change accordingly. The problem is that there doesn't seem to be any way to create a nice layout for printing an Invoice. This limits the usefulness as an invoicing database but there are other potential uses for example stock records etc. This is really just for you to get an idea of what Bento can do. It's by no means a good replacement for FileMaker Pro but then it doesn't set out to.

One thing I did miss out was this button...

This button on the "Related Records List" field will take you back to the original Library so you can add records that way or so you can view the details properly and in a nicer format.

Summing up
What amazes me about all this is just how simple it is to do. While FileMaker is much more powerful and more able to produce better print layouts, Bento is by far easier to use. We're talking about what I call a 30 minute app, where (with a bit of playing) you can come to all this on your own. I managed to work all this out without looking at the manual at all. That is great design. FileMaker on the other hand is what I call an RTFM app...one that is very complex and is best to read the manual before entering the app. That's not a bad thing on FileMaker's part. It is because it is a far more substantial application than Bento.

Using Multiple Bento Databases
At the start of this article I mentioned how Bento really differs from FileMaker. This is not a minor issue as on the surface, it means you can't really use different databases, just different Libraries. This means you end up with very large databases and very long load times.

There is a way to get around this however. First you need to find the Bento database. This can be found in "~/Library/Application Support/Bento". Rename this to say "bento1.bentodb" (ensure the .bentodb extension remains).

Open Bento and it will create a new database. Close down Bento. Now hold down the Option key and load up Bento again. You should see this screen:

Click "Choose" and navigate to the above path. Once selected click "OK" and you you're all go. Not nice but it works.