Summary
Company mapping is needed when creating records of other models, as the Company model is at the top of the Planhat data hierarchy. E.g. each Asset record needs to be associated with a specific Company, and so on
When importing data via spreadsheet, this mapping is very flexible, enabling you to use different types of IDs
You can choose between Planhat ID, Source ID and External ID. You map between spreadsheet columns and IDs in the import form, rather than using prefixes in the spreadsheet to specify the type of ID
For End Users, you can alternatively map to Companies via email domains
Who is this article for?
Planhat users who are importing data via spreadsheet
Series
Importing data via Excel spreadsheet - match records to Companies ⬅️ You are here
Article contents
Introduction
This is a technical deep-dive article
Read on if you'd like to learn more about matching records to Companies when importing data via Excel spreadsheets.
If you'd just like an introduction to importing data via Excel file, please refer to our main article for an overview of the key points.
As part of the process when importing a spreadsheet, you'll see configurable mapping between spreadsheet columns and Planhat fields.
In this article, we take a more detailed look at the top mapping section: "Match records to Companies", as shown in the screenshot below. This is where you define how records in the spreadsheet (e.g. Assets in the example screenshot below) are linked to their parent Company records.
Here you map between columns in your spreadsheet (on the left-hand side of the form) and Company IDs in Planhat (on the right-hand side).
You can map to Companies via any of the three ID types (which we discuss in further detail below):
Planhat ID
Source ID
External ID
For End Users, you can alternatively map to Companies by email domain.
By default (when uploading a completed template), the "Company Id" spreadsheet column is mapped to the Planhat ID ("Id") - as is shown in the screenshot above - but you must edit or remove this if you do not have Company Planhat IDs stored in that column in your spreadsheet. We talk you through this in examples below.
🚀 Tip
As you might imagine, if you are uploading a spreadsheet of Company records, you will not see the "Match records to Companies" part of the form, as it does not apply.
📌 Definitions
Planhat "models" (also called "data models") are similar to the "objects" you may be familiar with from other tools
E.g. "Company" is the model representing organizations that are your customers or prospects (i.e. your accounts)
"Records" are data items within those models
E.g. "Samsung" and "Pfizer" could be Company records
Background - Planhat data structure
The Company model is at the top of the Planhat data model structure, and most other models (e.g. End Users, Deals, Assets and so on) relate to Companies. Therefore, when you're uploading an Excel file where you will be creating records of one of these other models, you specify how you are mapping each record to its parent Company.
For example, if you want to add a new End User "Kerry Corby", this person needs to belong to a specific Company, e.g. "Lenovo".
Or let's say you are uploading a spreadsheet containing data on some Assets, and you want to create these as new Asset records in Planhat. As the Asset records can't exist in Planhat "loose" - without being connected to a Company - in the spreadsheet you use an ID to specify the Company each Asset should be linked to. For example:
Types of IDs in Planhat
This Company mapping is very flexible - you can choose from one of the three different types of IDs that are used for record identification in Planhat.
These IDs are used elsewhere in Planhat - e.g. for record mapping when syncing data via integrations and API imports - rather than being something specific to spreadsheet uploads.
The 3 different types of record IDs in Planhat are as follows. Remember that in this case we are considering these IDs in the context of the Company model.
Planhat ID
Often simply referred to as "_id" or "Id"
This is the default ID that is generated automatically by Planhat for each record
You will find this ID in the URL when you are looking at a specific record in Planhat, like in the example screenshot below
Click the image to view it enlarged
Source ID
Sometimes written as "sourceId" or "SourceId (CRM)"
This is another type of ID, typically synced into Planhat from CRM integrations (e.g. Salesforce)
It's stored in a standard field on models - e.g. the example screenshot below shows this in a Company table
External ID
Also called "externalId" or "External Id"
This is another ID, which can be from any source, e.g. the ID you use in your own product
It's stored in a standard field on models - e.g. the example screenshot below shows this in a Company table
🚀 Tip
When you're creating End Users, it's also possible to map to Companies via email domains and not actually use any of these IDs. We go over this here.
Worked example 1 - mapping "Company Id" column to "SourceId (CRM)" field
Summary of steps
Clear mapping of "Company Id" column to "Id" (Planhat ID)
Add mapping of "Company Id" column to "SourceId (CRM)"
Let's say we want to create some new Assets via Excel spreadsheet import.
We start by downloading the Assets template:
In the Asset blank template, we start filling in the details of our new Assets. In order to link them to the right Companies in the import, we choose an ID and enter it in the "Company Id" column.
In this particular example, we are using the Source ID, and we want to create one Asset for the Company "Zoom" and one Asset for the Company "Walmart".
Back in the import modal (form), we upload our filled-in template file. That then loads up the mapping with the "Match records to Companies" section at the top like so:
The spreadsheet is represented by the left-hand side - where it says "Source Column". Here we need to define which "Planhat Company Field" on the right-hand side (i.e. which type of ID) we've used in the "Company Id" column in the spreadsheet we uploaded.
In our particular example, we used the Source ID (technically "SourceId (CRM)" field), and so we need to remove the mapping in the first line (for the Planhat ID, here simply called "Id") ...
... and add in the mapping for the Source ID instead:
🚀 Tip
Although in the example above, we removed the "Company Id" column from the line where it was mapped with the "Id" field (by clicking on "Company Id" and then clicking on the trash can / bin icon), as an alternative you could actually delete that whole mapping line instead (by clicking on the trash can / bin icon at the end of the row). The important thing is just that you don't leave "Company Id" mapped to "Id".
After confirming the column/field mappings (discussed in the next article in this series), we click the "Import" button in the bottom right.
We get the confirmation message that the Assets were successfully created ...
... and checking the Companies Zoom and Walmart, we can see that the Assets were linked to the correct Companies:
🚀 Tip
Note how you don't need to use "extid-" or "scrid" at the start of a non-Planhat ID in the "Company Id" column in the spreadsheet (as was required previously) - you simply need to ensure that you have mapped in the import form which Planhat field (ID) your column values correspond to, like we showed in this example.
Worked example 2 - mapping End Users via email domains
Summary of steps
Clear mapping of "Company Id" column to "Id" (Planhat ID)
In this worked example, we are going to take a look at creating End Users. Although we can associate new End Users with Companies based on any of the three usual Company IDs (the Planhat ID, Source ID or External ID), for End Users we can alternatively map to Companies based on the domains (e.g. example.com) in the End User email addresses.
Let's say we have selected "End Users" in the initial import form and have downloaded the End User template (spreadsheet file) to fill in.
In this case, we want to add some new End Users to the Company called "Wrangler". Looking at the "Related Domains" field in Planhat, we can see that the domain for Wrangler is "wrangler.com". This means that we can map new End Users to this Company if they have "@wrangler.com" in their email address.
In this example, we don't enter any values in the "Company Id" column in the spreadsheet file.
When we fill in the top section of the import form, we ensure that we don't keep the "Company Id" column mapped at the top - i.e. we clear the default mapping of "Company Id" being mapped to "Id". This is vital - otherwise Planhat will try to map via the "Company Id" column when we import, and will fail because it's blank in our spreadsheet.
Clicking "Import" in the bottom right of the modal, we see that the import has been successful and these 3 End Users have been created:
Checking the Company "Wrangler", we can confirm that these End Users have been created and assigned to the correct Company:
📌 Important to note
For new End Users to be mapped to Companies in this way, the email domains of the new End Users first need to be saved in the "Related Domains" field on the relevant Companies (as the relevant domain was in this example). We discuss this more in our troubleshooting article here.
Further reading
For additional details, you can refer to other articles in this series: