Productivity solutions guide
http://www.macworld.co.uk/filemaker
1. There isn’t room to take you through every menu selection and button push in just two pages, but this simple invoicing system can actually be built in under ten minutes using the techniques shown here and on other pages. A copy of the final file
is also available by email from dickiedyce@uk.com. The system is based on four tables: Order, Contact, Product and OrderItem. Because we’re making it ourselves, we can place all the tables into the same file. Choose File
New Database and create the four tables with the fields as shown in step 2. Create all the fields as text fields, except for Cost, Quantity, Linetotal, VAT, VAT Rate, Subtotal and Total. These should be created as number fields. Where needed we’ll change them to calculation fields when we get to steps 6 and 7.
2. Once you’ve created the bare tables (using the fields shown in the bottom half of each block above), click the Relationship tab to get to the view above. To create the OrderItem to Order relationship, click on the OrderID field in the OrderItem table and drag onto the corresponding OrderID field in the Order table. Do the same for the other two relationships: drag between the ProductID fields in OrderItem and Product tables, and the CustomerID and ContactID in Order and Contact tables respectively. (Why call it CustomerID? At a later date, you might also want to add Bill To and Ship To details.) When you click OK to finish, FileMaker will build your database, with a default layout for each of the four tables.
3. In order to be able to add order items to an order, the relationship definition between Order and OrderItem needs to be changed. Double-click on the link, highlighted in step 2, and make sure that the Allow creation checkbox is checked on the OrderItem side of the dialog.
4. The tables begin with an auto-enter serial number: OrderItem begins with OrderItemID. It also has a calculation Linetotal, the product of Cost and Quantity rounded to 2 places, and two looked-up fields, Description and Cost, that copy pricing and info from the Product table. We do this in step 8.
5. Let’s look at the Order table. Order is the centre of the database, and it has the most complex table. To track each order it needs a field, ContactID, to identify the customer, and a number calculation fields to keep track of the subtotal of order items, any shipping, the VAT, and the total.
6. We need a subtotal field to add up the Linetotals of the order items belonging to each order—the records that match the OrderItem relationship. FileMaker provides a Sum() function, similar to Excel’s, for summing a related record’s field values: The calculation in this case is therefore Sum(OrderItem::Linetotal).
7. How do we set up the Order table’s VAT calculation field? First, the VAT Rate field is set to auto-enter a value of 0.175 (i.e. 17.5%). Then create a calculation field to multiply the Shipping and Subtotal fields by it, in order to get the total VAT. Finally, we use the Round(number,precision) function to get it to 2 decimal places.
8. The OrderItem & Product tables are linked. A pop-up list containing Product IDs will auto-fill product names and prices by looking up values from the Product table. The dialog shows OrderItem’s Cost field options: (check Looked-up Value on the auto-enter tab). The description field should be set up as a looked up field.
9. We may have linked the Order and OrderItem tables, but the Orders layout needs a portal to show, and to let us create, OrderItems on individual Orders. Adding a portal is simple: use the portal tool to draw where you’d like the related OrderItems to appear, and from portal setup dialog select which relationship to show.
10. At this point, FileMaker offers a helping hand—the Add Fields to Portal dialog. Select which fields you want to appear on a portal row, and clicking OK will place the fields there for you. Notice the names of the fields. Fields from related tables, are shown using double colons to make them easy to spot—relationship_name::field_name.
11. To create our pop-up menu and list we’ll use value lists. For the contact pop-up menu, we define a list with the Use values from field option, specifying the ContactID as the value and checking the Also Display Values box to show the associated Company. We’ll use this to show related Contact records.
12. Each OrderItem record on our portal (from step 9) has a ProductID field. We can format it as a pop-up list, using a Products value list created in a similar way to the Contacts list from step 11. Since ProductID is the basis for the lookup fields, Cost and Description (from step 8), choosing an item from the list will fill in a product’s details. We must also format our CustomerID field as a pop-up menu using the the Customers value list.
13. Once we’ve chosen a ContactID value from our CustomerID pop-up menu, we want to show the contact details it relates the current order to using standard fields, from the Contact relationship. A quick way to achieve this is to cut-&-paste the fields from the Contact layout.
14. In use, every order has a portal showing order items. Fill in one order item, and a blank row appears for you to add the next. Select your customer from the pop-up menu. The ProductID pop-up makes completing a row easy; add a quantity and calculation fields do the maths for us.