Solved

Changing the default starting Identity in Sage CRM

Posted on 2009-05-20
12
842 Views
Last Modified: 2012-08-13
Hi,

I would like to know how to go about changing the default 6000 start number in Sage CRM for new identity numbers to a custom value. As the related solution shows, I have already attempted altering the NextId value contained in the CRM database table "SQL_Identity" for the custom table in question.

The SQL update goes through Ok and shows the next id for that table as 300 my desired start point. However if I go and run the SP that fires this identity process it returns a value of 6500 and the Id_Next value reverts away from 300.

Thanks
0
Comment
Question by:TartanTaurus
  • 8
  • 4
12 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24429822
Hi,

This is down to the rep_ranges issues I suspected may be an issue. I'll need to have a read of the sp that generates the ID's in Sage CRM and figure out what values to set and in what table.

Lee
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24429897
Hi,

I'll assume the table that you wish to change the starting ID for is SocialWorkRegion.

Runs this SQL and post back the results

select * from rep_ranges where range_tableid = (select bord_tableid from custom_tables where bord_caption = 'SocialWorkRegion')

Cheers,

Lee



0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24429901
Sorry, replace bord_caption with bord_name. It shouldn't make a difference but it can if you have changed the name of the table in translations.

Cheers,

Lee
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:TartanTaurus
ID: 24430064
Hi,

I want to alter the start number of the number we generated in the previous thread (I believe this comes from the "CustomRefs" table we created. I've referenced this table in the SQL and this is the return:
All start Range_:
TableId  RangeStart   RangeEnd   NextRangeStart  NextRangeEnd  Control_NextRange
10169	6500	   6999	      7000	   7099	               7100

Open in new window

0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
ID: 24430249
Ok, the ID you want to start at, 300, must be between RangeStart and RangeEnd. To make this work ensure that noone is logged is as you don't want changes occurring while you alter the table values. The next stage is to back up the database. Trust me on this. These tables are not ones you wish to nail. I know, I've done it. I ended up spending several hours writing some SQL code to regenerate the SQL_Identity table as well in case anything ever happened to it again.

So, you've backed up your database. The next thing to do is to alter the value in the SQL_Identity table.

update sql_identity set id_nextid = 300 where id_tableid = 10169

I'm assuming the table ID from your response above. Please ensure you're updating the correct entry.

Next, you need to fix the rep_ranges table.

update rep_ranges set range_rangestart = 1, range_rangeend = 999, range_nextrangestart = 1000, range_nextrangeend = 1999, range_control_nextrange = 2000 where range_tableid = 10169

That should do it.

Cheers,

Lee
0
 
LVL 1

Author Comment

by:TartanTaurus
ID: 24430452
First class, thanks very much! I will accept that as the solution.

The value returned from the SP is now in the format of AB-CD-00300. How can I change that line to lose the additional 0's I only need to see AB-CD-300.

This is the line in question:
select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + right('0000' + cast(@iCustomId as nvarchar(5)), 5)

I can only see 4 0's yet there's 5 appear in the output...thought I'd better ask than destroy the SP!

Thanks
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24430662
Change:

select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + right('0000' + cast(@iCustomId as nvarchar(5)), 5)

to

select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + cast(@iCustomId as nvarchar(5))

Cheers,

Lee
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24430678
5 digits appeared because I was creating a string in the form "0000" + CustomId which would give "0000300". The right() function in SQL was telling it to return the right 5 characters leaving "00300". It's a trick for displaying leading zeros in SQL.

Lee


0
 
LVL 1

Author Comment

by:TartanTaurus
ID: 24430886
That's done the trick alright! Thanks very much for all your help, much appreciated!

Best regards,

Chris
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24430890
Any time :)

Lee
0
 
LVL 1

Author Comment

by:TartanTaurus
ID: 24431039
Is there any way I can contact you on this forum via personal message? I'm not going to request help or anything that would break forum rules! I just have a question I'd like to ask away from the public forum :)

Thanks

Chris
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24439518
I don't think EE has a private messaging system. There is only one other Sage CRM forum on the web that is public which has recently taken a bit a nosedive as it has lost a years worth of posts. I'm on there. If you search the web for Sage CRM forum you'll most likely find it. It does have some useful info on there in general.

Cheers,

Lee
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQl server restarts itself 6 40
Need help creating a stored procedure 4 63
How can I exclude some wording in a like statement? 39 75
SQL query with cast 38 51
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question