Find with a *VERBATIM* match from a value list

[danek]

OK a little bit of background here:

I have a database that I did some time ago, where each record in one of the tables had a set of attributes, which were represented by a field displayed as checkboxes. This worked fine and well for some time, until the users decided they kept needing to add more attributes to the value list, causing problems during finds.

Allow me to illustrate by example: suppose there was an attribute "Foo" which later received extensions to "Foo 2" and "Foo 3". If the user wanted to search for all records with attribute "Foo", all the records that had attributes "Foo 2" and "Foo 3" would come up as well. This particular case was solvable by retronaming "Foo" to "Foo 1", but I think you're starting to get the idea...

It just so happens that a lot of the attributes they define share common words. For example, they might have an attribute "Foo", an attribute "Bar", and an attribute "Foo Bar". I found that renaming with qualifiers ("Foo ONLY"; "Bar ONLY") helped in cases like these, but the big question is (because I'm setting up another database that will have a similar feature...)

Is there a way to force searches to match an item off a value list *exactly*, instead of doing a partial match?

Note that "exactly" doesn't exactly mean "exactly" ... I want individual items to match exactly, but I don't want to need to have every attribute match the search criteria in order to find a record... For example, searching for items with attribute "Foo" should also find items that are "Foo" and "Bar", or "Foo" and "Foo Bar", or "Foo" and "Bar" and "Foo Bar", but it should not find items that are "Foo Bar" without also being "Foo".

The possible solution I have contemplated is to make a separate field for each attribute (instead of one field for "attributes" displayed as a check-box list off of a value list for "attributes") . My problems with this solution are twofold:

1. There would be DOZENS of fields instead of just one.

2. The other way, if the users need to add more attributes, it's easy enough to go to the value list and change it. Having them add a new field, find a new place for it in the layout, etc., or having them call us every time they need to make a change, is not an attractive option.

I have a feeling I'm more or less boned here, but if anybody knows some search voodoo (or if I'm missing something really obvious and dumb) that would help in a situation like this, I'd be very grateful. I don't want to have to explain that "attributes must be named in such and such a way paying close attention to such and such" and the various ugly and hackish ways to name things so that finding works correctly...

[Edited so that the title would make sense]

Edited May 26, 2008 by Guest

[Fitch]

If you scripted the find, you could perform the find, and then loop through the found set and omit the records that don't contain the search term on its own line.

[danek]

That's a good idea. My only question is, if there are multiple search terms, how do I get the script to do the matching?

[David Jondreau]

Have you tried using the quotes (""), equals (=), and double equals (==) in your searches? You may have to script the find so that you can automatically insert those operators.

I find your explanation confusing, so I can't give more detailed advice. Providing real world examples rather than made up ones works better for me. A sample file works even better.

[Fitch]

DJ, my first instinct too was to suggest the Find operators. But it won't work in this case. The problem is, when you use == it will find fields that exactly match; but even though one of its values exactly matches, it won't be returned in the search results when the field contains multiple values. I had to try it myself to see what he's talking about.

@danek, multiple search terms are doable with careful scripting. I'm sorry but I don't have time to get into it right now.

Another approach you might consider is to put your "attributes" into a separate table, which users could edit by adding a new record. The attributes could be selected by ID rather than by name, and possibly linked by a join table. Again I don't have time to get way into it, but the point of this paragraph is that using dozens of fields is probably a Bad Idea.

[comment]

Actually, you'd need to be looking for "¶foo¶", but there are two problems with that: it would have to be scripted (Filemaker will not let you enter a carriage return in Find mode) but what's worse, it would fail when "foo" is the first or last value in the list.

One possible solution is to set up a global field with the same value list, define a relationship matching the global to the attributes field, and use GTRR to find the related records.

Alternatively, apply the "one fact per field" rule and split the attributes into individual child records, as Tom suggested.

[danek]

Another approach you might consider is to put your "attributes" into a separate table, which users could edit by adding a new record. The attributes could be selected by ID rather than by name, and possibly linked by a join table. Again I don't have time to get way into it, but the point of this paragraph is that using dozens of fields is probably a Bad Idea.

I like where you are going with this approach. I will have to read up on join tables. Thank you.

[danek]

Wow, that was REALLY easy. And no need for a join table, either. (What is a join table? Sounds like it might be useful to know for the future.) I just created a new table with two fields, Attribute UID (serial, autogenerated, not modifiable) and Attribute Name. Then I made my value list based off of Attribute UID, also displaying values from Attribute Name, and only displaying values from Attribute Name.

Made a checkbox thingy and it worked like a charm.

Only "gotcha" was that I had to add a bunch of leading zeroes to the serial number for Attribute UID. Otherwise, searching for "1" also grabs all records with "10" "11" "12" "13" etc. (I suspected this might happen so I tested it.)

Now the users can just add a record to the "Attribute" table whenever they need more checkbox thingies. I am very happy.

Thanks for the tip, Fitch.

[lando]

I think I have a similar problem...

I want to create a find request that finds any record that matches a list of values. For instance:

find all records where the value in PROGRAM is equal to one of the codes in a list of codes A,B,C, or D. The hitch seems to be supplying the "list" to the find request. There is no equivalent of "True is string1 is in string2" is there? The related table idea sounds like it will work - or is there an easier method in FIleMaker?

Edited July 30, 2008 by Guest