Filter Report using Check-Box List-Box xdlg

[G Gabriel]

You create a Check-Box List-Box xdlg showing unique values from a field from a table. Your choice(s) will be stored in a variable, let's call it vchoice.

You want to tun a report using as a filter the choice(s) in vchoice.

How?

My first thoughts: vchoice is a list, an array or a collection (it does not matter). I thought, the way to do this, would be to iterate over the items (choices) in that array. But before embarking on an xbasic routine to run a *for_each loop, it seemed to me that would not be the way to do it, since each pass will generate a group of records that matches each item in the array! That would result in a dozen of reports if you have a dozen of choices. Or, you could initialize another array and append more records to it in each pass, which begs another question: how to generate a report from an array?

Maybe I am confused, but assuming that generating reports based on several choices is rather a common practice, then there must be a well defined method to pass vchoice values to the report print filter, one that is a bit simpler than what I described above. So my question is: How do you do it?

Thanks

[Tim Kiebert]

Re: Filter Report using Check-Box List-Box xdlg

I think you would only want to run your filter/query once. So that means constructing the filter first by processing your array. Come to think of it you may be able to use the array directly in the filter using either the inlist() or is_one_of() functions.

filter = "Is_one_of(Yourfield,thearray)"

After a bit of testing I came up with this. Run this code in alphasports. You may need to edit the report and set 'additional fiter' to 'none' but I didn't and the code ran as I think you desire.

DIM SHARED vchoice as C
DIM SHARED varC_result as C
auto_list_vchoice = table.external_record_content_get("product","Product_Id","","")

temp_count = w_count(auto_list_vchoice,crlf())
DELETE a_vchoice
DIM a_vchoice[temp_count] as c
a_vchoice.initialize(auto_list_vchoice)
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("choose products",<<%dlg%
{region}
Type prompt here:| [.40,15vchoice^$$a_vchoice];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%)


vchoice2 = crlf_to_comma(vchoice)
query.filter = "inlist2(Product_Id,"+s_quote(vchoice2)+")"
query.order = "recno()"

:Report.Preview("Product List",query.filter,query.order)

[G Gabriel]

Re: Filter Report using Check-Box List-Box xdlg

Tim:
Thanks for the response. That's pretty much what I've tried and that's also where you get stuck: at the part where you try to pass vchoice to the filter where neither function( inlist2() , Is-One_of()) works.

See the error message I got when I ran your code.

By the way: the help file indicates that the result of inlist2() is C !! one would think it would be L ! Also, the help file indicates that the first parameter of this function "Value" is "The text you want to find".. it does not suggest if you could use an expression. I tried to play with it in the IW and kept getting error messages regardless whether I use a text or an expression !

[Tom Cone Jr]

Re: Filter Report using Check-Box List-Box xdlg

g,

I've confirmed that Tim's script runs correctly using AlphaSports. Time to show us your data and your script, so we can help you see what you're doing differently.

-- tom

[Tim Kiebert]

Re: Filter Report using Check-Box List-Box xdlg

That's strange. I have rerun my sample in alphasports and I am getting a report of the selected products. I put a msgbox() just after the query.filter line to show me query.filter and it showed me

inlist2(Product_Id,"P001,P002")

which is the same as the error message you get except for the single quotes surrounding the whole expression in the error message. BUt that could be just the error messasge quoting the error.

I am afraid I don't know what else to suggest but post the code. However,you say that it was my code that gave you the error.

[G Gabriel]

Re: Filter Report using Check-Box List-Box xdlg

Folks:
I think I figured out what might be wrong. I must be using an older built that does not support inlist2() !!
I am going to try a newer built and let you know.
Thanks

P.S.
For those who think they are wasting their time by monitoring the board on a regular basis: you are not. See what happens when you get rusty like me? you start ask questions that otherwise you would not have asked! Can't help it though, don't have much time for the board right now.

[G Gabriel]

Re: Filter Report using Check-Box List-Box xdlg

Yep...that's what it was!!
Thanks to all.

[G Gabriel]

Re: Filter Report using Check-Box List-Box xdlg

Not quite there yet.

