How do I OR multiple Logical Fields together - Power Search

[TheSmitchell]

I'm implementing a search tool using the new Power Search---I'm liking it, by the way; very nice. OK. To the question:

I have 5 logical fields in my database that I want to do an '.OR.' search. Thus far, all my search options have been '.AND.'. Which is usually fine. But how do I combine 5 logical fields in such a way that I can do an '.OR.' on them. To date, all operations on the grid I'm searching are '.AND.'ed together. Is it possible to specify an '.OR.' operation for part of my Power Search criteria? If not with Power Search, suggestions?

---
Sarah Mitchell

[TheSmitchell]

Re: How do I OR multiple Logical Fields together - Power Search

In the interest of keeping this topic from being a "Dead End" --- because I'm sure someone in the future will come along here and go "how do I do that?"

I have not found a way to do this that does not require writing a custom filter and setting the linked grid's user filter. A potential feature request here would be a way to specify an advanced filter for Power Search --- the same way we specify the filter for a grid's data source.

It is possible that I could have done this with Power Search As-Is, but my logic foo is not that awesome. Additionally, I have moved on from this point and no longer require an answer. But if the BBverse would like to tackle this in-depth, they are welcome to do so. To pose the question more simply:

• I have a text field where the user can enter in a string to search a Description field and 3 check boxes that correspond to a set of Exclusive fields within my database.
• I want the search logic to be as follows (excluding DESCRIPTION, A, B, or C from the filter in the case where the user did not select those fields): "DESCRIPTION LIKE %:DESCRIPTION% AND (A = :A OR B = :B OR C = :C)"
• The user checks 2 boxes, A & B, and entered in "Susan" for the description field.
• I happen to know "Susan" exists within the description of many a record, including some that are A and B type records. However, using Power Search, I know that the search will return no records because the generated filter is... "DESCRIPTION LIKE %:DESCRIPTION% AND A = :A AND B = :B"
• Assumption: Power Search, being blind to the fact that my fields A, B, and C are mutually exclusive, will not do what I want.

As previously established, I can write my own custom filter:

description = {dialog.object}.getValue('DESCRIPTION');
a = {dialog.object}.getValue('A');
b = {dialog.object}.getValue('B');

my_filter = "DESCRIPTION LIKE %:DESCRIPTION% AND (A = :A OR B = :B)"
my_args = description + '|||C|DESCRIPTION' + crlf();
my_args = my_args + a + '|||L|A' + crlf();
my_args = my_args + b + '|||L|B';
{dialog.EmbeddedGrid_MYGRIDALIAS}.gridFilterSet(my_filter, my_args);

Can I accomplish this with Power Search? Can I setup my controls in some fashion that will allow me to use Power Search and NOT write my own custom search filter? Assume that redesigning the database is not an option.

---
Sarah Mitchell

[jennygong]

Re: How do I OR multiple Logical Fields together - Power Search

Hi Sarah, I also have the same question. Can I do very complex logic in search? for example, A AND (B OR C). I am working on grid.

[GGandhi]

Re: How do I OR multiple Logical Fields together - Power Search

hello

the search part can do either AND or OR but a combination, i am not sure it can do. using your example above there are only two conditions based on the equation,
it could be (A and B) OR (A and C) if this were to be true, then you can define calculated fields for (A and B) and (A and C) the use the search for (A and B) or (A and C).

will that do?

[TheSmitchell]

Re: How do I OR multiple Logical Fields together - Power Search

Hi Sarah, I also have the same question. Can I do very complex logic in search? for example, A AND (B OR C). I am working on grid.

If you're just using a regular grid and want to compute your own search filter, you can do so in the onSearchPartFilterCompute server-side event. I use it lots. Very handy.

function  OnSearchPartFilterCompute as v (e as p)

'Request - The Request object. Includes Request.Variables, which should be used instead of the older e.rv construct
'Session - The Session object. Should be used instead of the older e.session construct
'		 - To set a session variable: Session.myNewSessionVariable = "Alpha"
[B][COLOR="#FF0000"]'Useful information in 'e'
'e.rtc.Filter_SearchPart = filter computed from submitted data in the Search Part
'e.rtc.Parameters_SearchPart = parameter value for arguments in the filter.
'e.SearchDataSubmitted - Search data submitted
[/COLOR][/B]'e.tmpl - Grid definition

