Subject Re: Two table filter in report
From Charlie <TM@TC.com>
Date Mon, 24 Apr 2017 17:00:08 -0400
Newsgroups dbase.getting-started

Hi Mervyn.. Thanks a bunch.  I had forgotten about joining.  Worked well!

Mervyn Bick Wrote:

> On 2017-04-24 11:31 AM, Charlie wrote:
> > Hi.. I have a report with two tables in it.  I can easily filter one of the tables which happens to be in the detail but the table in the header I cannot seem to figure out how to include that in the filter.  Here is the code.  The last line is me grasping at straws to try to get this to work.  BTW both tables have the same field that I am trying to filter on:
> >
> >       filt = form.golfers_scores1.rowset.fields["dbname"].value
> >                 set procedure to test.rep
> >                 form.oRep = new testreport()
> >                 form.oRep.streamsource1.rowset.filter := [dbname=']+ filt+[']
> >                 form.oRep.reportgroup.headerband.filter := [dbname=']+ filt+[']
> >
> > Thanks for any help!!
> >
>
> It depends on what your report is for but it is not usual to use the
> reportgroup headerband other than for a title page.  (You can of course
> use it as you have done here but it is not usual. :-) )
>
> Nine time out of ten one would set the reportgroup headerband height to 0.
>
> Add a group from the Reports tab of the Component Palette.  Select the
> group and drop it onto the detail band.
>
> No "band", be it detailband, headerband or footerband, has a filter
> property.  One filters a rowset.
>
> One can use multiple tables in a dBASE report but life becomes much
> simpler if you JOIN the two table to make one rowset.
>
> As I don't have the structure of your table this may need work but you
> would use something like
>
>    select g.dbname,g.fld1,s.score,s.gamedate from golfers g inner join
> scores s on g.dbname = s.dbname order by g.dbname
>
> The g and s are correlation names (aliases) so that dBASE knows which
> tables you are referring to.  List whatever fields you want in the list
> of fields.  You can't set an index as you can't build one for a JOIN'ed
> rowset so you need to use the "order by" clause.  When using a group
> object on a report one needs to order the rowset on the field used to
> set the groupby property of the group.  This is not essential if you are
> only ever going to print a report for one golfer at a time.
>
> Now you can set the filter for the stramsource1 rowset and all should
> work as planned.
>
> If you set the filter the report will be for one golfer.  If you don't
> set the filter the report will include all golfers.  You can, if
> necessary, set the group1.headerband.beginNewFrame property true which
> will force a new page for each golfer.
>
> Mervyn.
>
>
>
>
>
>
>