9 Replies Latest reply on Apr 26, 2010 1:52 PM by jenh

    How to use values from middle() function



      How to use values from middle() function


      Using Filemaker Pro 10


      I need to take out several character from a string for calculations


      Middle (fieldname;3;2;) appears it could do then I need to take the 3rd and 4th character and use in calculations to translate to numbers.   (ie: if the 3rd character = A put 2010 in field year.)


      Is this possible? 



        • 1. Re: How to use values from middle() function

          Middle returns the values that you want replaced from what it seems like.


          It seems like you would want to use the Replace calculation based on what you are stating.

          Replace ( Text; start; numberofcharacters; replacement text )



          Replace (text;3;1;"2010")


          There's also other ways to replace letters.  one of my favorite functions is substitute.

          • 2. Re: How to use values from middle() function

            Ty for the response. 


            I do not want to substitute or replace the field but add new fields.   2 fields.  Year and Month.


            I have a serial number where the 3rd character needs to be mapped to  

            one value of a range (16 fields, 1988-2010) that will translate to a value of a 4 digit new field called year.  


            The 4th character will map/translate to one of 12 values to represent the month. (January - December)


            It seems the middle() function will not produce the results that I need.  


            Further reference or suggestions are greatly appreciated.    





            • 3. Re: How to use values from middle() function

              I might not be understanding the situation still.  Hopefully this might answer it:


              The & will concatenate text together and you can use right and left to keep the whole word.



              right(<text>,2) & "2010" & left(<text>, length(<text>-2))

              • 4. Re: How to use values from middle() function

                thank you again, apologies..


                I currently have a field.   The first 4 are letters.  The 3rd and 4th letter represent year and month.  


                I need to translate the 3rd letter into a new field that is 4 numbers in length and represent year. 

                The year can range from 1988 to 2010.  


                The 4th letter will create a new field that will spell out the month.  

                The 4th letter will be A - N representing January through December. 


                So there may be a field that the first 4 letters are AAAA. 


                For example:

                There needs to be a new field value for the "year" for the 3rd letter A that would equal 1988.

                There needs to be a new field value for the "month" for the 4th letter A  that would equal January.


                I thougth that I could pull out the value of the 3rd and 4th character by using the middle() function.


                middle(Existing_Field_name; 3; 2)    2 = year and month.


                Then translate the 2 into variables $year and $month of which I've been trying to figure out how to do.


                to continue with a field calculaiton for both year and month as following;


                if $year = A,

                  Set variable[$year; Value: 1988]

                   Set field [Table:year:$year]


                if $year = B

                Set variable[$year; Value: 1989]

                   Set field [Table:year:$year]


                etc..  same for field month.. 

                if $month = A,

                  Set variable[$month; Value: January]

                   Set field [Table:month:$month]


                if $month = B

                Set variable[$month; Value: February]

                   Set field [Table:month:$month]


                again any suggestions or references appreciated.  TY







                • 5. Re: How to use values from middle() function

                  I think I understand the situation better.  Thank you for the clarification, I hope I have a better grasp of the situation this time.  :)


                  So the 3rd letter represents a year range from 1988 to 2010 and the 4th letter represents a month.

                  Using Middle may help you in that regards then.


                  Is the year based on the alphabetical increment starting from 1988?  ie a = 1988, b = 1989, c = 1990... etc.

                  If so, I suppose you don't have to worry about it until 2014, but how will it accommodate after 2014?  Depending on this answer, this may break the calculation after 2014.


                  In regards to having the calculation that works until 2014 if this is the case, probably the easier method would be to take the unicode value of the letter and then add it to the year.  ie :

                  calc_Year (calc field) =  1988+Code(Upper(Middle (<field>; 3;1)))-Code("A")

                  [ Upper will force the capitalization for the code in the event that the field has a lowercase a in it ]


                  Another question I have is : How is the 4th letter mapped to the month?  since A ~ N is 14 letters, and there are many months that start with the same letters, (example : January, June, July), I'm not sure what the exact translation for that might be and there may be an easy way to handle it based on the pattern.  without knowing the pattern, you can do something similar to what you stated as a calculation :


                  calc_month =







                  $Month="J"; "January";

                  $Month="F"; "February";


                    $Month="D"; "December"




                  If it's based on the same pattern of A for January, B for February, etc... 

                  You can do this:

                  Choose ( Code ( Upper ( Middle(text;4;1) ) ) - Code ("A"); "January"; "February", ... "December")


                  There's other ways of handling it as well.  These are just examples.

                  • 6. Re: How to use values from middle() function

                    You want a script?


                    ## 1. extract your year and month codes:

                    Set Variable( $yr ; Middle(field;3;1) )

                    Set Variable( $mo ; Right(field;1) )


                    ## 2. set up a little trick:

                    Set Variable( $index ; "ABCDEFGHIJK..." )

                    Set Variable( $yr_index ; Position( $index ; $yr ; 1 ; 1 ) )

                    Set Variable( $mo_index ; Position( $index ; $mo ; 1 ; 1 ) )


                    ## 3. calculate the corresponding values:

                    Set Field( Year ;1987 + $yr_index )

                    Set Field( Month ; MonthName( Date( $mo_index ; 1 ; 2010 ) )


                    • 7. Re: How to use values from middle() function

                      TY..  very helpful!   As far as the first question; We actually do not have be concerned with the year after 2008 for now.  For time being I intend to just calculate to 2008 (z). 


                      As for the month I'd like your suggestion modified.. since it it based on the pattern of A=January, B=February, C=March.. etc...


                      I would think below may sufficient for both the year and month fields.  


                      calc_month =







                      $Month="A"; "January";

                      $Month="B"; "February";


                        $Month="N"; "December"





                      Thanks for suggestions.. very helpful.   The $Month=Upper(Middle(text;4;1)) just may be the missing piece.  Now I know how to transfer the value to a variable.  I appreciat your time and effort. 

                      • 8. Re: How to use values from middle() function

                        Glad to be of service and thank you for the feedback on your approach.  :)

                        • 9. Re: How to use values from middle() function

                          Thank you! 


                          I'm going give this script a go too!   How efficient!