Database tasks in Numbers

With the demise of Bento, FileMaker’s consumer-based database app, database choices for those on a budget begin to look a little thin. However, a solution might be closer than you think v. For simple database tasks, iWork’s Numbers, now free to purchasers of new Mac or iOS devices (and cheap anyway), might be all you need.

Numbers may be billed as a spreadsheet, but at its heart a database is simply a way of organising data, and Numbers does this very well. In fact, a spreadsheet table in Numbers can be thought of as a simple ‘flat file’ database. Such a database consists of a single table, with one column or two containing lots of associated fields. If you were tracking your clients, you could, say, have a column of client names, with cells underneath each containing their contact details.

The limitations of the flat-file approach become apparent when you want to use the information within it elsewhere. More powerful databases are relational: they contain more than one table and an entry in one can link to an entry in another. They enable you to store information in a modular fashion, and there’s less duplication of data: you don’t need to re-enter data, just look it up in another table.

You can mimic this more powerful type of database in Numbers, by using its VLOOKUP (Vertical Lookup) function. VLOOKUP takes a value from one table and searches for that same value in another table. When it finds it, it returns the value of a corresponding cell from a specified column the same row of that table. This turns Numbers into a powerful database. Let’s say you’re a small business looking to invoice clients, but you don’t want to manually enter their address or product details on every invoice. Instead you can have one table containing a list of clients and another comprising a stock list. Thanks to Numbers’ Lookup function, simply entering certain data into a cell will automatically enter other information into cells next to it.

Here, we’ll use Numbers’ VLOOKUP function to generate an invoice using client information stored in one table, and stock information kept in another.

MAKE THE INVOICE
  Create the invoice sheet in Numbers. Numbers comes with a perfectly serviceable invoice template in its Template Chooser, so use that as a starting point. It currently holds static data for the address fields and invoice entries, but we’ll change that later.

QUICK IMPORT
  If you have existing contact details in the Contacts app, you can save yourself trouble. Select the contacts you want and drag them over an open Numbers window. Note, though, that this trick doesn’t yet work in the latest version of iWork, so you’ll have to import the contacts into a Numbers ’09 document, then copy it into the current document.


PRODUCTS TABLE
 Set up the two tables that will act as the database sources to populate your invoice. In the Invoice template, click the *+’ button below the toolbar to create a new sheet. The first table of these will comprise a stock list: a collection of products for sale together with their prices and other information.

CLIENTS TABLE
 The second table that you’ll create in the same sheet is made up of a list of your business contacts, including each contact’s name, address and postcode details, entered in a separate cells along a row. This data will be used to populate the address field of the invoice sheet.

ADD A CODE
  Add a column on the left-hand side of the Clients table (right-click the leftmost column and select Add Column Before) where you can add a three-letter abbreviation unique to each client. This will be matched by the code you later enter in the invoice to refer to this client.

EDIT INVOICE
 In the invoice sheet, delete the text box containing the name and address, and replace it with a plain table. Click the Table button in the Toolbar and choose a simple table style from the list. In the Table Inspector, uncheck the options for Table Name and Alternating Row Color, and remove the grid lines.

POPULATE THE CELLS
 Leave one cell empty to hold the customer reference, which you’ll add when you create each invoice. The reference that you add will be matched against code next to each client in the Clients table. There’s no requirement for his code, but by matching short codes, it makes errors less likely.


SAVE AS TEMPLATE
You don’t want all this work to go into every invoice, so to re-use the template, empty the Invoice Sheet of any data and choose File > Save as Template. Click the Add to Template Chooser button, and the template will be available from the Template Chooser from now on.

Ul SEND AN INVOICE
 By sending the invoice as a PDF, you can only send the invoice sheet, rather than all sheets. With the invoice sheet active, choose File > Print. In the Print dialog, choose one of the PDF options and in the Print Preview Inspector, make sure that the option to print This Sheet is selected.

COPY FORMULAS AGAIN
Copy the formula to the other invoice item cells in the same way as you did for the address fields, but don’t preserve the row and column of the Lookup value, as this changes on each invoice line. When you come to enter the product name in the invoice, the Lookup function will enter its price.

AUTOMATIC ADDITIONS
One advantage of using the Invoice template is you only need now to enter the quantity of the product ordered (column B, here) and the untouched Cost column (column D), and thanks to the pre-built formulas that came with the template, the invoice will calculate the total automatically.

RE-USE LOOKUP
 Enter the Lookup formula in the cell that holds the product price (cell C2). Use the same Lookup formula, adjusting it so that when you enter the name of the product in column A, the formula will search the first column of the Product List table and then return its price in the next column.

EDIT INVOICE ITEMS
Once the formulas have been added for the address, you can turn your attention to the Invoice items, which appear on a separate table on the Invoice sheet. Clear the contents of column A - you’ll enter the name of the product yourself when you create the invoice.