Filtering records on subreport in set report

[[email protected]]

Hello everyone,
A5V8 on Windows XP SP3
I've been on this issue before but I've lost everything and I'm building it again and got stuck.
I have a subreport on a report in a set. The set has the primary table which is a customer account, each customer has a 3 character code, 001, 002 etc..
The subreport covers the work items carried out for each customer each month and has the same 3 character customer code as the linking field. Of course there are lots of records in the 'work' file and some customer codes have several entries for each month.
My problem is to filter the records from the 'work' table into each customer code account, retrieving the records for the same month as the account and all records for each customer code during that month. The acount has a 'date prepared' field from which the month in question can be calculated.
At the moment all the months work entries are being drawn onto the account report. So what code do I nuse please and where do I put it?
Thanks, Trevor.

[Stan Mathews]

Re: Filtering records on subreport in set report

Are you asking for something like

date_prepared = {03/01/2012}

? month(date_prepared) = month(date())
= .T.

[[email protected]]

Re: Filtering records on subreport in set report

Hi Stan,
Thanks, but no. That will help me find which month's records to pull from the 'work' file, but what I need to know is how to retrieve just one customers records for just that one month, from the 'work' table into the subreport on the 'account' set's report.
For example the account set report might be for customer number 005 (a character field) and the subreport within it would say;

WORK CARRIED OUT THIS MONTH

CODE DATE WORK DONE WHAT WAS DONE HOURS SPENT COST
xxx xx\xx\xxxx unblock drains 1.25 32.00

This information comes from the 'work' table which has the same customer code (3 characters) and the records that I need to fetch into the subreport need to be only those for that particular customer and for that particular month (the month of that particular account). There may be one record or multiple ones for that customer code in that month. At the moment each customer code's account is displaying all of the month's work (for every customer) in the subreport.
I hope that this is clearer.
Thanks again,
Trevor.

[Stan Mathews]

Re: Filtering records on subreport in set report

If you filter the report to one customer, only their records should show up in the sub report.

[Al Buchholz]

Re: Filtering records on subreport in set report

Trevor

What you are describing doesn't make sense to me.

A subreport should show only the data for a customer if there is a grouping for the customer and the subreport is on the detail section of the report.

Probably time for a example...

[[email protected]]

Re: Filtering records on subreport in set report

Here is a lashup of what I want to do and some data to use.

The accountset12 has a report which has a customer code which is unique to each customer and shows an opening balance from last month, payments received and new balance before the new month's account.
Then there is a subreport for work from workmar2012. Here the linking field is customer code (cuscode) and I want to show in the subreport all the items of work from the work table for that one customer code, there may be none, there may be several for each code.
Then the same for bookings012. Again the link is cuscode and I want to show all the bookings with 'Arrive' in the month (say March 2012 for this example) for each individual customer code, again there may be none or 5 if there is an arrival every week in a month that has 5 Thursdays in it.

At the bottom of each subreport I need a subtotal of all the work (or bookings) for that customer code to add to the other items on the account and give a grand total etc.. Once I know what code I need to do this and where to place it I can flesh out the rest of the account. For customer codes with no work or no bookings in the month, I can enter a 'no work done' or 'no bookings' entry so that the code is still picked up. At the moment I am just using SCANNING("bookings012") etc., and getting all the customer codes' bookings\work.

Thanks folks.

[Al Buchholz]

Re: Filtering records on subreport in set report

Trevor

Sorry about the delay.

Unzip these files and add the set to your database.

Put the files in the same directory and then press the green plus sign to add the set to the db.

There is a report account_al that is a start of what I think you are looking for.

add a footer to the subreport, use the genie to make a calculated field subtotal..


acountset_al.zip

[[email protected]]

Re: Filtering records on subreport in set report

Thanks very much, Al,
I've spent some time on it now and I think I've got the hang of it.
Thanks again,
Trevor.

[[email protected]]

Re: Filtering records on subreport in set report

Hi Al,
Thanks for all your help, my end of month report works well. I'm now having a problem getting the closing carry forward balance, which is a calculated value, into a new (next month's) account table. The final balance calc. value was created at report level and I don't seem to be able to create them now at set level. So what I need is a way of getting the closing balances of the set report into the opening balance of the new table. Before, I used to do this with a 'duplicate' and 'append' but of course I can't do this from a report.
Thanks for any help you can give,
Trevor.

[Al Buchholz]

Re: Filtering records on subreport in set report

Actually reports can do some writing...

reportcoding.PNG

[[email protected]]

Re: Filtering records on subreport in set report

Thanks, Al.
Looks like I need to learn action scripting, never used that before. I'll bury my head in the relevant help section and see what I can do.
Trevor.

[[email protected]]

Re: Filtering records on subreport in set report

Hello Al, I'm sorry but I can't find a category in action scripting that suits the need. I have built the report for end-of-month accounts and it works well, thank you. Before I drop the account table from the set and replace it with next months, I want to copy the new table's opening balance with the old ones closing balance. The closing balance on the report is a calculated field and the opening balance in the new account will be just a number field.
Before, I used to copy the whole account table, but without records and then use an operation to append the value of the closing balance to the new account\opening balance.
Regards,
Trevor.

[Ray in Capetown]

Re: Filtering records on subreport in set report

send a sample of the working one.
and describe where you want the closing total to go

Or you might want to try this sort of thing -
1 use a summary operation to create a C/F balance table
2 That table could become the new one, renamed to start new entries with
3 repeat 1 at end of month

[Al Buchholz]

Re: Filtering records on subreport in set report

Hello Al, I'm sorry but I can't find a category in action scripting that suits the need. I have built the report for end-of-month accounts and it works well, thank you. Before I drop the account table from the set and replace it with next months, I want to copy the new table's opening balance with the old ones closing balance. The closing balance on the report is a calculated field and the opening balance in the new account will be just a number field.
Before, I used to copy the whole account table, but without records and then use an operation to append the value of the closing balance to the new account\opening balance.
Regards,
Trevor.

The xbasic change record is what I was considering.
xbasicchangerecord.PNG
Ray's suggestion 1 sounds like another idea too..

[[email protected]]

Re: Filtering records on subreport in set report

Thank you both for your replies, they are appreciated. I'll send the database when I get back from holiday next week. I'll also try to get to grips with the two suggestions that you have made. The problem here is really me! I'm not an alpha 5 student although I've been using A5 since the V4 days. I just bought it as a means to an end to run my small office, learned only enough until it worked and then that was that. I started to build the three main tables into a set just recently so that I could quickly end up with a report that I could send out by email (when I started with V4 98% of our customers hadn't heard of email)! So I'm 3\4 of the way there, just need a little more help please.
Thanks again,
Trevor.