save pdf report one record at a time

[Cheryl Lemire]

I'm Baaaaaaaaaaaaack :D

I have a report designed that is filtered based on a logical field value being set to TRUE.

Instead of saving the report with all the records that match the filter into ONE pdf file, I would like to 'loop' through my table and create a separate report for each record. This way I have a separate pdf file for each customer instead of all of them in one pdf.

I am sure this is quite simple, but for some reason I cannot figure out how to set my report filter so that it knows to only grab one record at a time.

This is for billing purposes where I need to create 3 different pdf files for each customer, then combine the three files into one creating the final invoice. I will be using the pdf_append() for this portion and plan to create the report filenames based on a cust_no + report_name.

Ideas please?
Thanks

[Tom Henkel]

Re: save pdf report one record at a time

I would think that the easiest way would be to build a "while" loop in your calling script which would run the report separately for each customer.


something like this:

tbl = table.open(your master table name)
tbl.index_primary_put(your index)
tbl.fetch_first()
while .not.tbl.eof()
  filter code here
  report.print(your report)
  tbl.fetch_next()
end while

You would obviously need to figure out some sort of scheme to rename the pdf's as they are generated, but I think you can do that in the report itself.

Tom

[Cheryl Lemire]

Re: save pdf report one record at a time

Hi Tom,

My problem is with determining the filter that I need or perhaps the index I should be using.

tbl = table.open("customer")
tbl.index_primary_put("Cust_No")
tbl.fetch_first()
while .not.tbl.eof()
  report.preview("B_Cast_Test")
  tbl.fetch_next()
end while

The above code returns the same ONE report with all the records the same as running the report on its own. The difference being the above is looping through my customer table so I am stuck in an endless loop

The current filter set in the report itself is:

Broadcast_raw->Report_Date >= "10/01/2007" .AND. Broadcast_raw->Report_Date <= "10/02/2007" .AND. Customer->OK_to_bill = .t.

The report is based on a set where Broadcast_raw is the parent linked 1:1 on an ID field in an ID table and that linked 1:1 on an ID field (cust_no) in the Customer table.

I am not sure if the report being based on a set makes a difference or not.

Lets say in my customer table I have 4 out of 100 customers marked with ok_to_bill as true. Customer numbers would be 001, 002, 003, 004.

The broadcast_raw table has 12 records that match the dates between 10/01 and 10/02.

I want one report for customer 001 with the records between 10/01 and 10/02. Then a second report for customer 002, then 003, etc.

I know this has to be simple and I am just missing the right filter, or possibly the right index.

Thanks

[Stan Mathews]

Re: save pdf report one record at a time

Dim global which as C
tbl = table.open("customer")
tbl.index_primary_put("Cust_No")
tbl.fetch_first()
while .not.tbl.eof()
  which = tbl.Cust_No
  report.preview("B_Cast_Test")
  tbl.fetch_next()
end while

and change the report filter to

Broadcast_raw->Report_Date >= "10/01/2007" .AND. Broadcast_raw->Report_Date <= "10/02/2007" .AND. Customer->OK_to_bill = .t. .and. Customer->Cust_no = var->which

[Cheryl Lemire]

Re: save pdf report one record at a time

Thanks Stan. Your suggestion made perfect sense to me and I was certain it was going to work .... but, since I am testing using preview mode just to verify that I am getting the proper results ... I added a query prior to previewing the report so that if there are no matching records, it skips that preview and goes to the next record in my customer table.

The following code seems to be looping through the table correctly and providing separate previews for each matching customer.

However, it is not giving me the last matching record. In my testing, when I run my report with the hard coded filter NOT using the which variable for the cust_no value, the report returns correctly with 3 customers in the one report. The following code, however, is only returning the first 2 and not giving me the last customer.

dim global which as c

tbl = table.open("customer.dbf")
tbl.index_primary_put("Cust_No")
tbl.fetch_first()
WHILE .not.tbl.eof()
    which = str(tbl.Cust_No)
    'Check for matching records
    DIM GLOBAL matching_records as N
    matching_records = a5_get_records_in_query( "broadcast-customers.set", "broadcast_raw->Date >= {10/01/2007} .AND. broadcast_raw->Date <= {10/02/2007} .AND. Customer->Ok_To_Bill=.t. .AND. str(Customer->Cust_No)=Var->which" )
    IF ( matching_records > 0 )
        report.preview("B_Cast_Test2")
        tbl.fetch_next()
    ELSE
        END'
    END IF
END WHILE

Any ideas?

Edited: Forgot to mention that the cust_no field is numeric which is why you see the str() in my query. The a5_get_records_in_query filter is exactly the same as the filter I have set in the report.

[Cheryl Lemire]

Re: save pdf report one record at a time

In addition to my last post:

The previous code works in a copy of the db that I made where I stripped down the records to allow for quicker processing during my testing. When I run the same code in the actual db that has tens of thousands of records, I get NO results.

I have tried compacting the actual db and that did not seem to resolve the issue. However, when running the report without the which variable in the filter, I get the proper three records. So this is the same in both db's, the actual and the test db that I made.

Thanks

[Tom Cone Jr]

Re: save pdf report one record at a time

Good morning, Cheryl.

I can't see the reason why the additional search term is causing the query to fail. Perhaps it's a timing issue since you DIM the 'which' variable each time through the loop?

Since your script is stepping through all the customer records and running a set based query for each record I wonder if there might be ways to speed this up? You might try creating an inverted set and then query the inverted set to find the 3 reports you need. Each customer record could then be flagged or marked. Then you'd open the customer table, filter for flagged or marked records and step through the 3 records one at a time (generating a separate report for each), instead of stepping through all 10,000.