After installing the new built, the script works in Alphasports, but not in my database. Every time I run the :Report.Preview I get a message "no pages in this report", translation: alpha is not finding the chosen item(s) in that field which is not possible since these items were checked from the values in that field.

So the question is: how come it works in Alphasports? the only difference I see is that, the field chosen in Alphasports is made up of one word while the field values in my database could be made of one or more words. To illustrate:

Say you have a table of contacts and you want to print a report showing those who live in zip code 70012, 90121 or 65034 chances are the script will work. On the other hand if you want to show those who live in Boston, Dallas or New Orleans, it won't!

Something happens when you convert crlf to comma or when you look for items in the comma delimited list matching values in the field.

I modified the expression to trim the values in the field. That did not work.

Maybe I should look for other functions or maybe the expression needs further tweaking. Any suggestions?

Thanks

[G Gabriel]

Re: Filter Report using Check-Box List-Box xdlg

On further testing:
It does not matter if the field value is made up of one word or several. I replaced the values in alphasport with values made up of several words and the script worked. I went back to my DB, deleted the script, re-wrote it exactly as it was, and now it works!! this is one of those inexplicable things in alpha, or perhaps in development in general !!

At any rate, the script is so slow is rather impractical. It takes over a minute to run!

Are there any better suggestions!
Thanks

[Tom Cone Jr]

Re: Filter Report using Check-Box List-Box xdlg

It's difficult to optimize this since random choices are permitted. Not sure if it will help, but you might try creating a permanent index on the field you're using in the selection listbox.

If the display of the listbox itself is part of the slow down, consider using an ordinary browse, marking the desired records, and then querying for marked() records.

-- tom

[G Gabriel]

Re: Filter Report using Check-Box List-Box xdlg

Tom:
The xdlg displays fine, it's the the initialization of the report that is extremely slow, in fact you could see the number of records been processed, 102, 112, 122... at the toolbar. That's awfully slow! If you are patient enough, or if you go get a cup of coffee and come back, you will see the report exactly as you wanted it, you just have to be patient to the point of impractically!

I tried to put the filter in the report itself: it didn't make any difference.
I think the bottleneck is Inlist2() (ditto is_one_of()). I think it works the same way as "locate text" since it does not utilize a key. I guess it scans every record for the different choices you made, one record at a time!

That's why I was hoping for a better expression that will match your choice(s) with values in the field. I haven't scanned the entire help file yet for that illusive expression, but it seems that alpha has not come up with one yet. Don't know if I want to try to create a custom function.

[Tom Cone Jr]

Re: Filter Report using Check-Box List-Box xdlg

Other ideas to try:

a) copy the selected records to a temp table and base your report on it, without filters at all.

b) mark the selected records and filter report to show only marked records.

I don't see unusual delay in running the script against AlphaSports. If your table is larger, or if your report is based on a complex set, using the temp table approach should speed things up a bunch.

--tom

[G Gabriel]

Re: Filter Report using Check-Box List-Box xdlg

Tom:
The delay comes from the fact that the table is large and the search process is not indexed. Alphasports has very few records, you won't notice any delay.

To copy records to a third table, you pretty much run at the same bottleneck with the exception that instead of attempting filtering for a print operation you will be filtering for a copy operation.

To clarify: the problem is in passing the choices you made to the report filter (or the copy operation filter). Since your choices you made are in the form of a crlf array, you need some expression either to iterate over the array, or to "flatten" the array, that would work, ideally, with an expression that relies on an index thus speeding the process. I am not aware of any expression that does that. This is the "missing link". The functions available (inlist2() and is_one_of()) are not ideal.

[Tom Cone Jr]

Re: Filter Report using Check-Box List-Box xdlg

Since your choices you made are in the form of a crlf array, you need some expression either to iterate over the array, or to "flatten" the array, that would work, ideally, with an expression that relies on an index thus speeding the process.

A series of indexed find by keys could be easily scripted and would run quickly.

-- tom

[G Gabriel]

Re: Filter Report using Check-Box List-Box xdlg

Tom:
I am not sure how you would do that? The copy (actually you need to append not copy) expression will require a filter. How are you going to pass the values from the crlf array to the filter of the append expression/operation?
Thanks