Different serial number

[chemlady]

Hi All,

The question I'm about to ask is similar to some already posted here and I've read a whole bunch of them but I still can't solve my problem. So, please help me.

I need to create a different 5-digit sequential serial number for each category of items in my inventory DB.

For example:

Category: Packaging ----> item ID 7XXXX

Category: Raw Material----> item ID 1XXXX

Category: Resale----> item ID 3XXXX

Category field is a member of VL formatted as a pop-up list and the appropriate category is selected when creating a new record.

Thanks in advance!

[comment]

Hmmm.. do you want numbers 1000 - 2999 reserved for Raw Materials, 3000-6999 for Resales, and from 7000 and up for Packaging?

Or do you want XXXX to be a common sequential number, and attach a prefix of 1/3/7 on top of that?

[chemlady]

Comment,

Yes, I want to (actually my boss wants to) have 10000-29999 reserved for Raw Materials, 30000-69999 for Resale and the rest for Packaging.

In the future though, I might need to add categories and, for example, 10000-19999 will be left for Raw Materials (if I don't have that many records by that time) and 20000-29999 for something else.

Any ideas?

Thanks!

[comment]

I was afraid that would be your answer. I don't know of a 100% fool-proof system to do this. The best I can think of is a self-join on Category, then ItemID auto-enters:

Last ( SameCategory::ItemID ) + 1

[Himitsu]

couldn't you just create a table for categories, have a field that sets the lower serial number and upper serial number limit. Then on the products area, it would be an autoenter calculation with a relationship based upon the category it goes to. Then maybe you could get the greatest value based upon the relationship of categorie (basically a self join relationship on itself) then have it take the highest +1. I don't know exactly how to do it, but I know it can be done. This system I have seen was used to display a preferred value in a portal. It would take the highest value and add 1 to it. Then you would sort the portal based on the value. Matt Patrowski had a good video on that some time ago. Because to me, if you have an auto enter serial number and just add the category ID to it, then raw materials would have 300001 and packing's first would be 100002 (1 being the packing and 3 being the raw materials. Calc field CatagoriID + startSerial (start serial just being an auto enter serial with 00000 entered in for first). But you can see where the problem is. It would quickly consume your ID numbers for all categories... I will try to get back to this post with the calc.

[chemlady]

I just tried what you suggested. It works, if I format the itemID field as number, then create a record for each category manually entering into the itemID field the starting number for my serials (10000, 30000 and 70000) and then change the itemID field to auto-enter calculation.

I'm just wondering if this approach will cause some unexpected problems...

Thanks!

[comment]

If we knew that, they wouldn't be "unexpected", would they?

I vaguely recall Ender comparing various approaches to this - but I can't find the thread. Off the top, I am thinking of 2 users creating records, and getting the same number before committing their records? Is that possible?

It wouldn't hurt to validate on unique, I think.

[chemlady]

I don't know if 2 users can get the same ID. What happens if it happens and one of them gets the validation failed message? Will he/she need to redo the record anew?

I was thinking about you approach and already found an "unexpected" problem . If a user deletes the last record in one of the categories, the next created record will have the serial number of the deleted one. And it's a no-no in this situation.

[comment]

User deletes last record, next created record re-uses the deleted number - that is an expected problem. I didn't think this would be a no-no.

As for the first question - I don't know. I have no experience with networked systems.

Let's try something else: a scripted creation of new records (always a good idea, IMHO) and 3 tables holding the serial numbers, one table per category. The relationships to the 3 tables are based on a user-specific value, let's call it gUserID, and allow creation of records in the 3 tables.

The user drafts a new record using global fields.

When the user clicks "Add Record", the script selects the appropriate subscript according to the category, and:

# CREATE A NEW RELATED RECORD IN ONE OF THE 3 TABLES

Set Field [PackagingIDs::UserID ; gUserID]

# CREATE A NEW RECORD IN THIS TABLE

New Record/Request

# GET ITEM ID FROM RELATED TABLE

Set Field [thisTable::ItemID ; PackagingIDs::SerialNumber]

# POPULATE THE REST OF THE FIELDS

Set Field [thisTable::XYZ ; gXYZ]

# BREAK OFF THE RELATIONSHIP

Set Field [PackagingIDs::UserID ; "nosuchuser"]

Commit Records/Requests

[chemlady]

Well, at my level of FM "expertise" the majority of problems are still unexpected.

I was hoping this could be done with a calculation, but I'll try your script.

Thanks!

[Himitsu]

chemlady... then instead of getting the highest value of the record, then have it set a global the controls the serial numbers. So, make a global for each category table or if you are using just one table for categories (in case you need to add another category on the fly) then make a field that stores the hightest ID number. Then make a new record script that would set field(highestID; =highestID + 1) then next line would be new record request and then set field(ID; =highestID) so if you ever did delete the record, the value would still remain in the field as long as no one touched it. Of course, the first record would have no value so you would have to create one record and call in ID start record... just an idea.

[comment]

Himitsu,

globals are user-specific. If the file is shared, each user has his/hers own global values, and only the host's changes to global field data are saved.

Your suggestion could be implemented by having a table NUMERATOR, with one field and three records (one for each category). The script could be something like:

# CREATE A NEW RECORD IN THIS TABLE

New Record/Request

Set Field [thisTable::Category ; Get (ScriptParameter]

# GET ITEM ID FROM NUMERATOR

Set Field [thisTable::ItemID ; NUMERATOR::SerialNumber]

# SET NEXT ITEM ID IN NUMERATOR

Set Field [NUMERATOR::SerialNumber ; NUMERATOR::SerialNumber + 1]

Commit Records/Requests

But it seems to me that there's still a chance, though a very small one, that 2 users running the same script at the same time will get the same number.

[chemlady]

with one field and three records (one for each category).

Sorry, but I don't understand this. How do I tell the script which record to pick?

I made a Numerator table with one record and three fields. Then made a script, but it does not work. Here it is:

New Record/Request

Pause/Resume Script [ Indefinitely ]#meanwhile the user picks the category

Set Field [ Inventory::Item_ID_pk; Case ( Inventory::Category

= "Packaging";Serial_ID_Numerator::Packaging;Inventory::Category = "Resale";Serial_ID_Numerator::Resale;

Serial_ID_Numerator::Raw_Materials) ]

Set Field [ Serial_ID_Numerator::Packaging; If ( Serial_ID_Numerator::Packaging < Inventory::Item_ID_pk and Last ( Inventory::Category ) = "Packaging" ; Serial_ID_Numerator::Packaging +

1 ; Serial_ID_Numerator::Packaging ) ]

Set Field [ Serial_ID_Numerator::Raw_Materials; If ( Serial_ID_Numerator::Raw_Materials < Inventory::Item_ID_pk and Last ( Inventory::Category ) = "Raw Material" ; Serial_ID_Numerator::

Raw_Materials + 1 ; Serial_ID_Numerator::Raw_Materials ) ]

Set Field [ Serial_ID_Numerator::Resale; If ( Serial_ID_Numerator::Resale < Inventory::Item_ID_pk and Last ( Inventory::Category ) = "Resale" ; Serial_ID_Numerator::Resale + 1 ;

Serial_ID_Numerator::Resale ) ]

Commit Records/Requests

I get a message: "This operation could not be completed because the target is not part of a related table"

What did I do wrong?

Thanks

[chemlady]

I understand that I need a relationship, but from what to what?

[chemlady]

I figured it out.

Set up the Numerator table to contain 3 records. Created another field, Category, in the Numerator table and made a relationship from that field to the Category field in the Invenory table. Then script, just like Comment suggested:

New Record/Request

Pause/Resume Script [ Indefinitely ]

Set Field [ Inventory::Item_ID_pk; Numerator::ID ]

Set Field [ Numerator::ID; Numerator::ID + 1 ]

Commit Records/Requests

Comment and Himitsu, thank you both!