5 Replies Latest reply on Jan 5, 2012 5:49 PM by Mike_Mitchell

    Can I do a Search and Replace in a number data field?

    MikeS

      I have a field that is unique and it contains: five digits, a hyphen and two digits. 12345-12. The last two digits represent the year. The first five are the membership number. I want to to remove the hypen and leave the 5 digits in the field and put the other two in a separate field. Any way of doing this besides doing all 5,000+ fields manually? I want to make the 5 digit field auto creat a new number on entry and then add the current year on the end. I can do that part, I don't know how to undo the original set up.

        • 1. Re: Can I do a Search and Replace in a number data field?
          Mike_Mitchell

          Hi, mstenberg (I'd address you by name, but you didn't provide one).

           

          You can do this in two steps. Say your initial field is Field1. Your new field (where you want the last two digits) is Field2. We're going to use the Replace Field Contents function (from the Records menu).

           

          1) FIRST STEP: Make a backup of your database. The Replace function is FileMaker's nuclear weapon. If you screw up, you can't Undo.       

           

          2) Find all records in the relevant table (Ctrl-J).

           

           

          3) Place your cursor in the new field, Field2. Choose Replace Field Contents and then choose Replace with Calculated Result. You'll get the calculation dialog. Enter this calculation:

           

          Right ( Field1 ; 2 )

           

          This will take the rightmost 2 characters from Field1 and place them in Field2. It does not alter the contents of Field1.

           

          4) Place the cursor in the old field, Field1. Choose Replace Field Contents again, choose Replace with Calculated Result. Enter this calculation:

           

          Left ( Field1 ; 5 )

           

          This will take the leftmost 5 characters from Field1 and place them in ... Field1. In other words, you "replace" the contents of Field1 with the first 5 characters of Field1.

           

          Let me know if this doesn't make sense.

           

          Mike

          • 2. Re: Can I do a Search and Replace in a number data field?
            MikeS

            Wow, I can't believe I got a response so quick! I spend more time trying to do this than it took to get the solution. And yes that makes sense. I did not know what formula to use from the calculations list. It's mind boggling when you see it all. I tried many formulas and the Search and Replace function to no avail.

            Thank you so much.

             

            Mike Stenberg

            Value Added Services Inc

            vasinc@comcast.net

            www.vasgraphics.com

             

            Cell:  612-597-2900

            • 3. Re: Can I do a Search and Replace in a number data field?
              chris.schmitz

              Good answer Mike.

               

              Also, mstenberg, if you're doing this to new records on a regular basis it might be a good idea to create a field called MembershipCode where the code is entered and then take Mike's calculations and apply them to the membership number and membership year fields as auto enter claculations like:

               

              MembershipCode (TextField)

              MembershipNumber (auto enter calculation = Left (MembershipCode; 5)

              MembershipYear (auto enter calculation = Right (MembershipCode; 2)

               

              That way you don't have to do a manual replace field contents from time to time, it will just do it automatically when you enter the code into the code field.

               

              If you're only needing to do this once then  Mike's post is a good solution.

              • 4. Re: Can I do a Search and Replace in a number data field?
                MikeS

                Mike Mitchell,

                 

                Thank you so much!! In just a few minutes I completed the task and only had a few records with errors. You saved me about 15+ hours of work. I would have had to do each one manually. I've worked with FMP for several years but do not know some of the details like this. I am now going through the FMP11 training series. I have done a lot with my database and still have sooooo much to learn.

                 

                Again, thank you for the quick response and the easy method.

                 

                Mike Stenberg

                Value Added Services Inc

                vasinc@comcast.net

                www.vasgraphics.com

                 

                Cell:  612-597-2900