by John Mark Osborne, <jmo@best.com>
Serializing by Category
Changing a record serial number based on a selected category
Maybe you thought lookups weren't that useful anymore now that FileMaker is relational. Here is an example where lookups are better suited for a job than relationships.
The idea behind this technique is to enable you to serialize your records by category. For instance, you might want to serialize each of your clients records independently. The result you want to achieve might look like the following records:
Client Serial #
------ --------
Claris 1
Claris 2
Apple 1
Lotus 1
Claris 3
Apple 2
Lotus 2
Apple 3
Another example for the Category field would be to have your serial number start over each time a new month occurs. So, you could make the category a calculation field that returns the current month. My point here is not to limit yourself on what a category can be.
Are you interested yet? I'm hoping you are so here is the theory behind the technique.
What you need is a lookup that finds the last record you entered for each category. In order to accomplish this task you will need to use the lookup option, copy next lower value. This alone will not allow you to locate the last record for a particular category. You will also need a relationship that is based on a match field that is a calculation.
Before we pull apart the calculation match field you'll need to know that the first field you would create in this solution, in addition to your Category field, would be a Counter field which simply auto enters a serial number incremented by 1.
So, the calculation looks like this:
Category & Right("00000" & Counter, 6)
The reason leading zeros are added to the Counter field is because the result of the formula needs to be text since the category field is made up of text. When using the next lower option on a text match field the order of numbers will not be the same as in a number field. For instance, the numbers 1, 111, 5 and 15 will sort in the following order in a text field:
1
111
15
5
However, if you add leading zeroes onto the fields they will sort like this:
001
005
015
111
So, the calculation field is a simple way of adding leading zeros to each serial number. Here is the data from the beginning of the article with the calculation shown in a third column:
Client Serial # Match Category
------ -------- -----------------
Claris 1 Claris000001
Claris 2 Claris000002
Apple 1 Apple000001
Lotus 1 Lotus000001
Claris 3 Claris000003
Apple 2 Apple000002
Lotus 2 Lotus000002
Apple 3 Apple000003
I chose to add 5 zeroes to each number and then take the last 6 characters of that concatenation because I thought 999,999 serial numbers was enough. You can add as many zeroes as you desire. The key is to make sure the serial numbers with the added leading zeroes always stay the same number of digits. So, use my exact calculation until you understand how the Right function accomplishes that task.
The Match Category calculation field above will sort like so:
Apple000001
Apple000002
Apple000003
Claris000001
Claris000002
Claris000003
Lotus000001
Lotus000002
Therefore, when you perform a lookup using the Match Category calculation field and have the next lower option set it, it is easy to see how the newest record in each category will locate the last record for that category.
Were not done yet! There is a problem with the lookup we just described. When a new category is entered, there are no existing records in that category to locate. For example, if you entered a new record with the category of IBM it would start with a serial number of 4. Why you ask? Well, the next lowest value in our example data is Claris with a serial number of 3. That's because IBM will sort after Claris but before Lotus.
To get around this issue another lookup is needed. This lookup is far less complicated. It will be based on a self-join relationship with the Category field on both sides of the relationship. The value that it copies is irrelevant. What it does not copy is very important. What I mean by this is best explained by revealing another calculation. This calculation is the value you will display on your layout to show the Serialize by Category number:
Case(IsEmpty(First Serial), 1, Previous Serial + 1)
First Serial is a number field that will lookup based on the self-join relationship most recently described. When the category you enter is the first of its kind then the lookup will not locate anything and the First Serial number field will be blank. Now the calculation above makes sense. On the first record of each category the First Serial number field will be blank and thus the Serialize by Category field will display a 1. Subsequent records in the category will have a value in the First Serial field and thus the calculation will result in the Previous Serial field (the Previous field is the lookup field first described and it copies over the result of the Serialize by Category calculation).
Hopefully, this article has set the foundation for this technique. You may still have questions but I think those will be best answered by checking out the Bonus file included called, "SERIALIZ.FP3".
By the way, you may be wondering why I said at the beginning of this article that lookups are better than relationships. Well, if you use a relationship to try to accomplish this same task you will need to use the auto enter feature to place a calculated value. You would use a formula like so:
Max(Self Join::Category) + 1
Actually, your formula would be a little more complicated than this since it would need to take into account the initial record in a category. However, one of the problems that occur with this approach is that you cannot change your category once you have entered it. That's because you need to clear the serial number that was auto entered before it will auto enter again. Lookups don't have that same problem. In addition, if you duplicate a record then you will also break this method. The reason is the same as the previous problem. Auto enter fields need to be cleared before they will work again.
Have fun!
## END ##