GTRR to emulate SQL Search

[Bryn Kaufman]

Hi,

I am using the GTRR to try and emulate a SQL Search. As long as I check of some data for each field it works perfect, but if I leave a related field blank it comes back with nothing.

How can I use GTRR but let my clients leave one or more fields blank and still come back with the right results?

For example, in SQL you might use WHERE City = "New York". Then if you leave off the WHERE City = "New York" you would get all the cities.

In GTRR you can check off New York as one of the cities, but if you leave it blank it assumes it should have no cities instead of all cities so no records are found because they all have a city.

Any help would be very much appreciated. :o

Edited April 15, 2006 by Guest

[-Queue-]

Would it not be easier to perform a normal find instead of using GTRR in this case? It can be done using relationships; however, I think the easiest way (adding a new field for each search field with a value of searchField & "¶All", and doing the same for the data fields, then relating them) adds more overhead than should be necessary when a find will do the job more easily.

[Bryn Kaufman]

Thank you for the tip.

I did end up using a normal find and then a series of Constrain Found Set commands and this performed the exact search I was looking for.

It took the script from 1 line to over 600 lines which is why I was hoping the GTRR would work, but it is not as bad as it sounds as it took me about 3 hours to complete the script and it works.

If anyone is looking to emulate a more complex SQL type search this method seems to be the best way.

If anyone has any questions on this please let me know as I would be glad to explain the solution in more detail.

[-Queue-]

600 lines?! How many fields are being searched?

[Bryn Kaufman]

There are 14 fields being searched. The challenge is that each field needs an OR search.

For example, in SQL it would look something like this:

WHERE field1 = var1a OR field1 = var1b OR field1 = var1c AND field2 = var2a OR field2 = VAR2b etc....

I don't think there is a way to do it with less lines of code but if you would like to look at the search screen and the code you can do so from the following link or I have also attached the .pdf file.

http://www.oahure.com/temp/EasySearch.pdf

EasySearch.pdf

[-Queue-]

It appears that you are using a repeating field for each item. Is that the case? If so, I think you could greatly simplify by using a single global field for each category and formatting it as a checkbox.

I may be misinterpreting because I do not have FM 8 and no idea what GetValue( ) is.

[comment]

These two threads might be of interest to you:

Link

Link

[Fenton]

So it's a "or" search within each field (checkbox), but an "and" search between fields (only those with a match in the found set resulting from using the previous field).

Alternate method:

The first search, within the field, can be done via a relationship. That's easy with a chekbox. Each "choice" field would be a global, related to the regular field.

The "and" part, filtering the new found set by the previous found set, could be done with the FilterValues() function. You'd just need to capture the IDs of the previous found set, to use as the filter applied to those found.

The fastest way to capture would be with the Copy All Records copy/paste method, getting just the IDs. A custom function using GetNthRecord would also work.

It's not real pretty, but it would be a lot less than 600 lines. I imagine it would be pretty fast (depending on the found set sizes). I wonder how it would compare to your Finds? I think a Find would be faster on large found sets.

(P.S. My relational method, on 3 fields took 55 lines, 17 of which were 1-time, approx. +13 lines per each field. If you used a CF you'd have far less lines, but it wouldn't be as fast.)

Edited April 18, 2006 by Guest
Moved file to later post

[-Queue-]

I did something similar a while back, but it was for an earlier version. You may get some ideas by converting the file in this thread, though it can be streamlined using variables and MiddleValues( ) instead of globals, Position( ), and the adding of commas.

[Bryn Kaufman]

Hi,

It is not a repeating field but it is similar. What I am doing is using the checkbox set to store multiple values to one field separated by a carriage return.

Example:

New York

Chicago

Philadelphia

All in one field.

The GetValue() is the position of the word in the field so GetValue(fieldname;1) = New York. This allows me to pull each value out of the field separately, store it in a variable, and then use it later on when I go into Find mode.

[Bryn Kaufman]

Thanks for all the great suggestions!!!

[-Queue-]

Okay, so GetValue( fieldx; N ) is equivalent to Substitute( MiddleValues( fieldX; N; 1 ); ¶; "" ). That's quite a nice simplification for 8.

Well, you could obviously speed up your script by nesting portions that are reliant on previous steps being true. For example, if GetValue( fieldX; 1 ) is empty, then GetValue( fieldX; N ) is also empty. So there is no need to run any more steps on fieldX. (BTW, using "not IsEmpty( )" is more efficient than using "Length( )").

