1 2 Previous Next 15 Replies Latest reply on May 7, 2014 10:18 AM by JamesKnecht

    Unique Job Number Based on Date



      Unique Job Number Based on Date


           Having trouble getting this to work.  I read some threads here, but they are fairly old.   Our dBase system generates a job number, during order entry, which is a unique 11 digit dumber based upon year, month, day + a 3 digit number.  First number of the day would be 20140426001.  The 3 digit number resets every day.  At worst case, I can use a simple auto entry serial number, set to be unique & not empty, but we are used to our numbers telling us when the job was entered.


           Any help would be appreciated & this will operate in a multi-user environment.  Please phrase your comments like you were talking to a child.  I am very new to Filemaker, but thanks to some help here, I have finished the customer file... and it is kick ass!

        • 1. Re: Unique Job Number Based on Date

               Such methods of generating a unique ID that "encode" additional info are not ideal Primary Keys. A Primary key is a field used to uniquely identify each record in a table so that it can be linked to other records in relationships. If you need such a value to support existing procedures and expectations, you can create a field to store such a value for labeling and search purposes, but don't use it as your table's primary key.

               There are two methods that I can think of that could be used for this, both start from a date field that auto-enters the record's creation date. With one method, a script would be run every morning (this can be scheduled if you host from FileMaker server) that resets a serial number field back to 1 for the new day's series of values. The other uses a self join to produce the same result. (A self join links a record to other records in the same database table in a relationship.)

               The self join relationship would match records by the auto-entered date value:

               Yourtable::DateField = YourTable 2::DateField

               You create YourTable 2 by selecting YourTable in Manage | Database | Relationships and clicking the duplicate button (Two green plus signs).

               With the self join a number field can either auto-enter Max ( YourTable 2::SequenceField ) + 1 or a script for creating each new project record can use set field to assign this value. If you set up an auto-enter calculation, be sure to clear the "do not evaluate if all referenced fields are empty" and the "do not replace existing value..." check boxes.

               Note that this is just to get a field that starts over at 1 for each new day so if you use a script once a day to reset an auto-entered serial number things are simpler.

               Then you can set up a number or text field with this auto-enter calculation:

               Year ( DateField ) & Right ( "0" & Month ( DateField ) ; 2 ) & Right ( "0" & Day ( DateField ) ; 2 ) & Right ( "00" & SequenceField ; 3 )

               SequenceField is either your serial number field reset once a day or the Field that gets a serial number from your script or auto-enter calculation. The right functions and date functions used put together the values that you have specified with the correct leading zeroes. These functions can be looked up in help.

          • 2. Re: Unique Job Number Based on Date

                 In dBase, I store the current date in a field, and then check that field against the current date.  When the two don't match, I know the date has changed, so the sequence number is reset to 001 & then the date field is updated.  Would that not work here?  

                 This is not being used on some huge enterprise operation... just a small business entering 50 or so orders a day.

            • 3. Re: Unique Job Number Based on Date

                   I can now generate the job number but can't figure out the script to reset the 3 digit number daily.  I assume when I get the script right I should set a trigger for record load?

                   I have a new table, GENERATE JOB NUMBER, with 3 fields.

                   Date: Simply a date field with no validation or calculation

                   Three Digits:  An auto enter serial number, generate on creation, no calculated values, validate always, users not allowed to override during entry, strict numeric only, not empty & unique value.

                   Calculation:  A calculation field, calculation results numeric, 1 repitition, do not evaluate if empty not selected & not storing calculation results.  Calculated as :   Year(DATE)   &  Right ( "0"  &  Month(DATE) ;2 )  & Right ( "0"  & Day(DATE) ; 2 )  & THREE DIGITS


                   Is the above correct & help on the script would be appreciated.  This is a script I wrote that does not work.  Plan would be to add a step to update Date field with current date, but wanted to try one step 1st.

                   If [Generate Job Number::Date<>GET(CurrentDate)]

                     Replace Field Contents[No Dialog;Generate Job Number::Three Digits="001"]

                   End If


              • 4. Re: Unique Job Number Based on Date

                     You shouldn't need a new table just to generate the Job Number.

                     The script step Set Next Serial Value can be used to reset the Next serial value to 1 for a field that auto-enters a serial number.


                          In dBase, I store the current date in a field, and then check that field against the current date.  When the two don't match, I know the date has changed,

                     Such a method could be used in FileMaker. The key is to ensure that such a script always runs at the correct time. The methods I described run such a script once a day as that is all that should be needed, but you could set up a script to do this every time you open the file if you make it a practice to close the file each day. You could also use a button with a script to create each new project record that checks the date in this fashion as long as the script makes any such changes before creating the new project record.

                • 5. Re: Unique Job Number Based on Date

                       I tried this and it is working.  The Date field is updated to the current date & the 3 digit field is reset to 001.

                       If [Generate Job Number::Date<>GET(CurrentDate)]

                          Set Field[Generate Job Number::Three Digits;"00" & 1]

                          Insert Current Date[Select;Generate Job Number::Date]

                       End If


                       Should this give me a secure job number?  I figured the safest way to make sure this run is to set a layout trigger to fire on record load.  I understand a job number might not be used, if the record isn't committed, but that doesn't bother me.

                       I set this up in a special table for test & understand it can be part of the main job table.

                  • 6. Re: Unique Job Number Based on Date

                         Dangit... this is not working.  When I hit new record, the 3 digit is not updating by one & the calculation field displays 00001.  However when I click on the calculation field, it then displays the job number in proper format... but with the 3 digit still set at 001.

                    • 7. Re: Unique Job Number Based on Date

                           The way I have done it is to create a new record, perform a find on jobs with creation date = current date, then generate the job number via calculation of ("date string" & - & "record count")   Where the date string is however you want your date to look and record count is formatted as 3 digits.

                           You can do this via a script and freeze the window so that the user doesn't see the screen flickering while it is happening. It should be pretty fast, no one has noticed any hesitation on my solution.

                      • 8. Re: Unique Job Number Based on Date

                             Nothing that I can see in your example is incrementing the three digits field. And I cannot tell from your script, the "context" in which it executes. On what table is your layout based where you have the OnRecordLoad trigger being tripped? I doubt that it's based on a table named Generate Job Number. I would guess that it's based on Projects. Thus, the If statement may or may not be evaluating correctly as it will depend on whether Generate Job Number is linked to projects in a valid relationship that always matches to the same record in Generate Job Number or if the date field has global storage specified.

                             In any case, using OnRecordLoad seems a bit excessive as you only need to set the value back to 1 once a day, not every time a record loads.

                        • 9. Re: Unique Job Number Based on Date

                                    In any case, using OnRecordLoad seems a bit excessive as you only need to set the value back to 1 once a day, not every time a record loads.

                               Funny, but I don't see an easy way of determining when the date has changed, so I trigger the script OnRecordLoad.  I set up a date field which automatically loads the current date.  I then compare that to the oldest date from past records.  If the date has changed, I reset the 3 digit field to 1.Seems to work fine.

                               If [Max(Generate Job Number::Date) < Get(CurrentDate)]

                                  Set Next Serial Value [Generate Job Number::Three Digits;1]

                               End If

                               For reference, I set up a test table, Generate Job Number, just to test this code.  This will be used on the main job table once I create that code.  Also, since I am not certain this will always return a unique value, in a multi-user environment, I decided to include a separate number field, which is a serial number set to unique.  I will use that number to establish relationships with other tables, but use this for our reference & filing purposes.  To me, this is best of both worlds.  I have a unique number used for relationships & another (hopefully unique) number that tells us something about when the job was entered.

                          • 10. Re: Unique Job Number Based on Date

                                 There are multiple ways to set up a script to run once day or at least less often than once each time a record loads.

                                 OnFirstWindowOpen can run a script for this purpose once each time the file is opened.

                                 If hosted over a network via FileMaker Server, a server schedule can run such a script once per day.

                                 An OS based Task Manager such as Windows Scheduled Tasks can be set to open a "robot" FileMaker file once a day and it can update this setting.

                                 And for files not hosted from server but left open 24/7, InstallOnTimerScript can be set up to run such an update script on a 24 hour interval.

                            • 11. Re: Unique Job Number Based on Date

                                   Thanks.  The plan is to run this on Server when we go live.  I'll change the trigger at that point.

                                   Will the calculated job number be unique in a multi-user environment, or am I safer to set up a simple primary key as I did?

                              • 12. Re: Unique Job Number Based on Date

                                     Whether a single user or multi-user system, always use either an auto-entered serial number or Get (UUID) value as the primary key. Do NOT use the value computed here as the primary key. In a multi-user environment there is a small chance that you could get two records with the same ID value if two or more users create a new record at just the wrong instant in time. (More than one use can get the same value from the Max function at the same time and then both user's new records get the same max + 1 value.) Set up this field as either a text field that is assigned a value by a script that creates the new record or as an auto-enter calculation and then set a unique values validation rule on it to keep a duplicate from being created. (If this should happen, one user will get an error message and have to try again to create a new record.)

                                • 13. Re: Unique Job Number Based on Date

                                       I assumed the calculated number might not always be unique, thus my use of the unique serial number field.  Thanks for confirming my assumption.  I have lots to learn & you are quite the teacher.

                                  • 14. Re: Unique Job Number Based on Date

                                         Yes, but I am recommending that you use a separate serial number field that is ONLY a serial number as the primary key. Use this calculated key as a secondary value in the main table so that users that need it can use it in reports and for searching, sorting etc, but keep things "safe" by relying on a simple serial number as your actual primary key.

                                         The ideal primary key should be:


                                         Never ever changed once assigned to a newly created record

                                         Devoid of any "encoded meaning"

                                         and as simple and "bullet proof" as possible

                                    1 2 Previous Next