Query filter

[Tom Henkel]

I am trying to build a filter from a checkbox selection of values.

This is part of a larger script, but I am just trying to get the filter to work. I need to generate a report based on the selection from an x-dialog checkbox list (which works)

I have created a plain character list, and also a crlf() separated list.

What I'm trying to do is to select only records where the "worker code" is one of the values in the list.
sample:

DIM WLIST AS C
'Inlist2

Wlist = <<%list%
FC04,FC10,FC11
%list%
debugcheck()
filt = "inlist2(trim(Assign_Wcode),wlist)"
ans =a5_get_records_in_query("cb_assignment",filt)

'is_one_of

DIM WLIST AS C
Wlist = <<%list%
FC04
FC10
FC11
%list%
debugcheck()
filt = "is_one_of(trim(Assign_Wcode),wlist)"
ans =a5_get_records_in_query("cb_assignment",filt)

the field _assign_wcode" is a 5 character filed in the table "cb_assignments".

The query returns 0 no matter which way I code it.

I know that there are numerous records that would fit the criteria, but none are returned.

any assistance would be appreciated.

Tom

[armasoft]

Re: Query filter

Tom try changing the filter to

is_one_of(trim(Assign_Wcode),var->wlist)

or

is_one_of(Assign_Wcode,var->wlist)

[Tom Henkel]

Re: Query filter

Just tested it. Works perfectly!

More times than not, the simple answer is the right one.

I spoke too quickly.

This works great in the get-records in query, however when I use it to build a filter for a report, we go off the tracks.

I tried building the report filter using another method, and it still hangs the report up.

I'm going to try to do a query and then have the report use that query.

[Stan Mathews]

Re: Query filter

Inside quotes Alpha has no idea what you really mean with wlist.

"is_one_of(trim(Assign_Wcode),"+s_quote(wlist)+")"

is probably what you need for a filter string.

For a report it should work as

is_one_of(assign_wcode,wlist)

so long as wlist is defined with sufficient scope to be seen by the report.

[Tom Henkel]

Re: Query filter

Thanks, Stan.

What's kind of interesting is that I went a different path and built the individual filter for each of the workers selected, and the "get records in query returns a valid number, but the report using theexact same query just hangs up.

I've included the code to build the query, and a snapshot of the debugger window.

I use 2 functions that we created, xdlg_cal() and workerselect_multi() that get the dates requested and allow the user to select appropriate workers.

  	workerselect_multi("")
  	if w_count(vWorkers,crlf())=0
  		cancel_msg()
  		goto endit
  	end if
'debugcheck()
  	if w_count(vWorkers,crlf())=1
 		wlist = left(vWorkers,4)
 	  else 
	  	for i = 1 to w_count(vWorkers,crlf())
	  		wlist = wlist + left(word(vWorkers,i,crlf()),4)
	  		if i < w_count(vWorkers,crlf())
	  			'wlist = wlist +" "
	  			wlist = wlist+crlf()
	  		end if
	  	next i
	end if
end if
debugcheck()
if Rep_type ="Summary"
	 filt  = "between(assign_date,[varD->vd_start_date],[varD->vd_end_date]) .and.left(Assign_Wcode,2)=[varC->vUnit] "
	 filt1  = replace_parameters("between(assign_date,[varD->vd_start_date],[varD->vd_end_date]) .and.left(Assign_Wcode,2)=[varC->vUnit] ",local_variables())
	rep_name = "Assignment Report"
  else
  	dim filt2 as C = ""
  	for each foo in wlist
  		filt2 = filt2+ "ut(assign_wcode) = "+ s_quote(foo.value)+" .or. "
  	next
  	len2 = len(filt2)
  	filt2 = left(filt2,len2-6)
  	filt2 = ".and.("+filt2+")"
	 filt1  = replace_parameters("between(assign_date,[varD->vd_start_date],[varD->vd_end_date]) .and.left(Assign_Wcode,2)=[varC->vUnit] ",local_variables())
	filt = replace_parameters("between(assign_date,[varD->vd_start_date],[varD->vd_end_date]) "+filt2,local_variables())
  	rep_name = "Assignment Detail"
end if

ansq =a5_get_records_in_query("cb_assignment",filt)
if ansq = 0
	ui_msg_box(agency,"There are no Assignment Records for Unit "+vUnit+crlf()+"No Report will be produced")
	goto endit
end if
query.filter = replace_parameters(filt,local_variables())
query.order = ""
ansP = ui_get_print_or_preview("Preview")
if ansP = "Print"
	report.print(rep_name,query.filter,query.order)
  else
  	report.preview(rep_name,query.filter,query.order)
end if
endit:

If I remove the selected workers from the filter, the report works perfectly, however it shows all workers in the unit.

debugger.jpg

TOm

[armasoft]

Re: Query filter

Is varD-> valid syntax? I've only ever used var->

[Tom Henkel]

Re: Query filter

That is what we've always used. From way back...

VarD = Date variable
VarC = Character
VarN = Numeric

[armasoft]

Re: Query filter

Just tried ?var->mydate AND ?varD->mydate in the interactive. first works, second = field not recognized.

[Tom Henkel]

Re: Query filter

Use the genie to build a dialog looking for dates. It will give you the varD.

[armasoft]

Re: Query filter

Just built this using the genie... (cleandate > Var->ar_date1 ) .or. (bin1 = 1 )

[Tom Henkel]

Re: Query filter

got nothing...

Just used to using the code.

[armasoft]

Re: Query filter

Apologies, looking at your debugger screenshot your code appears to be working for the a5_get_records_in_query call. I've not used the 'replace_parameters' function before which seem to need this syntax.

Is it possible that the line "query.filter = replace_parameters(filt,local_variables())" should just be "query.filter = filt" as you've already processed replace_parameters earlier in the code

[Tom Henkel]

Re: Query filter

I've refined the code a bit since the post. I removed "filt1", and the 2nd replace_parameters()

[Al Buchholz]

Re: Query filter

Tom

Are you using debug(1) to view what is going?

[Tom Henkel]

Re: Query filter

Al,

in a word, yes. See my Post #5. We just have it tailored so that only certain people can see the debug. That is why you may have seen "debugcheck()" (our function)

I'm not really sure what is going on, but we're experiencing really strange goings on with Alpha. We're running v12 build 4491 and addins 4932.

When we hit the "a5_get_records_in_query", it will occasionally just explode and close Alpha,

Sometimes it gets through that, then explodes on the report.

I've been looking at this for 3 days now, and can't figure out what the he** is going on.

Tom