Report filter, yes, Query, no

[Raymond Lyons]

Does anyone have any idea why a variant of a Peter Wayne function filters a report just fine but seemingly can't be made to work in a query? The function, based on stuff on p.59 of Peter's book, determines the number of work days between 2 dates given holidays that are entered in a table :

Workdays(Date(),Permdueon,"holidays")

[Peter.Wayne]

RE: Report filter, yes, Query, no

Can you be more specific about the function and the context in which you are using it?

[Raymond Lyons]

RE: Report filter, yes, Query, no

Peter,

The function, report filter and some query code follows. As to the context, I want the report to print a record only if certain fields are or are not blank, when a certain date has not yet come and gone, and most importantly when there are a table specified number of workdays left before a deadline date that exists in each record. A kind of tickler report for the deadline. And that all works as a report filter. BUT, if a record prints in the report my users want the date it was printed (or at least a flag) to be recorded in the record. That's why I thought I would switch to a query, print based on the query and then update the appropriate field with date() after it is confirmed that the report printed OK.

If this won't work for some reason, one option would be to revert to calendar days. But my users would not like that. Or I could give up having the date the record was printed except on hard copy, but my users really wouldn't like that. I suppose I could try to figure out a way to fetch through records without involving a query, determine workdays and update a field with the number of workdays, then go from there. But that could be very time consuming for a large table. Or maybe there is some even better way I haven't thought of yet, assuming that a simple query just is not going to work.

Thanks for any help you can be.

Ray
**********************
function Workdays as N(First as D,Last as D,Name_of_Hol_Tbl as C)
option strict
dim dow1 as n
dim dow2 as n
dim weekends as n
dim nholidays as n
dim offset as n
dim hol as p 'pointer to the holiday table
dim ip as p
dim query.description as c
dim query.filter as c
dim query.options as c
dim query.order as c
dim fld as p 'pointer to the field in holiday
dim field_name as c
dim indx as p
on error goto no_holiday_table
hol=table.open(Name_of_Hol_tbl)
on error goto 0

fld=hol.field_get(1) '**Pointer to 1st field in the file
if fld.type_get()"D" then '**Make sure the 1st field is a date, just to be safe
workdays=-1
exit function
end if
field_name=fld.name_get() '**name of the 1st field-will be used below
'**Each offset below either adds or subtracts weekend days to the date
'**in the date field. Then all you need is the # weekends in between
dow1=dow(first)
offset=case(dow1=7,2,dow1=1,1,.t.,0)
first=first+offset
dow2=dow(last)
offset=case(dow2=7,-1,dow2=1,-2,.t.,0)
last=last+offset
'**Now just calculate # of weekends and add a fudge factor in the event,
'**that first - last goes, say, from a Thurs to a Monday
weekends=int((last-first)/7)+iif(dow(first)>dow(last),1,0)
'**Now check for holidays
query.description="holidays between dates"
query.order=""
query.options=""
query.filter="between("+field_name+",first,last)"
ip=hol.query_create()
nholidays=ip.records_get()
'**Finally, just calculate the workdays
workdays=last-first+1-nholidays-2*weekends
indx=hol.index_get("holidays between dates")
indx.drop()
hol.close()
exit function

no_holiday_table:
on error goto 0
workdays=-1
exit function
end function

***********************
(.NOT.DELETED()).AND.(ISBLANK("permission").AND.ISBLANK("P_PTRDATE")).and.date()Permduedat.AND.Workdays (Date(),Calc->Permduedat,"holidays")Perwarn_At

"Holidays" is the name of the holidays table. The above is the filter in the report and it works fine. Note that the report runs on a set and the calculated fields are global on the set level. Also note that I have tried doing away with the calculated fields but that did not help. G_setup->Perwarn_At is a numerical field, as is the result of the workdays function. I also tried doing away G_setup->Perwarn_at by just putting in a number, but that too did not help.
**********************
In addition to using query builder under the query genie, I have tried numerous things in Xbasic, including the following:

tbl = table.current()
query.description = "Test 1 version"
query.order = ""
query.filter="ISBLANK("REFDATA->PERMISSION").AND.ISBLANK("P_PTRDATE").AND.CALC->PERMDUEDAT>=DATE().AND.Workdays(Date(),Calc->Permduedat,"holidays")Perwarn_At"
�I also tried various simplifications like the following, to no avail.
�query.filter="(Workdays(Date(),Calc->Permduedat,"holidays"))Perwarn_At"
query.options = "I"
tbl.query_create()

[Peter.Wayne]

RE: Report filter, yes, Query, no

The query.filter you say you are using in the query won't work because
you are not properly using single quotes within the double
quotes of the query.filter.

[Raymond Lyons]

RE: Report filter, yes, Query, no

