Performing AND finds in portals

[bkasid]

Hello,

Hopefully this is a simple question that many of you have already solved.

THE SITUATION: I have a many-to-many relationship between my Companies file and a Cities file. I have it set up correctly (as far as I know) with a 3rd join table between the two (CitiesServed).

THE PROBLEM: I want to be able to do a find for all the companies that serve in more than ONE specific city -- something like "find all companies that serve BOTH city1 AND city2".

I've noticed when performing a find in my companies table I can only enter ONE city into my portal (to the CitiesServed table) and if I create a new find request and enter the second city it just finds all companies with either city1 OR city2.

I hope this makes sence. It seems like it would be a common requirement. Any help or insight would be very appreaciated.

Thanks in advance.

Brian

[QuinTech]

Hi Brian. Maybe you could create a value list based off the related list of cities, and then make a calc field called CitiesServed = ValueListItems ( thatDB , thatList ). You could then search for *city1*city2* in CitiesServed to get the result you're looking for.

A workaround, yes, but it works!

Jerry

[bkasid]

Jerry,

Thanks for your feedback but unfortunatly I'm a bit of a novice and I don't really follow your solution. Could you please elaborate a bit?

Is there no way for filemaker to do this sort of search?

Thanks, Brian

[smorr]

Step 1: Create a relationship between your companies and your citiesServed tables. (you probably have this already)

Step 2: Create a valuelist "citiesServedValues" based on this relationship so that you can get a value list for the city names of the cities served by a company in question

Step 3: Create a calculated field "citiesServed" in your companies table(file) with calculation (text) = valuelistitems(get(filename),"citiesServedValues") set this to unstore calculation.

Then you can search this calculated field for the query "*city1*city2*" The asterisks are important.

[QuinTech]

To elaborate on step 2, this value list is "Use values from field">"Only related values". That's how the wording is in v6, smorr or someone else please correct me if it's different in 7.

[RalphL]

There is a method that worked in pre 7 and I guess it will still work in 7.

In CitiesServed make the field City a repeating field but only show the first one.

Make a find layout in Companies that looks much like your browse layout but make the portal rows several lines high so that you can several replications of the city field. Enter City 1 into the first and City 2 into the second and perform the find then return to the normal layout.

[bkasid]

Thanks very much to everyone for their help on this.

- Brian