Exact find using a value list

[JerrySalem]

This has been driving me nuts and my client needs it solved now. So it goes to you guru's now

You would think this is simple, but noooooo....

I have a format file used to find items. Works great, can find item by ID num, or by name. But there also is a value list that shows catagories. I want to be able to select a catagory from the VL and get all the items in that catagory.

The problem is the catagory names overlap.

Example; there are catagories with the names "red", "red dots", "blue", "blue small", "blue large" and so on.

A find for catagory of "red" using the value list gives me the items in both catagories, "red" and "red dots".

How can I do a exact field contents match but using a value list?

TIA

Jerry

[LaRetta]

Hi Jerry,

There are two methods you might consider, scripted find and Go To Related. See attached demo. Oh, I didn't notice the category. This won't work with CDML.

Cheers

LaRetta

[Unable]

You can also use the resources available on these forums.

[JerrySalem]

Thanks for your quick reply, you guys must be working as hard as I am on a friday afternoon

LaRetta- thanks so much for sending the sample file. That is sort of what I want. But I need to do it in CMDL, this is a different beast.

Unable- I of course searched hi and low on this (and other) forums.

The file you pointed to lets you select the operator (greater than, less than etc) from a value list, and the search field from a field.

I am searching for a solution that will hide the operator (and make it field contents match) and display a value list for the search field.

still searching....

jerry

[Leb i Sol]

here:

=============================================================

<form action="FMPro" method="post" name="form">

<input name="-db" type="hidden" value="DB.fp5">

<input type="hidden" name="-lay" value="WEB">

<input type="hidden" name="-format" value="Results.html">

<input type="hidden" name="-error" value="errors/failed.html">

<SELECT NAME="-op" id="nameoptions">

<option selected>contains </option>

<option>equals </option>

<option>begins with </option>

<option>ends with </option>

</SELECT>

<input name="db_FIELD" type="text" id="dd_FIELD" size="25">

<input name="-find" type="submit" value="Submit">

====================================================================

All the best!

[Anatoli]

From CDML Reference database:

-Op

What it does

Specifies the comparison operator to apply to the field name/value pair that follows it in a -Find action.

Value is

The operator to use. There are short and long versions of each operator. The default operator is "begins with". The list of valid operators is:

Short Long FileMaker Pro Equivalent Operator

eq equals =word

cn contains "word"

bw begins with word*

ew ends with *word

gt greater than > word

gte greater than or equals >= word

lt less than < word

lte less than or equals <= word

neq not equals omit, word

You can use any FileMaker Pro Find operator by specifying the begins with (bw) parameter. E.g., To specify the "Find Content Match" (==) operator, you would specify the begins with parameter (bw) and then you would place the characters "==" before the actual search criteria. The required lines would look like this:

<input type="hidden" name="-Op" value="bw">

<input type="text" name="First" value="==Keith">

Syntax example(s)

Specify an operator using a link

<a href="-DB=Contacts&-Format=format.html&-Op=eq&First=Bob&-Find">Find Bob but not Bobbie</a>

Specify an operator using a form action

<form action="FMPro" method="post">

<input type="hidden" name="-DB" value="contacts.fp5">

<input type="hidden" name="-Format" value="format.html">

<input type="hidden" name="-Op" value="eq">

<input type="text" name="First" value="Bob">

<input type="submit" name="-Find" value="Find Bob">

</form>

Other tags that are required

-DB, -Format, field name, -Find

See also

-LOP

HTH

[JerrySalem]

Again, thanks for the quick reply.

Leb i Sol-

two reasons why your solution isn't what I am looking for.

1) I am looking for a hard coded 'exact match' for some reason that I can't fathom, FMI decided that that option need not be included in the alowed -op variables. (equals is not the same as exact match, a find of equals red will give you a found set of items named red and red ballon, where a find of exact match to red, will only give you a found set of the item named red). So the exact match option isn't included in the select list.

2) I am looking for a hard coded exact match and a value list for the data entry field. Your solution has a text entry field.

Anatoli-

The CDML reference database is of no help (I already read it). I don't want the user to see the "==" before each item in the select list.

Still searching....

Jerry

[Unable]

