Using the Trim () function will delete leading and trailing spaces. Using it as part of an AutoEnter Calc will do that for you during data entry.
To get rid of extra spaces within a text block, use the Substitute () function.
Ok, I set AutoEnter calculation to Trim ( Self ) which solves the problem of keeping new data entry clean.
How would I go about cleaning up existing records where some fields have trailing spaces?
If you have the Auto-Enter with Replacing set up, you can just go to each field and do a Records->Replace Field Contents…
Use the "Specify Calculation…" option, and enter the field in question as the calculation. The idea is to "replace the current value back into the field". This will trigger the Auto-Enter replacement, and you're done.
Caveat: this will also update any modification tracking fields in the table where the trimmed field is.
If the developer has tracking of modification dates/accoutns/etc., those fields' setting need to be turned off before this is run, and then reset to track them after the replace function is completed.
I always run a manual backup before tackling any replace function during data cleanup, just in case.... There is no undo for the Replace field contents process.
Good advice, it's no fun explaining to a client that the mod dates are meaningless because you've messed them all up.
I don't like changing the field definition. Bulk data transformation is safely done on a copy and it doesn't take much extra time. *
- Locate the record set that needs to be modified.
- Export these as a FileMaker file.
- Make changes as required.
- Push the data back into the database, including the original modification date.
Sometimes you want modifies/updates to occur. In that case do one import with modifies/updates. Then do a second one without it, just putting the original modification dates back into place.
* Unless the record set is large. When the record set is large, having a backup and working on a copy are even more important!
Really excellent info, I never thought how that kind of data cleanup would affect the modified date which we use extensively. Thanks for that.
Sent from my iPad