{"id":6565,"date":"2014-01-18T09:31:46","date_gmt":"2014-01-18T09:31:46","guid":{"rendered":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/?p=6565"},"modified":"2014-01-18T09:31:46","modified_gmt":"2014-01-18T09:31:46","slug":"sagecrm-record-id-generation-old-and-new-methods","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/","title":{"rendered":"SageCRM Record Id generation old and new methods"},"content":{"rendered":"

How you design your database has huge effect on how your system performs. Primary key, foreign key constraints help to keep the tables linked to each other and store the data in unified manner. From long time SageCRM has followed strategy of generating primary keys through stored procedures rather than relying on the inbuilt primary key features of SQL. Now SageCRM 7.2 follows the principal of Primary keys in SQL, however stores them in similar manner to metadata tables.
\nNeed to Know the \u201cBest Practices for Integrating Third Party Apps with Sage ERP X3\u2033
\n<\/i><\/b><\/span>Join this session<\/span><\/a><\/span>\u00a0on Saturday 8th February, 2014 16h10 \u2013 17h15 to walk through some Best Practices for integrating Third Party or your custom apps with Sage ERP X3 and how each of them is best suited for your app. Some technical points and tips and tricks to definitely consider before you start planning your integration.<\/span>
\nAlso, get a chance to meet the Greytrix Team Exhibiting at\u00a0Sage South Africa Annual Conference Sage Insights<\/span><\/a><\/span>\u00a02014 from Thursday 6th \u2013 9th February 2014.<\/span><\/strong>
\nHere is how the identity was maintained in previous versions of SageCRM<\/span><\/b>
\nUnique ID\u2019s for Tables:-\u00a0 <\/b>
\nWe have SQL table called Custom_Tables<\/b>. If you go through record in this table, you will come to know all primary and secondary entities are stored with unique id Bord_TableId<\/b>. This becomes driving parameter in all the CRM tables for saving linked entity records.<\/p>\n

\"1\"<\/a><\/p>\n

Unique ID\u2019s for records:- \u00a0<\/b>
\nTo store the record increment value, one more table is present in CRM named SQL_identity. <\/b>In this table Id_TableId<\/b> field <\/b>holds the value of table id from above table and Id_NextId<\/b> field holds value for next record id.<\/b>
\nLet\u2019s consider Cases table for an example.<\/b>
\nTable Name: Cases
\nTable Unique ID: 3<\/b>
\nTable Current Record ID: 40848
\nTable Current Next Record ID: 40849
\nEntries in SQL_Identity table<\/span>:-<\/b><\/p>\n

\"2\"<\/a><\/p>\n

Entries in Cases table<\/span>:-<\/b><\/p>\n

\u00a0\"3\"<\/a><\/p>\n

Note:\u00a0 By default unique id start value is always 6001 for all tables.\u00a0\u00a0\u00a0\u00a0 <\/b>
\nFor every entity the unique id is generated using eware_get_identity_<\/b>id procedure and relaed parameters are updated in above mentioned tables.
\nWhat\u2019s up with Sage CRM 7.2 then?<\/span><\/b>
\nNow eware_get_identity_id<\/b> stored procedure is not available in CRM database. Your Record Id fields have become primary keys now. \u201cIdentity<\/b>\u201d property of record id columns in SQL table is set to Yes<\/b> for uniqueness and auto increment is set to 1.
\nRest of the things mentioned above are still behaving in similar manner to support upgrades may be.
\nHow the changed structure can affect?<\/span><\/b>
\nIf you have designed custom entities and calling the eware_get_identity_id<\/b> procedure manually in the routine you may consider recreating this procedure.
\nAlso Read:<\/strong><\/em><\/span>
\n1) How to identify and backup correct Sage CRM Database<\/a>
\n2) Retrieve deleted records in Sage CRM<\/a>
\n3) Quick look on Sage CRM tables<\/a>
\n4) Run SQL Queries through Sage CRM Components<\/a>
\n5) Control tab display using multiple SQL clauses\u00a0<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

How you design your database has huge effect on how your system performs. Primary key, foreign key constraints help to keep the tables linked to each other and store the data in unified manner. From long time SageCRM has followed strategy of generating primary keys through stored procedures rather than relying on the inbuilt primary\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":[83,332,380,382],"tags":[2031,2033,2325,2329],"class_list":["post-6565","post","type-post","status-publish","format-standard","hentry","category-crm72","category-sage-crm","category-sql","category-sql-identity","tag-sage-crm-7-1","tag-sage-crm-7-2","tag-sql","tag-sql-identity"],"yoast_head":"\nSageCRM Record Id generation old and new methods - 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\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SageCRM Record Id generation old and new methods - Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"og:description\" content=\"How you design your database has huge effect on how your system performs. Primary key, foreign key constraints help to keep the tables linked to each other and store the data in unified manner. From long time SageCRM has followed strategy of generating primary keys through stored procedures rather than relying on the inbuilt primary\u2026 Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/\" \/>\n<meta property=\"og:site_name\" content=\"Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-18T09:31:46+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/01\/15-300x156.jpg\" \/>\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=\"2 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\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/\",\"name\":\"SageCRM Record Id generation old and new methods - Sage CRM \u2013 Tips, Tricks and Components\",\"isPartOf\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/01\/15-300x156.jpg\",\"datePublished\":\"2014-01-18T09:31:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#primaryimage\",\"url\":\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/01\/15-300x156.jpg\",\"contentUrl\":\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/01\/15-300x156.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SageCRM Record Id generation old and new methods\"}]},{\"@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":"SageCRM Record Id generation old and new methods - 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\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/","og_locale":"en_US","og_type":"article","og_title":"SageCRM Record Id generation old and new methods - Sage CRM \u2013 Tips, Tricks and Components","og_description":"How you design your database has huge effect on how your system performs. Primary key, foreign key constraints help to keep the tables linked to each other and store the data in unified manner. From long time SageCRM has followed strategy of generating primary keys through stored procedures rather than relying on the inbuilt primary\u2026 Read More »","og_url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/","og_site_name":"Sage CRM \u2013 Tips, Tricks and Components","article_published_time":"2014-01-18T09:31:46+00:00","og_image":[{"url":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/01\/15-300x156.jpg","type":"","width":"","height":""}],"author":"greysagecrm","twitter_card":"summary_large_image","twitter_misc":{"Written by":"greysagecrm","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/","name":"SageCRM Record Id generation old and new methods - Sage CRM \u2013 Tips, Tricks and Components","isPartOf":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#primaryimage"},"image":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#primaryimage"},"thumbnailUrl":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/01\/15-300x156.jpg","datePublished":"2014-01-18T09:31:46+00:00","author":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f"},"breadcrumb":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#primaryimage","url":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/01\/15-300x156.jpg","contentUrl":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/01\/15-300x156.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/01\/18\/sagecrm-record-id-generation-old-and-new-methods\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/"},{"@type":"ListItem","position":2,"name":"SageCRM Record Id generation old and new methods"}]},{"@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\/6565","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=6565"}],"version-history":[{"count":0,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/6565\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/media?parent=6565"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/categories?post=6565"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/tags?post=6565"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}