11 Replies Latest reply on Jul 2, 2014 12:37 PM by philmodjunk

    Resetting Auto Serial Number Daily

    ChadBarnard

      Title

      Resetting Auto Serial Number Daily

      Post

           Hey guys,

           I've found a bunch of related posts on the net, but they never seem to give the whole answer, so here it goes.

           My company uses a job number based on date and how many jobs are written up daily: 140701-001 (YYMMDD-jobs per day).  

           Here's what I have to make job number work:

           Let ( D = Get ( CurrentDate ) ; Right ( Year ( D ) ; 2 ) & Right ( "0" & Month ( D ) ; 2 ) & Right ( "0" & Day ( D ) ; 2 ) & "-" & Right( "00" & SerialNumber; 3) )

           The "SerialNumber" is an auto-enter Serial field.  My primary key field is separate.

           I need to have the last three digits reset daily.  Is a "Set Next Serial Value" script the trick?

           Thanks in advance for any help.

        • 1. Re: Resetting Auto Serial Number Daily
          philmodjunk

               I strongly recommend that you NOT use this field as a primary key to link tables in relationships in your database.

               This is the the script step to reset the auto entered serial number. You'll need to run the script once a day at a time when users will not be using the database (or they might get a value of 001 when they don't want such.)

          • 2. Re: Resetting Auto Serial Number Daily
            ChadBarnard

                 Sorry I wasn't clear in my first post.  This is not the primary key field.  I have a separate primary key field.

                 Is there no way to set up an automatic change?

            • 3. Re: Resetting Auto Serial Number Daily
              philmodjunk

                   to repeat, set next serial value is the correct script step to use. It's a matter of performing that script at the right time in order to do this.

              • 4. Re: Resetting Auto Serial Number Daily
                ChadBarnard

                     I found an old post (http://forums.filemaker.com/posts/6b59209209?commentId=113832#113832) where you coded out this:


                "Freeze Window
                Go To Layout [POCounter]
                Show All Records
                Go To Record [Last]
                If [ Year ( Get ( CurrentDate ) ) > POCounter::POyear ]
                    Set Next Serial Value [POcounter::POCounterID; "001"]
                End If

                Go To Layout [original layout]"

                     I modified it to this:
                      

                "Freeze Window
                Go To Layout ["Orders" (Orders)]
                Show All Records
                Go To Record [Last]
                If [ Year ( Get ( CurrentDate ) ) > Orders:WorkOrderDate ]
                    Set Next Serial Value [Orders:SerialNumber; "001"]
                End If

                Go To Layout [original layout]"

                      

                Where "WorkOrderDate" is the date that the job was created and "SerialNumber" is an Auto-enter serial field.  This should work, right?
                     
                     I'll test it first thing in the morning and let you know.  If this does work, I guess the next step would be to automate it with a InstallOnTimerScript on the server side?

                     Thanks again for the taking the time to look at this and helping out.

                • 5. Re: Resetting Auto Serial Number Daily
                  philmodjunk

                       You'll need to modify that. The original was set up to run once a year and thus looked only at the year part of the date field.

                  If [ Year ( Get ( CurrentDate ) ) > Orders:WorkOrderDate ]

                  Should be:

                  If [ Get ( CurrentDate )  > Orders:WorkOrderDate ]

                       This is for a script that runs each time that you open the file. One draw back to that method is you could have a user leave their file open overnight and then this script does not run on their machine. If they then create a new records before someone else opens the file and trips the script trigger, you get a value that is part of yesterday's sequence. If you host the file using FileMaker Server, you can set up a server schedule to run once a day near midnight and that makes for better scheduling.

                       There are also ways to use a date based self join relationship and the Max function to get a sequence of values that does not need to be reset by script each day.

                       If you add a new table occurrence of Orders and link it by WorkOrderDate to the original occurrence of Orders, this expression will compute the next value in the series starting with 1 for each new date in WorkOrderDate:

                       Orders::WorkOrderDate = Orders|SameDate::WorkOrderDate

                       Max ( Orders|SameDate::SerialNumber ) + 1

                  • 6. Re: Resetting Auto Serial Number Daily
                    ChadBarnard

                         Luckily, we're a small company and only one person enters in orders.  I would like to automate it, so that that person is unaware of what's going on.  I'm interested in the "date based self join relationship" and the Max function that you mentioned.  I'm totally new to this, so bare with me.  (I'm reading manuals and watching tutorials as fast as possible, but I have a long way to go.).

                         Are you saying to link the WorkOrderDate from the new occurrence to the WorkOrderDate of the original occurrence?  If so, I'm assuming that's what "Orders::WorkOrderDate = Orders|SameDate::WorkOrderDate" meant - with Orders|SameDate being the second occurrence.  If I'm still on the right track, would the Max code line replace the Set Next Serial Value code line?

                    Thanks for your help.

                    • 7. Re: Resetting Auto Serial Number Daily
                      philmodjunk

                           You have the relationship right.

                           But you can use this expression in one of two ways:

                           Set field can use this expression in a script to assign this value to the field.

                           Or the field can be set up with an auto-enter calculation that uses this expression. If you go with the auto-enter calculation option, be sure to clear two check boxes: "Do not evaluate if all referenced fields are empty" and "Do not replace existing value".

                           And since this produces a number value, 1, 2 ,3

                           Use ( Right ( "00" & SerialNumber ; 3 ) to get 001, 002, 003...

                      • 8. Re: Resetting Auto Serial Number Daily
                        ChadBarnard

                             So where should the "Max ( Orders|SameDate::SerialNumber ) + 1" fall in the following calculation:

                        Let ( D = Get ( CurrentDate ) ; Right ( Year ( D ) ; 2 ) & Right ( "0" & Month ( D ) ; 2 ) & Right ( "0" & Day ( D ) ; 2 ) & "-" & Right( "00" & SerialNumber; 3) )

                             Before the Let, after the Let, embedded after the "& "-" Right(etc", or am I totally off base?

                             Thanks

                              

                        • 9. Re: Resetting Auto Serial Number Daily
                          philmodjunk

                               It doesn't go in that calculation. it's used to assign a value to SerialNumber--which still must be a separate field supplying a sequence number to the calculation that you just posted.

                          • 10. Re: Resetting Auto Serial Number Daily
                            ChadBarnard

                                 Like this?

                                 Orders::JobNumber has the calculation

                                                        Let ( D = Get ( CurrentDate ) ; Right ( Year ( D ) ; 2 ) & Right ( "0" & Month ( D ) ; 2 ) & Right ( "0" & Day ( D ) ; 2 ) & "-" & Right( "00" & SerialNumber; 3) )

                                 and Orders::SerialNumber should have a calculation of

                                                       Max ( Orders|SameDate::SerialNumber ) +1

                            • 11. Re: Resetting Auto Serial Number Daily
                              philmodjunk

                                   If you make that an auto-entered serial number, yes.