What report delegate can I use to create a table/cursor for the report to run

[StevenH]

Thanks Yvonne for your help with my first report!

I'm working on a second tabular report that is similar but is condensed. My requirements for this report seems to be beyond the report builder's capabilities.

My code needs to select certain fields from a table, grab a field from a child table, accumulate first names into a firstnames field when they are of the same household and have the same last name. So, if I have records like:

These records make up one household

Parent Table Child Table
FirstName LastName RAaddress VoteHistory
1. John Doe 123 Any St 'VVV'
2. May Doe 123 Any St 'VVVVV'
3. Sally Doe 123 Any St 'FFF'

FYI, 'V' = voted, 'F' = failed to vote. The more "V"s the better.

I want to capture the best votehistory for the household. I'm ok with writing the code in VFP to create a report table for this report. My issue is where to put the code. I'm thinking in the Before Generate delegate. Also, I need to know where to put the code that closes the cursor/table and returns to the Parent/master table. Alternatively, I could leave it open and just overwrite it as needed with the Set Safety On/Off command. But to save resources, I prefer to close and erase it.

My report would look like this:

RAddress Last FirstNames Best Votehistory Otherfields
123 Any St Doe May, John, Sally 'VVVVV' blah, blah

Thanks!
Steve

[yvonne.milne]

Hi Steve,

The 'Before Generate' delegate is called before the database is opened, the 'Before Open' is called after the database is opened, but before the table is opened. So, you could use either of these to populate a table with the required data.

The 'After Generate' delegate is called after the report has been run and the table closed, so could be used to select another workarea/cursor or delete/drop files that are no longer required.

Regards,

Yvonne

[StevenH]

Thanks Yvonne!!
Steve

[StevenH]

Hi Yvonne,

I created my code in the "Before Open" delegate and tested it in VFP. I have it creating a table named, "WalkingList_Temp.dbf".

In the report space, I had to set a table in the Data Source tab even if it is just an empty table; otherwise I cannot access the delegates. I set the table to WalkingList_temp. That allowed me to access the Before Open delegate where I have my code to select certain fields from Voter_Master and also a field from a child table. The table is now created as I need it with records.

However, nothing shows up in the report grid. It seems like the WalkingList_Temp.dbf is not being accessed or has to be refreshed.

Any thoughts of what I need to fix?

Thanks,
Steve

[yvonne.milne]

Hi Steve,

Are you getting any errors reported (drive:\lianja\errors\) or if you run with SET DEBUG ON (drive:\lianja\debug\)?

I am not seeing any issues with this if the table is populated in the 'Before Open'.

Regards,

Yvonne

[StevenH]

Hi Yvonne,

There were some errors. It seems like some of the VFP SQL select features are not supported in lianja. I had errors reported on the select statement and when I removed the following, those error msgs went away:

select rep+pre as precinct, space(75) as name, raddress,....

I changed rep+pre as precinct to, rep, pre and dropped the name part of the statement. Then did an alter table to add those fields and populate them.

However, while those error msgs no longer appear I also had and error on:

if not used('voter_master')
select 0
use voter_master
else
select voter_master
endif

The error msg was on the, use voter_master with a msg that the file was already opened. Seemed like the Used() function was not working for some reason. Since the table was already opened, I dropped the if/endif to test if the table was already opened and that error went away.

The sql select to a table still does not seem to be working but I'm not getting an error msg. But, I'm not sure I'm working the debugger correctly.

In my code I have, "set debugger on". That doesn't seem to be doing anything. If I click on the debug icon in the left panel, there is nothing there. I was getting a debug file in \lianja\debug\ that showed some errors but after the above, no errors are shown. See attached file. I don't know what all the lines mean but the parts that showed errors are no longer appearing.

I was getting a error file but after the above corrections, none have been generated.

I have also attached a screenshot of the console. It shows there is an error in the Before Open as well as the After Generate which is where I do a bit of clean up after the report is supposedly generated. But, there is nothing specific, just notes that there is an error.

FYI, the After Generate code is to select WalkingList_temp and zap it. I'm not worrying about that for now so I haven't attached anything on it.

I have attached my code in the "Before Open" delegate. Perhaps you can see something I'm overlooking.

Also, I watched a tutorial that showed some cool usage of the app inspector with the console but I'm unable to reproduce that. Can you tell me how to do it?

Thanks,
Steve

I do have a lianja error file in :\lianja\error\
I have attached a screen shot of it

[barrymavin]

Hi Steve,

You can debug your report delegate code in Lianja.

In the console workspace.

type:

ed yourtest

Then paste your delegate code into the editor.

Then click the "Run" icon in the headerbar.





Once you have your code working you can cut and paste it back into your report delegate.

Your missing the table in the SQL SELECT FROM clause.

You don't need to open tables manually. Just include them in the SQL SELECT FROM clause.

[StevenH]

Thanks Barry!

FYI, I originally had the table in the FROM clause. I must have accidentally removed it as I tried to isolate the error.

Steve

[StevenH]

Thanks for the tip on debugging!

Turns out the order by clause is what got me again. It doesn't work.

I removed the order by clause and turned it into a Sort clause and my sql statement and sort works.

Thanks again!
Steve

[StevenH]

For anyone who is reviewing this post. Turns out the Rep+Pre as Precinct works okay so I added that back.
Not sure about the, select space(75) as name. I didn't try putting that back in.

To try and isolate the problem, I cut out chunks of the select statement and apparently cut out the Rep+Pre as Precinct along with the order by clause. I was so sure the order by was good that I assumed it was the former clause that was the problem. Turns out:

In release 8.05 the order by is buggy and is expected to be corrected in 8.1. That was the issue, when I removed that my sql select command worked fine.

That forced me to select to a temp file and then sort the temp file to my "final" file.

As usual the folks at Lianja were super helpful. Gotta love these folks.