Solved

Changing the default starting Identity in Sage CRM

Posted on 2009-05-20
12
844 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Record open by another user 6 73
SQL Express connecting form remote error 26 7 55
Delete from table 6 48
View SQL 2005 Job package 16 77
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

749 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