save pdf report one record at a time

[Cheryl Lemire]

Re: save pdf report one record at a time

here you go :)

[Tom Cone Jr]

Re: save pdf report one record at a time

Cheryl, how about some data in the tables?

I notice that your set structure is different from what I've been using. You don't have any one to many links, and you're filtering for values in a grandchild table instead of a child table. Not sure if that will prove significant, but I would think it might. will take another look when some test data is provided. -- tom

[Tom Cone Jr]

Re: save pdf report one record at a time

Now that I understand your set structure I see that crosslevel() is not needed. This should work for you, unless I've screwed up entering dummy data here.

filter = "customer->ok_to_bill .and. between(date, {01/01/2007}, {01/31/2007})"
query.filter = filter

Notice that the table name prefix is not needed when specifying the "date" field. This is because the date field is in the primary table of the set. The customer table is the grandchild in your set structure. Prefixing the field name with the table name is required for it.

[Cheryl Lemire]

Re: save pdf report one record at a time

Hi Tom,

I have not yet tried your expression. My question is: why don't you have the 'true' for this:

customer->ok_to_bill
Thanks

[Tom Cone Jr]

Re: save pdf report one record at a time

why don't you have the 'true' for this:

customer->ok_to_billr

Basically, I'm lazy. This is a logical field. It's not necessary to include the equals and the dot T dot.

For false I usually do

.NOT. customer->ok_to_bill

I hardly ever do

customer->ok_to_bill = .F.

because I can never remember how Alpha handles empty logicals.

The fieldname by itself will return true if it holds a T or Y.

.NOT. fieldname will return true if the field is empty OR false.

[Cheryl Lemire]

Re: save pdf report one record at a time

I LOVE LAZY :)

I also get confused at times with the logical fields. I like your thought process and I like learning something new. I never realized the default and always just 'assumed' that I had to put something there. Thanks.

I will try the new filter shortly and get back to all with my results.

[Cheryl Lemire]

Re: save pdf report one record at a time

OK. Attached is a new zip with sample data in it. If you go to the mnu_master form you will find 4 buttons. The 2nd button returns the desired results in this test db, but returns nothing in the actual db with all the records in it. This is using my original code and not Tom's idea.

The 4th button is my HORRIBLE and FAILED attempt to use Tom's idea. One of the problems I am having with this is how to only return the three customers one time. My attempt seems to be returning the report for every record.

[Tom Cone Jr]

Re: save pdf report one record at a time

Cheryl, what is your goal ? How many reports are you trying to print / save ?

Using your current three level set there are 161 records in the sample that meet your filtering criteria. Even though the customer table only has 3 records in it, each of those records is linked to multiple ID table records, and each of the ID table records are linked to multiple broadcase_raw table records. Is it possible you have your set upside down?

I'm afraid it's back to square one and recommend you build a simple form based on the set. Put controls on the form to show only the link field values from all three tables. Don't use browses. Step through the set from top to bottom, one record at a time. Watch how the link field values change. this is what the filter and the report are seeing. I'll bet it's different than you were expecting.

[Tom Cone Jr]

Re: save pdf report one record at a time

On further exploration I see that the report which shows the three customer records (b_cast_test) is not a typical report. It has no details band. This explains why you're not seeing all 161 records in the report. You're just seeing the group and subgroup headers and footers and they get printed only when the groups break.

I fear we've been working this afternoon on the wrong problem.

I'm going to stop.

[Cheryl Lemire]

Re: save pdf report one record at a time

Hi Tom,

Now you have me confused. The set and report return the needed and expected results as they are now. This particular report was designed to purposely eliminate the detailed items and only provide a summary for each customer. There is another report that does show the same data including all of the detailed line items.

I'm not sure why you think we have been working on the wrong problem. My problem was this: instead of getting one report for all the records, I wanted a separate report for each customer.

Currently the user is printing three different reports and collating them together manually for the various customers. They are then either mailing them or manually faxing them. For the email ones they go through one customer at a time and drag the 3 different reports into their email to send.

We decided that we could increase production if I were able to save a separate pdf report for each customer for the three different types of reports that we are working with, using a naming convention that will work for us, and then use the pdf_append() function to combine the 3 reports into one.

From there I will be able to email/fax/print the single combined report to each customer based on their preferences and automate it within alpha to handle the email/fax options. The ones that need to be snail mailed would still get printed, but the manual collation between the three reports would not be needed.

This will cut down drastically the manual labor and time needed for them to process the three reports. Also, not all customers will be getting all three reports and two of the reports have two options, one with the details and the other option being the summary only which is the sample I have in the test db uploaded here.

On my mnu_master form, the b_cast_test2 button gives me the desired result. The problem with this is that it is NOT working in the actual db and I have no idea why. I am guessing that there is one or more records in the set tables somewhere that might have garbage data or something causing it to return no matching records to the filter.