Hard as it is to believe, I am way beyond messing up on quotation marks in things such as this. It's just that I forgot that this system drops the slashes that are in front of the offending double quotes that are in what I copied to paste in here. In addition to that I tried single quotes with the eaxct same results.

Ray

[Stan Mathews]

RE: Report filter, yes, Query, no

Ray,

If you want to repost your script with the backslashes intact, just substitute the html equivalent

(\) ---- substitute keyboard characters for the "words" below.

"ampersand"#92"semicolon" for each of the backslashes.

Stan

[Raymond Lyons]

RE: Report filter, yes, Query, no

Stan,

Thanks, though without experimenting I'm not sure I get how to do what you are saying. And in any case it's pretty obvious in the code where the backslashes go or alternatively where to use single quotes.

My tentative conclusion is that A5 can't deal with a query within a query. Still hope that is wrong though.

Ray

[Peter.Wayne]

RE: Report filter, yes, Query, no

Can you do the workdays() calculation outside of the query?
Won't your workdays() function just return a number, such
as 4 or 8?

Can't you write
wkdays=workdays(date(),calc->permduedat,"holidays")
and then rewrite the query filter as
query.filter="isblank('permission') .and. isblank\
('p_ptrdate') .and. wkdaysperwarn_at"

Would this work?

[Stan Mathews]

RE: Report filter, yes, Query, no

Raymond,

Just trying to pass along things I learned here. If I paste this in right from one of Cal Locklin's earlier posts:

&amp#92;,&nbsp&nbsp&nbsp&nbsp when put in your post in place of the backslashes, should produce a backslash in the post as "translated" by this message board.

Stan

[Raymond Lyons]

RE: Report filter, yes, Query, no

Stan,

Thanks, now I see what you were talking about. And I guess I was just dense when I first read it. Again, thanks.

Ray

[Raymond Lyons]

RE: Report filter, yes, Query, no

>>Can you do the workdays() calculation outside of the query?>Won't your workdays() function just return a number, such as 4 or 8?permduedat,"holidays")
and then rewrite the query filter as
query.filter="isblank('permission') .and. isblank
('p_ptrdate') .and. wkdaysperwarn_at"

won't work. Actually, it would run, but it won't do the job because, again, I need to check the workdays remaining a for deadline that can vary for each record.

Any other ideas would be appreciated very much.

Thanks.

Ray Lyons

[Peter.Wayne]

RE: Report filter, yes, Query, no

What is the calc->permduedate expression? Maybe you
should just include that expression in the workdays() function, instead of trying to refer to calc->permduedate in the query filter.

[Raymond Lyons]

RE: Report filter, yes, Query, no

>>Maybe you should just include that expression in the workdays() function, instead of trying to refer to calc->permduedate in the query filter.

[Raymond Lyons]

RE: Report filter, yes, Query, no

Because it appears that one cannot have a function in a query filter if the function itself runs a query, I have arrived at the following solution to the problem, though I am not particularly thrilled with it:

The solution involves adding a field for Workdays to the main table and then breaking the query that would not run into 3 parts.

The 1st part is the 1st part of the malfunctioning query and results in the records to update with workdays:
query.filter="(ISBLANK('REFDATA->PERMISSION').AND.ISBLANK('P_PTRDATE').AND.CALC->PERMDUEDAT>=DATE())"

The 2nd part takes the middle part of the malfunctioning query and uses it to update the added WKDAYS field. For those who may try such an update through the Update Card, note that you cannot build it through the expression builder as it will get some bogus error when you try to save the expression (I say "bogus" because it will in fact work if you merely type in the expression without going to the expression builder). In Xbasic we get, in part:

update.field1 = "WKDAYS"
update.expr1 = "Workdays(Date(),Calc->Permduedat,\holidays\)"
[In case the above line screws up the word holidays is supposed to be enclosed by a backslash and double quotes.]

Now we have to combine the results of the above two in a final query, which is basically the malfunction query with the work done by the function in the new WKDAYS field:

query.filter="(ISBLANK('REFDATA->PERMISSION').AND.ISBLANK('P_PTRDATE').AND.CALC->PERMDUEDAT>=DATE().AND.WKDAYSPERWARN_AT)"

This is used to both run the report and update the date the report was run in the main table. Note that I can't just run a query on our newly update WKDAYS field because it includes day values larger than I want to print, and I have to rerun the other part of the filter to ensure that we don't print records that have already been printed or are otherwise irrelevant. And I don't just run the update on all records in the table mainly because when the table gets large this comparatively slow running update could take too long (also I don't want to destroy historical data on how many workdays were left when the old report runs were printed, though of course that could be handled without the first query).

At least this works.

Ray Lyons

[Raymond Lyons]

RE: Report filter, yes, Query, no

Shoot, I left out the double quotes! I'll try again:

update.expr1 = "Workdays(Date(),Calc->Permduedat,\"holidays\")"

Ray