Filter Report using Check-Box List-Box xdlg

[Tom Cone Jr]

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

g,

You misunderstand. I wouldn't use a filtered append op. I'd use a script that loops through the array. On each iteration I'd do an indexed fetch_find(), a record_data_get(), and a record_data_set() to put copies of the found records in a temp table that has the same structure. Suggest you search message board for related threads discussing use of temporary, temp, or intermediate tables with reports.

--tom

[Tom Cone Jr]

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

Finian's essay in this thread illustrates the technique I'm talking about.

http://msgboard.alphasoftware.com/al...able+temporary

Be sure to read the thread all the way to the end.

-- tom

[G Gabriel]

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

Tom:
I must not have been clear enough. The discussion in the thread you referenced deals with LQO. The problem here is a bit different. It's the step right before alpha gets to search & retrieve (the LQO) operation.

1-Step one: you choose certain item(s) through the xdlg, for example: Boston & Dallas in the example I used before

2-Step two: These items, by virtue of how the receiving variable in the xdlg is designed, will be stored as a crlf array. so now you have a variable, vchoice, that looks like this:

Dallas
Boston

3-Here is the snag (the bottleneck):
how do you get this array into the operation filter? whether that operation is print, copy, append or whatever? The thread you referenced purports to creating "temp" tables. But to do so, to append records from your existing table to a temp table, you've got to "translate" this array to the filter of that operation, this "translation" is the time consuming one.

The available means to do so in alpha is to use either is_one_of() or inlist2(), neither one of them runs fast.

How did I come to the conclusion that this is the bottleneck?

If you use the same script, one that will create the xdlg, receive your choices and then print the report but instead of inlist2() you hard code the filter, the script will run & generate the report in a second, something like this:

