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:

Fields:

VendorID

ItemTypeID

CatID

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!

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.

Dan

Well, make sure you never delete a record then.

ABC1

abc2

ABC3

ABC4

Then, delete ABC2

Next creation will be ABC4 leading to a duplicate

IMO, this approach is bug sensitive.

Great!

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?

Thanx!

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

Hi Ugo!

WHERE oh WHERE do u get this stuff.....works like a charm!

the most crutial was "Serial" field....briliant!

Much Obliged!

Arrgh! I defer to you sir, you are the master. I hadn't thought of the deletion problem. Nicely done.

Dan

Guys,

I didn't have an epiphany with this one. This is a known "tip", which I first get from Bob on these Forums, and recently found Ray had a sample for this on his site.

Thanks though.