Building a dynamic search formula

Panorama X
1

I am drawing a blank on the appropriate programming technique I need and would appreciate the wisdom of the group.

I have a database with approximately 300,000 accounting entries covering multiple years. I have created a form with a text list that displays several fields from the database. I want the user to be able to search the database with options for multiple criteria and so I have created five popup lists that populate choosing listchoices( from the five different main fields in the database. I have also set up two text editor boxes for entering a start and end date range.

I am not concerned about dynamic searching. I have a button “Run Query” that triggers a procedure that searches the database and redisplays the text list based on the results.

Here is my problem - how do I write the query procedure so that it ignores the fields where a user has not made a choice from one of the lists? It is unlikely that a user would want to filter on all five but might choose one or two or even three. As well, the user may or may not choose to restrict their search to a specific date range.

The only thing I can come up with is:

If fgDoneeChoice (the variable from the text list where the choice is stored) ≠ ““

select Donee = fgDoneeChoice endif

If fgPayeeChoice ≠ ““ selectwithin Payee = fgPayeeChoice endif

If fgFundChoice ≠ ““ selectwithin Fund = fgFundChoice endif

If fgTypeChoice ≠ ““ selectwithin TransType = fgTypeChoice endif

If fgAccountChoice ≠ ““ selectwithin Account = fgAccountChoice endif

if fgStartDate ≠ ”” and fgEndDate ≠”” selectwithin Date => date(fgStartDate) and Date <= date(fgEndDate) endif

This works, but obviously involves potentially six separate searches and while the speed is ok, it would be faster if somehow this could be combined into one search. Also the text list object flashes for each successive search.

Any ideas?

Thanks

2

It seems like a perfect opportunity to use execute. You’d use the User’s choices to build a select formula.

LetFileGlobal fgSelect = {Select Donee = fgDoneeChoice }

    +?(fgPayeeChoice ≠ ““,{ and Payee = fgPayeeChoice},"")

    +?(fgFundChoice ≠ ““,{ and Fund = fgFundChoice},"")

    +?(fgTypeChoice ≠ ““,{ and TransType = fgTypeChoice},"")

    +?(fgAccountChoice ≠ ““,{ and Account = fgAccountChoice},"")

    +?(fgStartDate ≠ ”” and fgEndDate ≠ ””,{ and Date => date(fgStartDate) and Date <= date(fgEndDate)},"")

+{ If info(“Empty”) = -1 Message “Nada” EndIf }

Execute fgSelect

This is all written off the top of my head and may have a number of tweaks needed but it should be pretty close.

1 Like
3

If you only want to display the matching records (no selection), you could use the Query field of the textlist object with the following formula, without a need to run a procedure.

(Donee = fgDoneeChoice or fgDoneeChoice="") and (Payee = fgPayeeChoice or fgPayeeChoice = "") and (Fund = fgFundChoice or fgFundChoice = "") and (TransType = fgTypeChoice or fgTypeChoice = "") and (Account = fgAccountChoice or fgAccountChoice = "") and (between(Date,date(fgStartDate),date(fgEndDate)) or fgStartDate ="" or fgEndDate="")
4

That’s what I was looking for. Thanks. I had thought about execute as well but this is simpler.

5

Hugo’s solution will also work with the select statement (no execute needed).

However, if you are looking for maximum speed, Jim Cook’s execute technique is going to be the fastest. With a third of a million records, the difference might be significant.

If you are doing all six selections, there won’t be any speed difference. But say you are doing only two selections. In that case, Jim’s solution will only calculate the two comparisons you are actually using. But Hugo’s solution will always calculate all six comparisons, even if some of them aren’t actually used in the final result. So that is just wasted time for no reason. For a small database the difference won’t matter, but it will add up when using a big database.

Your original solution using selectwithin is probably faster than you think. First of all, like the execute solution, it will only perform the comparisons that are actually needed. Secondly, once you start working on a subset, the selections will become much faster. When doing a select within Panorama will take almost zero time to skip the records that are already unselected. In fact, the selectwithin solution might be the fastest, though it will depend on the order of the selections and your particular data set. The sooner your selections narrow the subset, the faster the overall search will be. If you’re doing this a lot it might be worth actually performing some timing tests. I’ll bet there would be a large percentage of scenarios where this is the fastest approach.

However, if you do use the selectwithin approach, you would certainly want to eliminate the flashing by using noshow/endnoshow. This will also greatly increase the speed.

6

Unless I’m mistaken, it should be SafeSelectWithin

7

Unless I’m mistaken, it should be SafeSelectWithin

I’m glad you mentioned this - I was going to comment on the fact that Kevin was apparently not checking to make sure that his select operations actually selected something, but I was in a rush for an appointment so I skipped that.

I suspect that safeselect (and safeselectwithing) is probably not the appropriate tool for Kevin’s application. Based on his problem description I don’t think he cares about keeping the original selection, and there is definitely a significant performance cost to using the “safe” options. In fact, safeselect can be nearly twice as slow as a regular select, because if the select is successful, it winds up doing all of the comparisons twice.

I do now see a problem I didn’t notice before in Kevin’s original code. If fgDoneeChoice is empty, then this code will be doing one or more selectwithin operations without ever having done a select first. So the starting point will be whatever random selection happened to be in place. I’m sure that’s not what Kevin wanted.

Here’s a complete solution using selectwithin that solves this problem, and checks for failure. Obviously I haven’t tested this code, but it should be pretty close unless I’ve made some silly mistake. Now that I’ve written the whole thing down, it’s more complicated than I thought when I started. Hugo’s is definitely way shorter and more elegant. But this might be the fastest solution if you want to try it. If the selection isn’t valid for whatever reason (no options chosen, or no records match the criteria) this solution does NOT leave you with the original selected records.

no-show
selectall
let selectStatus = "nil"
shortcall within,fgDoneeChoice,"Donee"
shortcall within,fgPayeeChoice,"Payee"
shortcall within,fgFundChoice,"Fund"
shortcall within,fgTypeChoice,"TransType"
shortcall within,fgPayeeChoice,"Payee"
shortcall within,fgAccountChoice,"Account"
if selectStatus<>"empty"
    if fgStartDate<>"" and fgEndDate<>""
        if selectStatus = "nil" selectStatus = "ok" endif
        selectwithin between(Date,date(fgStartDate),date(fgEndDate))
        if info("empty")
            selectStatus = "empty"
        endif
    endif
endif
showpage
endnoshow
if selectStatus = "ok"
    // selection is valid, do whatever you want
else
    // display error message if you want
endif
return

within:
    if selectStatus = "empty" return endif
    if parameter(1)="" return endif
    if selectStatus = "nil"
        selectStatus = "ok"
    endif
    selectwithin fieldvalue(parameter(2))=parameter(1)
    if info("empty")
        selectStatus = "empty"
    endif
    return