1 2 Previous Next 18 Replies Latest reply on Nov 20, 2012 10:56 AM by BryanN

    Custom Serialization

    BryanN

      Title

      Custom Serialization & Value List Sorting

      Post

      So I'm trying to find a better way to do this but I'm coming up short.  For the life of this business, they have come up with Job Numbers based on the 2 digit year, month, then an up counting serial from 001, an example for a new job opening today would be:

       

      12-09-012 (year-month-serial)

       

      In my db, I don't use this is my PK, I have another system for that... however, I would love that when they create new jobs, it would automatically be able to assign the number to the job so I don't have to worry about job number duplication.  Any way I can do this?

       

      On a similar note, I have imported all of our jobs from 1995 on.  Problem is this, if I want to see the jobs from most recent to least recent, doing a decending sort by job number will result in jobs from 1999 down to 1995 (when the business started) coming before ones from 2012:

       

      99-01-001  comes before

      12-01-001

       

      I tried creating a value list that could be used in the sort but for some reason, the best it will do is count up from 2000 on (00-01-xxx)

       

      Any ideas on how I can switch this?

        • 1. Re: Custom Serialization & Value List Sorting
          philmodjunk

          For Sorting, use a calculation field that extracts the first two digits and computes the 4 digit year. Then sort on that field. Here's a possible expression for that:

          Let ( [ yy = getasNumber ( Left ( JobNumberField ; 2 ) ) ;
                    ss = Right ( JobNumberField ; 3 ) ;
                    mm = middle ( JobNumberField ; 4 ; 2 ) ];
                    ( If ( yy > 50 ; 1900 ; 2000 ) + yy ) & "." & mm & ss
                )

          Select Number as your return type.

          For your example values

          12-09-012 produces 1212.09012
          99-01-001 produces 1999.01001
          12-01-001 produces 1212.01001

          This should sort correctly by year, month, serial number

          For your auto-generating the JobNumber in the first place, the first problem is that if you have two or more users generating JobNumbers at the same time, you have a significant chance of getting duplicates. The first thing to do is to specify Unique Values as a validation rule for this field.

          This way, you at least get an error message and the user can try again if the job number they get duplicates an existing value.

          To auto-enter the correct value, I'd use one field for the Serial Number and a Date field that auto-enters the creation date to supply the needed values:

          aCreationDate, SerialNumber

          I'd then define cMonth as: aCreationDate - Day ( aCreationDate ) + 1

          so I could set up this self join relationship:

          YourTable::cMonth = YourTable 2::cMonth

          Then you can use Max ( YourTable 2::SerialNumber ) + 1 to assign a new serial number value to SerialNumber and you can then auto-enter:

          Right ( Year ( aCreationDate ) ; 2 ) & "-" & Right ( "0" & Month ( aCreationDate ) ; 2 ) & "-" & Right ( "00" & SerialNumber ; 3 )

          • 2. Re: Custom Serialization & Value List Sorting
            BryanN

            For Sorting, use a calculation field that extracts the first two digits and computes the 4 digit year. Then sort on that field. Here's a possible expression for that:

            Let ( [ yy = getasNumber ( Left ( JobNumberField ; 2 ) ) ;
                      ss = Right ( JobNumberField ; 3 ) ;
                      mm = middle ( JobNumberField ; 4 ; 2 ) ];
                      ( If ( yy > 50 ; 1900 ; 2000 ) + yy ) & "." & mm & ss
                  )

            Select Number as your return type.

            For your example values

            12-09-012 produces 1212.09012
            99-01-001 produces 1999.01001
            12-01-001 produces 1212.01001

            This should sort correctly by year, month, serial number

            That makes sense, I'll see what I can do with that.

             

            For your auto-generating the JobNumber in the first place, the first problem is that if you have two or more users generating JobNumbers at the same time, you have a significant chance of getting duplicates. The first thing to do is to specify Unique Values as a validation rule for this field.

            This way, you at least get an error message and the user can try again if the job number they get duplicates an existing value.

             

            Already got ya on that one, I validate by total number of digits and unique values.  However, it doesn't prevent them from writing 12-01-001 as 1201001 and having it still record and pass validation..

             

            To auto-enter the correct value, I'd use one field for the Serial Number and a Date field that auto-enters the creation date to supply the needed values:

            aCreationDate, SerialNumber

            I'd then define cMonth as: aCreationDate - Day ( aCreationDate ) + 1

            so I could set up this self join relationship:

            YourTable::cMonth = YourTable 2::cMonth

            Then you can use Max ( YourTable 2::SerialNumber ) + 1 to assign a new serial number value to SerialNumber and you can then auto-enter:

            Right ( Year ( aCreationDate ) ; 2 ) & "-" & Right ( "0" & Month ( aCreationDate ) ; 2 ) & "-" & Right ( "00" & SerialNumber ; 3 )

             

            With that, is there a way to have the serial portion reset every month?  Typically, they would do 12-09-001 but then when October hits, it's 12-10-001.

            • 3. Re: Custom Serialization & Value List Sorting
              philmodjunk

              The self join relationship is what "resets" the serial number each month. When you create the first new record of the month, Max ( YourTable 2::SerialNumber ) returns 0 and then you add one to it to get 001 for the first new record of the month.

              • 4. Re: Custom Serialization & Value List Sorting
                BryanN

                The self join relationship is what "resets" the serial number each month. When you create the first new record of the month, Max ( YourTable 2::SerialNumber ) returns 0 and then you add one to it to get 001 for the first new record of the month.

                 

                Got ya.  I'm gonna have to read that over a few times on some ritalin to make sure I got it but I'm gonna include that on my first big update to our CRM.  Thanks Phil.

                • 5. Re: Custom Serialization & Value List Sorting
                  BryanN

                       So I'm having a little trouble getting this setup right.  Here's what I'm getting from or suggestion for it so far:

                        

                         
                  •           Create 3 Fields in my "jobs" table:
                         
                  1.           c.Month: which is a calculation field that pulls the month that the record is created in.
                  2.      
                  3.           SerialNumber: which is number field that has an auto entered value starting at 001
                  4.      
                  5.           Creation Date: which is a date field that has an auto entered value based on the date record is created

                        

                         
                  •           Next, I make a second occurance of my table and make cMonth=cMonth as my criteria, allowing record create on both sides of the relationship.

                        

                         
                  •           Last, make a 4th field in the "jobs" table called Auto Generated Job Number which is a calculation (text result) field that takes the calculations from the previous fields and puts them together in a string that will resember our format "twodigityear-twodigitmonth-resettingserialnumberforthemonth" AKA 12-11-034

                        

                       Here's the issues I'm finding:

                         
                  1.           C.Month  -  shouldn't this calculation just be pulling Month ( Creation Date ) instead of what you wrote: "aCreationDate - Day ( aCreationDate ) + 1"?  I'm having a hard time understanding how the creation date (which will be a full time stamp minus the day numer of the month plus 1 will help this at all?
                  2.      
                  3.           Serial Number - Should this just be auto entered on creation starting at "001" with an increment of "1"?  Will it preserve the formatting of "001" or just shorten it to "1"
                  4.      
                  5.           Creation Date - I already have this field created and running fine, just auto-entering on creation the full timestamp.  Do I need to create a new one with just the date?
                  6.      
                  7.           For any of these fields, do I need to make them a text, number, date or calculation field?
                  • 6. Re: Custom Serialization & Value List Sorting
                    philmodjunk

                         1) It doesn't pull the month. It calculates the date of the first day of the same month.
                         3) Make sure that this is a field of type date.

                         That 4th field will not update correctly unless you specify two details:

                         Clear the "do not evaluate if all referenced fields are empty" checkbox.

                         Clear the "do not replace existing values checkbox.

                         Your questions:

                         1) As I already stated, this computes the date of the first day of the month--this way you match by month AND year from the value in this one field.
                         2) This should NOT be an auto-entered serial number setting. It is an auto-entered calculation that uses Max ( YourTable 2::SerialNumber ) + 1 to calculate the next value in the series.
                         3) You don't need two fields with the same data. Use the one you already have.
                         4)

                         The creation date field should be of type date.
                         cMonth should be of type calculation with date as the return type. (Though a date field, with this expression as an auto-entered calculation should also work, come to think of it.)
                         Your field that combines all this into a single value should be a calculation field that returns text.
                          

                    • 7. Re: Custom Serialization & Value List Sorting
                      BryanN

                            

                           1) It doesn't pull the month. It calculates the date of the first day of the same month. Got ya
                           3) Make sure that this is a field of type date. Done

                           That 4th field will not update correctly unless you specify two details:

                           Clear the "do not evaluate if all referenced fields are empty" checkbox. Done

                           Clear the "do not replace existing values checkbox. Done

                           Your questions:

                           1) As I already stated, this computes the date of the first day of the month--this way you match by month AND year from the value in this one field. I see.  I'm trying to understand how this one works, as I know it's used as the match field.  So we are taking today as an example:  Created date (11/12/2012) - Day (Created Date [12 in this case]) which would equal 11/00/2012 + 1 which would equal 11/01/2012?  If the value is always created like this, then both instances of the table in the realtionship would always be equal wouldn't they?  Sorry if I'm a little dumb, I'm struggling to understand the concept.  cMonth from instance 1 would always equal cMonth from instance 2 based on how I'm looking at it?
                           2) This should NOT be an auto-entered serial number setting. It is an auto-entered calculation that uses Max ( YourTable 2::SerialNumber ) + 1 to calculate the next value in the series. So it's referring to itself, in the second occurance?
                           3) You don't need two fields with the same data. Use the one you already have. Noted
                           4)

                           The creation date field should be of type date. Got it
                           cMonth should be of type calculation with date as the return type. (Though a date field, with this expression as an auto-entered calculation should also work, come to think of it.) Got it
                           Your field that combines all this into a single value should be a calculation field that returns text. Got that.

                           With the items in place so far, When I create a new record, right now it gives me "12-11-00"

                            

                           Obviously something is off :)  Thanks again for your help!

                      • 8. Re: Custom Serialization & Value List Sorting
                        philmodjunk

                             1) take a look at what happens when you feed in other November 2012 dates into this calculation. They will all return 11/01/2012, producing a common value for all records dated for the same month and year.

                             What version of FileMaker are you using? I have a demo file around here somewhere. I'll share it, but in the version you are using so that you don't have any file conversion issues.

                        • 9. Re: Custom Serialization & Value List Sorting
                          BryanN

                                

                               

                                    1) take a look at what happens when you feed in other November 2012 dates into this calculation. They will all return 11/01/2012, producing a common value for all records dated for the same month and year.

                               

                                    What version of FileMaker are you using? I have a demo file around here somewhere. I'll share it, but in the version you are using so that you don't have any file conversion issues.

                                

                               Yeah, it's not returning the correct date.  It's still just returning today's date instead of Nov1.  I'm using the latest FMP12v3.

                          • 10. Re: Custom Serialization & Value List Sorting
                            BryanN

                                 Looks like the calculation for c.Month doesn't like the full timestamp, it calculates fine though when I use just the date so I had to create a date field.  Also, the serial calculation isn't working.  Here's a screenshot of what I'm seeing (the last field is my manually entered job# (just so I can have some sort of reference to know what I'm lookign at)

                                  

                            • 11. Re: Custom Serialization & Value List Sorting
                              philmodjunk

                                   The version of FileMaker has nothing to do with the results of this calculation.

                                   Your field, however, is not a date field, it's a time stamp field. Timestamps store date and time in the form of a single number representing the seconds since 12/31/000. Dates store the date in days since the same date.

                                   Modify your calculation to be:

                                   Let ( D = GetAsDate ( Created_Date ) ; D - Day ( D ) + 1 )

                              • 12. Re: Custom Serialization & Value List Sorting
                                BryanN

                                      

                                     

                                          The version of FileMaker has nothing to do with the results of this calculation.

                                     

                                          Your field, however, is not a date field, it's a time stamp field. Timestamps store date and time in the form of a single number representing the seconds since 12/31/000. Dates store the date in days since the same date.

                                     

                                          Modify your calculation to be:

                                     

                                          Let ( D = GetAsDate ( Created_Date ) ; D - Day ( D ) + 1 )

                                     Yeah I kind of figured that out so I just went ahead and created a c.Date field to auto enter the date.  Now, for every record, cMonth comes up as the first day of the month that the record was created.  Good.  Now, I've gotten the serial field to work so far (it's counting up like it should).  However, the autoenteredWO field is only inputting 12-11-001 for every new record instead of using the c.Serial field. Here's what I have in the autoenteredWO field (it's a calculation field with text as the result, always evaluating even if fields are empty):

                                      

                                     Right ( Year ( c.Date ) ; 2 ) & "-" & Right ( "0" & Month ( c.Date ) ; 2 ) & "-" & Right ("00"& c.Serial_Number ; 3 )

                                      

                                     I noticed something, looking at table:cSerial as in my screenshot, it shoes the values counting up as they should.  However, in table2:cSerial, they are all staying at 1.  Even though the formula is calling up the values from the original table occurance, it looks like it's pulling values from table2?

                                • 13. Re: Custom Serialization & Value List Sorting
                                  philmodjunk

                                       Your expression refers to c.Date, but your screen shot shows c.Month. So I can't see what value is currently in date.

                                       What is the data type for AuotEnteredWO and why is it from a related table in your screen shot? The data type should be text.

                                       PS. I strongly recommend that you NOT use this field as the primary key in relationships. Stick to a simple auto-entered, never modified serial number field for that purpose and just keep this field to stick on layouts and to use in searches and sorts.

                                  • 14. Re: Custom Serialization & Value List Sorting
                                    BryanN

                                          

                                         

                                              Your expression refers to c.Date, but your screen shot shows c.Month. So I can't see what value is currently in date.

                                         

                                              What is the data type for AuotEnteredWO and why is it from a related table in your screen shot? The data type should be text.

                                         

                                              PS. I strongly recommend that you NOT use this field as the primary key in relationships. Stick to a simple auto-entered, never modified serial number field for that purpose and just keep this field to stick on layouts and to use in searches and sorts.

                                         Sorry bout that, as I went to take a screen shot with c.Date, I noticed it started working all of a sudden?

                                         Data type for AutoEnteredWO is text, calculation field, as soon as I fixed the table view column to be from the original table occurance, it started showing up just fine!

                                         Yeah, I'm not using it as a PK, just the identifier for our guys so I don't have to change the way we do business.

                                         On another note, now that we have it working, to implement, I'll need to copy all of my settings and formulas into my existing WONumber field. Given that all of our previous jobs already have values in the field, will changing it over from a text field to a calculation/text field erase all of our previous values?

                                         The only reason I'm wanting to re-use the WONumber field is because I already have a lot of formulas that use it.

                                    1 2 Previous Next