Jump to content

generate ItemID

This topic is 7477 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello People!

Perhaps this sould be under scripts but I have feeling that right definition of fields might do it.

Here is what I have so far:





Prefix_ItemID= VendorID&""&ItemTypeID&""&CatID

No_ItemID = autoenter serial 1...1

ItemID =Prefix_ItemID&""&No_ItemID

This approach works only for the 1st item

eg. ItemID = "ABC1" and ONLY IF I want to create another item made by the same A B C (vendor,item type,category) will correctly increment by 1.

How do I get arround the fact that I might be creating itemID "CCC1" many records later?

Thank you for the help!

Link to comment
Share on other sites

Create Prefix_ItemID just as you have it. Then create a self-join relationship called Prefix so that Prefix_ItemID = Prefix_ItemID. Then create an auto-enter calc field called CountPrefix= Count(Prefix::Prefix_ItemID). ItemID will then be Prefix_ItemID & "_" & If(CountPrefix="",1,CountPrefix+1).

That should do it. The sticky bit is that CountPrefix will be empty the first time you create a Prefix_ItemID. So in Item ID its corrected with the If statement and the addition of 1.


Link to comment
Share on other sites


Thanx Dan!

I had a feeling that some "find prevois" or "count" might be involved. Pretty slick!

One quick question:

Since this is ItemID= Unstored,Text and can not be indexed nor validated do you suggest I creat a "Temp_ItemID" and then copy-paste it so I can index it, or is this an overkill?


Link to comment
Share on other sites

You'd need to work on the serial and have it lookup the next one by the SelfJoin.

c_Compound = Item&Category&...

SelfJoinOnCompound = c_Compound::c_Compound

Serial= AutoEnter Number (increment) - Lookup from NextSerial by SelfJoinOnCompound - define it to return 1 if not found.

NextSerial = Max(;):SelfJoinOnCompound:Serial)+1

So that the ID is autoentered as Compound&Serial and preferably Compound&Right("0000&&serial,4) to have a workable text ID

Link to comment
Share on other sites

This topic is 7477 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.