Jerry, regarding the sample file to which I referred you, you are so wrong in your above statement.

Please read the text on venus.htm. It's there in blue and white. Duh!

[Unable]

I should add, since your research abilities seem lacking, that if you click the link at the bottom of venus.htm, you will navigate to a working example of the field contents match.

Double Duh!

[Leb i Sol]

" I am looking for a hard coded exact match and a value list for the data entry field. Your solution has a text entry field."

---LMAO....what is the definition of "data" in Phili?

I would seriosly check the posts by Unable.....this is an old discussion ( finding emails..exact vs. split search....).

then again....it is friday... and brains are working on "weekend mode"

All the best!

[JerrySalem]

OK, deep breath.

What I am trying to do is create a find where I can select a catagory from a drop down list. My problem is the catagegory names overlap. If a user picks the category 'red' from the drop down list I don't want the found set to contain items from the category 'red dots'.

The categories are in a drop down list.

[Anatoli]

Jerry,

You probably don't understand the CDML help.

Just pass required operator from hidden input into the same field "category" before passing the "red" from drop down list into the field "category"

HTH

[Unable]

Jerry, on venus.htm I wrote, "The basic form code for a field contents match ..." followed by two lines of code.

Those two lines are the basic format to be used on every field upon which a field contents match is to be performed.

You need to understand that you must edit these two lines to achieve your desired goal. In the first line you need to edit the name of the field. In the second line you need to edit to make your drop-down. It is really that simple.

[Garry Claridge]

Jerry,

Try this:

<select name="category==">

<option value="">Select Item</option>

[FMP-ValueList: category, List=categoryVL]

<option value="[FMP-ValueListItem]">[FMP-ValueListItem]</option>

[/FMP-ValueList]

</select>

It should do what you are after. All the best.

Garry

[JerrySalem]

Success!

Garry you are my hero!

Not to seem ungrateful I have just have a question.

My search page has other fields on it. If I don't select a category, any search fails because the request includes 'category ==' in the request.

Can my users have their cake and eat it to? Using your solution how do I handle not selecting a category but selecting another parameter?

TIA

Jerry

[Unable]

Jerry, On Aug 22 I gave you a link which provided you with the correct answer to your question. I even told you where to find the basic code.

You didn't get it.

On Aug 23 I told you, "You need to understand that you must edit these two lines to achieve your desired goal. In the first line you need to edit the name of the field. In the second line you need to edit to make your drop-down. It is really that simple. "

Had you followed those instructions your code would have looked something something like:

<input type="hidden" name="category" value="==">

<select name="category">

<option value="">Select Item</option>

[FMP-ValueList: state, List=categoryVL]

<option value="[FMP-ValueListItem]">[FMP-ValueListItem]</option>

[/FMP-ValueList]

</select>

It can't be my accent 'cause I'm originally from Philly. But you didn't get it.

Garry posted some code for you which, while slightly different from the above, gives exactly the same results. You got it because he did the work for you.

I'm glad you found a hero who will do the work for you.

You had the answer two days ago.

[Anatoli]

It will be easily possible in Lasso with action parameters on response page. You are searching for == nothing and that cannot be empty.

But you can specify -- as was suggested -- "select operator" with another list which can be empty or exact search...

And BTW, I was suggesting the same as Garry

[Garry Claridge]

You can try this:

<select name="category==">

<option value="*">Select Item</option>

[FMP-ValueList: category, List=categoryVL]

<option value="[FMP-ValueListItem]">[FMP-ValueListItem]</option>

[/FMP-ValueList]

</select>

It selects anything with an entry in that field. Even though it is supposed to also select anything with zero characters.

If the need arises you can use some Javascript to put the "==" in front of the value.

You could also try this:

<select name="category">

<option value="">Select Item</option>

[FMP-ValueList: category, List=categoryVL]

<option value="==[FMP-ValueListItem]">[FMP-ValueListItem]</option>

[/FMP-ValueList]

</select>

All the best.

Garry

[Unable]

or

[Steve T.]

Hi, J! I didn't read all the posts of this thread (forgive me, oh FMFORUMS gods!) but can you use an IF STATEMENT based on what the value of what the category is, i.e. if they do not select a category can you use an IF to ignore that option?