An even better approach would query the set directly, and then step through the resulting query list one at a time. No inverted set would be needed. Here's an example illustrating the query technique I have in mind. The code is running against the vendors set in AlphaSports and is trying to find all the vendors in Massachusetts who have products with descriptions that begin with the letter "F".

stbl = table.open("vendors.set")
query.order = ""
query.options = "MT"
filter = "trim(vendor->state_region) = 'MA' .and. left(product->description,1) = 'F'"
query.filter = "crosslevel(" + quote(filter) + ")"
sndx = stbl.query_create()
?sndx.records_get()
= 1

sndx.drop()
stbl.close()

It seems to me that once you run a query like this against your set, you could use the pointer to the filtered set (stbl in this example) to step through the records you want to print reports for.

[Stan Mathews]

Re: save pdf report one record at a time

WHILE .not.tbl.eof()

should be

WHILE .not. tbl.fetch_eof()

tbl.eof() occurs when the last record is encountered so it is not processed
tbl.fetch_eof() occurs when you attempt to fetch past the last record

[Cheryl Lemire]

Re: save pdf report one record at a time

Hi Stan,

Thanks, that solved the problem with my not getting the last record. That will teach me to just copy/paste somebody's code without actually looking at it :)

I still have the second problem unfortunately. That being, the code now works great in my stripped down test db, but returns NOTHING in the actual db. I have rebuilt the indexes on all tables and packed the 3 tables involved in the set. I have also compacted the database with hopes this would resolve the live db issue. None of these things resolved the problem.

I am thinking that there is a record or two that would be causing the problem in the live db, but I have no idea what to even look for in the records.

Any ideas?

Hi Tom,

I was trying to play around with your idea as well but keep getting an incorrect data type error when substituting your code with my filter. I tried the following three filters below:

dim stbl as p
stbl = table.open("broadcast-customers.set")
query.order = ""
query.options = "MT"
filter = "(between(Date,ctod(\"10/01/2007\"),ctod(\"10/02/2007\")) )  .and. (Customer->Ok_to_bill = .t.  )"
query.filter = "crosslevel(" + quote(filter) + ")"
sndx = stbl.query_create()
?sndx.records_get()
ERROR: Argument is incorrect data type

dim stbl as p
stbl = table.open("broadcast-customers.set")
query.order = ""
query.options = "MT"
filter = "broadcast_raw->Date >= {10/01/2007} .AND. broadcast_raw->Date <= {10/02/2007} .AND. Customer->Ok_To_Bill=.t."
query.filter = "crosslevel(" + quote(filter) + ")"
sndx = stbl.query_create()
?sndx.records_get()
ERROR: Argument is incorrect data type

dim stbl as p
stbl = table.open("broadcast-customers.set")
query.order = ""
query.options = "MT"
filter = "(between(broadcast_raw->Date,ctod(\"10/01/2007\"),ctod(\"10/02/2007\")) )  .and. (Customer->Ok_to_bill = .t.  )"
query.filter = "crosslevel(" + quote(filter) + ")"
sndx = stbl.query_create()
?sndx.records_get()
ERROR: Argument is incorrect data type

Thanks

[JohnZaleski]

Re: save pdf report one record at a time

I'm in total agreement with Tom let's say you establish a pointer to a queried group of records "QP"
QP.fetch_first()
while .not QP.fetch_eof()
found_one = QP.recno()
query.filter = "recno() = " + alltrim(str(found_one))
::Report.SaveAs("your_name",PDF",query.filter
qp.fetch_next()
end while

[Tom Cone Jr]

Re: save pdf report one record at a time

Cheryl, it's hard to debug your query filter expression without sample tables, but I would think something like this would work just fine

filter = "between(broadcast_raw->Date, {10/01/2007}, {10/02/2007})  .and. Customer->Ok_to_bill"
query.filter = "crosslevel(" + quote(filter) + ")"

[Cheryl Lemire]

Re: save pdf report one record at a time

John, I too agree which is why I am playing with that code now.

Tom,

I am still awaiting permission from the owner to upload my stripped down version to provide a sample. As you know, my BIGGEST problem in Alpha has ALWAYS been with my FILTERS. You would think by now I would get it :(

The following does not work either:

stbl = table.open("broadcast-customers.set")
query.order = ""
query.options = "MT"
filter = "between(broadcast_raw->Date, {10/01/2007},{10/02/2007}) .AND. Customer->Ok_To_Bill=.t."
query.filter = "crosslevel(" + quote(filter) + ")"
sndx = stbl.query_create()
?sndx.records_get()
ERROR: Argument is incorrect data type

[Tom Cone Jr]

Re: save pdf report one record at a time

Cheryl, how about the expression that I suggested? :)

[Cheryl Lemire]

Re: save pdf report one record at a time

Hi Tom,

I did try the one you suggested and it did not work either. But you did not have any value for the ok_to_bill field which needs to be true. Sorry I did not post that, my bad :o

stbl = table.open("broadcast-customers.set")
query.order = ""
query.options = "MT"
filter = "between(broadcast_raw->Date, {10/01/2007}, {10/02/2007})  .and. Customer->Ok_to_bill"
query.filter = "crosslevel(" + quote(filter) + ")"
sndx = stbl.query_create()
?sndx.records_get()
ERROR: Argument is incorrect data type

[Tom Cone Jr]

Re: save pdf report one record at a time

Cheryl, break the expression down and test each half separately. Use a script, don't work exclusively in the interactive editor unless you verify that all statements are active.

We don't need the app. We don't need the real data. We just need the tables and the specifications for the set.