The Import Field Mapping dialog box - FileMaker Pro Tutorial

of a FileMaker custom app is to store and manage a bunch of different data. The best way to get large amounts of data into your app is to do what's called an import. So for example, in this case I have a Products table. And that Products table does not have any records in it. And in our example let's say there's another application that has a lot of products data, or connect via API, which are both native functions in FileMaker that can extract data from other applications. So in this case, let's say we just have to dump data out of this other application so that FileMaker can import it. First you'll need to export that data in a format that FileMaker supports for import functions. For example, other FileMaker Pro Advanced Files, Custom-Separated Values, Comma-Separated Values, Merge Files, and Excel in both different file formats, the .xls or the .xlsx and even dBase Files. So let's say in this example our other application can dump data out in the form of an Excel file. So now what we do is determine which table we want to bring our data into. So in this case I'm going to say we want to go to a layout that's based on products because this layout is based on the products table. That way we can import data into the table by choosing that context. So I'll go into the File menu to Import Records, and I'll choose the file option. So you'll see in this dropdown all the different file formats that are available to us. But I'm going to choose the xlxs file, or the Excel file. I'm going to hit Open. And because I've chosen Excel I have this one other screen which we normally don't see with other formats. This is just simply asking me which of the three worksheets I want to import in. I hit OK. And then we see the new Import mapping dialog box that was just introduced in FileMaker Pro 18 Advanced. And the first thing we're asked is to choose which type of import we want to perform. There are three types of import. There is the Adding Records option. When you add records the import process creates a new record in the target file for each importable record in the source and then imports the fields that we select. There's also the ability to update matching records, and when we choose this option the matching record in fields in the target file are updated with the data from the source file. And then finally there's the ability to replace records. When choosing this option the data in the target file is replaced with data from the source file. So in our example, we're going to keep it at the only option we have available here, which is add. Next we want to make sure that we have the correct source file chosen. So you see here is the Products Excel file that we chose. And then the target, this is where the data is going to go inside of our FileMaker app. So I've got the current table products chosen. I can also create a new table if I don't have that table context already. And before we do any importing we have to take a look at the data. So take a look at the left-hand side of the screen over here. Notice that we're on record number two of 32 records. That tells us that we're bringing in potentially 32 records of data. But why don't we start on number two. That is because FileMaker Pro 18 Advanced now has the intelligence to know that the source file was a file type that uses rows. In this case it was Excel. And that the first row might actually not be data, and therefore, with a preview of record number two, we can assume that the first record is data. In this case it's even different. The first row of our file, as we can see here, is actually not even data at all. This is just a formatting row. A lot of times individuals will create different rows just to do formatting inside their document. And that's not data that we want to import in. So now in FileMaker Pro 18 Advanced, we have the ability to exclude that data. So what I'll do is I'll navigate over to the second record. And this is the record that actually contains the row You can sort by Creation Order, Field Name, Field Type, Custom Order, whichever one might help you do the mapping. If you notice that matching names is not available, that usually means that's because your source file doesn't have field names or you have not yet designated one of the records as the field names. Now of course, we've done that over here by designating row number two as the field names. So we have the matching names option available to us. But now we still have a bunch of other fields that we need to map. So you'll notice that some of 'em still say do not import. If we intend to actually import those in we need to map those fields. The reason that FileMaker didn't do that for us because they didn't actually have an exact match name. Now in FileMaker Pro 18 Advanced, this has become easier than it's ever been. So for example you'll notice that part number does not have a corresponding part number target field. But if I click this dropdown, I can see that there is a part number, it just has the word number spelled out. By simply clicking through some of these options you'll see that it's very easy for us to be able to map these. And if you don't find something, you can use the type ahead. In that case you see that we don't have a field for shelf. We also don't have one for on sale, but that is one that we want to bring in. So I'm going to pick this manage database icon and I'm going to add a field called On Sale. I didn't have to lose my place at all. I can go right back here and I can map those. If you can't find it by scrolling through the list you can also use the type ahead filter. And with just a few easy clicks we're able to map everything from the source into the appropriate fields in the target. No more scrolling around and losing your place. But wait, there's more that's really interesting here. You'll notice that when we were mapping, some of the fields, like Part Number, Created By, Day Created, and Date Modified have a gear icon next to them. The gear icon indicates that that field has an auto enter option to find. So this gives us an opportunity on a field-by-field basis to determine whether or not we want to bring source data into that field, or if we want to allow FileMaker to follow the rules that we've set up in the field options for auto enter. So let's say we want to keep the original creation account instead of overriding it with whatever the name of the account is that the user is logged in at the time of the import. But when it comes to Date Modified we do want to actually perform the auto enter option. So Date Created, Created By, and Part Number we're going to leave as is, but Date Modified we actually want to bring today's date or the modification date, into the Date Modified field. So no more all or nothing. FileMaker Pro 18 Advanced allows us to go field by field to choose which auto enter options will be performed on import. will be performed on import. Now you'll notice on the bottom of the screen Now you'll notice on the bottom of the screen perform the auto-enter options perform the auto-enter options for fields during import has a dash. for fields during import has a dash. That indicates that FileMaker found other fields That indicates that FileMaker found other fields with auto-enter import that were with auto-enter import that were not in our target field list. not in our target field list. For example, if I click on this icon For example, if I click on this icon I can see that there are other fields, I can see that there are other fields, primarily the pkProductID field, primarily the pkProductID field, which is not shown on the target which is not shown on the target because I didn't have a source field to map it to, because I didn't have a source field to map it to, and we still want to automatically and we still want to automatically populate those records on import. populate those records on import. So now I have full control over which fields So now I have full control over which fields have auto-enter performed when I import them have auto-enter performed when I import them on a field-by-field basis, on a field-by-field basis, and also even ones that aren't part of the mapping. and also even ones that aren't part of the mapping. So now it's time to hit Import. So now it's time to hit Import. And I see that all 30 records And I see that all 30 records have been imported successfully have been imported successfully in my Import Summary dialog box. in my Import Summary dialog box. And I'll also notice that if I look at And I'll also notice that if I look at the Date Modified field I see that it has today's date, the Date Modified field I see that it has today's date, whereas the Date Created did not update with today's date, whereas the Date Created did not update with today's date, exactly what we wanted to have happen. exactly what we wanted to have happen. I also see that the pkProductID field I also see that the pkProductID field was automatically populated for all 30 records was automatically populated for all 30 records that were imported into the database, that were imported into the database, thus, they now all have their own primary key field. thus, they now all have their own primary key field. The new import field mapping dialog box The new import field mapping dialog box allows you to more easily map imported source data allows you to more easily map imported source data to target fields through type ahead to target fields through type ahead instead of drag and drop. instead of drag and drop. You'll find this new dialog box You'll find this new dialog box both when you import data manually both when you import data manually as well as when you script to import data. as well as when you script to import data.