RE: What is meant by “Reference Types” during CSV import?
Hello,
Could someone please explain clearly what NetSuite documentation means by Reference Types during a CSV import? Honestly, I’m a software engineer used to reading difficult technical documentation and it’s been a long, long time since I’ve read something that was quite so confusing…
Let me explain the context of what I’m trying to do. I’d like to perform an “Add or Update” operation and would like to understand how NetSuite decides which row in the source CSV file will be treated as new (“Add”) and which as existing (“Update”). It’s obvious that for each CSV row X, NetSuite has to look at the columns and treat some of them as the “identity key” and treat the rest as not such. Then it looks in its pre-existing records to see if there’s a record Y whose fields corresponding to X’s “identity key” in the mapping defined in step 4 of the Import Assistant – whose such fields have values equal to X’s. If so, this will be an “Update” – if not, this will be an “Add”. I can’t imagine this working any other way.
So now how does NetSuite decide which columns are the “identity key”? From the sentence: Your selection of a reference type in the Import Assistant’s Field Mapping step determines how CSV file records are mapped to existing NetSuite records in the documentation for “Add or Update”, I understand that these “reference types” are somehow involved.
But when I go to their documentation, I’m just losing it… A reference type enables you to map a column in an imported CSV file to a NetSuite field that can have one or more list values, such as a dropdown list or a multi-select.
A list of bewilderments:
- What does the above possibly mean?
- What does that have to do with choosing an “identity key” for an import?
- Why only Internal ID, External ID, Names, and ScriptID are available as options? In particular, does “Names” mean a column called “Name” as the “identity key”? What if I don’t have a column “Name” but have columns “Maiden Name” and “Last Name”, of which I want only “Last Name” to be used? Can this be accomplished?
- Why I see the option “Choose Reference Type” only next to some CSV columns? (I see that these are the ones that have a fixed list of values – seems to fit that cryptic sentence I quoted above.) What if I want to choose an “identity key” but don’t have any fixed-list columns and therefore can’t select any “reference type”?
- What if I have two fixed-list columns and choose “Internal ID” in the “Choose Reference Type” option for one of them, and choose “Names” in the same option for the second of them? Shouldn’t the choice of “identity key” be for the entire import, not for individual columns?
I’m sorry for the lamenting tone, but I hope it reflects the feeling of complete confusion that accompanies trying to understand this. If someone could explain clearly how to choose an “identity key” for an “Add or Update” operation, in particular if the mysterious “Names” choice can help with that when I want to use a column other than “External ID” or “Internal ID”, and what to do if none or more than one of my fields let me “Choose Reference Type” – I’ll be very grateful, and hopefully it’ll save others some time and nerves. Thank you!
Later edit: this official FAQ helps a bit as regards Internal ID and External ID, but doesn’t help much with “Names” or what to do if none of my fields let me “Choose Reference Type”.
The tl;dr for your initial issue:
The record External ID or Internal ID (not both, unless you’re trying to update the External ID). That’s it. You must map one of these on ‘Add or Update’ actions. For things with line level stuff (e.g. journal entries, invoices, etc.) you’ll also need to map the Line ID. You can tell you’re mapping the correct field because there’s a little key shaped icon on top of the NetSuite N right before the field name. This is mapped in Step 4 along with the actual fields you want to update.
Regarding your later edit:
The reference types (via the pencil icon) are just allowing you a bunch of different ways to set list/record values. Names = (generally) exactly how the record appears in the UI. There are a few times when this doesn’t work exactly as you’d expect though, which is why/when External ID or Internal ID can come in handy as an alternate method to make sure you’re selecting the correct value in this drop-down type field.
Ones without choose reference type just means that they are not multi-select fields. Just drag your column over and then drag over the corresponding column field from the NetSuite side. No pencil icon stuff or other action required.
On a personal note:
It sounds like you’re fairly new to NetSuite and don’t have anyone to walk you through any of this. Totally understand where that can be extremely frustrating if you’re relying on SuiteAnswers alone. I think if you had someone walk you through it once or twice, you’d get the hang it of it very quick, and it’d make a lot of sense. Not sure my answers are going to do even a fraction of the good that being able to spend 5 minutes answering questions in real time would do. If you do want to do that feel free to DM me on the slack group: James (Jimbo) Morris. There’s also a number of good youtube videos on CSV imports out there that can be pretty useful if that’s more your style.
