Serial Numbers that count separately per project

[Jacob22x]

I have a database that has many projects, and each project has many issues associated with it. (Two tables, Project and Issue). In the issue table I have a field "ReferenceNumber."

My boss wants it to be job specific. So, I added a field to the Project table called "IssueCount" that is an unstored calculation: Count (Issue::_kIssueID). This gives me a total of the number of issues per project.

Then, I defined the field "ReferenceNumber" as a text field with an auto-enter calculation:

(Project::IssueCount + 1) & "-" & Issue::ProjectNumber.

This gives me a reference number such as 1-W4012F, which creates a per project sequential count. My problem is that now that we are running the database with FileMaker Server and multiple clients, the reference numbers are repeating. Example: A user creates four issues, the first three are assigned the reference number 1-W4012F, and the fourth is 4-W4012F. I assume this is because the host isn't updating the calculation as quickly as they are entering new issues.

Any solutions?

[Tina Marie]

Hi Jacob

I just read your post, but I have to head out but I will be back at my computer in about an hour.

If you can hang on, I will give to you what one of my solutions for this would be. It's very simple, and you should be able to implent it.

I just have to run at the moment but I will get back with you.

Thanks

Tina Marie

[cobra]

Hi Jacob,

I'm assuming here that ReferenceNumber values need to be unique.

The problem with using Count(Issue::_kIssueID) is that when Issue records are deleted and then created, you may end up with duplicate ReferenceNumber values.

For example, you have 4 issue records associated with a particular project. Your IssueCount would be 4 and is used to create the ReferenceNumber 5-W0001.

If a user then deletes an issue record with reference numbers 1-W0001 to 4-W0001, your next ReferenceNumber will also end up being 5-W0001.

A solution is to create a numerical field (you can call it NextReferenceNumber) and increment it by 1 (using a Set Field script step) after a ReferenceNumber is used. Remember to set the autoenter value for NextReferenceNumber to 1 (so newly-created projects records always have a NextReferenceNumber of 1) and only increment it by 1 after you have used the number.

This will also get rid of the other error you experienced.

For project records that already exist, you will need to write a script to loop through the project records and set the value of NextReferenceNumber to:

GetAsNumber(Middle(Max(Issue::ReferenceNumber), 1, Position(Max(Issue::ReferenceNumber), "-", 1, 1)-1)) + 1

Edit: Added + 1 to the calculation above...otherwise your next ReferenceNumber would have been the same value as an existing ReferenceNumber.

Which will get the largest associated Issue ReferenceNumber for a particular project.

The script need only be run once - to facilitate a smooth transition to using the new method of generating ReferenceNumber values.

Take your database offline while you do the update, as you don't want anyone entering a new ReferenceNumber while you're making the transition.

Hope this helps!

Cobra