6 Replies Latest reply on Dec 30, 2010 2:02 PM by njem

    Find blank in particular field across records and replace

    njem

      Title

      Find blank in particular field across records and replace

      Post

      I have a bunch of data massaging to do. How would I change all the blank "status" fields across a bunch of records and put "unknown" or something in? Don't want to change all the status fields, just the blank ones. I read where Find/Replace could be used for most things but it doesn't seem to find blank and doesn't seem to like formulas (=). Is Find/Replace really the only way FM has to do this kind of thing?

        • 1. Re: Find blank in particular field across records and replace
          RickWhitelaw

          You could do a find with " " in the field and then Replace Field Contents with "unknown" but I really don't see the point. Or you could create a calculation that evaluates whether the field (Self) is empty and carry on.

          RW

          • 2. Re: Find blank in particular field across records and replace
            Rajkumar

            At this step, one possible solution is to write a script, which'll check for each field, for the mentioned layouts, and if the field is empty, then it can be replaced with any assigned string(say "unknown"). and call this script whenever required.

            • 3. Re: Find blank in particular field across records and replace
              philmodjunk
              1. Make a back up copy of your file just in case you make a mistake
              2. Enter find mode.
              3. Put = in the status field
              4. Perform the find
              5. Use Replace Field Contents to update the field with the value, such as "Unknown" that you want to put in the blank field.
              6. If there is more than one field in your record where you need to do this, repeat for each such field.

               

              This process can also be scripted.

              • 4. Re: Find blank in particular field across records and replace
                njem

                Thanks guys but I'm missing something here. There doesn't seem to be any way to get the "find" to find empty. Either manually or by script. I've tried = and "" and isempty(field). = it won't accept, "" it accepts but never finds, and isempty it takes the result code 0 literally and is looking for 0. I note when I try to put = in the script step it objects and says "A number, text constant, field name, or "(" is expected here" if that's any clue.

                To go back a step I'm going this to fix up some data. I like 9 out of 10 things better in FM but one thing Access had was that visual query builder. I'm no expert at SQL but I could build the most complex queries, have them create resulting tables and run further queries on them, do just about anything imaginable with the data. Is that just not possible in FM? They expect if you want to do that you'll have an SQL server and some expensive SQL tools? Are there any popular third party tools that do a similar thing with FM DBs?

                Thanks

                • 5. Re: Find blank in particular field across records and replace
                  philmodjunk

                  I've described the manual method above. In a script, step 3 would look like this:

                  Set Field [YourTable::yourField ; "="]

                  This only works if the field is local to the current layout's table. If it's from a related table's record, you have to use "*" and omit record (while still in find mode) to omit all records that aren't empty.

                  • 6. Re: Find blank in particular field across records and replace
                    njem

                    I've worked around it by playing with a couple approaches. One is to do a find first of records with the field empty, then use ReplaceFieldContents. The other is to do a loop and increment through the records and test each one and modify if.

                    I'm still wondering about a visual query tool though. If I had that FM would be perfect. When I'm done with this project I'll make a separate post asking if anyone knows of any.