Record Numbering

[polanskiman]

How do I change the Record Numbering say CON01 to CON0001 for multiple contacts at once.

[joebordes]

Once the record is created there is no way of changing that value from within the application, you will have to go directly into the database.
See if the next SQL and attached image help.

Code:

SELECT `account_no`,substr(`account_no`,1,3), concat(substr(`account_no`,1,3),lpad(substr(`account_no`,4),5,'0')) from `vtiger_account`;
UPDATE `vtiger_account` SET `account_no`= concat(substr(`account_no`,0,3),lpad(substr(`account_no`,3),5,'0')) where {whatever condition};

[attachment=0]<!-- ia0 -->Update_sequence_field.png<!-- ia0 -->[/attachment]

[polanskiman]

joebordes Wrote:Once the record is created there is no way of changing that value from within the application, you will have to go directly into the database.
See if the next SQL and attached image help.

Code:

SELECT `account_no`,substr(`account_no`,1,3), concat(substr(`account_no`,1,3),lpad(substr(`account_no`,4),5,'0')) from `vtiger_account`;
UPDATE `vtiger_account` SET `account_no`= concat(substr(`account_no`,0,3),lpad(substr(`account_no`,3),5,'0')) where {whatever condition};

What does this mean?

Code:

where {whatever condition};

[Ekim]

You need to set a condition for your statement. to change only the needed rows.

[polanskiman]

Ekim Wrote:You need to set a condition for your statement. to change only the needed rows.

I do not know what condition I should set. All I need is to change the Record Numbering from CON01 to CON00001 for multiple contacts at once. The first SQL query provided by Joe works but then for the UPDATE query I am not sure what condition I have to put.

[joebordes]

If you want to change all the records, completly eliminate the where condition, if you want to change a subset of the accounts then you put a condition the selects that subset. In other words I was trying to say that you can add a condition there to change only some records, not all.

[polanskiman]

joebordes Wrote:If you want to change all the records, completly eliminate the where condition, if you want to change a subset of the accounts then you put a condition the selects that subset. In other words I was trying to say that you can add a condition there to change only some records, not all.

Understood and thanks. Forgive my ignorance in SQL!

Unfortunatly after applying the UPDATE query without the condtion (as I need to apply the change to all the records) I ended up with the account_no column being like so: 000G1, 000G2 etc etc

[attachment=0]<!-- ia0 -->account_no.gif<!-- ia0 -->[/attachment]

[joebordes]

Yes, there is an error in the update:

Code:

substr(`account_no`,0,3)

instead of

Code:

substr(`account_no`,1,3)

Now you are going to have to play around with the substring/lpad to get it right (sorry)

I guess it will be something down the line

Code:

UPDATE `vtiger_account` SET `account_no`= concat('ORG',lpad(substr(`account_no`,4),5,'0')) where substr(`account_no`,1,4)=='000G';

that will fix the first 9, then you need another one for the next 90

Code:

UPDATE `vtiger_account` SET `account_no`= concat('ORG',lpad(substr(`account_no`,3),5,'0')) where substr(`account_no`,1,3)=='00G';

PLEASE test the assignment before launching

[polanskiman]

joebordes Wrote:Yes, there is an error in the update:

Code:

substr(`account_no`,0,3)

instead of

Code:

substr(`account_no`,1,3)

Now you are going to have to play around with the substring/lpad to get it right (sorry)

I guess it will be something down the line

Code:

UPDATE `vtiger_account` SET `account_no`= concat('ORG',lpad(substr(`account_no`,4),5,'0')) where substr(`account_no`,1,4)=='000G';

that will fix the first 9, then you need another one for the next 90

Code:

UPDATE `vtiger_account` SET `account_no`= concat('ORG',lpad(substr(`account_no`,3),5,'0')) where substr(`account_no`,1,3)=='00G';

PLEASE test the assignment before launching

I guess I will have to delete and re-import the whole database. There are 1650 records and those queries did not work either. Thank you anyways.

[joebordes]

Send me access information if you can, it isn't that hard, I'll fix it for you...