Salesforce Certified Platform App Builder – 3 – Data Modeling and Management Part 6

39. Importing Data – Which Tool is Best?

So we spent quite a bit of time with the data loader now. And so I want to talk about other data import tools and data manipulation tools that are available to you in Salesforce. And so I’ve searched for the word data here and I’m on the Data loader page where we looked at previously for downloading the different links so that we can install the data loader. But in addition to that, we’ve got options for data export, which has to do with doing a weekly your monthly export of all your data records. But as far as importing your records, another option you have is the Data Import Wizard. I’ve mentioned this briefly in this course, but I wanted to go a little more in depth in the Data Import Wizard. And so the Data Import Wizard, the main things to keep in mind, especially as it relates to the exam, is that it’s designed to handle records in the amount of 50,000 records or less.

And then as well, there’s only certain objects that are supported. And so if you scroll down, you can see the Import your Data in Three Easy Steps section. And so you can choose your data to import. First of all, once you’ve prepared your data for import, but I’m just going to launch the wizard and we’re going to talk about this briefly and then we’ll decide which tool is best for your scenario.

The main things to keep in mind or to know for the exam in everyday life as a developer or administrator on the Salesforce platform is which standard objects the Data Import Wizard supports. And the only standard objects that it supports are these. In this list on the left that would be Accounts and Contacts, leads, Solutions and campaign members. If you need to import records for a standard object other than the ones listed here, then you need to go with the Data Import Wizard as far as a determining factor of which tool would be best.

Another determining factor is this magic number of 50,000 records. If you need to import more than 50,000 records at a time for Accounts, Contacts, Leads, Solutions, or Campaign members, then you’ve got to roll out the Data Import Wizard and also need to then use the Data loader instead. The Data Import Wizard can also support the import of custom objects. And so if I scroll down here, I see the different custom objects that we’ve created along with a few other custom objects that are available through some of the apps that I’ve now installed in my own Salesforce instance. And so if, for instance, I wanted to import a long list of episode records for different TV show episodes, I could click on Episodes to select it. And then the next option here is asking me what I want to do.

Do I want to add new records? The equivalent of that would be the insert button in the data loader. Do I want to update existing records, which would be the update button on the data loader, or do I want to add new and update existing records which would be the upset button on the data loader. And so we’ve got insert, update and upset. So the Data Import Wizard is a little more user friendly and intended for more of a nontechnical audience to handle. And so usually administrators will be more familiar with the Data Import Wizard and developers will deal more with the data loader. And so if you wanted to insert then new episode records, you would just click add new records and then you’ve got this matching criteria and so you can match by the name of the episode.

And then also you can specify which production field in your file specifies the master detail relationship. Would it be the salesforce. com ID or would it be the production name? Then you can also trigger workflow rules and processes off of the import. And then finally you would drag and drop your CSV file into this location here and then click next to finish the import of these files. I’m not going to go through and complete the process of mass importing a lot of episodes at this point into our salesforce instance. But the main things I want you to bear in mind in determining which tool is best is this 50,000 magic number for Data Import Wizard. It can’t go beyond that and then the limitations around which standard objects are supported, which are accounts, contacts, leads, solutions and campaign members.

So anything beyond that, the answer would be that you need to use the data loader if you need to do 100,000 records or if you need to mass import opportunities for example. Those would both be scenarios where you would want to use the data loader. And so now other ways that we can do data manipulation and the mass updating of records would be through the mass transfer of records, which is coming up in the next lecture.

40. Mass Transfer of Records

