10 Replies Latest reply on Nov 18, 2013 4:27 PM by thong127

    Unique Serial

    filemakernew

      Hello All,

       

      Im trying to edit a custom function for my serial for every month. I tried their suggestion but still i dont get any luck. Im trying to remove the "-" in between the numbers. they told me to remove this line

      "year & "-" & month" and change it to "year & month". but when i tried its not giving me the serial anymore.

       

      The custom function is

       

      /*

      MonthlySerial custom function

       

       

      Author: Daniele Raybaudi

       

       

      Format: MonthlySerial ( AEfield ; digit )

       

       

      Result: text

       

       

      parameters:

      AEfield: text - the text field wich will hold the serial number; it must be setted as auto-enter, always evaluate

      digit: number - the number of digit (#) that must increment by one and restart from one every new month

       

       

      This custom function can be used for:

       

       

      1) Autoenter a serial number that increments monthly

      2) the format of the serial number is essentially: YY-MM###

      3) the serial number may have how many digits (#) you choosed

       

       

      The Auto-Enter field must be setted to always evaluate;

      the custom function isn't recursive, so you can use it as a simple calc;

      the custom function will work only with FMP8 and above.

       

       

       

       

      05/23/2006

       

       

      */

      Let([

      year = Right ( "00" & Year ( Get ( CurrentDate ) ); 2 );

      month = Right ( "00" & Month ( Get ( CurrentDate ) ); 2 );

      lastId =GetNthRecord (AEfield; Get ( TotalRecordCount ) - 1);

      lastMonth = Middle ( lastId; 4 ; 2 )

      ];

      year & "-" & month

      &

      Case(

      month ≠ lastMonth or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit ; digit-1 ) & "1";

      SerialIncrement ( Right ( lastId ; digit ) ; 1 )

      )

      )

       

      Please help me.

       

      Thank you.

       

      Newbee

       

       


        • 1. Re: Unique Serial
          taylorsharpe

          Let ( [

           

          F1 = "SELECT Max ( Serial ) FROM Table_Name" ;

          F2 = ExecuteSQL ( F1 ; Char ( 9) ; ¶ ) ;

          F3 = Get ( CurrentDate ) ;

          F4 = Right ( Year ( F3 ) ; 2 ) & "-" & RIght ( "0" & Month ( F3 ) ; 2 ) ;

          F5 = Right ( Serial ; 3 ) ;

          F6 = Left ( Serial ; 5 ) ;

          F7a = "SELECT Count ( Serial ) FROM Table_Name WHERE Serial LIKE ?" ;

          F7b = ExecuteSQL ( F71 ; Char ( 9 ) ; ¶ ; F4 & "%" ) ;

          F7 = If ( F6 = F4 ; F5 + 1 ; If ( F7b > 0 ; F5 + 1 ; 1 ) ) ;

          F8 = F4 & Right ( "000" & F7 ; 3 )

           

          ] ;

           

          F8

           

          )

          • 2. Re: Unique Serial
            erolst

            You need to change

             

            year & "-" & month

             

            to

             

            year & month

             

            (no quotes), NOT "year & month". year and month are variables that must not be quoted, while the quotes in "-" denote a literal string (a piece of text).

             

            I'm pretty sure "they" suggested the same.

            • 3. Re: Unique Serial
              erolst

              Taylor, what's it with you, ExecuteSQL and those F variables? Been to some seminar lately?

              • 4. Re: Unique Serial
                taylorsharpe

                Been having fun with ExecuteSQL ever since it came out in 12.  It does have limitations like the "Max" call on the first "F1" is a real slow call.  As for the F's.... maybe it reminds me of grades in school... <ooops>.  Maybe I should use A's next time!

                • 5. Re: Unique Serial
                  erolst

                  Yeah, we all love ESQL(), despite its limitations and implementational quirks; but it's only recently that I've seen you use this F… notation.

                  taylorsharpe wrote:

                  Maybe I should use A's next time!

                   

                  Can't get worse than

                   

                  If ( F6 = F4 ; F5 + 1 ; If ( F7b > 0 ; F5 + 1 ; 1 ) ) <shudder>

                   

                  Once you introduce "F7b", pretty soon you'll need a lookup table to keep track of who's who in your calc …

                  • 6. Re: Unique Serial
                    taylorsharpe

                    Yeah, more descriptive variable names would be a lot better.  But I just got in the habit of trying to keep variable names short (2 digits or sometimes 3). 

                     

                    For my paying customers that I document on... I add a "// insert comment" at the end of each line to explain what I am doing.  But for freebee help here, I'm lazy. 

                     

                    e.g.    F3 = F1 + F2  // I am adding two variables together to get their total

                    • 7. Re: Unique Serial
                      DavidJondreau

                      erolst is right about the quotes.

                       

                      I'll add that you should avoid using the names of functions, like Month() and Year() as variable names.

                      • 8. Re: Unique Serial

                        filemakernew wrote:

                         

                        lastId =GetNthRecord (AEfield; Get ( TotalRecordCount ) - 1);

                         


                         

                        Roll-your-own serial numbers will break in multi-user mode. This is not a safe process to use if your relationships depend upon it.

                         

                        ADDED:  Also, serials should be meaningless. 

                        • 9. Re: Unique Serial
                          taylorsharpe

                          I agree with LaRetta about creating your own serial numbers being a problem with multiple users that create records very close to each other.  I often create such fields as you describe for internal reporting purposes, but I make maker serial numbers UUIDs and use them for relationships, etc., and just don't show the users it. 

                          • 10. Re: Unique Serial
                            thong127

                            Hi erolts,

                             

                            I tried already to change to "year & month" no quotes but it didnt give me the correct serial for the next succeding serial.