Alpha Video Training
Results 1 to 23 of 23

Thread: Calc field on Report referenced in script

  1. #1
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Calc field on Report referenced in script

    Is there a way to reference a field that has been calculated in a report via a script? I have a report that produces a closing balance which I want to place inside a table via a script. Can this be done? I need a way to actually reference the name of the calculated field in the report. Thanks for any help.

  2. #2
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Calc field on Report referenced in script

    Open the report in design mode.

    Click on the object of the calculated field on the report

    Open the object explorer - view - object explorer

    Click Current Object

    Click on value in the right window

    Click Copy to Keyboard

    Choose Property Name - relative

    Now the clipboard can be pasted into a script.

    You may be able to use scripting within the report.
    Click the menu Report - Section Events - pick the proper section and script a write to a table as needed.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  3. #3
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    Al,
    Boy was that fast. I will try that soon and see if that works for me. Thank you for your incredibly quick reply.

  4. #4
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Calc field on Report referenced in script

    Quote Originally Posted by foxxrunning View Post
    Al,
    Boy was that fast. I will try that soon and see if that works for me. Thank you for your incredibly quick reply.
    Lucky timing.

    You may want to use a function called from one of the report events passing the value(s) needed.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    Do you mean something like the onprint event? The two thing you have already suggested seem like they both are what I want. Haven't been able to get to alpha yet to try. Will do so tomorrow. Thanks again.

  6. #6
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,200

    Default Re: Calc field on Report referenced in script

    Hi David,
    Did you not already ask this question in another thread?
    https://forum.alphasoftware.com/show...nt-from-report
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  7. #7
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    I think I might have asked a similar question, but I wasn't able to use it and revised the question somewhat.

  8. #8
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Calc field on Report referenced in script

    Try this in the Report Section Detail OnRecord event

    Code:
    dim tbl as p
    tbl = table.current()
    tbl.change_begin()
    	tbl.balance = topparent:Baldue.value 
    tbl.change_end()
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  9. #9
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    Sounds Perfect. Thanks.

  10. #10
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    Have tried all of the suggestions in various combinations, and nothing I have written affects the opening_balance field in my table. On my report which is based upon a set of three different tables, the opening_balance field is the parent table. Using the topparent:(name of calc field) = parenttable.field does not change the field. I think Alpha is confused because the topparent: designation doesn't know which table to place the change into. Hope this is descriptive enough. What am I missing re: topparent and how might I get it to point to the appropriate table? Thanks for all your help.

  11. #11
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Calc field on Report referenced in script

    Quote Originally Posted by foxxrunning View Post
    Have tried all of the suggestions in various combinations, and nothing I have written affects the opening_balance field in my table. On my report which is based upon a set of three different tables, the opening_balance field is the parent table. Using the topparent:(name of calc field) = parenttable.field does not change the field. I think Alpha is confused because the topparent: designation doesn't know which table to place the change into. Hope this is descriptive enough. What am I missing re: topparent and how might I get it to point to the appropriate table? Thanks for all your help.
    David

    You are not following Robin's suggestion. It works.

    Your example is setting the value in the object of the report.

    Her example is setting the value in the primary table.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  12. #12
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Calc field on Report referenced in script

    And I followed Al's instructions. (post #2) using David's transdupe table. Although I did make some changes to that table...it was nothing that affected the report.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  13. #13
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    This is the code that I put into the report section detail onrecord event.

    dim tbl as p
    tbl = table.current()
    tbl.change_begin()
    tbl.Opening_Balance = topparent:BalanceDue1.value
    tbl.change_end()

    It did not make any change to my primary tables field "opening_balance". I cannot figure out what I am doing incorrectly. Thank you all for helping.

  14. #14
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Calc field on Report referenced in script

    Using the sample table Mike created in your other post (link in post 6 above), the report is based on a set
    and there is a group being used to get the balance, therefore the section event you need would be
    for the group PID OnRecord - which is where I put this script.

    dim tbl as p
    tbl = table.current() 'patients
    tbl.change_begin()
    tbl.bal_due = topparent:Cf_bal.value
    tbl.change_end()

    (the sample table I was using above belonged to a different thread - sorry about that...)
    Last edited by MoGrace; 08-17-2019 at 12:26 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  15. #15
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Calc field on Report referenced in script

    Quote Originally Posted by foxxrunning View Post
    This is the code that I put into the report section detail onrecord event.

    dim tbl as p
    tbl = table.current()
    tbl.change_begin()
    tbl.Opening_Balance = topparent:BalanceDue1.value
    tbl.change_end()

    It did not make any change to my primary tables field "opening_balance". I cannot figure out what I am doing incorrectly. Thank you all for helping.
    Is opening_balance defined as a user entered field or a calculated field?
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  16. #16
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    Opening_Balance is a user entered field which I had hoped to be filled by a calculated field on the report to be used on the next report as (of course) the opening balance. Not sure how good that is in terms of bookkeeping rules, but it was convenient to my way of thinking so I tried it.

  17. #17
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    Robin
    In line 4 of this code, what should my code be realizing that my opening_balance would replace tbl.bal_due in your code?

    1 dim tbl as p
    2 tbl = table.current() 'patients
    3 tbl.change_begin()
    4 tbl.bal_due = topparent:Cf_bal.value
    5 tbl.change_end()

  18. #18
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Calc field on Report referenced in script

    Quote Originally Posted by foxxrunning View Post
    Opening_Balance is a user entered field which I had hoped to be filled by a calculated field on the report to be used on the next report as (of course) the opening balance. Not sure how good that is in terms of bookkeeping rules, but it was convenient to my way of thinking so I tried it.
    David

    Something else is going wrong.
    Please post a working sample with irrelevant data in it.

    Or send one privately.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  19. #19
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    Al I am not sure I know how to do what you request. You are correct that this does not work for me, but I think that if I were to better understand line 4 of the code above, I could get the change I needed. For example, I do not understand what the CF in this line means: topparent:Cf_bal.value I think I understand that the topparent points to my parent table "Patients" and the bal_value is the value in the calculated field. If I were clearer about the syntax of that statement I might be able to amend what I have here. What would you need if I were to post a working sample?

  20. #20
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,366

    Default Re: Calc field on Report referenced in script

    For the life of me, I cannot understand why you want a Calculated Report Field to update a Table.
    Is there a reason you don't want to handle the data in the Tables?
    If you calculate on a Form, you can do the same in a Table.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  21. #21
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Calc field on Report referenced in script

    Since you did not provide us with a sample, I used Mike's sample - did you look at it?
    cf_bal is the calc field defined on the report that gets a subtotal of the table field in the group for PID:

    cf_bal = total(Trans->Amount,grp->Pid,grp->Detail)

    If you have a similar calc field on your report placed in the group footer - that is the value
    you need for your opening_balance in line 4

    tbl.opening_balance = topparent:Cf_bal.value

    hope that helps...not knowing what your table looks like makes this a guessing game
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  22. #22
    Member
    Real Name
    David Gilbert
    Join Date
    Dec 2006
    Location
    Nanuet New York
    Posts
    92

    Default Re: Calc field on Report referenced in script

    Thank you all for your suggestions. After attempting all of the examples it was pointed out to me by Al Buchholz that I did not have a pointer to my primary table in one of the calculated fields. By placing a tablename->before the Opening Balance field, everything worked. Thank you all for your suggestions and help.

  23. #23
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Calc field on Report referenced in script

    Glad you got it working
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

Similar Threads

  1. Calc field on a report
    By Clunes in forum Alpha Five Version 10 - Desktop Applications
    Replies: 4
    Last Post: 05-08-2010, 12:19 PM
  2. Report Calc Field
    By Mortie in forum Alpha Five Version 5
    Replies: 1
    Last Post: 12-30-2003, 10:57 AM
  3. calc field in report?
    By Ludwig Beil in forum Alpha Five Version 5
    Replies: 2
    Last Post: 11-27-2002, 07:31 AM
  4. Calc Report Field
    By Thomas Holt in forum Alpha Five Version 4
    Replies: 4
    Last Post: 06-19-2001, 08:35 AM
  5. How is a Logical Field Referenced?
    By forskare in forum Alpha Five Version 4
    Replies: 5
    Last Post: 12-30-2000, 08:48 AM

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
  •