So let’s discuss mass transferring of records. Now, mass transfer of records is somewhat supported in Lightning Experience. I’m on the leads. Tab in lightning experience. So if I click on another related list such as all Open leads as well, I don’t see any sort of link for transferring leads to another user. For instance, now from the Lead leads homepage in Classic, there’s a link at the bottom of the screen. I’ll show that in a moment where you can transfer all leads to another user. I also wanted to show the Kanban board for the Leads object. We previously looked at this for opportunities, but you can also have the same Kanban display for list view on Leads as well. We’ve got our three different statuses on leads as Open, not Contacted, working, contacted and Closed not Converted. And this also supports drag and drop capability to in order to update the status of leads. But the main thing to take away here is that there’s no way to do a transfer of these lead records from the Leads home page. If I go to set up though and do a search for mass, you see some options here to mass delete records, mass transfer approval requests, mass transfer records and mass update addresses.

So we want to click Mass Transfer of Records and you can see that you can mass transfer leads through this means enlightening experience and you can transfer from a user and to a user. And then you can do additional criteria for finding leads that match the following criteria. But what I’d like to do instead is show you the equivalent leads homepage in Salesforce Classic. So we’re going to click to switch to Salesforce Classic. Now that we’ve switched over to Classic, we can go to the Leads Home page by clicking the Leads tab. And if you scroll down, you see under Tools here transfer Leads. So we click on that link that takes us to the same screen. So that’s just a different way of getting to the same screen and so it’s just a user interface or click Path Difference. You also notice here if I search for mass instead of it’s the same four links as well. So the mass transfer of records and then selecting Transfer Leads all takes us to the same place.

So let’s say that I want to transfer all of my Open leads to Jim Doe, who’s the other user record in my Salesforce instance. So I’m going to transfer from user with the last name of Wheeler, locating myself via Search and then I’m going to search for Jim Doe, searching by his last name here and selecting him. From the search results. I could add additional criteria such as having a lead status specified of still working, but I’m just going to leave this set to none and find all of my lead records. And so you notice here the Lead status is working contacted. So let’s say that I don’t want to transfer those that have a lead status of working, contacted, or closed, not converted. But let’s say I want to offload the leads that I’ve not contacted yet to Jim Doe. So I’m going to copy this so I get this text correctly entered and then I’m going to specify further the criteria for the lead status. Now that I can see what the options are, I’m going to just do lead status equals Open, not Contact and click Find and that will narrow the list down to the two leads that I’ve not contacted yet.

So I’m going to select both of those lead records and click Transfer and that will in essence transfer those lead records to Jim Doe. And so the screen updates to say that the two leads were transferred. So now there’s no remaining lead records that match this criteria. So the search results also update and are now cleared out. So another way that you can mass transfer records is using the data loader. And we previously looked at the data loader, showed you how to configure that somewhat and also how to log in. And so I’m already logged into my data loader instance and so I want to mass transfer lead records, but I want to do that using the data loader. The best way to do transfers is to first do an export and then manipulate your data accordingly and then do an import or an update, I guess I should say, because we’re working with just existing records and not importing any new ones. I’m going to click Export and being asked to authenticate again.

So I’m going to click Login and then I don’t need my security token. You may not or you may if you do need to use your security token, just dependent on the end of your password and click Login. And then I’ve already selected to do an export. And so once I’m logged in, it should bring me to, after I allow access again, it’ll bring me back to the Login screen, but it shows Login successful. Remember, don’t click Log in again but just click Next. And now here I’m selecting the object that I want to export. One thing to note real quickly is this checkbox will show all salesforce objects you occasionally will need to get to things that aren’t readily apparent from the data loader. And so if I scroll through without that checked, there’s a certain amount of objects that are shown.

But if I click show all salesforce objects, that updates to show a lot of additional objects that are kind of hidden from view by default that you may have occasion to deal with as you dive deeper into your development and career on the salesforce platform. But for now we’re going to leave that unchecked because we’re trying to export lead records. And so I’m just going to choose a target for this extraction now by clicking Browse and I’m going to say this to my downloads folder. I’m going to call this all leads. CSV. And so this is a CSV file comma separated value file that exports out of the data loader. And that’s the same format that we will then import with. I’m going to click Save, and that saves the path for this exporter extraction of all Lead records. So I’m going to click next I’m going to select All Fields by default. And when I do that, notice what happens down here in this generated query down below.

