Productivity solutions guide
http://www.macworld.co.uk/filemaker
FileMaker includes a PDF on how to customize FileMaker’s template solutions, but the suggestions are more in terms of adding fields and layouts to one solution.
Almost any business solution requires some form of table for customers or suppliers. (Indeed, most will probably require tables for both.) It would be useful if the Contact Management template automatically tied contact data into the Purchase Orders template. It isn’t difficult to do, and the time savings and improvements in data accuracy are very rewarding.
Here, we’ll link them together using a pop-up menu: selecting a contact from the pop-up menu on a purchase order will copy the relevant details across from the contacts database. Taking advantage of FileMaker’s ability to look-up data, we’ll ensure you can change contacts address details for future orders without altering historical orders.
1. Open FileMaker and from the New Database dialog that appears, select the Create a new file using a template option. From the pop-up menu make sure you’re in the Business—Finances section; the template you want to choose is Purchase Orders.fp7. Click OK.
2. A file dialog will appear, asking where to save the file, and what to call it. For now, you can save it to the desktop: can move all the files together into a folder somewhere later. Click the Save button. You should now see your new, empty database appear. Let’s start by entering a new record.
3. Choose Records
New Record, or press Command-N. Press the tab key to move from field to field until you get to the first Product ID field. Enter a ficticious ID, and then tab to the few fields, adding made-up product details as you go. Notice that FileMaker calculates the subtotal and grand totals for you.
4. You’ve tried out your first template. Time for another one. Effectively, we’ll repeat the steps 1-3: Choose FileNew Database. We’ll need Contact Management.fp7 from the Business—People & Assets section. Save your copy to the desktop, and when the file opens, create at least two new records and fill them in appropriately.
5. Choose WindowPurchase Orders to go back to the first database, and then click FileDefineDatabase. With the Fields tab selected, type Bill To ID into the Field Name box, and click the Create button. This new field will tell the database to whom a PO is to be billed. Click OK.
6. With somewhere to store the ID of the contact to bill. We now need to get FileMaker to give us a list of contacts: we’ll get FileMaker to generate this from the contacts file. Choose WindowContact Management, and then FileDefineValue Lists. At the bottom of the dialog, click the New button.
7. You could manually create a list of contacts, but it’d need updating for every new contact. Luckily you can define a list using the distinct values stored in a field. Name your new list Contacts, and click the Use values from field option, to choose the field representing those values you want in the list.
8. In this case we want to display Contact ID numbers. FileMaker understands that numbers by themselves might not make much sense, so we can check the Also Display Values box, to show an associated value—in this case Full Name. Keep clicking the OK button until you’ve closed all the dialogs.
9. Next, we’ll create our pop-up menu of contacts. Choose WindowPurchase Orders to switch to the PO database. Switch to Layout mode (Command-L). Move the newly added Bill To ID field above the Bill To field, and choose FormatField Format…. Choose Pop-up Menu, and select Define Value Lists.
10. Click the Use value list from another file option, and then select Add File Reference. From the resultant file dialog, choose the Contact Management.fp7 file. Up pops the value lists from the Contact file. Select the Contacts value list we created in steps 7 and 8. Keep clicking OK until all the dialogs are closed.
11. Now we’ll link the two files. Choose FileDefineDatabase, and click on the Relationships tab. Click the left-most button to add a table. From the pop-up, menu choose Contact Management. From the list below select Contact Management, name the occurence Bill To Contacts, and click OK.
12. We can now see our two tables: Purchase Orders and the instance of the contacts table that represents our billing contacts. To link them, click on the Bill To ID field in the Purchase Order table, and drag onto the Contact ID field in the Bill To Contacts table. FileMaker creates the link for us.
13. We’re ready to use FIleMaker’s look-up function. Click the Fields tab. When we select a Contact ID from our new pop-up menu, we want FileMaker to fill in (look-up) the corresponding Bill To values. In the field list, double-click the Bill To Name field. Click the Looked-up Value option. Now, from the Lookup from related table pop-up menu choose Bill To Contacts. You should now see the field list from the Contact Management file. Select the ::Full Name item, and click OK twice to get back to the Fields dialog. Repeat this process for the Bill To Company, Street, City, State and Postal Code fields, in each case selecting the appropriate fields (Company, Address 1, City 1, State Province 1, Postal Code 1) from the Contacts table. When you’re finished, click OK, and then switch to Browse mode (Command-B).
14. Let’s put it to the test. Click on your new pop-up menu. You should see a list of contacts from the Contact Management file. Select someone from the list; their ID is copied into our Bill To ID field, and FileMaker then updates any field which is set to look-up values based on Bill To ID, and voilá! Our Bill To details are filled in.