combining 2 value lists on a search form

[army]

I have two fields, category1 and category2 with the same value list - category.

I want to search for records with a selected category in category1 or category2 which I can do with:

FMPro?-db=photo.fp5&-lay=data&-format=search_results.htm&-op=eq&category=Arts&-LOP=OR&category2=Arts&-sortfield=pictname&-max=100&-find%20">Arts</option>

How would you write the code to do this search on a form with a single drop down field (instead of a separate drop down for category1 and category2).

And a related question, can you do a key word search on two fields at the same time using one text field ?

[Leb i Sol]

Q:

can you do a key word search on two fields at the same time using one text field ?

A:

maybe you can combine field1 and field2 into Joined_Filed3 and then use the "caontains" search on Joined_Field3....asuming that cat1 and cat2 are not in 100s-1000s of records.

...just a thought until some one with more expreience reads the post

good luck...

[army]

Thanks, you use a calculated field category1 & category2, right ?

[Leb i Sol]

I think so....the idea is to create a field that contains all the categories and maybe separated by a comma:

joined_field would have :

Arts, Plitics, Music, Images

and then when you do a search you would search the filed "joined_field" that contains (since you know that the cat1 and cat2 are 100% different from each other)....whatever u select from drop down list....

...but I have not done it myslef in FM I just know that this is the priciple in SQL based searches. Look arround the forum I think there has been posts regarding this...sorry I am not of more help.

good luck army

[army]

I tried it, I created calculated field combcat which contains the values from category1 and category2 and I used this code:

Category : <input type="hidden" name="-op" value=cn> <select name="combcat" class="formtypeplain" id="combcat">

<option value="" selected>All

[FMP-option: category]

It didn't work. but thanks for the suggestion. I think it probabley would work if it was just searching a text field, but not with the drop down. Thanks again for your help.

[Leb i Sol]

hm there is something wrong with that value-list....or u didn't post all of the code:

<select name="SearchField" value="">		

<option value="" selected>All</option>		

[FMP-ValueList: SerachFiled, List=ListNameinFM]	  <option value="[FMP-ValueListItem]">[FMP-ValueListItem]</option>	

[/FMP-ValueList]					  </select> 

I don't see why it would not work regardless if it is a text field or a drop list...the "entry" is still just a text string...check your list again.

I guess the easier way to test it would be making a new field and manually entering some data (cats), make a html file with a text field insted of drop-list and see if the search works...

so in FM:

somefield: A,B,C,D

and then on html:

<input type="hidden" name="-op" value=cn> <input name="somefield" type="text">

If this simple principal works then you know your drop list and/or joined field are not generated correctly but the search concept works...

again...just a suggestion not a solution since I have not tryed it. Perhaps you can post your code for others ones you get it running!

All the best to you...

[army]

I tried it as a text field and I could not get it to work either, I get error messages when I do the search. I suspect you are right about not doing the join correclty. I don't really know what I'm doing yet with FileMaker. This forum is extremely helpful and thank you very much for your help.

[Leb i Sol]

no problem....and yes this is one of the best forums outthere...regadrless of the topic most people seem to be very nice and take their time to help out and honestly if it was not for the fmforums I would have given up log time ago.

Anyway, joined fields post : HERE

but basically:

fields:

cat1 -text

cat2 -text

cats -calculated reslut (the same) is text and

calculation is:

cat1 &"," & cat2

with a result of eg. Arts,Photos

about synthax:

basically the filed_name must be "parenthasised" with "& from both sides in a string (except the begining and the end since no fields are preciding or follwing) if you are using few fields in a join....much like parethasies are used (field_name)

now u serach this "joined" field called "cats"

give it try...

[Leb i Sol]

now as far as your value list is concerned you have to explain a bit more about it....

how are they generated (cutom, from a field etc.)?

do u have a field "category" associated with each record (probably do)?

etc...

so the ball is in your 1/2 of a court.... 3 sec. to go....do you shoot a 3 pt for the win or 2 to tie the game

[army]

I am attaching a sample database file where I joined the two categories.

combination.zip

[Unable]

I have not been looking at the rest of the problem, but when it comes to your calc field I believe that you are going to need to change the calc from "number" to "text".

[Leb i Sol]

Unable is abs. right...fileds must match.

I looked at your DB and have it attached (fixed) however I would *really* consider making a realtional database where you would have Cat1 and Cat2 as two separate files that can be used to gereate drop-down lists dynamically. As you have it set up: if you wish to add a cat1 or a cat2 u must manually change your value lists in order to create drop-downs...therefor u might have issues down the road when u decide to update....but this is up to you!

combcat.zip

[Unable]

It is my understanding that when using a calc field for display of data through CDML, it is best to set the calculation as "unstored". Your field combcat is not.

Also, for serving your db through a browser you should probably set it in Sharing... for Single User, not Multi-User.

[army]

Thank you, when I changed the calcultation result is text it worked ! You made my day.