The location entry field has to be a standard field - not drop down or some such.
There is a RoomA, ARoom, RMA, Rma possiblity - but always a common distinguishing character?
So, if you did a find for "A" in that field, it would find all the records you desire, and you could Replace Contents with "Room A"?
Unfortunately that wouldn't work since users could use numbers. For example Room 31, Rm31 and therefore there could be Room 31, Room 34, Room 3009 and then there is nothing unique to the location name.
I don't see why you can't format the field as a value list so that you get a consistent entry of the value. There are ways to try to match to multiple entries but they won't work perfectly and your results will still be "messy". Better to avoid the data entry issue by using a value list in the first place.
I do realize that would be the best option, and if I could do that I would. However the database I am working with is only responsible for taking in existing data and producing reports. The data is created using an existing iphone app which exports data to a csv file, which is imported by the db in question.
I would suggest setting up your own iPhone app using FileMaker Go so that this no longer is an issue. This could even be set up to synch the data directly to your database without needing a csv file.
Since you can import the data, you might try this approach.
Define a new table with two fields: DataEntry ; StandardName
Set a unique values, validate always field option on DataEntry
Import one or many of your CSV files into this table mapping your room name field to DataEntry. This will filter out duplicates and leave you with one example of every way that the room was named, with duplicate entries automatically filtered out. You want to capture as many different examples of how users are naming the rooms as you can.
Format StandardName with a value list of the names you want to see used. Pull up all of these records and select the correct standard name for each record. Add a record to the table with the correct standard name in Data Entry if one is not already present.
Match this table by DataEntry to the room name field in your original table.
After import, you can do two things:
Use replace field contents to copy the entry in StandardName into your room name field in your original table to clean up the data entry to make it a standard value.
Perform a find for all entries that do not have a matching record in this new table so that you can both manually give it a standard name and to also add an additional record with a matching DataEntry value in this "correction" table so that future imports can be automatically fixed.
So I think I found a solution, however some things aren't working.
This is what I did, I made two global variables named rightloc and wrongloc (for right and wrong locations). In a layout I have these two fields being populated with a value list made up of the records in the regular location field. The user selects from a pull down the location that is wrong (wrongloc), they then set the right location (rightloc) from a drop down of the same value list. A button calls the following script.
Perform Quick Find [table::location=table::wrongloc]
Replace Field Contents [no dialog; table::location; table::rightloc]
In general, this finds all the records where the location is equal to the wrong location as set by the user. It then replaces the regular location data with what the user set as the right location.
THe problem is that I get an error message "No records meet this find criteria" in addition, it doesn't seem to work within the found set, it replaces all the location fields with rightloc, not just the ones that matched wrongloc. Is the problem using the Perform Quick Find?
Looping through the complete set of records will become frustrating.
If you have a Auto-Enter Creation Date field (and Auto-Enter Modified Date maybe Auto-Enter Modified By?) you could limit the loop to new records.
And the table I describe could be used with an auto-enter calculation to correct the location during import such that only new examples of "wrong" location names need be fixed by updating your correction table and then using find/replace to fix them. But this would need be done on a field separate from the one into which the user entered location name is imported so that you don't lose that data during import.