Can't Filemaker do a simple SQL Search?

[Bryn Kaufman]

I have a simple SQL Search that needs to be done but it looks like Filemaker can't do it. Any help would be greatly appreciated.

I am search a table that contains homes in my area. In plain English the search would look like this:

Select all fields where Region = region1 or region2 or region5 or region7 or region8 or region9 and condition = excellent or above average or average and view = ocean or mountain or beach and price > 100,000 and price < 200,000.

Now this search is fairly easy to do using SQL, but in Filemaker it looks like I would need to create 54 search requests, which of course no user would ever do.

I called Filemaker support twice on this issue and the only suggestion is to write a custom script, but when there is a lot of fields to search a custom script to emulate a SQL search is very difficult.

Again, any suggestions would be greatly appreciated. I love Filemaker's ease of use but I think I hit a brick wall.

[comment]

The brick wall can be bypassed by using a relationship instead of Find. Define some global fields to hold the requests, define a multi-criteria relationship, and use Go to Related Record [show related only] to create the desired found set.

In your example, you would need these global fields:

gRegion

gCondition

gView

gPriceLow

gPriceHigh

The relationship would be:

gRegion = Region

AND

gCondition = Condition

AND

gView = View

AND

gPriceLow ≤ Price

AND

gPriceHigh ≥ Price

The gRegion field needs to hold all the desired regions, separated by a carriage return (if you use a checkbox format for the field, that's what you'll get). Same for all the other global fields, except the price range, of course.

[Bryn Kaufman]

Thanks for your suggestion but there is one issue still which prevents it from working. I am hoping there is a solution to this one issue.

Using the relationship works great as long as there is just one entry in the field of the table I am searching, but if two or more entries are in that field it will not find it.

For example, with the view field, if the view contains Ocean, then it will match if my user checks Ocean for the view.

However, most views contain multiple entries, such as Ocean, Mountain, City. So now if my user checks Ocean it no longer finds the record because Ocean does not equal Ocean, Mountain, City.

Can you help me to bypass the brick wall again?

Thanks.

[comment]

If there are multiple entries in the Region field, they too must be separated by carriage returns. IOW, if:

gRegion = Ocean¶Mountain

Record1

Region = Ocean

Record2

Region = Mountain¶City

Record3

Region = Ocean¶City

Record4

Region = Mountain

all four records will be related.

[Bryn Kaufman]

Thanks again! That worked, but one last issue.

The data comes to me from the MLS.

The multiple entries in a field are now separated by commas so it looks like this.

Ocean, Mountain, Marina

so for the it to work I need to change it to:

Ocean

Mountain

Marina

I tried replacing the , with a ¶ but that did not work.

Can you point me in the right direction to take field data that is separated by commas and switch it to having each entry on a separate line?

I think after this is done this solution will work great! :thumbup:

[comment]

You didn't say HOW the data comes in, but assuming the format is consistent, you can use a calculation of:

Substitute ( Region ; ", " ; ¶ )

[Bryn Kaufman]

OK, thanks!!! I now have a good solution to a problem that I thought was impossible to solve.

[comment]

Good. BTW, those fields don't have to be global. You can put them in your Customers table, or create a separate Requests table tied to Customers via CustomerID. Then you can put a portal to Listings on the Customer layout, showing all listings that meet the current customer's request.

[Reed]

You could also simplify this w/o using a relationship as well, if you use the constrain found set command. You could script the whole process so all the users see are the interface fields.

Not as slick as multi-criteria joins though...