on 06-15-2011 3:18 PM
Hi all,
I have a problem in creating auto numbering in BP for Customer type using Formatted search
My query is like this
declare @tempNo as char(20)
set @tempNo=(select Isnull(max(REPLACE(OCRD.CardCode , LEFT(OCRD.CardCode,10),'')),0) + 1
from OCRD where (CardType ='C') and LEFT(OCRD.CardCode,3) = 'CU.' )
set @tempNo='CU.'+left(convert(varchar,GETDATE(),112),6)+'.'+@tempNo
select cast(@tempNo as char(20))
It should be creating the new number for example
CU.201101.1
CU.201102.2
CU.201103.3
CU.201103.4
The format will be like this CU.YYYYMM.autonumbering
So if the current year is still the same it will always increase the number and it will reset the number become 1 if the current year is change.
But when i execute the number , the number doesn't increase, still generate the last number CU.201103.4
I have use this query in generate item number, it runs well but different format.
Is there some thing wrong with my query?
Thanks in advance
Regards
Jia shun
Edited by: Jia Shun on Jun 15, 2011 4:30 PM
To generate this date-dependent BP code, try this FMS:
declare @ddm varchar(20)
set @ddm='CU.'+(select convert(char(6),GETDATE(),112))+'.'
Select @ddm+
isnull((Select ltrim(max(substring(c.CardCode,9,6)+1))
From OCRD c
Where substring(c.CardCode,1,10)=@ddm ),'1')
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jia,
I see two possible reasons that you could investigate
1. Because you are using several string functions (LEFT and REPLACE), there might be an arithmetic error in one of the functions.
2. You declared the variable @tempNo as CHAR, and then you use it in an arithmetic operation (+ 1). You could try using a second variable declared as INT, use that for the arithmetic operation and then cast it back as CHAR and combine it with your original variable.
If neither work, could you post the query that you are using for item codes (you say that one is working correctly) ?
Regards,
Johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
107 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.