11 Replies Latest reply on Mar 27, 2011 10:34 PM by lowededwookie

    Serial Number based on date



      Serial Number based on date


      Hi, can someone help me here?

      I want to make a database that creates a job number based on the day. For example


      So while the date is 24/03/11 the last four digits will increase by one but as the date changes the last four digits start from 0001 again like so


      And so on.

      The idea is to create a job logging system with features that we need rather than trying to modify off the shelf products to do what we need. The RF number will be the primary key where job updates get recorded against that reference.

      Is this possible or does this have to be done with something like PHP which then updates the records?

      Thanks in advance.

      I'm newish to FileMaker Pro but now that I have some time I can sit down with this and try and learn it properly and what better way than to create a decent project rather than just contacts lists etc. :)

      Incidentally I use Bento exclusively for this sort of thing at the moment but want something that can be used by people without Apple gear.

        • 1. Re: Serial Number based on date

          For the sake of this example lets say your Primary table occurrance in the RG (relational graph) is called "MyTable" and you have a date field that is called "theDate".

          Go to the relational graph create a duplicate of the table occurrance MyTable. Then name your duplicate "MyTable 2 MyTable_by_Date"

          Next you will need to link the two tables together using your date field. The link should look something like this:

          MyTable::theDate  =  MyTable 2 MyTable_by_Date::theDate

          The following are the fields you will need to define in MyTable:

          theDate [Date] (Date field that will be used to link all the RF records together by date, you can set this to autoenter the creation date if you like.)

          theSNbr [Text][Autoenter;Calculated value;Do Not Replace Existing value of field (if any)]  [Uncheck "Do Not Evaluate if all referenced fields are empty] = Right("0000" & (Count(MyTable 2 MyTable_by_Date::theDate) + 1) ; 4)

          theRFnbr [Calculation][Text] = "RF" & right(year(theDate);2) & right("0" & month(theDate);2) & right("0" & day(theDate);2) & theSNbr

          This uses the relationship to count the total number of occurrances of the date field for a given date upon record creation, which in turn increments the number automatically.

          • 2. Re: Serial Number based on date

            Wow, thanks for the help.

            I think I understand what you're saying. Sorry, I tend to work visually so I'm just picturing this in my mind.

            Can you answer me one question though?

            How do you strip the "/" from the date. Is it this section:

            theRFnbr [Calculation][Text] = "RF" & right(year(theDate);2) & right("0" & month(theDate);2) & right("0" & day(theDate);2) & theSNbr

            Once again thanks for your help.

            • 3. Re: Serial Number based on date

              You are correct. We are stripping out the "/" from the date by grabbing each portion of the date separately by using the date functions: year(), month(), day().

              The rest of it is just padding a "0" in front of the month or day in case it winds up being a single digit month or day such as 3/1/2011.

              I use the right() function to make sure that I only get a 2 digit result after padding on the "0" or to make sure I only get the last 2 digits of the year.

              • 4. Re: Serial Number based on date

                This is not the best option for implementing a primary key in FileMaker. Primary keys should not have any intrinsic "meaning". That requires exposing them on layouts where users may later want to change the values or format and then that messes up the references to related tables. In multi-user systems where you have more than one person creating these records, it can also be difficult to ensure that no duplicate values are possible when two users create a new record at the same instant in time.

                It's much simpler and safer to use a standard Serial Number field for the primary key and then use such a numbering system as this only as a "label" field defined in the main table. You can then use it to search and sort your data, but you are no longer relying on it to correctly link to related records in other tables.

                • 5. Re: Serial Number based on date

                  I agree with Phil on this.
                  Do not use this as your way of uniquely linking your tables together for the purposes of relating parent records to child records - aka PK (Primary Key) to FK (Foreign Key) in your tables. You should only use it for asthetic reasons for your clients such as searching etc. It is a hard lesson to learn that you should not expose your PK/FK to the users. It should generally always be behind the scenes.

                  It is true that this approach could have issues in a multi-user environment as it is currently represented.  However there is a way to fix the multi-user issue with a bit more elbow grease. To accomplish this, you wil need to create a new child table that will contain unique date records where each date record will then keep track of the last SN used.

                  The trick to making this work is by activating the script trigger On Record Commit located on the layout setup for table MyTable which will run a script that will check the current number being used during creation against the child table, and by using a loop to check for open record errors, and then update the current SN for the date record in this new table. Now, lets get to how it's done:

                  Create a new table called "MyDates".
                  Create 2 fields in the table MyDates:
                  theDate [Validation rule: Unique]

                  Go to the RG (relational graph) and duplicate the MyDates TO (Table occurrence) and rename the duplicate "MyTable 2 MyDates"
                  Link "MyTable" to "MyTable 2 MyDates" as follows:
                  MyTable::theDate = MyTable 2 MyDates::theDate

                  Make sure you have a layout that is linked to the original TO (Table occurrence) "MyDates"
                  Now create a script that will be used as the script trigger and call it "Update MyDates_SNbr" and give it the following script steps:

                  #--------- Prevents dialog errors from showing - we will handle errors via the get(lasterror) function.
                  Set Error Capture [ON]
                  #--------- grab the Date and the SN from the new record prior to committing the record.
                  set Variable [$theDate ; MyTable::theDate]
                  setVariable [$theSNbr ; MyTable::theSNbr]
                  #--------- Determine if another user has already used the same SN for the given date
                  Freeze Window
                  #--------- create a temporary window with coordinates that will force the window to be off-screen to keep the user from seeing the temporary window appear and disappear.
                  New Window [Top:-5000;Left-5000]
                  Go To Layout[MyDate]
                  Enter Find Mode[uncheck the pause option]
                  Set Field[MyDates::theDate ; $theDate]
                  Perform Find
                  if(get(foundcount) = 0
                            #----------- No Record Yet Exists
                            New Record/Request
                            set field[MyDates::theDate ; $theDate]
                           #----------- Attempt to commit unique date record
                            Commit Records/Requests[Perform Without Dialog]
                       if[get(lasterror) > 0]
                            #---------- Another user has managed to create a primary date record before you could commit yours, so revert the record creation.
                            Revert Record/Request[Perform without dialog]
                            #---------- Find the record that was created by another user.
                            Enter Find Mode[uncheck the pause option]
                            Set Field[MyDates::theDate ; $theDate]
                            Perform Find
                       end if
                  end if
                  #-------- Attempt to open/reserve the current date record for making modifications
                       set variable[$counter ; $counter + 1]
                       Open Record/Request
                       #----- Reserve Record successful, exit loop
                       Exit Loop If[get(lasterror) = 0
                       #----- Reserve Record failed, wait and try again
                       Pause[1 second]
                       #----- exit loop with error if more than 5 seconds have passed.
                       Exit Loop If[$counter > 5]
                  End Loop
                  #-------- Deal with the record lock error if it occurred
                  if[$counter > 5
                       #----- Close the temporary window
                       Close Window [current window]
                       #----- Revert the current record the user just created to prevent duplicates from occurring
                       Revert Record/Request [perform without dialog]
                       Show Custom Dialog [Alert ; An error occurred while attempting to create the record. The primary date record is locked.]
                       Halt Script
                  end if
                  #-------- No Error Occurred, Update the unique date record's SN
                  if[MyDates::theSNbr ≥ $theSNbr]
                       set field[MyDates::theSNbr ; MyDates::theSNbr + 1]
                       set Variable[$theSNbr ; MyDates::theSNbr ]
                       #----- Release the record so other users can update it after you are done.
                       Commit Records/Requests [Perform without Dialog]
                       Close Window [current window]
                       #--------- Update the users record with the corrected $theSNbr
                       set field[MyTable theSNbr ; $theSNbr]
                       #--------- Nobody beat us to the punch! Update the reserved date record with our SN
                       set field[MyDates::theSNbr ; $theSNbr]
                       #----- Release the record so other users can update it after you are done.
                       Commit Records/Requests[Perform without dialog]
                       Close Window [current window]
                  end if

                  Save the script and close it. Now you will just need to activate and link the script trigger [On RecordCommit] under Layout Setup.  Link the script trigger to the script 'Update MyDate_SNbr".   Use this script trigger only on the layout(s) where the record creation may occur for the table occurrence "MyTable".

                  • 6. Re: Serial Number based on date

                    Some of the reasons for hiding PK's from the users are more a case of "human engineering" than software engineering. If your users see that number exposed on your layout, you then may find yourself fielding requests to change its format or content. (add leading zeroes, include the year, Add a letter to identify trial materials so we can tell them from approved materials...) If it's a PK field, you have to deal with the consequences this change makes for tables of related data. If this is just a "search and sort" data field, you can just smile, make the requested changes and all your data stays linked without any additional work with the related tables required...

                    • 7. Re: Serial Number based on date

                      Thanks guys you are awesome.

                      I understand the issue with the serial key so I will use that instead to link the databases but still have the Reference Number created as above.

                      If I do this however, will the above method still generate a different number? It will be imperative that the Reference Number is different as this field would be used for billing purposes.

                      Can I still use the Reference Number as a relational field?

                      • 8. Re: Serial Number based on date

                        I would say that if you combine the first method with the script above to deal with multiple users, then I would say that yes, you could use it in some circumstances, but I think it would be a good idea to tell us how you will be using it to see if you can avoid using it as a relational link.  Since the idea is to keep each record unique by appending the 0001, 0002, etc there is very little or no difference between that and a standard serial number field, in which case I would use the serial number field to do the linking and use your RF field to do searches in, etc.

                        • 9. Re: Serial Number based on date

                          Brian this worked exactly how I wanted it, thanks.

                          I guess I'd better explain the reason for the reference number being the way it is and why I want it as the primary key.

                          The idea is to create a call logging system that allows engineers to update against the call and for admin to bill according to those updates.

                          The idea is that the Reference Number means something. So an engineer can look at the number and instantly know that the job is the first job on the 27/03/2011 for example or the 345 job on the 29/10/2022 because the job would look like this RF1102270001 or RF2210290354 respectively.

                          The problem I have with using a straight serial number on the job is that sooner or later it becomes really quite unwieldy. At least with the Reference Number as above you can narrow the search down easier. If the customer wants to check the progress of their call they can give the first six digits which the admin staff can bring up using a calendar field which will narrow the search down a lot quicker. As soon as you start searching for straight numbers it becomes incredibly unwieldy and prone to mistakes.

                          I wanted the Reference Number as the Primary Key because I figured it would be easier to keep everything together and locked into the proper job number with less risk.

                          I'm still getting to grips with relational databases but it's so cool to know there is wonderful help from such as you and Phil.

                          One thing I want to know though,

                          MyTable::theDate  =  MyTable 2 MyTable_by_Date::theDate

                          Does this link back to itself? It doesn't seem to create a new table. Is there a reason for this?

                          • 10. Re: Serial Number based on date

                            Regarding: MyTable::theDate  =  MyTable 2 MyTable_by_Date::theDate

                            Yes this is a very important part of how it works.  I know it seems strange having a table link to itself, but it is the only way to make sure that this solution works as intended.  This is what I often refer to as a utility relationship. It only exists to make something within the solution work, like a car part in an engine.  This relationship is what allows MyTable to look inside itself (using the date as the matching key) to see how many records contain a matching date in order to be able to count them and then use that result to create the unique serial number that you need - in an automated fashion.  This can be seen in the calcualtion field "theSNbr":

                            thSNbr = Right("0000" & (Count(MyTable 2 MyTable_by_Date::theDate) + 1) ; 4)

                            The hardest thing to keep a handle on in a relational database is the concept of multiple table occurrences (TO's) in the relational graph (RG).  You could have 100 TOs in the RG for MyTable and they would all have different names and exist for different reasons.  The only thing that would place MyTable in the parental role in a parent to child relationship (also known as Primary Key to Foreign Key) is how you are using it.  Generally, it is the Layout that controls this context, because you must choose what TO the layout is using, which in turn controls how you will look into other tables via your relationships from that specific TO.

                            To make this process a little easier, when you create a new table within a file, FileMaker Pro automatically creates the 1st TO for you in the RG and also creates a layout that links to that TO.

                            I hope this makes sense :)

                            • 11. Re: Serial Number based on date

                              It does, thank you very much.

                              I had a play and removed the link to itself and it did break. Readding the link made it work again.

                              Once again thank you so much for your help. With this I've managed to progress a little more with my call system. Now all I need to do is add the job update table, the inventory table, a way to invoice (I'm picking that's a report), and a way to look at engineer utilisation and link it all together. Pretty simple really. :)