7 Replies Latest reply on Apr 3, 2013 8:33 AM by RonaldCarr

    Find / Replace all but certain values

    RonaldCarr

      Title

      Find / Replace all but certain values

      Post

           I have a field named Availability which I want to contain only FT or PT or ANY.

           If the value in this field is anything else, I want to move the value to a field AvailabilityComments and Null value the field Availability.

           I have been trying to run Omit queries to restrict the values to non-acceptable values per above, but can't get Omit to work on constrained view: i.e., if I omit "PT" then Constrain and try to omit "FT" I end up with nothing.

            

           My assumption was that if I ended up with a constrained view of non-acceptable values I could run a script to move values and Null Availability.

           Maybe i just have the wrong approach?

           Thanks!

            

           Ron

        • 1. Re: Find / Replace all but certain values
          JimMac

               I would do an exact match of  each of the 3 items in an OR logic search.  Then do an Omit to find all the non-acceptable values.

               Jim...

          • 2. Re: Find / Replace all but certain values
            RonaldCarr

                 Well, I would love to do an OR search.

                 If I search for PT or FT I get only the "PT or FT" entries.

                 If I serach for "PT" or "FT" I get the same thing, which makes no sense at all

                 If I search for PT and OMIT, I cannot then Constrain the Found set to search for FT and Omit.

                 What should an OR logic search on a single field look like?

                 Surprised that Help doesn't show this...

                 Ron

            • 3. Re: Find / Replace all but certain values
              JimMac

                   It is kinda weird, but you need 3 different Find requests BEFORE preforming the find.

                   Enter Find

                   in the Availability Field enter first Criteria of exact match  as  "==FT"

                   Then Add New Requestsurprise

                   in the Availability Field enter second Criteria of exact match  as  "==PT"

                   Then Add New Requestsurprise

                   in the Availability Field enter third Criteria of exact match  as  "==ANY"

                   Thus 3 separate requests  meaning FT OR PT OR ANYyes

                   Now perform Find with the 3 requests.  Then do an OMIT.wink

                   Jim...

              • 4. Re: Find / Replace all but certain values
                JimMac

                     Don't feel badly, it took me about 4 days to figure it out using FMP6.  It was 2 sentences almost hidden in the User Guide.  But for future help...

                http://help.filemaker.com/app/answers/detail/a_id/5210/kw/portal%20search

                     This was under Multiple Requests.

                     Jim...

                • 5. Re: Find / Replace all but certain values
                  RonaldCarr

                       Yes linked page explained much better!

                       So it did work, omit worked.

                       Now I would like to change all remaining records, field Availability, to Null or empty.

                       1. Find/Replace won't work as there are a lot of values that should be erased.

                       Idea?

                       Ron

                  • 6. Re: Find / Replace all but certain values
                    JimMac

                         If you are manually doing this and not scripting....

                         Once you have isolated the  Faulty set of records... Reading your first post....

                         1) Put in focos or select the AvailabilityComments  field... Under the Records menu.. Select Replace Field Contents...

                         2) To move  the data.. select Replace with calculated result:   and click Specify..

                         3) then put is this calculation  Availability which will move the data for you.  Read the top sentence on the Repalce Field Contents dialog to verify.

                         4) Click Replace  which will move the faulty data to the new field.

                         5) Next put in focus the Availability field and repeat but this time the Calcualtions shoud be Null or just ""

                         6) Done

                         Of course back up your file before doing this to confirm this procedure.

                         Jim...

                    • 7. Re: Find / Replace all but certain values
                      RonaldCarr

                           Thanks Jim.

                           That Records option is so often grayed out, when you don't have a field selected, that I missed it.

                           Ron