Thread Details
  • State Suggested Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 353 subscribers
  • Views 1132 views
  • Users 0 members are here
Forum Actions
Related

Usage of other primary keys than SugarCRM GUIFD

Michael Malich

Good Morning,
I have an ongoing discussion with some of my colleagues regarding the usage of the standard GUID SugarCRM uses as the Primary Key of records.

We are all new to SugarCRM and I managed anyway to get all CRM data from our old system into the new SugarCRM instance.
In the old application companies and contacts have IDs known for example in external campaign platforms.
My first thought was using all the old legacy IDs as IDs in SugarCRM. I did that and everything was pretty easy including a M2M link between Opportunities and Contacts by using the Sugar API that expects the IDs of the records to link them.

Now some colleagues are saying that the usage of the GUID is recommended and it is not recommended to use other values as the PK.
This has been mentioned during a training my colleagues joined and SugarCRM support replies the same.

I'm working with CRM systems and databases since a very long time. My experience tells me that I can use any key values I want.
But I have to make sure that the values have a length that fits into the PK fields
Also it must be 100% sure that no conflict between the old IDs and SugarCRM generated IDs could ever happen.

Is there anything I'm not aware of that is special in SugarCRM. And I'm not talking about changing existing GUID values in the application.
I'm only talking about the usage of legacy IDs as SugarCRM IDs for a one time data migration.

Thanks a lot in advance
Michael

  • 0

    Hi Michael,

    from a technical standpoint, and my experience as a SugarCRM developer, there is no issue with using any other format as a PK as long as it is unique. As you already said yourself the only thing you need to be sure of is that the custom format doesn't conflict in any way with the default Guid format in Sugar. And as long as that isn't the case there is no issue. Sugar doesn't really care about the format in my experience. 

    But if you had the chance of importing the data again and without using the custom format as PK I would try to do so. To be able to find the data based on the old pk's you could add a extra field to store this value.

  • 0 in reply to Jeroen Somhorst

    Hi Jeroen,
    Thanks a lot for your reply. I'm happy to hear that I'm not the only one with that opion :-)

    We already have a customized field with the old legacy_id for each record.
    This first data migration doesn't contain a lot of data from only a few tables.
    I've used the GUI interface import and used for example for contacts the company name to link a contact One2Many. That works well as long as you have no Many2Many links. But we have that between Opps and Contacts.
    So I used the Sugar API to read the necessary data and to import the Many2many link. That works well but it's a lot of additional work one can save by using legacy IDs as SugarCRM IDs.

  • 0 Sugar Employee Badge in reply to Michael Malich

    Hi ,

    I wouldn't recommend updating PKs other than our out-of-the-box structure, you might run into issues on upgrades, addons and any other Sugar code that might rely on that GUID structure, in other words, try to avoid this approach.

    We do, however, have a mechanism built for this type of scenarios which is to use "sync_key" to sync up your data from external (old system) to Sugar. We use it internally for our integration purposes and that's our recommended approach to you.

    "sync_key" field is available on every single Sugar module and is used to:

    - Speed up integrations (our recommended approach for upserts into sugar)

    - Retrieve your "external system" record by using it's own ID.

    Give it a shot and let us know.

    SugarCRM | Principal Developer Advocate

  • 0 in reply to Rafael Fernandes

    Hi Rafael,

    thanks a lot for your reply and I will check the "sync key" functionality you mentioned.
    But it looks like it "only" updates records and can't be used for what I'm looking for.
    But seems to be very helpful for interfaces.

    But I'm not talking about an interface. I'm talking about a one time data migration.
    All the tables I'm migrating have relations with others and have foreign keys for these relations.
    As soon as I do not use the PK of these records as the ID in SugarCRM, I have to read for example the ID of an account before I import the contacts belonging to that account. Because I don't know the ID SugarCRM has created for the account.
    This has to be done for every One2Many relation and for each Many2Many relations too.
    Feels like a big overhead for migrating an application into SugarCRM.

    Also regarding the usage of the GUID I would like to show you what is written in the Sugar Developer Documentation.

    https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_10.0/Data_Framework/Database/

    There is described that it is possible to use other PK formats and the GUID is not mandatory.
    There is also described what's important to use a different format.

    I'm new to SugarCRM and I appreciate recommendations but I can't imagine why using a different UNIQUE key should ever make any problems. I've migrated databases into other CRMs in the past and had never problems like this. Only if you don't take care and have maybe ID conflicts in the future.

    Sorry for the detailed reply, but I would like to really understand it in best case based on real problems you heard about.

    Thanks
    Michael