6 Replies Latest reply on Jul 2, 2017 1:29 AM by ChrisJohnston





      We have a code that is a text field


      it's a three digit number - three letter month followed by two digit year





      we need a script to be able to add a value of 10 to the first three digits whilst at the same time add 1 to the 16.


      So essentially change 225-Aug16 to 235-Aug17


      Is there an easy way to do this?

        • 1. Re: Script

          Let ( [ T = yourTextFieldHere ;

                     N = right ( "00" & left ( T ; 3 ) + 10 ; 3 ) ;

                    Y = Right ( T ; 2 ) ];

                    N & middle ( T ; 4 ; 4 ) & Y


          • 2. Re: Script

            Apologies for the stupid question, where can I find let in the script options?

            • 3. Re: Script

              in the script you select set field(yourtextfield) and in the calculation you are using what phil said

              • 4. Re: Script

                techlife wrote:

                Where can I find let in the script options?

                You don't; Let() is a function that you use in the result expression, i.e.


                Set Field [ target field: YourTable::yourTextFieldHere ; result calculation: PhilModJunk's calculation ]

                • 5. Re: Script

                  And if you have a batch of records to update this way, you might use Replace Field Contents.

                  • 6. Re: Script

                    Let can look a little confusing at first... at least for me, it was four years ago when I was new to FileMaker. It was actually @philmodjunk who helped me with it also. Look here... it was my first post in this community But to solve yours what @philmodjunk is saying will work if you want to just add things like you ask. I am sensing that you want more than what you ask. Because you are working with a date concept, you need more than just some math. That is because math for actual dates will not work on your abbreviated string. I am assuming that if you have  225-Aug16 calculate to 235-Aug17 It will be fine. However, if you try that on 245-Aug31 and get 255-Aug32, I don't think that is what you want. I think you would want 255-Sep1 or 255-Sep01. So I played around with this. Back to Let... when it was shown to me it looked complicated, I worked with it, and now I see it is an invaluable tool for creating complex calculations for fields and scripts. I tried to show what user above helped with in a let and tried to describe it how I have learned it thinking maybe it can help you also. But I also included the way that will calculate the date properly.