Solved

Changing the default starting Identity in Sage CRM

Posted on 2009-05-20
12
838 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now