Leb i Sol Posted February 2, 2004 Share Posted February 2, 2004 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! Link to comment Share on other sites More sharing options...
DanBrill Posted February 2, 2004 Share Posted February 2, 2004 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 Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted February 2, 2004 Share Posted February 2, 2004 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. Link to comment Share on other sites More sharing options...
Leb i Sol Posted February 2, 2004 Author Share Posted February 2, 2004 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! Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted February 2, 2004 Share Posted February 2, 2004 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 More sharing options...
Leb i Sol Posted February 2, 2004 Author Share Posted February 2, 2004 Hi Ugo! WHERE oh WHERE do u get this stuff.....works like a charm! the most crutial was "Serial" field....briliant! Much Obliged! Link to comment Share on other sites More sharing options...
DanBrill Posted February 2, 2004 Share Posted February 2, 2004 Arrgh! I defer to you sir, you are the master. I hadn't thought of the deletion problem. Nicely done. Dan Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted February 2, 2004 Share Posted February 2, 2004 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7386 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 accountSign in
Already have an account? Sign in here.
Sign In Now