Sync External IDs as Bigint?

Hi,

We are a sugarondemand Enterprise customer (9.3) and are trying to implement a ZenDesk integration. To ensure ZD doesn't try to create an existing account, and to sync cases correctly, we wanted to add a Z'Desk ID field to the Accounts module. IDs are at least 12 char, but the Sugar Integer max size is 255, or 10 char (and there is no way to increase it). Additionally, the integrator we are having to use, Skyvia, will require the data types to match, so I can't do a work-around there. 

Surely we cannot be alone in this goal, so I'm hoping someone can offer some suggestions / alternatives: 

1. The user setting up Z'Desk found information that at some point you could modify an integer to be a bigint type - is this still a possibility? 

2. Is there a better way to cross-reference records when working with external ids and syncing two systems like in this case?

Thanks in advance!

Missy

Parents
  • Hi Missy,

    I am surprised that Skyvia would require the field to be an integer type. Most use cases I have seen utilized the text field (varchar type, maximum of 255 alphanumeric characters) to store IDs referenced by other systems. Whether that ID is all numeric or not doesn't matter as they normally do not make that field generally editable by users to enter unexpected characters (i.e. only updated via import or via an API call). 

    I am not familiar with Skyvia, but can you confirm with them on whether they expect the data to be integer-compatible or if they expect the field type to be stored as an integer type? If it is the latter, why does their integration require that specific schema?

  • Sorry for the delay @chris raffle - I used to be notified via email when posts were replied to :/ 

    Anyhow, I totally failed at editing my post, and jumbled my message. To clarify: Skyvia's requirement is simply that field types be matching on both sides of the integration.

    Also, the field limit we are running into is the text field limit of 255. The ZD ID is so large that 255 won't be large enough. I guess my question was - how do we set it up to deposit the zd ID into a field on the Sugar-side "Account" when cases are created to make sure cases are synced to the correct Account.

    My understanding by our support team is that on the ZD-side, cases are created under a Contact (if they exist)...who is then associated to an Organization (if it exists). Of course, all this will rely on daily syncs to make sure the ZD-side has the most up-to-date db available. 

    Out goal is to have the unique ZD ID sync to the unique Sugar ID at the Account level....if we can figure out a field type

    Thanks for any help you might can offer!

    Missy 

    zendesk unique field field type record_id custom id 

  • Hi Missy,

    As you previously mentioned, the largest size a Sugar integer field can contain is 10 numeric characters (the max value is 2,147,483,647). A text field in Sugar can contain 255 alphanumeric characters as its largest size. That means it could 255 a's, 255 1's, or a mixture of 255 a's and 1's. That's why I suggested that most use cases would use a text field to contain a reference to an external system ID. Can you clarify why Skyvia's requirement is that the field types match? It's not common for an integration to compare the schema between the systems, so I want to be sure this is an actual technical requirement not a guideline they recommend to help avoid issues where the data stored may not match their expected format. 

Reply
  • Hi Missy,

    As you previously mentioned, the largest size a Sugar integer field can contain is 10 numeric characters (the max value is 2,147,483,647). A text field in Sugar can contain 255 alphanumeric characters as its largest size. That means it could 255 a's, 255 1's, or a mixture of 255 a's and 1's. That's why I suggested that most use cases would use a text field to contain a reference to an external system ID. Can you clarify why Skyvia's requirement is that the field types match? It's not common for an integration to compare the schema between the systems, so I want to be sure this is an actual technical requirement not a guideline they recommend to help avoid issues where the data stored may not match their expected format. 

Children
No Data