So I select All Fields and suddenly it’s generated this massive sock will query automatically for me. And so it’s doing a select statement and then starting in order with all the fields that are here, which are all the fields on the Lead record, it’s selecting them all here in the soccer query, starting with the ID of the Lead object for each record. And so I can narrow this down, I could clear all fields and instead I want to select all IDs. IDs are important to grab almost always when you’re dealing with data manipulation and salesforce. And then as well you could do a status and then the owner ID notice that to find the owner of a lead, you’ve got to grab the owner ID.

There’s no owner name, which would be nice, but there’s not. This would be an instance where you may want to do a formula field so you can search and do so queries by the actual name of the person rather than the ID. But for now, we don’t have a formula field for the owner’s name. And so we’re just going to go with what we have out of the box, which is the owner ID. And so then we can as well add conditions so we can create the where clauses to your query below. One thing to note is you can resize this to give you more width. Sometimes this operation field is hidden until you resize the width of this window. But I want to select Leads for the status, so I’m having to go back to the Leads object to get the exact wording for the different status fields.

So the label of that field is Lead Status, but the API name is just Status. And so I only want to transfer those that have a status of Open Not Contacted. So I’m going to copy that actually from another window that I have off screen of my salesforce instance. I’m going to specify the status is equals and then I’m going to paste in and it actually gives me the options here to select from, although it’s not the greatest user experience, so I forgot that it would do that actually. So I’m just going to select from the list Open Not Contacted and click to add that condition. So now it’s updated my soccer query. If you notice it added the where status equals Open Not Contacted. So now in this soccer query, I’m selecting the ID, the status and the owner ID from all lead records, where the status equals open, not contacted. So I’m going to click Finish and it’s warning me that I’ve chosen to perform an export and I can click yes to begin. So now there’s the operation in progress and so there’s the two successful extractions and no errors. So I’m going to view the extraction by clicking the button and this is a nice way to be able to see how it went. And basically it gives me these two lead records. And so these are very similar as far as the ID goes. You notice in this last of the 15 characters, this one is a Y and this one is a Z. And then the final three to round up the full 18 character ID have the uppercase EAH. So it’s hard to even tell the difference between these two. These are the IDs for the leads and these are the IDs for the owner, the owner ID. And so it’s the same owner.

So I’m going to close this and click OK. And then I’m going to open this export in Excel. So if I go to my downloads folder and click on that all leads CSV. So now I’ve got the all leads file. The CSV file opened in Microsoft Excel. And so I’ve got the two rows of data. That means there’s two lead records that match my criteria. And so I want to show you what you can do with these actual ID fields, such as here I’ve got this owner ID. I could copy that and then I can go into the URL bar on Salesforce and I can delete out the ID of the record that I’m currently on, which is a case notice that started with the prefix of 500. I’m going to paste this in five that’s prefix for a user account and hit Enter. And that will bring up the user record for the owner, which is Jim Doe. So now if I wanted to change these records over to myself, I could search for myself here and select myself and pull up my user record. And so I’m going to grab this user ID here. I’m pulling this off of just the homepage.

41. Helpful Practice Activity – Creating Unique ID / External ID

So now it’s time for us to do a helpful practice activity. We’re going to create a unique ID field. So in our example, what we’re going to do as I go into the Object Manager here is we’re going to access the contact record and then go to fields and relationships. And we want to create a new field. And for example, we’re wanting to do an integration between our salesforce instance and external internal accounting system. And so for an example would be that we have our own employees internally and we have a contact record for each employee in salesforce and then we have an external system that manages all of our accounting and part of that is for paychecks and payroll. So each employee has an employee ID number, for example. And so we’re going to create a text field for this unique ID field and we’re going to use this as an external ID. We’re going to call this employee ID. Let’s say that our system of record for our HR systems or Human Resources for the employee ID is a ten character ID field. And so we specified a length of ten.

