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.
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.
- Make a back up copy of your file just in case you make a mistake
- Enter find mode.
- Put = in the status field
- Perform the find
- Use Replace Field Contents to update the field with the value, such as "Unknown" that you want to put in the blank field.
- 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.
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?
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.
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.