My company has several different types of transactions, all kept in different FM7 files.

We want to add a tracking number to these files.

The tracking numbers are reserved ahead of time, so i will know what numbers will be used. The tracking number will be entered manually, and not necessarily at the time the transaction is created.

If a user types in a tracking number that is already in use in the same file, or in a different file, a suffix is generated.

The suffix is blank for the first instance of the tracking number, then is incremented alphabetically each time the same tracking number is used (A, B, C...etc).

What is the best way to keep track of the suffix for each tracking number and generate the correct suffix the next time the tracking number is used?

What do your tracking numbers look like? I think it may be best to script the process, possibly by providing a 'Validate' button to check whether the tracking number is an original or multiple and concatenating the number with the appropriate suffix in a new field. Keeping the tracking number in a separate field from the combined tracking number will help to eliminate confusion when counting the number of existing related numbers.