Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Changing the default starting Identity in Sage CRM

Posted on 2009-05-20
12
Medium Priority
?
859 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 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