Generating two part ID/SKU numbers

[NtSmedia]

I am working on a Filemaker solution (using v16.x Advanced) that requires the auto-generation of SKU codes that are built from 2 separate numbers. The first number, a ContactID, is auto-generated as a simple serial number in a "Contacts" table when a contact name is added. The second number needs to be generated in a separate "Items" table and needs to be the highest value for any given ContactID plus 1, call that number the ItemID, resulting in a "SKU" code of "ContactID-ItemID".

(I don't think the "max" formula will work on a local table)

Resulting SKU Examples:
1-1
1-2
2-1
2-2
etc.

In the "Items" table the user would first select the desired contact with a pop-up selector showing the ContactID and ContactName from a value list that comes from the "Contacts" table. Once the user has chosen the desired Contact I want the appropriate next ItemID value to be determined by calculation. From there it seems like a simple calculation to generate the desired "SKU" code.

But I'm stuck on the best and/or most efficient way to do this ... suggestions would be greatly appreciated!

[comment]

The best way to do this is not to do it. It is very difficult to generate separate series in the same table reliably. How badly do you need this? Why is it not sufficient to have a single consecutive series for all items, with the ContactID as a prefix - i.e.:

1-1
1-2
2-3
2-4
etc.

 

Edited January 9, 2021 by comment

[NtSmedia]

Thanks for the quick reply!

I do understand how straightforward it is to do this in a linear fashion and have not ruled out the simplest approach but there are reasons I am trying to do it this way. The biggest reasons are that this is a replacement of a legacy system and am hoping to retain continuity (I know, not justification in itself) and that the codes are more meaningful by providing staff some insight just by seeing the SKU that is not possible when the numbers are simply incremental. Also, the numbers will get very large rather quickly if linear and not distributed.

Can you describe the basic requirements and/or why you feel is difficult to accomplish reliably?

Much appreciated!

 

 

[comment]

Here are some scenarios to consider:

1. User creates a new item and selects a contact. An SKU is created and assigned to the item. Then user notices she made a mistake and selects another contact.

2. Same as #1, but the mistake is discovered only after another item has been created for one or both of the contacts involved.

3. Same as #1, but the mistake is discovered only after the item was included in an invoice.

4. Two users create a new item for the same contact roughly at the same time.

There is more, but I think these stress how important it is for a key to be (a) unique and (b) permanent.