10 Replies Latest reply on Jan 6, 2014 11:19 AM by thong127

    Remove the Dash in result

    thong127

      Title

      Remove the Dash in result

      Post

           Happy new year to all!
            
           Can you please help me how to remove the dash in the result of the below function that I copied from the other forum
            
           Please find link of the file.
            
            
           Thank you.
            
           thong
            
            
           /* 
           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 ) 
           ) 
           )

        • 1. Re: Remove the Dash in result
          philmodjunk

               The dash you are referring to would be the dash between Year and Month

               year & "-" & month
                
               If you want to remove it so that the values of year and moth run together:
               year  & month
                
               IF you want to use a different character in place of the dash:
               year & "/" & month
                
               Put whatever character you want in place of the / in the above example.
          • 2. Re: Remove the Dash in result
            thong127

                 Hi Phil,

                 I followed your suggestion, I remove the two quotation ( " "), the dash (-) and (&) symbol because I want them to run together, but it didn't  work. can you please suggest a different way to do it. Thank you.

            • 3. Re: Remove the Dash in result
              philmodjunk

                   When you did that what result did you get? "didn't work" doesn't give me much to go on...

              • 4. Re: Remove the Dash in result
                thong127

                     Hi Phil,

                     I deleted all the records, then removed the quotation, the dash and the one & symbol from the function. if i will create a new record it gives me 1401001, if I will create again a new record  it will give me same 1401001. if you can download and play with the file. I will appreciate it. thank you so much.

                      

                • 5. Re: Remove the Dash in result
                  philmodjunk

                       I will decline that offer.

                       It would seem that the change does remove the dash, however. But the results suggest that the rest of the calculation now fails to return the expected result.

                  • 6. Re: Remove the Dash in result
                    thong127

                         Thanks Phil.

                         for your suggestions. i will try again to play. hoping some members will also help me to get the result what I want.

                          

                    • 7. Re: Remove the Dash in result
                      philmodjunk

                           You might want to explain what this part of the expression is supposed to do:

                           Case( 
                           month   ≠   lastMonth  or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit  ; digit-1 ) & "1"; 
                           SerialIncrement ( Right ( lastId ; digit ) ; 1 )
                      • 8. Re: Remove the Dash in result
                        thong127

                             Hi Phil,

                             The no. of digit that will increment by 1 every beginning of each month. yymm###(1401001,1401002,1401003....) thank you.

                        • 9. Re: Remove the Dash in result
                          philmodjunk

                               Please do not use this value as a primary key to link a record in this table to records in other tables. A simple auto-entered serial number is much safer to use for that purpose.

                               Since the calculation uses GetNthRecord, it will only compute correctly if all records are always in your found set at the time that the record is created or that the value is assigned (if you use a script).

                               I use a different method for getting the incremental part of this number series:

                               Define a self join relationship:

                               YourTable::Year = YourTable|SameYear::Year

                               This requires that you have a Year field that auto-enters: Year ( Get ( CurrentDate ) )

                               And another field, Sequence, is then defined to auto-enter or a script assigns:

                               Max ( YourTable|SameYear::Sequence ) + 1

                               Then this calculation becomes:

                               Let ( [ T = Get ( CurrentDate ) ;
                                          Y = Right ( Year ( T ; 2 ) ;
                                          M = Right ( "0" & Month ( T ) ; 2 )
                                       ];
                                          T & M & Right ( "00" & Sequence ; 4 )
                                      )

                               That calculation seems a bit simpler. You should also set this up as an auto-enter calculation with a unique values validation to make sure that two users creating records at the same time don't accidentally produce a record where this field has a duplicate value.

                          • 10. Re: Remove the Dash in result
                            thong127

                                 Thanks Phil,

                                 I got it.