Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Senior Member
    Join Date
    Nov 2014
    Location
    Kailua, Hawaii
    Posts
    104

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

    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

  2. #2
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,829
    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

  3. #3
    Senior Member
    Join Date
    Nov 2014
    Location
    Kailua, Hawaii
    Posts
    104
    Thanks Yvonne!!
    Steve

  4. #4
    Senior Member
    Join Date
    Nov 2014
    Location
    Kailua, Hawaii
    Posts
    104
    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

  5. #5
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,829
    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

  6. #6
    Senior Member
    Join Date
    Nov 2014
    Location
    Kailua, Hawaii
    Posts
    104

    Report Workspace create table in "Before Open" not working

    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
    Attached Images Attached Images  
    Attached Files Attached Files
    Last edited by StevenH; 2023-01-19 at 20:58.

  7. #7
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    7,140
    Blog Entries
    22
    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.

    Click image for larger version. 

Name:	Screen Shot 2023-01-20 at 10.57.33 AM.jpg 
Views:	171 
Size:	68.3 KB 
ID:	2884

    Click image for larger version. 

Name:	Screen Shot 2023-01-20 at 10.57.54 AM.jpg 
Views:	138 
Size:	81.4 KB 
ID:	2885

    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.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  8. #8
    Senior Member
    Join Date
    Nov 2014
    Location
    Kailua, Hawaii
    Posts
    104
    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

  9. #9
    Senior Member
    Join Date
    Nov 2014
    Location
    Kailua, Hawaii
    Posts
    104
    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

  10. #10
    Senior Member
    Join Date
    Nov 2014
    Location
    Kailua, Hawaii
    Posts
    104
    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.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us