FileMaker Pro 12 Tutorial | Why Relationships Matter

the database we've been working with so far is pretty simple it has only one table a database with only one table is sometimes referred to as flat or you may hear it called a flat file database by flat we mean it has only two dimensions height represented by the multiple rows or records of data and width represented by the multiple columns or fields this simplicity can be a good thing for one thing it's easy to understand but it does come at a price the more data you accumulate in a flat data structure the more often the same information will be repeated in different rows for example in the database we have here the second and ninth entries both lists Penguin Books is the publisher you can imagine that by the time I have a thousand books entered into the database there may be dozens of them listing penguin books and there'll be other publishers that are represented multiple times as well one problem that arises from entering the same information many times over is just that we have to type it over and over again so it's burdensome but a more serious problem is that each time we type it there's the risk of a typographical error or some other difference in the way the information is entered if we end up with a publishers name entered several different ways then perhaps we don't know which way is correct and if we search for books by that publisher we may only turn up some of them in the search results we're still if we notice an error say a spelling error and corrected on one record then we have quite a bit of work to do to locate all the other places where the duplicates of the information appears and see if it needs to be corrected there as well at this point the seeming simplicity of a flat file database starts to look like more of a liability than an asset a way to solve a number of problems of this kind is to separate the data into two tables the table we already have here has its main focus on books each record contains information about a different book if we create a separate table that lists all the publishers we can then store the information about each publisher only once in the list of publishers and just link each book record to the corresponding publisher record a file that has two or more tables linked or related in this way is called a relational data and while it's a fairly simple trick it can solve a lot of problems in this lesson I'm going to show you how we can turn this flat file database into a relational database bear in mind as we go that the specific techniques and moves I'm about to use here will be discussed in much greater detail later in the series for now it's the principle that's important so bear with me while I walk you through the process so you can get a sense of where it leads to begin making this example database work on relational lines the first thing I need to do is to add another table to accomplish that I'll first choose the manage database command from the file menu as you can see the manage database dialog has tabs across the top four tables fields and relationships to make a change to the table structure of the file I'll first navigate to the tables tab here you can see that there's only one table listed and it has a default name that was derived from the original name of the file when I select it I can enter a more appropriate name into the table name field below the list when I do that and click the Change button it'll modify the table name and you can see the change reflected in the tables entry in the list of tables next I'll enter a name for the new table I want to add and then click the Create button to add it to the list of tables that's easy and I now have a new table but as the details column in the list of tables shows it doesn't yet have any fields or records to add fields I'll navigate to the fields tab and create a serial number field and to publish a name field for this table first I'll enter serial number using the hash symbol for the number component of the name into the field name box the serial field should be a number field which is one of the options in the type pulldown menu now I'll click the Create button and you'll see that the first field is added to the list of fields while I'm at it and with the serial number field still highlighted in the list of fields I'll also click on the options button over here at the right to have farm agar automatically enter serial numbers in this field creating the publisher name field is a similar process except of course the publisher name field will need to be a text field not a number field and of course it won't require the option for auto entry of numbers with the new table added and containing some fields I can now click OK and return to the database window the first thing you might notice now that the manage database dialog has been closed is that FileMaker has automatically changed to the name of the current layout it was previously called work file and now it corresponds to the new name of the table it's based on if I click on the layout drop-down menu you can see that FileMaker has also automatically added a new layout with the same name as the new table when I go to the new layout as you can see there are zero records in the table but if we look at the new layout in layout mode you can see that both of the fields are defined in the new table are already waiting for us on the layout I'm going to make some minor changes here and position the fields side by side before returning to browse mode which is simply a matter of dragging the fields and their labels around to new positions with the mouse change the font size down a couple of sizes also make those labels have a white text color so we can see them against the background and I'll reduce the height of the body part so that we can use this as a list layout let's see what that looks like in browse mode okay that'll do the next step is to add the publisher names to this new table since the publisher names have already been entered into the books table I'm simply going to import them to bring them across into the new table first though I'll need to make sure that I'm bringing only one instance of each publisher name across because we don't want to flick 'its in this case it's pretty easy because we don't have a lot of data and only one publisher appears more than once so I'm just going to go to the second instance of the publisher that's duplicated and manually omit that record this leaves me with a fan set of just the 14 books that have unique publisher names now rather than entering the publisher names one at a time into records in the new publishers table I can use filemaker's import records command to bring the publisher names across into the new table from the existing entries in the books table this trick requires choosing the file I already had open work far four is the source for the data import the import field mapping dialog requires me to choose what fields in the source table the books table will be brought across into each field in the current table the publishers table so bear with me for a moment as I set that up now I'll click the import button and FileMaker prompts to ask whether auto into values such as the automatic serial number field I created in the publishers table should be filled in for us during the import and yes it should I'll click the import button to proceed and there we have it that was easy at this point I have two tables in the file and they both have data but they aren't connected to link the tables I'll have to pay another visit to the manage database dialog first of all and need to add a publisher ID field to the books table so I'll go across to the books table and enter publisher ID and click create now I've created the field as a textfield but really it needs to be a number field to correspond to the serial number in the publishers table so I'll make that change next I'll go to the relationships tab of the manage database dialog you can see that on the relationships tab there's a box representing each of the two tables this is where things start to get really interesting I can now simply drag a line between one of the fields in one table and one of the fields in the other to make the connection between the two tables the particular connection I've made is one where if the value in the two fields joined by this interconnecting line matches then the records will be related it's the most common type of relationship and it's indicated by the equals sign on the label box here on the relationship line between the two boxes having created the relationship joining the two tables here I still need to update the data in the publication ID field to enable the right connections between specific records all the Smiths the manage database dialog and let's go back to the books table to make a couple of changes to the layout first of all I'll switch to form view and then go into layout mode move these fields a little over to the left to give us some more space and now I'll use the field tool in the status toolbar to bring down another field and add it to the layout publisher ID field and again with the field tool I'll bring down a field however this time rather than using a field from the current table I'll choose the related publishers table and add the publisher name field make the field a bit wider bring the field label up pop it over the top there let's have a look at what we've got when we go back to browse mode there we are all the existing data is still there on the left and we have the new publisher ID field plus the related field from the publisher table neither of which are showing any data at the moment so to get the connections between the two tables and now need to put a value into the publisher ID field to point to the appropriate record in the publishers table so I'm going to do that by selecting records replace field contents and I'll simply have FileMaker issue a serial number directly into the publisher ID field as soon as I do that you can see that the connection to the publishers table has been made and each of the records now shows the corresponding publisher name in the field in the publishers table just the same as the data that it shows in the publisher field the original field in the books table now that the records are linked up the publisher name field in the books table is redundant so I'll delete it back into the manage database dialog onto the fields tab and there's the books table and publisher field I'll click the delete button and confirm as soon as I do that you can see that the publisher field has immediately been removed from the books table because it no longer exists in the database now I'll go into layout mode and move things around a little so that we've got a more sensible arrangement on the layout what I'm doing is putting the two new fields over in place of the original publisher field that's been deleted now I'll select all of those fields and bring them back out into the middle where they were originally somewhere around there and return to browse mode let's look at this layout momentarily in list view and you can see that all of the records in the books table are showing the appropriate publisher name however what I didn't do was update the one omitted record so let me go and show emitted only I'll manually enter the ID which was number two for the publisher for this ninth record and now when I click show all we can see all fifteen records showing related data in the publisher name field from the publishers table what we have here is pretty basic but it's an important step one way to demonstrate how significant this is is to change the name of a publisher with this new structure when I edit the name of Penguin Books let's say I changed the name to penguin series now I've made that change on the 9th record without doing anything if I simply scroll up and view the second record the other record that had penguin books as the publisher as you can see the name is showing as penguin series now and in fact if I go across to the publishers table you can see that the name of the second publisher is penguin series if I changed the name back to the correct name penguin books here in the publishers table when I go back to the books table record number nine and record number two a showing the name updated as Penguin Books again so even though I edit the name of a publisher on one record the change is instantly visible and all of the other book records for the same publisher we now have a database structure where we can store and maintain the publisher name in only one place in the publishers table yet see it wherever it's relevant

Michael Martin

3 Responses

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment