9 Replies Latest reply on Sep 30, 2013 6:17 AM by philmodjunk

    Ideas? Matching un-matched record names that all mean the same thing.

    craig5005

      Title

      Ideas? Matching un-matched record names that all mean the same thing.

      Post

           Bad title I know.

           I've been thinking about this problem for the past hour or so, can't figure out a way to solve it.

           Users can manually enter a location where they are collecting data (example Room A).  However sometimes they enter it as Room A, roomA, aRoom, Room #A, RmA... the possibilities are endless really and I can't get around the manual entry.  So I need a way to merge these to all match.

            

           I want to be able to display all the locations through a checkbox populated by a value list of all the locations.  The user would select all the locations that are the same (room A = RmA = RoomA) and then choose one of the them populate all the location fields so they all match.  Any idea how this could be achieved?

            

           FMA11 Mac os x 10.6

        • 1. Re: Ideas? Matching un-matched record names that all mean the same thing.
          davidanders

               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"?

          https://www.google.com/search?q=replace+field+contents+filemaker

          • 2. Re: Ideas? Matching un-matched record names that all mean the same thing.
            craig5005

                 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.

            • 3. Re: Ideas? Matching un-matched record names that all mean the same thing.
              philmodjunk

                   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.

              • 4. Re: Ideas? Matching un-matched record names that all mean the same thing.
                craig5005

                     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.

                • 5. Re: Ideas? Matching un-matched record names that all mean the same thing.
                  philmodjunk

                       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.

                  • 6. Re: Ideas? Matching un-matched record names that all mean the same thing.
                    craig5005

                         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?

                    • 7. Re: Ideas? Matching un-matched record names that all mean the same thing.
                      craig5005

                           Ok figured out something that seems to work.  Would love opinion on whether this is sustainable with larger datasets (100,000 records).

                            

                      • 8. Re: Ideas? Matching un-matched record names that all mean the same thing.
                        davidanders

                             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.

                        • 9. Re: Ideas? Matching un-matched record names that all mean the same thing.
                          philmodjunk


                               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.