Import/update based on custom id field

[henrik999]

Hi.

I have a set of accounts in the SugarCRM base. I need to regularly update theese based on external lists imported with .csv files so existing records get updated with the information from the list, and nonexisting ones get created. Import/update works well, but now i need to match theese based on a custom field. The accounts have a unique id number from another system, and i need to be able to match against an index on this id field, which is a custom field created on the accounts module.

I read in an earlier thread from a couple of years ago that this is not possible yet. A couple of other people asked similar questions in other threads, but i could not find any solution there. When it comes to the import script, this is probably just a little sql join instead of just using indexes on the standard accounts table. Is there a function like this in SugarCRM now? We use v 6.1.3.

[Angel]

It is possible to do, but requires some manual work as you have to define an index for the field with the custom ID so you can select it as your match key when importing.

Unfortunately, I do not have the thread handy, but Clint posted an example of how to do that. Some digging around on the forums should yield it for you.

[bickart68]

The ID field inside of SugarCRM is defined as char(36) used to be varchar(36).

Anyway...
use the ID from the 3rd party system as the ID inside of SugarCRM and when you import using a create and update, you don't have to do anything special.

SugarCRM will work just fine with any ID as long as it is unique and is less than or equal to 36 chars

[henrik999]

Thanks for the replies!

bickart68: I thought about that. The only problem is that records will be created inside SugarCRM in addition to the ones being imported. And when a user create an account in SugarCRM, the record will have an autogenerated SugarCRM id since the id field cannot be used as a normal edit field. The imported records will then not be matched, and there will be duplicates.

[henrik999]

I hope this function is implemented in SugarCRM in the future.

In the meantime, if anyone have the same problem, here is the manual workaround way i fixed it.
I exported all the records by creating a report in SugarCRM containing the ID field of the records, and the custom field that I wanted to use for the next import (as stated in this thread). I then copied the two exported columns into the file I am going to import in excel beside the existing columns there, then used VLOOKUP to combine the right column in the import data with the column exported in the report, and then insert the other column from the same export into a new column in the import data. I then have the id field of the record in SugarCRM if it exists allready, so I can map it to the actual ID column when importing.

VLOOKUP example:

=IFERROR(VLOOKUP(B2;$AC$2:$AD$21;2;FALSE);"")

Where B is the column in the data I want to import that match one column from the exported data list, AC contain the same data from the SugarCRM report export, and AD contains the ID values from the export. I paste that formula into A2 (empty column for the ID values to match when importing), and copy it down the rest of the excel sheet.

Not very simple, but does not take too long time, and it works.