Alpha Video Training
Results 1 to 8 of 8

Thread: Creating a temp table for a report

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

    Default Creating a temp table for a report

    I have this huge set where all of a client's data is displayed on a form that various reports can be run from.
    But now they want a report that will show checkboxes based on which fields have entries from the different tables.

    So eg, say a client receives food stamps and social security. In the relevant table amounts are entered, but in this
    report, a check box should appear instead. And that is just 2 fields to be checked both from the same table.
    I have about 8 tables and 50 fields to search.

    I could use some ideas on how to go about doing this...with as little user interaction as possible!
    Robin

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

  2. #2
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,000

    Default Re: Creating a temp table for a report

    Can be done. I created a set with 12 tables and then created a temp separately with exact fields for printing a bank contract. Then went and filled the fields using an operation from a set. Made the report based on the temp table which never got deleted and printed the contract with Crystal Reports due to alpha not being able to. Used join, I think
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

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

    Default Re: Creating a temp table for a report

    Hi Robin,
    So 1) how do you want the end report to be grouped - by person with details of their 50 fields, or other? 2) Do you want to have all 50 fields displayed and those that have values a checkbox marked, or just the fields that have values show on the report?
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

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

    Default Re: Creating a temp table for a report

    Hi Robin,
    Fun exercise. Here is one way (see attached). Lot's of other possibilities depending upon what the requirements for your outcome report.
    Attached Files Attached Files
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

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

    Default Re: Creating a temp table for a report

    Thank you Mike, I will review and get back to you...
    Robin

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

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

    Default Re: Creating a temp table for a report

    This is an example of the data I want to pull from the tables & fields listed. Notice some of the fields do not have table-fields.
    This is the data that will then be presented to the user to review and select or fill in the remaining blanks (if any). This temporary table will then
    be posted as one record linked to the client and the report will be based on that table. The report will show all the fields.
    Other fields not shown will provide the linking data. I have the post completed since I can map on TALLY_ID.

    dataexample1.png
    Last edited by MoGrace; 05-06-2019 at 01:21 PM.
    Robin

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

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

    Default Re: Creating a temp table for a report

    Reviewing Mike's example gave me an idea on where to start.

    First I made a new set with just the tables I need to get the fields to test.
    Next I created a Copy operation to create the fields from those tables for a one record TmpTally table.
    I also added an Update to clear the TALLY and SEL_FLAG fields and a Post operation to reset the link fields in the Tally table.
    (I chose this instead of Zap & Append because it was easier.)

    From the TmpTally table I could get a field list to manipulate and map to the Tally records I want to update which is displayed to the user to complete
    since in some of the Tally records the fields are going to be blank.

    Both tables are sorted on the FLDNAME field which helped fetch_find() work faster I think.
    I added a unique index to Tally to remove any blank FLDNAME records except for the 1st which I could test for and skip.
    There are still a few mapping issues to tweak but here is the 'working' script - which is open to improvement!:

    Code:
    'Date Created: 07-May-2019 01:35:59 PM
    'Last Updated: 07-May-2019 02:04:34 PM
    'Created By  : Robin Q
    'Updated By  : Robin Q
    'Selected fields from the CL_TALLY.Set (5 tables) are copied to TmpTally for the current record.
    
    dim global Get_iKey as c      'global var is used to filter the operations
    dim hdr as p		       'Int_hdr
    dim tbl as p		       'Tally
    dim tmp as p		       'TmpTally
    dim fldname as c
    dim flds as c
    dim tfld as c
    dim tname as c
    dim xlist as c
    dim cnt as n = 0
    dim errcnt as n = 0
    dim fcnt as n = 0
    dim rec as n = 0
    dim xn as n = 1
    dim commit_flag as L
    dim flag as L
    
    'exclude character fields so only logical or numeric fields are in the list
    xlist=<<%str%
    intake_id
    indate
    memb_id
    name_str
    cs_wrkr
    %str%
    
    hdr = table.current()
    Get_iKey = hdr.intake_id
    copy.run("CL_Tally to TmpTally")      'not silent to show user a record was created
    xbasic_wait_for_idle(.15)
    update.run_silent("Clear Tally")
    xbasic_wait_for_idle(.15)
    post.run_silent("Int to Tally")
    
    tname = "TmpTally"
    flds = table.external_field_name_get(tname,"n")
    flds = word_subtract(flds,xlist,crlf())
    flds = sortsubstr(flds,crlf())
    fcnt = line_count(flds)
    
    tbl = table.open("Tally",FILE_RW_SHARED)
    tbl.index_primary_put("FLDNAME")
    tmp = table.open(tname,FILE_RW_SHARED)
    'tmp s/only contain 1 record
    tmp.fetch_first()		
    for xn = 1 to fcnt
    	fldname = word(flds,xn,crlf())
    	if fldname = "" then
    		goto SKIPIT
    	end if
    	tfld = tname+"->"+fldname    'this was the format isblank() would accept
    	flag = .not. isblank(tfld)	'reverse the result to post, if isblank returns true then flag is false
    	rec = tbl.fetch_find(fldname)		
    	if rec < 0 then
    		'matching record not found
    		goto SKIPIT
    	end if
    	commit_flag = .t.
    	on error goto ERRMSG
    	tbl.change_begin()			
    	if tbl.typeflag = "L"
    		tbl.sel_flag = flag
    	else 
    		tbl.tally = eval("tmp."+fldname)
    	end if
    	tbl.change_end(commit_flag)
    	if commit_flag then
    		cnt = cnt+1
    	end if
    SKIPIT:
    next
    
    ENDIT:
    tbl.close()
    tmp.close()
    Tln("Tally records posted "+Get_iKey,""+cnt,"Scripts")
    End
    ERRMSG:
    commit_flag = .f.
    script_play_local("ERRMSG")
    on error goto 0
    errcnt = errcnt+1
    'stop error loop at 2
    if errcnt < 3 then
    	RESUME NEXT
    else
    	RESUME ENDIT
    end if
    Last edited by MoGrace; 05-08-2019 at 12:45 PM.
    Robin

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

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

    Default Re: Creating a temp table for a report

    Sample form to display to user to complete before using buttons above. The grayed out fields are only visual to show non-entry fields.

    tally_form.png
    Robin

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

Similar Threads

  1. Creating Report based on a Crosstab table
    By sgerber in forum Alpha Five Version 11 - Desktop Applications
    Replies: 3
    Last Post: 09-16-2012, 03:47 PM
  2. Creating Report from Table Sets
    By Zradene in forum Alpha Five Version 9 - Desktop Applications
    Replies: 16
    Last Post: 03-03-2009, 04:33 AM
  3. Replies: 17
    Last Post: 10-31-2007, 09:00 PM
  4. Arrays vs Temp Table
    By G Gabriel in forum Alpha Five Version 7
    Replies: 13
    Last Post: 12-16-2006, 11:20 AM
  5. Help creating Temp table
    By Raymond Lyons in forum Alpha Five Version 7
    Replies: 15
    Last Post: 12-04-2006, 08:50 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
  •