cancel
Showing results for 
Search instead for 
Did you mean: 

Need FMS For Auto Generation of Item Code Based on Item Group Name and UDF

Former Member
0 Kudos

Dear Experts,

Need FMS For Auto Generation of Item Code Based on Item Group Name and U_SubCat

In UDF U_SubCat i have link User Defined table SubCat,

In this table i have some data like BO,HP,PP,Define New etc.

I want to generate item code based on this data

Scenario is

ItemGroupName             U_SubCat      ItemCode have to generate

LLM                              BO             --->     LLMBO001

LLM                             BO             ---->    LLMBO002

LLM                             HP             ---->   LLMHP001

SP                               BO             ---->    SPBO001

SP                               HP             --->     SPHP002

AutoRefresh on U_SubCat

please help me for this

Thanks & Regards,

Navanath

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Experts,

Can any one help me for this

Regards,

Navanath

pierrecanali
Active Participant
0 Kudos

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))

hope this will definitively solve your question.

Regards

Pierre

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi Pierre Canali,

Greetings !!!!!

Thanks a lot for your splendid solution. It is working really fine for me.

Great!! Thanks for your help. I have also learnt lot from this solution.

Hope we will have great association in future for such problem resolution and learning great things from you.

With Warm Regards

Navanath Gore

Former Member
0 Kudos

Hi ,

Please can you explain in detail as i have not understood what exactly you want to say.

Thanks & Regards,

Navanath

Former Member
0 Kudos

Hi Pierre

I had used both Queries but the first one Query error-->

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting the nvarchar value '"""' to data type int.  '' (SWEI)

Second Query Error-->

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting the nvarchar value 'O-1600A-25KA-30' to data type int.  FMS execution failed on field 'ItemCode' with query name 'AutoCodeGenration'

So please help me for this

Thanks & Regards,
Navanath

pierrecanali
Active Participant
0 Kudos

Hi Navanath,

the problem is that you have ItemCodes with last three digits not castable to numeric values (ex. O-1600A-25KA-30) so you cannot calculate the next number !

An idea would be to have some field, even an user field, used to filter ItemCodes letting the formatted search get the new code.

Regards

Pierre

Former Member
0 Kudos

Hi Pierre

Thanks for your reply

I tried using your Query but it gives me error

Invalid parameter  'User Tables' (OUTB) (ODBC -2001)  [Message 131-183]

Invalid parameter  '' (SWEI) (ODBC -2001)  [Message 131-183]

I have found one Query which give me Auto ItemCode generation based on U_SubCat but I want to Add Item Group Name Before this Query result so help me for this.

declare @len smallint;

select @len =4;

SELECT  CAST((Select $[OITM.U_SubCat]) AS VARCHAR)+right(replicate('0', @len) + ltrim(Cast (IsNull(Max(substring(ItemCode,5,Len(ItemCode))),0)+1 as Int)), @len)  NextNo FROM OITM T0

Where U_SubCat like (Select $[OITM.U_SubCat])

Thanks & Regars,

Navanath

pierrecanali
Active Participant
0 Kudos

Hi Navanath,

I had this working for me, before I was missing something


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

+ $[OITM.U_SubCat]

+ right(N'000' + convert(nvarchar(3), coalesce((select max(convert(int, right(ItemCode, 3))) + 1 from OITM), 1)), 3))

if you wish to use yours instead it should be:


declare @len smallint;

select @len =4;

SELECT (select ItmsGrpNam from OITB where ItmsGrpCod = $[$39.0]) + CAST((Select $[OITM.U_SubCat]) AS VARCHAR)+ right(replicate('0', @len) + ltrim(Cast (IsNull(Max(substring(ItemCode,5,Len(ItemCode))),0)+1 as Int)), @len)  NextNo FROM OITM T0 Where U_SubCat like (Select $[OITM.U_SubCat])

Regards

Pierre

pierrecanali
Active Participant
0 Kudos

Hi Navanath,

try with this:



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

+ $[OITM.U_SubCat]

+ right(N'000' + coalesce((select max(convert(int, right(ItemCode, 3))) + 1 from OITM), 1), 3)

Regards

Pierre