[Music] in this video we're going to finish up our import set by going over the field mapping field mapping includes how source fields are mapped to target fields how to identify record updates versus record inserts and potentially any logic that needs to run on specific fields this video also builds on the previous two videos about import sets okay our use case is to import some communication devices from a spreadsheet to the communication devices table if you haven't seen those videos i invite you to go back and watch them otherwise let's take a look at field mapping we'll begin by going to system import sets administration transform apps and locate and open our transform map we made in the last video in the related list we can see that there are no field mapping entries which makes this transform map considerably ineffective at the moment when we click the ui action form link auto map matching fields the system does its best to match the import set or source fields to the target table fields not bad we got 8 out of 9. now we can click either new and enter the field information manually or we can use another related link mapping assist to use a drag and drop ui to map any missing fields let's do that on the left we scroll down to the state field we'll choose the one with the name underscore state so as not to confuse it with the import set state and drag it to the field map like this we want to be careful because other fields move around depending on where we drop it next we'll take the status field on the right and place it alongside the state field looks good so let's say the final step in field mapping is to tell the system when to create or insert new records into the target table and when to update existing records this is done with the coalesce field for example we'll set the serial number field to true that way if we have multiple entries with the same serial number in that same spreadsheet we'll update an existing record rather than creating multiple target records with the same serial number in some cases we may need multiple coalesce fields like first name last name and street address when we select multiple coalesce fields all fields marked true must match in order to update an existing record let me say that again all fields marked true in the coalesce column must match a target records values in order to update an existing record even if one field doesn't match a new record will be created once we've identified which fields to coalesce on we can consider how we want to set the field create new record on empty coalesce fields at the top of the transform map form if all coalesce fields from the source are empty the record would normally be ignored or overwrite an existing record checking this allows us to create a new record in our case we're going to leave it unchecked since an empty serial number is an indication of a data issue and we don't want to create devices without a serial number while we're ready to go on with our example let's take a look at some other powerful yet often less used features in the field map entries when we open the record for model number for example it shows us the source table in field target table in field and whether it's going to coalesce this is the same information we saw on the related list however on the form we've got a few more options we can do at the field level if we need we can run a script on each field perhaps we need to convert the source string value to uppercase we could check this box use source script and set a return value like this to convert the value to upper case like transform scripts the field map script also has some default variables available including source target answer map log and action if this were a coalesced field we could also tell the system to coalesce empty fields or tell the coalesce operation to be case sensitive if our source field date format doesn't match the format on the target field we can always use the date format field to allow us to specify the incoming source date format okay now that we're familiar with most of the mapping features and we've identified our fields to map and coalesce values it's time to make it work by going back to the transform map record we'll click the transform ui action and process these records pick the import set and transform app and click transform system presents a status screen telling us what it did no errors here and when we go to the communication devices table we see our imported records with accurate information the type is cell phone and the status field says installed we've successfully built a data source from scratch built a transform map explored transform script options and created a field map with some options to import our data now when someone sends us an updated spreadsheet all we need to do is attach it to the data source record import the records and transform and unless the spreadsheet changes format all the same definitions apply and the data will be imported to update existing records or create new ones that's it for import sets i hope you'll watch the next video when we talk about scheduling imports so they run automatically