'Session - session variables. TIP: To set a session variable: Session.myNewSessionVariable = "Alpha"

'-----------------------Notes About this Event --------------------------------------------
'The purpose of this event is to allow you to modify the search filter before it is executed.
'You can modify the value of e.rtc.filter_SearchPart and e.rtc.Parameters_SearchPart
'For example:
'e.rtc.filter_searchPart = "lastname = :whatLastName"
'e.rtc.parameters_searchPart = e.SearchDataSubmitted.Lastname+"|||C|whatLastName"

'The .parameters_searchPart variable is only needed if the  .filter_SearchPart variable uses arguments.
'.parameters_searchPart is a CRLF delimited string with one entry for each argument used in the filter.
'It is of the form: parameterValue|||Type|argumentName
'Where Type is a one character Xbasic data type (e.g. C,N,E,D,T,S, or L) of the field you are searching.

'IMPORTANT: If your datasource is DBF, then your .filter_searchPart must use Xbasic syntax.
'If your datasource is SQL, then your .filter_searchPart must use SQL syntax.

'-----------------------------------------------------------------------------------------

'e also contains a pointer to the AjaxResponses array.
'To send a Javascript command to the browser, make an entry in this array.
'Example:
'if e.searchDataSubmitted.Company = "" then
'	e.cancel = .t.
'	dim aa as p
'	aa = e.rtc.A_AjaxResponses
'	aa[].text = "alert('Search Expression was recomputed.');"
'	aa[..].id = 100  'the order in which AjaxResponses are sent to the browser
'end if

end function

If you're embedding your grid in a Dialog2 object and are trying to make Power Search do the work, then not so much. Your best bet, I suspect, is to just write your own grid filters and manually submit the search when the button is clicked in your Dialog. You can do this either client-side with JavaScript or use a combination of client-side and server-side (ajaxCallback) to build your search filter. It depends on what kinds of restrictions you're applying, etc. EG, if you have search options that are filtered/restricted by log-in privileges, you probably want to calculate your search server-side.

Embedded grids have all the same functions available as regular grids. You could use the {Dialog.EmbeddedGrid_[GridAlias]}.gridFilterSet method to set a User filter on the grid when your search button is clicked. More information here: http://wiki.alphasoftware.com/~alpha...erSet%20Method.

Good luck!
---
Sarah

[jennygong]

Re: How do I OR multiple Logical Fields together - Power Search

i tried this before. It is useful just not okay with my case. Because i want to display grid based on the AND OR sql statement, not after click "Search". All these events must be after clicked "Search"

[TheSmitchell]

Re: How do I OR multiple Logical Fields together - Power Search

i tried this before. It is useful just not okay with my case. Because i want to display grid based on the AND OR sql statement, not after click "Search". All these events must be after clicked "Search"

Are you searching your grid or just building a SQL statement for the grid?

[jennygong]

Re: How do I OR multiple Logical Fields together - Power Search

I am building my grid. but my grid is based on the sql statement saved in database.
for example, i am trying to display a grid with AcctNumber="11???000???"
each "?" takes a space and means random number there. i wanna display AcctNumber which first two are 11, the middle is 000, the 2nd -fifth is random number, also the last 3 digits. Do you have any idea to do that?

[TheSmitchell]

Re: How do I OR multiple Logical Fields together - Power Search

You're trying to override settings in the Grid, not searching. This is woefully off-topic, but here's a linky that will help: http://wiki.alphasoftware.com/~alpha...dExecute+event. If you are also providing SQL statements as search parameters/options, then you would use OnSearchPartFilterCompute to apply them.

There are likely lots of forum posts on overriding grid settings that relate to your particular use case. I would encourage you to start a new thread, as well, on the topic of overriding grid SQL statements. You will likely get more help. This thread was and is intended for searching a grid in a Dialog using controls, etc, from the dialog. People who might be able to assist you may not read this thread since its topic is Power Search.