Good luck! --ST

[Garry Claridge]

No, you can not use an [FM-IF] to ignore the "category" field. This is because the [FM-If] is processed before the page is sent to the browser.

Nice try

All the best.

Garry

[Anatoli]

BTW -- It will be possible to do just that in Lasso on response page with Action_Param

[Unable]

The question was, " If I don't select a category, any search fails because the request includes 'category ==' in the request.

"Can my users have their cake and eat it to? Using your solution how do I handle not selecting a category but selecting another parameter?"

I replied "or".

AFAIK that is a value which is used to describe the CDML tag -LOP.

That tag with that value allows Jerry to do that which he desires.

It appears that making it simple does not work. How difficult do we need to make it?

[Garry Claridge]

What if he has more than one other field for searching!

and

Garry

[Unable]

Gee, I really don't know what Jerry should do. I already gave him one answer which he did not understand or could not do.

How hard is it to write:

<input type="hidden" name="-lop" value="or">

and test it?

If there are three fields and just one value="or" does not work, how hard is it to copy and paste that line into an appropriate spot and test it?

I know that, more often that not, "what if" can be answered by trying. And even when it does not work, one knows more than one did before one tried.

I know what I would do if it were my issue.

[Unable]

Garry, as I just indicated, testing will teach one a lot, one way or another.

Since I like learning, I thought the "or" would be worth testing and it was - for me. In doing so I tried the code which I suggested to Jerry above:

<input type="hidden" name="category" value="==">

<select name="category">

<option value="">Select Item</option>

[FMP-ValueList: state, List=categoryVL]

<option value="[FMP-ValueListItem]">[FMP-ValueListItem]</option>

[/FMP-ValueList]

</select>

I had tested it prior to that posting, but not rigorously enough (when there is only one unique entry you will find it). Upon further testing I learned that line did enter the symbol, but then a new request was created which was an "eq -find" on the data from the list ( a find which will return red and red gloves).

So I tried inserting the input line after select, it did not work. And again after /option and it did not work.

Then I considered what it was that I was attempting (insert == and a data string in the same field together).

So I looked at the structure, thought briefly and tried:

<select name="category">

<option value="">Select Item</option>

[FMP-ValueList: state, List=categoryVL]

<option value="==[FMP-ValueListItem]">[FMP-ValueListItem]</option>

[/FMP-ValueList]

</select>

I found that met that which I was attempting to achieve. So I stand by my earlier postings about the Sample File reference providing the information necessary to solve the issue which Jerry was having. All it took was some simple testing and an open mind. The testing and writing this to you took less than 30 minutes - not two or three days.

Incidentally, as with so many things CDML, you and I now know two ways of performing this entry. I'm pleased to share something with you. You contribute so much.

[Garry Claridge]

Yes, experimenting certainly is the best way to learn and find solutions. I have a couple of test databases and a handful of Format files which I can easily manipulate to test different problems. I'm use to doing that now so I can test fairly quickly.

There are many ways of "skining-a-cat", as the cliche goes! I usually try methods and post them as I go (depending on how much time I have). This is generally so that other people can test, comment, amend and contribute to the proposed solutions. It is also for my own benefit so that I can develop a broad library of solutions (and work-arounds).

All the best. Keep contributing, I appreciate it.

Garry

[Unable]

Not satisfied, I woke up this morning thinking about the above. I went to my example files and re-wrote the suggested code into my format file:

<input type="hidden" name="category" value="==">

<select name="category">

<option value="">Select Item</option>

[FMP-ValueList: state, List=categoryVL]

<option value="[FMP-ValueListItem]">[FMP-ValueListItem]</option>

[/FMP-ValueList]

</select>

I retested this and - it worked as a "field contents match" should, entering both the symbol and the data simultaneously into the named field. So perhaps my earlier code (since deleted) had an error when I found "red" and "red socks". From this recent testing there appear to be three ways to perform the "field contents match" with a "select" drop-down.

But don't take my word for it when you can test it yourself.

[Anatoli]

That is what I've suggested.

But it will not work, if nothing was selected in drop-down.

