From the course: FileMaker: Tips, Tricks, and Techniques

Importing to refresh data - FileMaker Pro Tutorial

From the course: FileMaker: Tips, Tricks, and Techniques

Start my 1-month free trial

Importing to refresh data

“

- If you've been working with FileMaker for a while, you're likely already familiar with how to get large sets of data into your database by importing. As a matter of fact, that technique's pretty easy. You just go under the File menu, hit Import, choose File, find the Import Source, and select Open. And in my case, I'll choose which column in my spreadsheet, I'm going to use as Field Names, and then map up all the rest of the fields to make sure that they're going to the right spot. And I can choose my source. Now, by default, you're probably already choosing Add all the time when you're doing your standard imports. And in that case, it'll import all of the records from your source file into your FileMaker database. Now, what you're probably not familiar with is that you can also use imports to update previous imports. So, let's say we've received this spreadsheet as a source file to import customers into our database. We import them into FileMaker much like we've just done. And then later, we find out that the person who is in charge of that source has made some changes to it. Now, you might think to yourself, "Oh boy, "I've already made some changes myself. "Like, maybe I've updated some of the phone numbers "or added notes." So, the dilemma you might feel that you have there, is that, well if I have to import all over again, then I'm going to lose all of the information that I've updated here. Well, there's a way that you can restore the updates that you've made in your FileMaker database while still benefiting from the update from the new source file. So the way that we do that, is to go into our FileMaker database, go back under File, Import Records. This time we're going to choose the updated version of the file. And we're going to be presented with the Specify Import Order dialog. Now the key here is that we're going to want to change our Import Acton from Add to Update. Add, added all the new records to the database, regardless of whether or not the database already had matching records. And Update, which is what we want to do here, matches records with information from the source file. And this is because our new source file, not only contains updated existing data, but it also includes a new record. Notice that this record doesn't even exist in our database. So there's another setting we're going to have to take into account there. And that's where we go back into this Import Action dialog and select the Add remaining data as new records option on the bottom. So next, we'll make sure that the first record is either Used as Field Names or actual data. So in this case, these are column headers, just like our first import. So I'll leave those as field names. I go to the second record and I see that that's data. I then want to go through and make sure that all my fields are mapped accordingly. And we see that these are all mapped as they should be. Now the important thing here is I have to tell FileMaker how to identify matches. So I have to pick a field or a series of fields, that when match are going to identify the records in our FileMaker database that need to be updated. So in my case, I'm going to use Email. So what I'm saying is, anytime you see an email in the source file, that matches an email in the FileMaker database, then I can update those records. That also means that if I see a record in the source file, that has an email that does not exist in the FileMaker database, it's going to add those as new records. So I locate the email in the source field, and then it's corresponding target field that it's mapped to, and I click on the Mapping dialog. You'll notice this blue option here, Match records based on this field. This blue option is only available, if you've chosen the import action of Update. So now what I've done, is I've said, "Anytime you see a record in our source file, "That has a value that matches "a record in our customer table, "then update that record." This is referred to as our match field. Now, we're not done yet. We also have the ability to tell FileMaker which of these fields will be updated when we do this update import. So I'm going to say, contact first is imported. We'll leave that as the green import option. A contact last, we can turn off. Company Name we'll leave as imported. And the rest of these, we'll turn off. Let's say that we have been updating all of the addresses in our FileMaker database. So we want to make sure that that is the single source of truth. And any notes we've potentially added in our database, we want those to be maintained. And also the phone number that you saw me update, and some of the Created By records, we want to leave as is. Right now, based on these settings, it's going to find records based on the match field of Email, matching Address Email in our customer table. And it's going to only update any changes that it finds between the two records, as they pertain to the First Name, Company, Website, Tax Rate, Discount Rate and Fax fields. And you'll also notice that we have the ability to change some of our auto-enter options. Since the two fields in our list that are affected by auto-enter options are not chosen to be imported, we don't have to actually do anything there. So let's go ahead an do our import. And it says that we've imported five records. And really what that means in this case is that doesn't necessarily mean that we've added five records, but that five records were determined as either new records or matches. So if we go into our source files, we see that the differences that were made were in the first record of the First Name, Company and State. Added the record in row 6 which is actually the 5th record. So we'll notice, that the contact First Name was changed. And the State was changed. And the Company Name. So all those updates happened appropriately. We also notice that the phone number that we added in the FileMaker side, and the notes that we added on the FileMaker side, have both maintained. Additionally, if we go all the way to the end of the records that were added, we see that the 6th row or the 5th record, Scout, was added to our FileMaker database. So what we've accomplished here, is we've successfully added any new records from our source file, while updating only the fields that we chose inside of our customer table. So you can see that this process can be very important for ongoing, updated imports. And if you don't want to update any data that you've been managing in your FileMaker database, while still being able to incorporate updates from your source file.

Contents