Clearing old info from a value list

[rkmossm]

I maintain a database for a club. The club is divided into various "Houses". A member can belong to more than one "House". I have a single text field called "Household" that displays check-boxes with the name of each House (there are currently seven houses in all).

I said "currently" because the number of houses changes year to year and, sometimes, the name of the House changes at the beginning of the year. All I do is edit the value list, adding new houses, and removing or renaming others as needed.

I also have a report (at the end of the year) where I show the winners of a contest we have and list their House in color coded text. But, I noticed last year that some of the people's names came out with the wrong House listed by them. This was my first indication of a problem.

I thought all this was fine until I started exporting the data in an Excel spreadsheet. When I export the information on the "House" field, it shows up in a multi-line box in Excel for those in multiple Houses. But, I also noticed it is exporting the names of households that a person USED to belong to (and now doesn't) or it will report the "same" household with the old name and the new name.

It's as if the database is retaining the old information even after it's changed.

I thought about exporting the info, cleaning it up in Excel, deleting it in Filemaker, and importing it back from Excel. But, I was hoping there was a better way.

[ernst]

Hi Richard,

I think your problem is that just redefining the valuelist that the 'household' field uses will not change the contents of that field.

If you want to clean up the database after removing a choice from your value list, you could perform a replace action on the 'household' field in the whole database to get rid of all occurrences of that choice.

You could do this as follows, remember to BACKUP your data before trying this though!

First choose 'Show all records' from the record menu,

Then choose 'Replace Contents' from the same menu and choose 'by calculation...'

In the calculation dialog enter: Substitute (household, "ValueToReplace", "")

Click 'Replace'

This shoud do the trick.

Regards,

Ernst.

[rkmossm]

Thank you, ernst. This was exactly the answer I needed.

I now understand the need to "substitute" via a calculation rather then simply changing the value list. In the future, I will do that.

After reading about this, I looked in the manual for both "replace" and "substitute". There was a small amount about replacing data, but if I hadn't known beforehand what it was doing, I probably still wouldn't have known to use this.

I then looked in an old Visual QuickStart book and there was a good explanation of the whole process for "substitute" that tracked exactly with your explanation. It even went into how to use it for changing incorrectly formatted data in addition to incorrect data.

Thanks for your help. Once again, this forum filled the need better than any manual.