{"id":321,"date":"2011-05-26T07:42:00","date_gmt":"2011-05-26T07:42:00","guid":{"rendered":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/?p=321"},"modified":"2011-05-26T07:42:00","modified_gmt":"2011-05-26T07:42:00","slug":"generate-the-next-crm-id-understanding-it-the-easy-way","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/","title":{"rendered":"Generate the Next CRM Id \u2013 Understanding it the easy way"},"content":{"rendered":"

Very often you come across an issue something like “Cannot insert duplicate key row in object ‘dbo.Phone’ with unique index ‘IDX_Phon_PhoneId\u201d. The issue clearly indicates that the primary id of record which is being inserted already exists. But how is this possible because the primary id of each record which is being inserted in Sage CRM is handled by the system. So how could this situation occur? Let me try and explain how this happens, what should be done and also how Sage CRM generates the next id.<\/p>\n

How it Works<\/span><\/p>\n

It starts with the eware_get_identity_id stored procedure where you need to pass the table name for whic you need the next id. If you edit the stored procedure you will see that performs the folowing steps
1. It gets the next Id from the SQL_Identity table for the specfic entity.
2. It then queries the Rep_Ranges table and fetches the record details again for the specfic table.
3. There are 5 fields in Rep_Ranges table that are used, namely
<\/span>a. Range_RangeStart
<\/span>b. Range_RangeEnd
<\/span>c. Range_NextRangeStart
<\/span>d. Range_NextRangeEnd
<\/span>e. Range_Control_NextRange
4. Now, when a new id is generated (in #1) it checks the newly generated with the ranges returned in #3.
5. If the new id is out of the ranges specified i.e. the new id is less than the Range Start or is more than Range End then all the ids within the range have used up and we will have to create a new range.
6. This is a intresting part. Once the system identifies that all the range values are used up it moves ranges up by one step by updating the folowing value i.e.
<\/span>a. Range_RangeStart > Range_NextRangeStart
<\/span>b. Range_RangeEnd > Range_NextRangeEnd
<\/span>c. Range_NextRangeStart > Range_Control_NextRange
<\/span>d. Range_NextRangeEnd > Range_Control_NextRange + Limit (fixed to 10000)
<\/span>e. Range_Control_NextRange > Range_Control_NextRange + Limit (fixed to 10000) – 1
7. The new id will now be the first value from the new range
8. The last and important step which system does is to update the SQL_Identity table to so eware_get_next_id will RETURN next id FROM within new range
9. At this point you have id for the new record which is to be inserted.<\/p>\n

<\/span><\/p>\n

How the issue Occurs<\/b><\/p>\n

<\/span><\/div>\n

The issue with the id normally occurs only when you are updating the data via a SQL import from a table via SQL cursor etc or making an entry directly in the entity. During this procedure the user normally forget to update the SQL_Identity and the Rep_Ranges tables and thus the crucial link between SQL_Identity, Rep_Ranges and the entity is broken and now they are not in sync. The SQL_Identify and Rep_Ranges table are not aware of the last id saved in the table. This results in eware_get_identity_id stored procedure returning an id which already exists<\/p>\n

<\/span><\/div>\n

How to Fix<\/b><\/p>\n

<\/span><\/div>\n

This issue can be fixed by following steps.
1. Get the maximum id generated for than entity. This can be done by executing the query Select max(id field<\/i>) from entity <\/i>
2. Once you get the max number from #1 you need to query the Rep_Ranges table and check the ranges set for the entity. For instance for Person the query would be Select * from Rep_Ranges where Range_TableId = 13.
3. This is a crucial step. Here you need to compare the max id obtained in #1 with the fields retrieved in #2.
4. If the new id is out of the ranges specified i.e. the new id is less than the Range Start or is more than Range End then all the ids within the range have used up and we will have to create a new range.<\/p>\n

5. If the new id is out of the ranges specified i.e. the new id is less than the Range Start or is more than Range End then all the ids within the range have used up and we will have to create a new range.
6. To do this we will have to move ranges up by one step by updating the folowing value i.e.
a. Range_RangeStart > Range_NextRangeStart
b. Range_RangeEnd > Range_NextRangeEnd
c. Range_NextRangeStart > Range_Control_NextRange
d. Range_NextRangeEnd > Range_Control_NextRange + Limit (fixed to 10000)
e. Range_Control_NextRange > Range_Control_NextRange + Limit (fixed to 10000) – 1<\/p>\n

Please note that this needs to be done for only the table for which the issue is occurring
7. This process has to be done till the newly created id (in #1) is in the range i.e. between Range Start and range End.<\/p>\n

The changes that are mentioned here would touch the Sage CRM metadata and needs to be done carefully and by an expert. Needless to say, please take a backup of the database before making any changes to the tables.<\/span><\/div>\n

If you find this content useful, please drop us an email at crm@greytrix.com<\/a>. <\/b><\/marquee><\/p>\n","protected":false},"excerpt":{"rendered":"

Very often you come across an issue something like “Cannot insert duplicate key row in object ‘dbo.Phone’ with unique index ‘IDX_Phon_PhoneId\u201d. The issue clearly indicates that the primary id of record which is being inserted already exists. But how is this possible because the primary id of each record which is being inserted in Sage\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[809,2497],"class_list":["post-321","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-crm-idrange","tag-unique-duplicate-sage-crm"],"yoast_head":"\nGenerate the Next CRM Id \u2013 Understanding it the easy way - Sage CRM \u2013 Tips, Tricks and Components<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Generate the Next CRM Id \u2013 Understanding it the easy way - Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"og:description\" content=\"Very often you come across an issue something like “Cannot insert duplicate key row in object ‘dbo.Phone’ with unique index ‘IDX_Phon_PhoneId\u201d. The issue clearly indicates that the primary id of record which is being inserted already exists. But how is this possible because the primary id of each record which is being inserted in Sage\u2026 Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/\" \/>\n<meta property=\"og:site_name\" content=\"Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"article:published_time\" content=\"2011-05-26T07:42:00+00:00\" \/>\n<meta name=\"author\" content=\"greysagecrm\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"greysagecrm\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/\",\"name\":\"Generate the Next CRM Id \u2013 Understanding it the easy way - Sage CRM \u2013 Tips, Tricks and Components\",\"isPartOf\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\"},\"datePublished\":\"2011-05-26T07:42:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Generate the Next CRM Id \u2013 Understanding it the easy way\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\",\"name\":\"Sage CRM \u2013 Tips, Tricks and Components\",\"description\":\"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\",\"name\":\"greysagecrm\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"caption\":\"greysagecrm\"},\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Generate the Next CRM Id \u2013 Understanding it the easy way - Sage CRM \u2013 Tips, Tricks and Components","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/","og_locale":"en_US","og_type":"article","og_title":"Generate the Next CRM Id \u2013 Understanding it the easy way - Sage CRM \u2013 Tips, Tricks and Components","og_description":"Very often you come across an issue something like “Cannot insert duplicate key row in object ‘dbo.Phone’ with unique index ‘IDX_Phon_PhoneId\u201d. The issue clearly indicates that the primary id of record which is being inserted already exists. But how is this possible because the primary id of each record which is being inserted in Sage\u2026 Read More »","og_url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/","og_site_name":"Sage CRM \u2013 Tips, Tricks and Components","article_published_time":"2011-05-26T07:42:00+00:00","author":"greysagecrm","twitter_card":"summary_large_image","twitter_misc":{"Written by":"greysagecrm","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/","name":"Generate the Next CRM Id \u2013 Understanding it the easy way - Sage CRM \u2013 Tips, Tricks and Components","isPartOf":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website"},"datePublished":"2011-05-26T07:42:00+00:00","author":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f"},"breadcrumb":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/26\/generate-the-next-crm-id-understanding-it-the-easy-way\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/"},{"@type":"ListItem","position":2,"name":"Generate the Next CRM Id \u2013 Understanding it the easy way"}]},{"@type":"WebSite","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/","name":"Sage CRM \u2013 Tips, Tricks and Components","description":"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f","name":"greysagecrm","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","caption":"greysagecrm"},"url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/"}]}},"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/321","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/comments?post=321"}],"version-history":[{"count":0,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/321\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/media?parent=321"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/categories?post=321"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/tags?post=321"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}