Re-initializing serial number each day

[dbCAN]

Hello,

I would like to create a calculated field where it is the current date "-" serial number starting at 1 and incrementing by 1 through the day.

ie. 02/23/08-1 then 02/23/08-2 etc through the day.

next day it would be 02/24/08-1 ....

I cannot figure out if you can re-initialize a counter for each day... or is there a different way to approach this?

thanks in advance,

[Pirsqed]

I actually made a system like this recently for a an invoice number generating system.

It includes the users initials, the date in a specific format, followed by a serial number, which resets during the next day.

I did this using a bit of a hack in FileMaker Pro 7, but FMP9 has a "Set Next Serial Value" which can reset a field. I'd have a number field that auto generates a number starting at one to act as just a counter. Then, your new record button is replaced with a script that combines the date and the counter, also searching for any records made today. If none are made today then you use the "Set Next Serial Value" script step to reset to counter field to "1"

If you need any more help than that, let me know. I'd be glad to help you get it working. I'm not sure if FMP8.5 has those options or not.

The hack I used in FMP7 is rather simple. If anyone is interested I'd be glad to elaborate on that, too.

[dbCAN]

Well I am pretty new at this (still learning) and would appreciate it if you could walk through the steps for a single field example. As you mentioned the system has to know if any numbers have been issued for that day and then the next in sequence.

I see 8.5 has serial number but it does not seem to be the same as you indicated... I'll dig some more but I assume your hack may be necessary.

regards,

[dbCAN]

It does have the "set next serial value". Would you be able to discuss the steps needed?

[Pirsqed]

Of course!

One bit of prep work before the script is made. Make a number field that has an auto-enter serial value. Increment by 1 and start with 1. You can just hide this field somewhere in the layout.

The script will start off Set Error Capture On and a search to see if any records have been created today. To do this, just use "Enter Find Mode" making sure to deselect the "Pause" option. Now, Insert Calculated Value. Specify the field with the date-seral in it. For the calculation itself, I would use just GetasText(Get(CurrentDate )). Then, Perform Find.

This will find any that are done today. We can assume if none are found that we need to reset the serial value to "1." Otherwise we'll leave it alone and let it increment itself. To do that use an If and specify as such: "Get(FoundCount) > 0"

Within that If you'll use the "Set Next Serial Value," specify the counter field and then specify "1" for the calculation.

After the End If, use New Record/Request. Then simply use Insert Calculated Result. Specify the date-serial field, then specify the calculation as such: date-serial field & "-" & counter field

That should be that. Then, whenever you press the new record button (which you'll replace with the above script) it will give you the next serial valuse, unless it's the first done that day.

If anything isn't clear, let me know.

Edited February 8, 2008 by Guest

[comment]

Be very careful with custom-made serial numbers - it's very easy to get duplicates and/or missing numbers, esp. with shared files. See, for example:

http://www.fmforums.com/forum/showtopic.php?tid/191695/post/273995/#273995

I did this using a bit of a hack in FileMaker Pro 7, but FMP9 has a "Set Next Serial Value"

:qwery: This script step has existed since at least version 6.

[Pirsqed]

:qwery: This script step has existed since at least version 6.

O_o

Well I'll be. You're absolutly right. No idea why I missed it before.

Edit: Is there really much of a risk of duplicate values? The time the script takes isn't exactly long.

I suppose after the new record is created you could search for duplicate numbers and then increment from there.

Edited February 8, 2008 by Guest

[Fenton]

No, there is not "much" risk. But any risk is unacceptable in IDs used for relationships. So create your custom ID for viewing, but use a real serial ID field for relationships.

[comment]

The question is: what ARE they really used for? If they are important, they shouldn't be exposed to the risk of duplicates, even if they are not used for relationships. If they are not important, why bother with them?

[comment]

Let me put it this way: the number of people who had reported problems with duplicate "custom-made" serials is much greater that the number of people who had reported they have won the lottery. But it could be that those that had won the lottery just didn't bother to let us know...

[Fenton]

I've been told to implement them (and I do mean "told") in scenarios where they say something like this:

"Our accounting department wants to see sequential numbers, so that they can tell if there is a gap, if there is a job which has not been invoiced yet" (etc.).

The irony of this is that they are asking you to implement something which is not 100% reliable in order to find out something which is easily 100% obtainable by a simple Find or List view in the database. I tell them this, but they still say the accounting department (who seem to have some inherent fear of databases) insist. What to do? I give it to them, but only as a "display" field, and use my real ID field for everything.

[comment]

The irony of this is that they are asking you to implement something which is not 100% reliable

Exactly. Of course, it CAN be done reliably - but it takes a lot of effort and considerable resources, so in most cases it's just not worth it.

[Søren Dyhr]

who seem to have some inherent fear of databases

Indeed this insisting on eyeballing gaps, which could be solved this way:

http://fmforums.com/forum/showpost.php?post/278877/

...is more likely to make a drifted view (spin ridden) at things, than a proper method - simply because it builds on an abstraction. But does marbled interior of a bank make it more solid, it's still dealing with an abstraction - money!

--sd

[dbCAN]

Well, I have followed your steps to the best of my ability and still the serial number continues to increment (I get no errors so obviously I have the find component not working properly??).

Any troubleshooting suggestions are welcome.

TIA