And then I’m going to enter a description. And so for example, let’s say that we’re using PeopleSoft to manage our people in our company and each employee has that unique employee ID. So here’s where we get into specifying a field as a unique record. And so if you see this unique designation, we can check the Do Not allow duplicate values and you have the options then of treating things as case insensitive or case sensitive. I’m going to say case sensitive. That means that treating ABC all uppercase would be treated as a different value from all lowercase ABC, for example. And so if your system allows for case sensitivity and upper case and lowercase letters, then you would want to select this radio button. And then I want to set this as an external ID field. Doing so sets this field as the unique record Identifier from an external system. And then I’m going to click next I’m going to accept the defaults here for the profile visibility and making this editable for all profiles. Then I’m going to click Save to save this new field, which is a unique ID field and also used for an external ID by clicking Save. So now if we go to a Contact record, let’s see what all contacts we have in our instance.

And so we don’t have any for our own company. So I’m going to create a new Contact record and I can do that by clicking the down arrow next to the Contacts tab and selecting New Contact. I’m going to create a Contact record for myself and then I’m going to create a new account as well for my own company. In my example, I am working within my own company which is called my Queer Media and I’m creating a new contact record for myself. That’s associated with my account record for my company. And so then our new field is the employee ID field. So from our PeopleSoft integration we have a PeopleSoft unique ID. And let’s say that it’s PS 1234-5678. That’s the full ten characters. And I’ve just done lowercase PS for PeopleSoft. For example.

Now I don’t know what PeopleSoft IDs look like, but just doing that for an example. So now I’ve created a new contact plus a new account. And so if I go to the details for my new contact record, we see my PeopleSoft ID of 1234-5678. So now, if you recall, we have previously created a user record for Jim Doe. And so I want to add a corresponding contact record for him since he’s a fictional employee for my company. So I’m going to do new contact and I’m going to enter in Jim Doe. And a lot of times in salesforce instances that you run into or you’re asked to develop, you’ll be tasked with anytime a new user account is activated in salesforce that you want a corresponding contact record to be created. You could do that through a trigger, and then as well, you could do a delete trigger for contact records, or you could set it as inactive for when a user leaves your company and you deactivate them. So I’m going to search for my Mike Wheeler Media and select that.

And so now I’ve got a contact record for my Jim Doe user. And so it’s a corresponding contact record for Jim Doe who had been an employee of Mike wither Media. I have not entered the PeopleSoft ID or Employee ID yet for him, so I’m going to try to do the same exact employee ID. Now we require this to be a unique value, and this lecture is called Creating a Unique ID and External ID. We did both at the same time. So this is a unique check to see if we’re blocked from entering in the same exact value. And you notice the lowercase PS at the start here because we specified treating those as unique and being case sensitive.

So I click Save and we review the following errors duplicate values found employee ID underscore C, duplicate values found on record with ID and it gives the record ID for my record. So if you weren’t sure where the duplicate was, you could copy that, paste that in the URL and find them. Now pasting a URL and lighting experience may not work. I’m not sure there’s been some spotty support of being able to paste URLs IDs into URL field in Lighting Experience. So you might have to switch to Classic even. It’d be nice improvement for Salesforce to do in Lighting Experiences to give you a clickable link to the other contact record. So I’m going to close out of that. And now I’m going to actually do an upper case to demonstrate case sensitivity. So these are a different case. So this is a unique employee ID. Even though the first two letters are the same, they’re in a different case.

So if I click save, it should allow me to do this. So now you’ve got a pretty good idea of how to make fields unique in Salesforce. And then as well, we specified on that field that it would be a unique Identifier and that’s used for integrations with external systems. And so I’ll lot of times you’ve got records in Salesforce, you’ve got records in an external system, and you need to have the unique ID from that external system so that you can tie it with your record here in Salesforce. So next we’re going to be dealing with exporting data from Salesforce in the next lecture.