..create xdlg & get choices to vchoice...
:Report.Preview("report_name,",'"field1=boston'.or.field1='dallas'",query.order)

Conversely, if you use:

vichoice2 = crlf_to_comma(vchoice)
query.filter = "inlist2(field1,"+s_quote(vchoice2)+")"
query.order = ""
:Report.Preview("Item Expense1",query.filter,query.order)

It will take forever, which leads me to believe that resolving inlist2() is the time consuming part. To create temp tables as in the thread you referenced, you have to resolve inlist2() first.

I mentioned in my first post that I might try appending an array to a new array or initialize an array from the table and then use array.matching to filter the desired ones. I do nt know if that will resolve the issue or create new snags.
Thanks

[Tom Cone Jr]

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

g,

the attached script may help. It illustrates how one might parse a list of variable length and construct a filter expression with the elements found in each line.

Create a new xbasic script on the code page of the AlphaSports sample database. Paste the attached text file into the script and save it. Then run it. (by the way it handles multi-word city names just fine).

the output from the script is found in the trace window.

Hope this helps.

-- tom

[G Gabriel]

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

Tom:
Thanks for your efforts. I was getting an error msge, see attached. I think you meant to put a semicolon between a5_eval_expression & local variable() which I did and now the script works fine and you could see the list in the trace window and the filter constructed the way I wanted. Awsome!
I think this will make for a great function!
Thanks

[G Gabriel]

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

Tom:
The last step in the whole routine is not working, the part where I incorporate filter in the print operation.

In your script, filter is the variable that will contain the final filter string. Now if you write the script like this:
:Report.Preview("report_name",filter,query.order)
You get a message "filter is not logical" !
I tried to evaluate "filter" before the report.Preview, but I keep getting simillar errors!

[Tim Kiebert]

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

G,

First, I eliminate the obvious and assume you replaced 'City' in Tom's code for your own field name.:) Or if you are running it in Alphasports it should be 'Bill_City' as this is the field where the list is coming from.

Second, below is the section of Tom's code that constructs the filter. I have highlighted in red a couple of sections to delete from the code. The filter was ending up with One too many sets of quotes.

filter = "[COLOR="Red"][B]\"[/B][/COLOR]City = "
for i = 1 to k
    line = word(citystring, i, crlf())
	trace.writeln("Line "+ltrim(str(i))+" "+line)
	if i <> k then
		filter = filter + " '"+line+"' .or. City = " 
	else
	    filter = filter + " '"+line+"'[COLOR="Red"][B]\"[/B][/COLOR]"	
	end if    
	trace.writeln(filter)
next i

It would be great if you could report back if this configuration runs faster (and by how much) than the inlist2() version.

[G Gabriel]

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

Tim:
The first & last \" need to remain to enclose the filter with double quotation. You could take those out and use s_quote later with the same result.

The script as is written produces the correct filter string in the trace window. The problem I am running into right now is when you put that string in :report.preview() you get an error message: filter is not logical.

I guess I need to get some sleep and think about this in the morning, but since it's morning time for you, maybe you could come up with the answer.

As to the speed, I believe it will be lightning fast. inlist2() is absurdly slow. Once I resolve this last issue, I will confirm that.
Thanks

[Tom Cone Jr]

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

g,

Tim was correct. I had too many quotation marks. Here's a corrected version, which actually runs a query against the customer table in AlphaSports. After running the script check the trace window to see how the filter was built, one line at a time. Then open the customer table's default browse to see whether or not the query ran successfully.

Thanks Tim!

-- tom

p.s. The approach I'm using has one known limitation. The filter expression itself cannot be more than 1023 chars long. If the user selected too many cities, he could break the script since the filter string became too long. Using short fieldnames will help. I recommend the script be modified to terminate cleanly with a warning message to the user if the length of the filter is too long. If this limitation is unacceptable you'll probably have to go back to my original idea and use a temp table, constructed using looped index find by keys to copy the selected records into the temp table.

[Tim Kiebert]

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

Tim:
The first & last \" need to remain to enclose the filter with double quotation. You could take those out and use s_quote later with the same result.
Thanks

I created a new report named 'Cust_Test' in alphasports based on the customer table. Made sure there was no record selection happening as part of the report. ie all records showing in report.

To the end of Tom's script added the following

report.preview("Cust_Test",filter)

and edited the filter section as outlined above so that the end of the script looks like this.

k = line_count(citystring)
filter = "Bill_City = "
for i = 1 to k
    line = word(citystring, i, crlf())
	trace.writeln("Line "+ltrim(str(i))+" "+line)
	if i <> k then
		filter = filter + " '"+line+"' .or. Bill_City = " 
	else
	    filter = filter + " '"+line+"'"	
	end if    
	trace.writeln(filter)
next i


report.preview("Cust_Test",filter)

If I leave the \" in I get the "Filter is not logical" mesage. When I take it out the report reports.

The filter needs to be entered as a character string I know but me thinks that since it is being passed in from a character variable that the characterness is being implied/supplied if you get what I mean.

I came to this conclusion by trying to create the filter in the 'Select Records' dialog while designing the report. I used the genie to create a multiple condition filter. And observed what ended up in the filter field and there is no opening or closing quotes. Indeed if you copy the filter text that Tom's script generates and paste it into the filter field of the record selection dialog then try to preview the report you will get the said error message. However, if you simply remove the double quotes from either end. it will bring up the report. Atleast that is how it behaves at my end.

[Tim Kiebert]

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

Tom,
I was just too slow. I was doing some testing in the interactive window before submitting my reply. However, the testing didn't clear matters up much. I thought maybe I could see how the strings got built differently. This is what I got in the IW.

var = "String"
Filter = ""
filter = "Character ="
filter = filter + " '" +var+"'"
?filter
= "Character = 'String'"


Filter2 = ""
filter2 = "\"Character ="
filter2 = filter2 + " '" +var+"'\""
?filter2
= "Character = 'String'"

One lesson I have learned from this is when testing to back up the IW with a write to the trace window.

[Tom Cone Jr]

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

Tim, I use the trace window a lot. It's a bit awkward sometimes since things have to be converted to character data types, but it doesn't interrupt the program flow like a msg box will.

I think g's problem (wish I knew his first name) was using the inlist2() inside a query spec, causing each record in the table to be examined one at a time. Hopefully the query string we've been working on will be much faster for him.

-- tom

[G Gabriel]

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

Tim/tom:
For security purposes, when online I try to give as fewer information about myself as possible. I think, this is a good practice, I am surprised at those who include all their info online. There is no telling who is reading this stuff. I will send it to your email.

Tom's script displays correctly on the trace window, but when you insert filter in the operation you get an error: filter not logical.

Tim's script produce an error: '....................' not a filter expression. I guess that is because it is not enclosed in double quotations.

I used Tim's script and tried to enclose it in double quotation with s_quote and I got: "\"................" . What do you make of that !?

So, we are down to that final step!
Any other ideas.

[Tom Cone Jr]

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

g, please confirm you cannot get my revised script to work with AlphaSports.

-- tom

[G Gabriel]

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

Tom:
The script runs fine in alphasports and the query filters appropriately. The problem is in incorporating the filter in the preview operation (either :report.preview() or review_report()) that's where you get the error messages.
The expression is not reading the filter as such for some reason.