7 Replies Latest reply on May 4, 2010 11:32 AM by comment_1

    Date Formatting

    Jon1009

      Title

      Date Formatting

      Post

      Hello Iam running Filemaker10 on Vista.

      I need to create a code like GMG01-030510-01 the middle bit is the date which comes from a date created field but all I can get is GMG01-03/05/10-1 how can I get the date without the / ?

       

      Ive setup the date created field to come but as 030510 but when its put though the calulation (Client Code &"-" &   Date &"-01") its has / in it

       

      Ive also tried a calculation with the Day ( date ) Month ( date ) Year ( date ) function but then i get GMG01-352010-01

       

      Any help would be great.

        • 1. Re: Date Formatting
          comment_1

          Try =

           

          Client Code

          &

          SerialIncrement ( "-000000" ; 10^4 * Day ( Datefield ) + 10^2 * Month ( Datefield ) + Mod ( Year ( Datefield ) ; 100 ) )

          &
          "-01"

          • 2. Re: Date Formatting
            Jon1009

            Thank you very much that worked :P

             

            Any chance you can explain whats going on?

            • 3. Re: Date Formatting
              comment_1

              Not much to explain: you take your date elements and create a number like this:

               

              3 * 10000 = 30000
              5 * 100   =   500
              10        =    10
              ------------------
                          30510

              Then you pad this number with zeros using SerialIncrement().


              • 4. Re: Date Formatting
                Jon1009

                Thanks for the reply makes sense.

                 

                Is there away i can check all the Client code field to see if GOG01-030510-01 exists if so its displays GOG01-030510-02 etc?

                • 5. Re: Date Formatting
                  comment_1

                  There are ways - but they are liable to fail in multi-user scenarios. The best way to ensure a unique ID is to use an auto-entered serial number (and not worry about sub-series).

                   

                  See also:

                  http://fmforums.com/forum/showtopic.php?tid/200432/post/313926/#313926

                  • 6. Re: Date Formatting
                    Jon1009

                    It would be very unlikley that we could have multi users adding samples at one time,

                     

                    What I really wanted to do was have it so when a user clicked the client code dropdown which has about 70 (GOG01, SMG01 etc) it would automattiacly add the date then check for any other samples from that client on the same day if so add 1 onto the end but

                    I can see how the serial number would be a easier and more pratical option but we cant change the format.

                     

                    I guess i could just have a portal at the bottom which brings up all the samples that day from the client and then the user adds the last digit to the ID 

                    • 7. Re: Date Formatting
                      comment_1

                       


                      Jon1009 wrote:

                      It would be very unlikley that we could have multi users adding samples at one time,


                       

                      Unlikely is more often than never.  You didn't say what is the purpose of this code, but if it's important to keep it unique, I wouldn't take any chances. Besides, what happens if a record is deleted or reassigned to another customer?

                       

                       

                      Anyway, if you still want to go that way, the "method" is to count the existing records through a relationship matching on customer and date, then add 1 to the result. I presume in version 10 one could improve on this by having a triggered script check for duplicates.