All I did in that code was open the customer table and begin looping through it to capture the cust_no value. I then ran a query on the set for the specific records I was looking for and if it found one that matched, preview my report (which in the real app will actually be saved as a pdf). I am previewing solely to make my testing easier. The query here is using the same filter as the one in the report itself, LESS the cust_no=var portion. The cust_no=var in my report filter gives me one report for the customer and then I go back and retrieve the next cust_no in the loop.

In the live db, this actually goes amazingly fast even though there are a lot more records to deal with. But the fact that I get NO report with this code in the actual db tells me that the set query I am using is returning '0' records which makes no sense to me. At worst it should return at least the same three reports that I get in the sample db.

That is when I tried to switch to your thought process of querying the set first and then looping through the resulted query to print just one report for each cust_no. But I cannot figure out how to loop through the queried records AND the customer table to grab my cust_no value.

[Tom Cone Jr]

Re: save pdf report one record at a time

Cheryl,

I've modified the 4th button to incorporate my approach, though you may not recognize it. :)

To overcome conflicts caused by stepping through the filtered set, while running a report based on the same set on each iteration, I took a different approach. After querying the set for the matching records, I then extracted the unique customer id numbers and put them in a simple array. I then step through the array and print the report for each element. Clean, neat, simple. Let me know if you have questions.

I modified the report layout slightly to show the customer -> cust_id field value in a group header. You might want to delete it now, but I needed to see what the cust_id field value was and couldn't find it in the existing layout.

-- tom

[Cheryl Lemire]

Re: save pdf report one record at a time

Tom,

Thanks a million. Your code works in the actual db. Here are my questions:

1. What is it in your code that makes it work in the actual db that is not in my code? Since your code is working, that tells me that the data in the set tables is ok.

2. I would have never gone down the array approach as I know NOTHING about arrays and every time I try to work with them, I just get migrains :) Can you explain the following to me a little more please, it seems like ending with next means it will go on forever L(

' now print / preview the reports
' do this by stepping through the array from element 1 to the last element
dim global which as C
for n = 1 to cust_list.size()
which = cust_list[n]
report.preview("B_Cast_Test4","","",.t.,.t.)
next

[Tom Cone Jr]

Re: save pdf report one record at a time

for n = 1 to cust_list.size()
     which = cust_list[n]
     report.preview("B_Cast_Test4","","",.t.,.t.)
next

Check the help file topic for "For ... Next". You'll find a pretty decent discussion. Here, the .size() method for the array "cust_list" returns the number of elements in the array. So this block of code says to Alpha Five:

1) assign the number 1 to the variable "n"
2) assign the element in the "n"th position in the array to the variable "which"
3) preview the report
4) go back to step (1) and do it all over again, but this time increment the
value of "n"

The loop will continue until "n" reaches the size of the array. i.e. until the number in "n" equals the number of the last element in the array. The last element gets processed, and then the loop ends.

If the array has 3 elements in it. Then "n" has the value 1 the first time through the loop. You retrieve the value stored in the first position (the first element) in the array by putting the index to that element in the brackets, like this: "cust_list[1]" This would return the value stored in the first element of the array. In the script Alpha sees the variable 'n' and substitutes the actual value so that "cust_list[n]" is the same as "cust_list[1] the first time through the loopo.

The second time through the loop 'n' equals 2 and pulls the second element from the array by using the same process of variable substitution. The third time through the loop 'n' equals 3 and the 3rd element is pulled from the array. Since 3 equals the .size() of the array the loops ends after the 3rd element is processed.

Hope this helps.

-- tom

[Cheryl Lemire]

Re: save pdf report one record at a time

I can't thank you enough Tom. As usual, you have helped tremendously and I have learned so much. Your explanation is GREAT and has given me a better understanding of arrays and their usefulness.

I don't use for loops often and seeing it with an array was a little confusing to me since my array knowledge stinks.

I can definitely see how your method in resolving this issue is effective and now that I have a better understanding of what is happening, I may actually be able to use it again in the future.

Thanks again for all your help. I hope that this explanation helps others as much as it did me.

[Tom Cone Jr]

Re: save pdf report one record at a time

What is it in your code that makes it work in the actual db that is not in my code?

Cheryl, I don't know why your script is failing. I am suspicious of two things:

a) I think it's a mistake to DIM a variable inside a tight loop like you've done. I would prefer to DIM the variable outside the loop. There's no reason to reinitialize it each time through the loop. I don't know how variables are discarded and re-initialized internally, but it's certain to involve throwing away the value in the variable, deleting the variable from memory, and then re-creating it. That's a lot to do each time you fetch a new customer record. If it were my code I'd DIM the variable before the While ... End While loop starts.

b) In my code I used parameters passed to the report.preview() method to make certain the preview was modal. You do not. I don't know if that's critical or not, but I did it in order to try to freeze the script while each report is on display.