FMS For Auto-Item Code Generation Based on Item Group and two other SubCat1, SubCat2 UDFs.

[Former Member]

Dear Experts,

I want to create FMS  for Auto-Item Code Generation Based on Item Group and  two other SubCat, SubCat1 UDFs.

Scenario is For Item Code Generation, I Select ItemGroup, then SubCat, Subcat1 UDFs. then I want to generate ItemCode.

I have Query For ItemGroup and SubCat field which is Working well, I want Additional SubCat1 Field In this FMS

Query For ItemGroup and SubCat----->

select ((select ItmsGrpNam from OITB where ItmsGrpCod = $[$39.0])   

+ $[OITM.U_SubCat] 

+ right(N'00000' + convert(nvarchar(5), (select isnull(count(*), 0) + 1 from OITM

where ItmsGrpCod = $[$39.0] and U_SubCat = $[OITM.U_SubCat])), 5))

only want to add another SubCat1 Field in this query

Your Kind help would be highly appreciated

Thanks & Regards,

Navanath

9975525471

[Former Member · Accepted solution]

Hi Navanath,

Try this

select ((select ItmsGrpNam from OITB where ItmsGrpCod = $[$39.0])  

+ $[OITM.U_SubCat]

+ right(N'00000' + convert(nvarchar(5), (select isnull(count(*), 0) + 1 from OITM

where ItmsGrpCod = $[$39.0] and U_SubCat = $[OITM.U_SubCat])), 5))

+ $[OITM.U_SubCat1]

+ right(N'00000' + convert(nvarchar(5), (select isnull(count(*), 0) + 1 from OITM

where ItmsGrpCod = $[$39.0] and U_SubCat1 = $[OITM.U_SubCat1])), 5))

[Former Member]

Hi Bharathraja,

Thanks for your prompt reply

i have checked your query but it gives me some error

[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'. '' (SWEI)

But I have created  some modification as

select ((select ItmsGrpNam from OITB where ItmsGrpCod = $[$39.0])   

+ $[OITM.U_SubCat]  + $[OITM.U_SubCat1]

+ right(N'00000' + convert(nvarchar(5), (select isnull(count(*), 0) + 1 from OITM

where (ItmsGrpCod = $[$39.0] and U_SubCat = $[OITM.U_SubCat] and U_SubCat1= $[OITM.U_SubCat1] ))), 5))

now its working perfect