Problem with duplicate value list entries

[retropia]

I'm using FMP10, and our database is published online. I'm the only one who has access to add entries to our value lists. The value list is created by entries into the field, as opposed to creating a list manually.

With 169 entries into the value list, 12 are duplicates. They are exact duplicates, i.e., if you do a search on duplicate version #1, I get 625 matches, and a search on duplicate version #2 also finds 625 matches.

I have no idea how these are being created. I know I didn't do it, and since no one else has access, I don't see how anyone else could be causing this. So, that is problem number one, how these duplicates are being created, and how can I stop it from happening?

Problem number two is how to delete these duplicates? I can't seem to find any way to delete value list entries that are created this way.

Thanks in advance!

[efen]

Are you sure that these are "exact duplicates"?

An extra space at the start//middle/end of the word will create a non-duplicate value

[IdealData]

Maybe the index has become damaged.

[retropia]

They appear to be exact duplicates, no blank spaces at the end. When I do a search on either one, I receive the same number of matches.

How would I fix the index if it is damaged?

[bruceR]

What do you see when you enter find mode and do command-I (insert from Index). This will show you the different values. Also: is this a value list where you are using "show values from second field" option?

[David Jondreau]

Is the database accessed by a web browser or by FileMaker Pro?

Is the field entered by a drop-down list or a pop-up menu?

If users are accessing by Filemaker Pro and a drop-down list, then they can enter any value they want in the field simply by typing it in. If it's Filemaker Pro and a pop-up menu, then users can paste any value into the field.

It sounds like the problem is likely a slight variation as already mentioned, like a trailing space. You can add an auto-enter calculation to the field to take care of future problems like that by using a function like Trim(). I use a custom function that removes text formatting, and a variety of issues ( leading and trailing spaces, tabs, and other characters).

That may not solve every issue in the future though, not if you're using a value list drawn from the field itself. If you want to limit the values, use a hardcoded list or a separate table to hold the values.

To fix the existing records, find (for example) your 625 records and use the Replace[] script step to change all the values to one standard value.