Also, I think the variables complicate the process more than helping it. You could remove them and use dynamic loops instead, as in the sample in the link I posted, which would dramatically reduce the number of script steps required. I would convert the file and update it if I could, but I do not have FM8. Perhaps someone else would consider undertaking it?

[Fenton]

Here is the file again. I removed a few superfluous steps (extra Commit Records, Go to Layout). Now it's 48 lines.

I added another script, which does much the same, but uses comment's idea to use ValueListItems to get the IDs. It seems a little faster. But I believe there is a lower size limit to ValueListItems than there is to field size (1 GB). Does anyone know what the size limit is? (I did an experiment when 7 came out, but I can't remember what I found out -(

The ValueListItems method is only 39 lines, and some of that are 1-time steps.

Text_FilterValues.zip

[comment]

I took Bryn's scripted approach, and simplified it. It's probably what Queue meant too - though I didn't require any version 8 features.

FindProperties.fp7.zip

[comment]

Even simpler.

FindProperties.fp7.zip

[-Queue-]

Yes, that is it exactly! The Constrain Found Set step still confuses me, so I would have created multiple requests for all the options; otherwise, that is what I was trying to do.

What is the purpose of the auto-enter field which references a non-existent value list?

[comment]

Actually, I am very grateful to Bryn, since using Constrain as OR wasn't that obvious to me either. With say only 2 checkboxes @ 5 options each, creating 1024 requests would be quite a task.

What is the purpose of the auto-enter field which references a non-existent value list?

None (other than keeping you on your toes). It's a leftover from the file I used as a starting point, and forgot to delete it.

[-Queue-]

Actually, it is not that great a task (unless you meant processor-wise and not script-creation-wise) because it can be done dynamically, using some nested Ifs. The sample in the link I posted above allows multiple values to be searched in four fields and is not too complicated, even in version 5.5. I imagine it would be much more simple in 8.

Ah, and here I thought I was missing something important and life-changing. :wink2:

[Søren Dyhr]

With say only 2 checkboxes @ 5 options each, creating 1024 requests would be quite a task.

Perhaps the title of the thread isn't that daft after all, when responsetime gets highly conditional and perhaps even lengthy is the manditory question: "Isn't there something wrong with the relational structure??"

Could multicriteria relations be utilized here, and what needs special attention??

The problem on the with a pure unscripted relational model, is how to cope/deal with null values, well Comment provided us with a cool way to handle the primary side of the definition, which really can't be utilized reliable on foreign side of the matter since indexing unfortunatly is manditory ...and works counter the ValuelistItems( functions evaluation.

Fortunatly does filemaker provide us with field level validation to prevent empties... and when thinking about it should the typer of the real data for each property not be able to get away with sloopyness anyway - so field level validations fits the bill.

--sd

RealEstate.zip

[Bryn Kaufman]

The database is downloaded from the Board of Realtors, it is a standard MLS database following the RETS standard.

Because it is already defined I don't think it makes sense to change the structure.

Answering the question of the thread I would say that the GTRR is not good for emulating a SQL search because of the problem I mentioned in the first post where if nothing is checked then nothing comes up.

It is opposite SQL in that if nothing is specified in a SQL search then everything comes up.

[Ender]

You should check Soren's demo. If nothing is checked, it assumes you want All. I kind of like this relational approach (suprise!) Although it requires some additional calcs and relationships, the data itself has the original structure, so there should be no problem getting it to work with your data source.

[comment]

At the top of this thread, Queue dismissed the simple idea of adding calc fields to provide "All" on both sides as it "adds more overhead than should be necessary".

[-Queue-]

So are you pro or con on that statement? I am not certain where I stand anymore.

[comment]

I am not too sure myself, but it doesn't matter. My point was that Søren's file achieves just about the same thing using a lot more resources.

BTW, if it's the number of fields that bothers you, it would be quite easy to concatenate all the checkbox fields into a single multi-line, using prefixes:

On the Property side:

AREA:City

AREA:All

CONDITION:Good

CONDITION:All

On the Request side:

AREA:City

AREA:Suburbs

CONDITION:All

[Søren Dyhr]

would be quite easy to concatenate all the checkbox fields into a single multi-line, using prefixes

Indeed easier, and a lovely scent of retro

--sd