cancel
Showing results for 
Search instead for 
Did you mean: 

ItemCode autogeneration

Former Member
0 Kudos

Hey All,

I was wondering if anyone has some working code or ideas to auto generate an item master code record. I have to do this for a client and I am not really sure how this might work.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Curtis,

I create a sql table as follows:

CREATE TABLE [dbo].[AC_TBLIDX](

[TblName] [varchar](10) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,

[Idx] [int] NOT NULL

) ON [PRIMARY]

Then I use a stored procedure to populate the value the first time, increment each successive time and return the updated.

I call this stored procedure thru code so that I can handle any user errors.

In my opinion this works better than a max function on a field within the oitm table.

You can also add extra fields that store a prefix or suffix.

snippet from stored procedure

if exists(select Idx from [dbo].[AC_TBLIDX] where TblName = @Tbl)

begin

update [dbo].[AC_TBLIDX]

set Idx = Idx + 1

where TblName = @Tbl

end

else

begin

insert into [dbo].[AC_TBLIDX] (TblName, Idx)

values (@Tbl, 0)

end

set @return_value = (select Idx from [dbo].[AC_TBLIDX] where TblName = @Tbl)

Former Member
0 Kudos

Thanks John,.

That looks like a good solution

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Curtis,

There is a SAP document out there that details how to assign an RMA number.

I use the same concept to assign a BP Code, with one exception.

I added a button to the item form that the user can click to assign the new item number. I do not use a formatted search.

Hope this helps.

John

Former Member
0 Kudos

Any idea where I could get that document? It sounds good.

Former Member
0 Kudos

Hi curtis

If your using alphanumeric or numeric you could add a formatted search to the Itemcode field.. It depends on how u want the code generated the rest shud be logic i guess..

Regards

Aris

former_member583013
Active Contributor
0 Kudos

Hello Curtis,

Are you trying to use only numerical numbers or is it a Alpha numeric combination?

Any sample numbers you have in mind?

Suda

Former Member
0 Kudos

No sample numbers. I was just looking for some logic that people have used in the past.