Find returns incorrect results

[kimrru]

I have a field using a custom value list. One of the items in the value list was Black/Blue and when a Find was performed, it resulted in 44 records. Then I changed list to have Black on one line and Blue on the next. Now when I search on Blue, I should get 4, but I get 44 and the results look as if the Black/Blue entry still existed. How can I make the distinction?

Thanks

[Vaughan]

Use the exact match find: ==blue

[kimrru]

My bad! I left out some information. This value list is in a checkbox set in my layout. So when I check Blue in the checkbox set during Find mode, I get the results for Blue/Black. Again, I'm sorry for the confusion.

[Vaughan]

Then you'll have to script the process, and prepend the "==" before performing the find.

Alternatively change the value list items to things that are unique. For instance, instead of "active" and "inactive" I tend to use "active" and "disabled". That way there is no confusion or ambiguity.

[kimrru]

The value list is clearly a list of different items. I used colors for this example.

Since I botched my first description of the problem, let me tell the tale again:

I have a checkbox set of 6 choices using Custom Value List: Red, Green, Blue/Black, Purple, Orange, and Yellow. When the user went into Find mode to search for all the records that were Blue/Black, FileMaker returned 44 records.

Just recently, I separated Blue/Black to Blue and Black making the checkbox set a custom value list of 7 choices and all the records that were previously marked as Blue/Black AND were black were unchecked as Blue and checked as Black. The result should be 4 records. Instead, when the user goes into Find mode to search for Black, the result is 44 which is the same result as if the user searches for Blue. It is as if FileMaker doesn't understand that Blue is no longer checked on these 4 records. Also, radio buttons will not work in this solution because (for my purposes) a record can be Red AND Black AND Orange. What can I do to correct this?

Thanks,

Kim

[Highbury]

Hi Kim,

Check boxes can sometimes bring odd results if the values are not unique. One way to examine the records that are causing the problem is to copy the field, put it conveniently next to the original,then just format it as a normal text field and see the values that are actually there, rather than the ones that are being displayed in the check box version. Remember, that if a value exists in a field that is not part of the check box set you have created, you will not actually be able to see it the 'non'conforming' value.

I suspect you actually ahve the previous values still existing, but invisible, alongside the new ones you created.

[kimrru]

I'm concerned about the concept of "values are not unique" because that's been brought up before. If a line in the custom value list says, for example, Blue/Black and then that one line is changed to two lines, one says Blue one says Black, does that mean the new values can never be unique because Blue/Black used to exist?

Could it be I'm in the wrong forum? Maybe the Value Lists forum would be better suited to this question.

[comment]

I think there are (at least) two separate issues being mixed together. It is not quite clear what you mean when you say you "separated Blue/Black to Blue and Black". Did you change the value list only, or did you make the change in each and every record? Changing a value list does NOT modify the records in any way. A value list only affects the way you enter data into a field, and the way the data in a field is displayed. I suggest you follow Highbury's advice to be sure of the actual contents of your field.

The other issue is finding. If a record contains "Black/Blue", searching for "Blue" will find it. The default search is for 'words that start with...', and "Blue" is a separate word in "Black/Blue".

[kimrru]

Interesting. Two different issues could be why it's so confusing. I'll start with the first problem.

I went into File>Define>Value Lists. Selected my "colors" value list to edit it. In the Use custom values box, it used to look like this:

Red

Green

Blue/Black

Purple

Orange

Yellow

Each value is separated by a carriage return. However, after I edited the list, the custom value list looks like this:

Red

Green

Blue

Black

Purple

Orange

Yellow

This, I imagine, is the first issue which means I'm in the wrong forum, but it seemed important to mention this to have the second issue make sense. Based on what I'm hearing, the values in this second list are not unique. Can someone explain why/how because each line seems unique to me, but may not be in terms of database structure. This seems like a very small list, so I don't understand how it can be so confusing.

The 4 out of 44 records that were previously marked as Blue/Black in the checkbox set of each record were unmarked as Blue then marked as Black.

The second issue is this. When the user clicks Apple+F and clicks Black in the checkbox set to search for the 4 records that should be marked Black, the user is then faced with all 44 records that were previously marked Blue/Black.

Thanks,

Kim

[comment]

As I said, changing the value list alone did nothing to the 44 records that were previously marked as Blue/Black. All that's happened is now you cannot SEE that they are still marked the same as before. You would have seen that had you followed the advice to place another instance of the field on the layout and format it as Edit Box.

---

Don't worry about the forum - if necessary, a moderator will move this to a more fitting section.

[kimrru]

I understand that changing the value list alone would not have changed the data...which is good otherwise that would be [insert explicative here].

So the problem really isn't the find, it's how the checkbox set works. (I learned something!) When I uncheck Blue and check Black, I expect to be able to find those records that are marked Black when I go into Find Mode. But, if I have to change my checkbox list to an edit box, I guess I'm just SOL because that doesn't work for us in regard to data entry, especially when we run into a record that needs to be Blue AND Black. Thank you for your help.

[comment]

So the problem really isn't the find, it's how the checkbox set works.

It sure seems that way.

I guess I'm just SOL because that doesn't work for us in regard to data entry, especially when we run into a record that needs to be Blue AND Black.

I don't think so. This has nothing to do with everyday data entry. When you changed your value list by separating "Black/Blue" into two individual values, you should have followed this by finding all records marked "Black/Blue" and replacing the field's contents with:

Substitute ( CheckboxField ; "Black/Blue" ; "Black¶Blue" )

I believe you can still do this (if you haven't messed too much with the data since). This is a one-time fix, and from this point on operations can go on same as before.

The edit box is for you, so that you can see the actual data in the field while you're fixing it - without it being filtered by the value list. Once you are satisfied the data is OK, you can remove it from the layout.

[kimrru]

wow, that's it!! Substitute...that's the fix I was looking for. I had looked at FilterValues() that I had seen in another similar post, but that didn't work for me. As far as data entry is concerned, I just manually unchecked what should have been unchecked, but that obviously didn't do it otherwise I woudln't be here right now. This is perfect. Thank you so much!