You can then check with JS for something in drop-down and only then send the

<input type="hidden" name="category" value="==">

or make that into another separate drop-down list.

[Unable]

Garry, I've been testing the code you suggested (as well as the code I've suggested). I've been checking it out on two valuelists. And I have been doing this with the logical operator value "or". This value seems significant in what occurs.

The first list is that of last names (single words), so only one word is being entered along with the field contents match symbol (==). And it is working fine entering ==name (finding "Bush" but not "Bushwhacker").

However the second list I have been testing is a list which consists of a single word "books" and several combinations such as "fiction books", "fishing books", "hunting books", etc. When I select "books" I get the right set of records returned, for ==books is being entered. However when I select any of the combinations I find all records containing the word "books", either alone or in combination.

Now whether I select a name or not, with the "or" in place if I select any of the word combinations, e.g. fishing books, I have observed that there are three requests being made. In the first request the name field is being filled with the == (and if a name has been selected that one word is also entered as part of a single request). The second request will show the entry is ==fishing, and the third request will show the entry is books. This means that a find is being performed on books (as if the find were .eq. books), which is why all records are being found. And since there is no record with just the word fishing, the ==fishing is not finding any records.

I've also tested this using a text entry instead of a valuelist with the same results when the logical operator value is or. And I've tested it with just one field coded in the format file, along with the LOP value or.

If my application is not corrupted, then it seems to me that if one is to use the field contents match in this manner via the www (i.e. with a drop-down valuelist and the logical operator value or) then the valuelist must contain word combinations which are read by FileMaker as a single word. In other words, fishing books must be listed in the database valuelist either as fishing_books or fishing(option-space)books.

I'd appreciate your input on this. Do you get these same results using a multi-word entry with the field contents match and the logical operator value or? If you don't, then perhaps my application is corrupted, which is why I don't post my example here. There are some things which are best not shared.

[Unable]

Perhaps I should add that any two word combination, when used with the -LOP value or, seemingly will generate two find requests - even when no symbols or -op's are coded.

[Garry Claridge]

Re: with a drop-down valuelist and the logical operator value or ... then the valuelist must contain word combinations which are read by FileMaker as a single word. In other words, fishing books must be listed in the database valuelist either as fishing_books or fishing(option-space)books.

Yes that is true. Looking at the equivalent URL method gives a hint:

&-lop=or&name=smith&name=jones&-find

is the same as

&-lop=or&name=smith jones&-find

is the same as this:

&-lop=or&name=smith+jones&-find

This:

&-lop=or&name==smith&name=jones&-find

Will return smith, jones, jonesfield, etc; but not smithfield.

Hope this helps to clear it up. It can be quite complex under various conditions.

All the best.

Garry

[Garry Claridge]

P.S.

I think that you may have to use quotes around the values such as 'Fishing Books' to get the search string you need. I remember trying this quite a while ago. If I get a chance, later today, I will give it a go (it is Monday morning here).

All the best.

Garry

[Garry Claridge]

Here are the results of some tests:

&-lop=or&name===bloggs+owen&-find

Finds owen only. Using "Modify Last Find" shows two requests, the first request looks like this "==bloggs ", the second "owen". Note the space after "==bloggs ", hence it only found "owen.

This:

<input type="hidden" name="-lop" value="or">

Name: <input type="text" name="name==" value="">

had exacly the same effect.

This:

&-lop=or&name===bloggs&name===owen&-find

worked as expected, i.e. found bloggs and owen.

This:

&-lop=or&name==bloggs&name==owen&-find

found all bloggs and owen.

This also found all bloggs and owen (entered as "bloggs owen"):

<input type="hidden" name="name" value="==">

<input type="hidden" name="-lop" value="or">

Name: <input type="text" name="name" value="">

The "-lop=or" certainly does have a few variants to the result!

All the best.

Garry

[Garry Claridge]

This:

<input type="hidden" name="name" value="==">

<input type="hidden" name="-lop" value="or">

Name: <input type="text" name="name" value="">

with the entry of:

"Bloggs Jr" owen

Gave the result of only "Bloggs Jr" and owen. Hence, "Fishing Books" with double-quotes should work

Good Luck.

Garry