Query filter for a set

[Sandy Garrett]

Often we want to know if a record contains a certain word or phrase, so their forms have a "Search for" button that contains

query.filter = "VAR->FindWhatText$ALLTEXT()"

Works fine for single tables. But for a form based on a set that contains IDS.DBF -> PIX.DBF we have to use

query.filter = "CONTAINSI(PIX->ELEMENTS,VAR->FindWhatText)"

to search in a PIX field. So far I have made two search buttons, but is there a more elegant way? Users expect both searches in a single button.

[Ray in Capetown]

Re: Query filter for a set

Have a look here Sandy
https://www.google.com/url?sa=t&rct=...52164340,d.ZG4

[Ray in Capetown]

Re: Query filter for a set

Or from and old post:
by Mike Wilson dd 13 December 2011

DIM records_found as N
dim vsearch_term as C="Ajax Supply Company"
dim vfilter as C
vfilter=s_quote(vsearch_term)+" $ Supplier->Supplier_name"
records_found = topparent.queryrun(vfilter,"recno()","","No","<Cross Level>",.f.)

or you can try the crosslevel() function to do both tables at the same time.

[Sandy Garrett]

Re: Query filter for a set

I couldn't figure how to use cross level or combine filters, and there's no browse or to many, so I made a little sample to explain the problem better. The ladies are accustomed to the single "Search for" box that looks in all the text in the form. That same script on the sample form works only for the Name field - Name belongs to the parent table. The commented query in the Search for script will work for the Elements field.

We would like a script that finds the searched for a term whether it is in Name or Elements - or both.

[Stan Mathews]

Re: Query filter for a set

query.filter = "flattenquery("+"("+quote(VAR->FindWhatText)+" $ Name) .or. ("+quote(VAR->FindWhatText)+" $ Pix->Elements)"+")"

[johnkoh]

Re: Query filter for a set

Hi,

Can I change the Field_Name with Quote() in this type of searching?

'Create an XDialog dialog box to prompt for parameters.
DIM SHARED vSearch as C
DIM SHARED vInfield as C
DIM SHARED varC_result as C

dim vTemp_list as c
dim vFieldlist as c
vTemp_list = <<%list%
Description
Part Number
Vendor
Type
%list%



ok_button_label = "&OK"
cancel_button_label = "&Cancel"

Delete XdialogStyle
dim XDialogStyle as p
XDialogStyle.AccentColor = "White"
XDialogStyle.Color = "#51+102+255"
varC_result = ui_dlg_box("Items",<<%dlg%
{Windowstyle=Gradient Radial Bottom Right}
{lf};
{region}
{Region}
Type the name to find;
{Endregion};
{Region}
Search for: [.40vSearch] in: [%v%.17vInfield^=vTemp_List] ;
{lf};

{Endregion};
{Region}
;
{Endregion};
{line=1,0};
<*15Search!vSearch> <15=cancel_button_label!CANCEL>;
{endregion};
%dlg%,<<%code%
IF a_dlg_button = "vSearch" then
a_dlg_button = ""
'set the Result of Fieldvalue
select
case vInfield ="Description"
vFieldlist = "itemDesc"
case vInfield = "Part Number"
vFieldlist = "parent_refId"
case vInfield = "Vendor"
vFieldlist = "Vendor_id"
case vInfield = "Type"
vFieldlist = "Type"
case else
msgbox("must have it")
end select


Query.Clear()
query.filter ="("+quote(VAR->vSearch)+" $ "+quote(vfieldlist)+ ")" 'Is it possible ?
query.order=""
dim tbl as p
tbl = table.open("prditem",FILE_RO_SHARED)
qry = tbl.query_create()
rcd = qry.records_get()
msgbox("result",str(rcd))
qry.drop()
tbl.close()
END IF
%code%)

[Stan Mathews]

Re: Query filter for a set

You don't need to (shouldn't) quote the field name.

query.filter = "("+quote(VAR->vSearch)+" $ "+vfieldlist+ ")"

[johnkoh]

Re: Query filter for a set

Stan,

Thank you.

[Sandy Garrett]

Re: Query filter for a set

query.filter = "flattenquery("+"("+quote(VAR->FindWhatText)+" $ Name) .or. ("+quote(VAR->FindWhatText)+" $ Pix->Elements)"+")"

Strange. The above works exactly as I asked. But sometimes we <F8> a form to view something or other. A browse view of the selected records gets Id all mixed up.