+- CoreBOSBB (https://discussions.corebos.org)
+-- Forum: Support (https://discussions.corebos.org/forumdisplay.php?fid=17)
+--- Forum: Administrator Support (https://discussions.corebos.org/forumdisplay.php?fid=8)
+--- Thread: Record Numbering (/showthread.php?tid=15)
Pages:
1
2
Record Numbering - polanskiman - 05-20-2014
How do I change the Record Numbering say CON01 to CON0001 for multiple contacts at once.
Re: Record Numbering - joebordes - 05-20-2014
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]
Re: Record Numbering - polanskiman - 05-21-2014
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};Re: Record Numbering - Ekim - 05-21-2014
You need to set a condition for your statement. to change only the needed rows.
Re: Record Numbering - polanskiman - 05-21-2014
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.
Re: Record Numbering - joebordes - 05-21-2014
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.
Re: Record Numbering - polanskiman - 05-21-2014
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]
Re: Record Numbering - joebordes - 05-21-2014
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
Re: Record Numbering - polanskiman - 05-21-2014
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.
Re: Record Numbering - joebordes - 05-21-2014
Send me access information if you can, it isn't that hard, I'll fix it for you...