Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Programming Puzzle 5 - Nested loops

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Programming Puzzle 5 - Nested loops

    Alright boys and girls, the preliminaries are over. Let's turn our attention
    to some actual data.



    Puzzles and Problems

    Beginning Xbasic Programming

    Puzzle 5 - Nested loops

    I've attached a sample database, containing the invoice_items table from AlphaSports, and an index (CDX) file.

    Write an xbasic script that opens this table, arranges its records in ascending order based
    on the "invoice_nu" field, and then computes the total for each invoice by summing
    the "extension" fields for all records that have the same invoice number. For each invoice
    write the invoice number, the number of different items on the invoice (not the item count,
    but the number of "lines" on the invoice), and the invoice total to the trace window. At
    the end, write the grand total of all the invoices to the trace window.

    Instructions:

    a) you may not use any of the built in summary functions, methods, or operations in Alpha Five. We're doing this one by hand.

    b) you may open the table only once

    c) you may not use any queries or filters



    NOTE: These puzzles and exercises will be studied by beginning programmers. Supply
    comments throughout your script to explain what your script is doing and why. You're
    writing for posterity. Be thorough. Thanks.

    Postscript: I've changed the attachment. It's now a complete database. Unzip the attachment and extract contents to an empty folder. This was necessary because the original attachment did not include supporting dictionary files that would have been necessary. Sorry for the confusion. -- tom
    Last edited by Tom Cone Jr; 06-07-2011, 09:44 AM. Reason: Replace attachment

  • #2
    Re: Programming Puzzle 5 - Nested loops

    Very good puzzle. No, there are no comments; I very seldom, if ever, put comments in my code.

    This code is used in our main database on a daily basis as part of a payment posting function. I have to summarize the claim totals and subtract the amount already paid to display a current amount due.

    Code:
    'Date Created: 07-Jun-2011 11:33:30 AM
    'Last Updated: 07-Jun-2011 05:10:51 PM
    'Created By  : andy
    'Updated By  : andy
    OPTION STRICT
    OPTION ENCRYPTED_TOKENS
    ON ERROR GOTO ERR_HANDLER
    
    DIM vInvoice_ItemsP as P
    
    DIM vOrderC as C
    DIM vFilterC as C
    
    DIM vIndxN as N
    DIM vGrandTotalN as N
    
    DIM arrInvoice[0] as P
    DIM arrInvoice[0].InvoiceC as C
    DIM arrInvoice[0].TotalN as N
    DIM arrInvoice[0].LinesN as N
    
    vInvoice_ItemsP = table.open("invoice_items",FILE_RW_SHARED)
    	vOrderC = "invoice_nu"
    	vInvoice_ItemsP.order(vOrderC)
    	vInvoice_ItemsP.fetch_first()
    	WHILE .not. vInvoice_ItemsP.fetch_eof()
    		vIndxN = arrInvoice.find(vInvoice_ItemsP.invoice_nu, "InvoiceC")
    		IF vIndxN > 0 THEN
    			arrInvoice[vIndxN].TotalN = arrInvoice[vIndxN].TotalN + vInvoice_ItemsP.extension
    			arrInvoice[vIndxN].LinesN = arrInvoice[vIndxN].LinesN + 1
    		ELSE
    			arrInvoice[].InvoiceC = vInvoice_ItemsP.invoice_nu
    			arrInvoice[..].TotalN = vInvoice_ItemsP.extension
    			arrInvoice[..].LinesN = 1
    		END IF
    		vInvoice_ItemsP.fetch_next()
    	END WHILE
    vInvoice_ItemsP.close()
    
    FOR vIndxN = 1 to arrInvoice.size()
    	trace.WriteLn( "Invoice: " + arrInvoice[vIndxN].InvoiceC + " Total: " + arrInvoice[vIndxN].TotalN + " Lines: " + arrInvoice[vIndxN].LinesN)
    	vGrandTotalN = vGrandTotalN + arrInvoice[vIndxN].TotalN
    NEXT
    
    trace.WriteLn("Grand Total: " + vGrandTotalN)
    
    
    END
    '-----------------------------------
    'Error handler
    '-----------------------------------
    ERR_HANDLER:
    	Dim err as N= error_code_get()
    	Dim msg as C= "Error # "+ err + crlf() + error_text_get(err)
    	msg = msg + crlf() + "Script: " + error_script_get()
    	msg = msg + crlf() + "Line #: " + error_line_number_get()
    
    	trace.writeln(msg)
    
    	ui_msg_box("Error", msg, ui_attention_symbol)
    	msg = ""
    	
    	END
    Results:
    Code:
    Invoice: 000001 Total: 949.29 Lines: 6
    Invoice: 000002 Total: 271.61 Lines: 2
    Invoice: 000003 Total: 2629.34 Lines: 6
    Invoice: 000004 Total: 987.17 Lines: 4
    Invoice: 000005 Total: 306.9 Lines: 4
    Invoice: 000006 Total: 184.84 Lines: 4
    Invoice: 000007 Total: 1245.83 Lines: 4
    Invoice: 000008 Total: 240.2 Lines: 2
    Invoice: 000009 Total: 107.1 Lines: 1
    Invoice: 000010 Total: 59.38 Lines: 3
    Invoice: 000011 Total: 194.15 Lines: 4
    Invoice: 000012 Total: 22.33 Lines: 2
    Invoice: 000013 Total: 210.64 Lines: 4
    Invoice: 000014 Total: 270.77 Lines: 3
    Invoice: 000015 Total: 155.35 Lines: 3
    Invoice: 000016 Total: 56.42 Lines: 2
    Invoice: 000017 Total: 100.08 Lines: 2
    Invoice: 000018 Total: 640.06 Lines: 5
    Invoice: 000019 Total: 329.8 Lines: 3
    Invoice: 000020 Total: 367.54 Lines: 3
    Invoice: 000021 Total: 422.36 Lines: 5
    Invoice: 000022 Total: 11.76 Lines: 2
    Invoice: 000023 Total: 45.9 Lines: 1
    Invoice: 000024 Total: 897.76 Lines: 3
    Invoice: 000025 Total: 602.35 Lines: 4
    Invoice: 000026 Total: 910.31 Lines: 6
    Invoice: 000027 Total: 343.71 Lines: 7
    Invoice: 000028 Total: 623.78 Lines: 11
    Invoice: 000029 Total: 400.52 Lines: 2
    Invoice: 000030 Total: 506.5 Lines: 4
    Invoice: 000031 Total: 11.77 Lines: 1
    Invoice: 000034 Total: 950.28 Lines: 6
    Invoice: 000035 Total: 950.28 Lines: 6
    Invoice: 000036 Total: 506.5 Lines: 4
    Grand Total: 16512.58
    Last edited by aschone; 06-08-2011, 09:34 AM.
    Andrew

    Comment


    • #3
      Re: Programming Puzzle 5 - Nested loops

      Andrew, thanks for the response. I was beginning to think maybe the exercise was too difficult for anyone to attempt.

      Your approach was not expected. You use several advanced features of the xbasic language and managed to produce a solution that does not use a nested loop. Darn !

      For the benefit of other readers, here's a summary of your approach (let me know if I've misunderstood something):

      a) At line 17 - 20 you declare an array of pointers ( a "Property" array), containing zero elements

      b) At line 22 - 25 you open the table, and then "order" the table (ascending) by invoice_nu, then you move the record pointer to the top of the sorted list. [ Our fearless scorekeeper, Ignatious A. Pickypicky, thinks you broke the rules of the puzzle here. The tbl.order() method is a form of query, in his esteemed opinion. He would have preferred it if you had simply set the Inv_num index primary, since it already contains a sorted list of index keys. ]

      c) In the code block from line 26 to 37 things get very interesting. Basically, you fetch through the sorted list of records one at a time. After each fetch your script tries to find the current invoice_nu field value in the array. If an element is present that corresponds to the current invoice_nu field value the current extension field value is added to a running total entry in your array (TotalN), and the line counter element in the array is incremented. Otherwise, a new element is added to the array, and the extension field value is stored there, and the line counter starts over at 1.

      Very clever, indeed.

      I'll post my own solution now. You may be surprised at the differences in my approach.

      Thanks again for a useful contribution to this thread.

      Comment


      • #4
        Re: Programming Puzzle 5 - Nested loops

        Here's my solution. It employs a nested loop. The records are arranged in index order, based on the invoice_nu field. This groups all items records for the same invoice together. The code then loops through all the records. When the script sees that the current invoice_nu has changed it "knows" that the last item for the previous invoice has been processed. It then loops through the next batch of records until the invoice_nu field value changes again.

        Code:
        'Date Created: 07-Jun-2011 09:40:06 AM
        'Last Updated: 08-Jun-2011 06:02:59 AM
        'Created By  : Tom
        'Updated By  : Tom Cone Jr
        'Puzzles and Problems
        '
        'Beginning Xbasic Programming
        '
        'Puzzle 5 - Nested loops 
        '
        'An "invoice_items" table and its index file are attached. These came from the AlphaSports sample that ships with Alpha Five.
        '
        'Write an xbasic script that opens this table, arranges its records in ascending order based
        'on the "invoice_number" field, and then computes the total for each invoice by summing
        'the "extension" fields for all records that have the same invoice number. For each invoice
        'write the invoice number, the number of different items on the invoice (not the item count, 
        'but the number of "lines" on the invoice), and the invoice total to the trace window. At
        'the end, write the grand total of all the invoices to the trace window.
        '
        'Instructions:
        '
        'a) you may not use any of the built in summary functions, methods, or operations in Alpha Five. We're doing this one by hand.
        '
        'b) you may open the table only once
        '
        'c) you may not use any queries or filters 
        '
        '
        
        ' Begin by declaring and initializng variables
        dim Items as P 			' will point to the invoice_items table
        dim vc_curr_inv as C = ""		' will hold the current invoice number
        dim vn_inv_tot as N = 0		' will hold be used to accumulate the invoice total
        dim vn_line_count as N = 0		' will hold the line count for each invoice
        dim vn_grand_tot as N = 0		' will hold the grand total
        
        ' Now open the table and set the desired sort sequence
        Items = table.open("invoice_items")	'Note: script assumes table will open
        idx = Items.index_primary_put("Inv_Num")  	'Invoice_nu, ascending, all
        				'NOTE:  this groups all invoice items together
        				'       that have the same invoice_nu field values
        
        Items.fetch_first()			' start at the top of the table
        
        while .not. items.fetch_eof()	' loop through all the records - loop ends when script
        			' tries to fetch past the end of file
        	vn_inv_tot = 0	' clear the accumulator for invoice total
        	vn_line_count = 0	' clear the accumulator for the line count
        	vc_curr_inv = Items.invoice_nu    'get the current invoice number
        	
        	'now fetch through the sorted list of invoice items records, accumulating
        	'the invoice total; but stop when we fetch past end of file, or when then
        	'invoice_nu no longer matches the invoice number being summarized.
        	
        	while .not. items.fetch_eof() .and. vc_curr_inv = items.invoice_nu
        		vn_inv_tot = vn_inv_tot + items.extension	' add current extension to invoice total
        		vn_line_count = vn_line_count + 1	' increment the line counter
                                        ' add current extension value to grand total
        		vn_grand_tot = vn_grand_tot + items.extension		
                                        items.fetch_next()
        	end while
        	
        	'on exit from inner loop vn_inv_tot holds the invoice total
        	'so now we write it to the trace window
        	
        	trace.writeln("Invoice number " + vc_curr_inv + " has " + str(vn_line_count,3) + " lines" +\
        	", and totals: " + str(vn_inv_tot,10,2))
        	
        	' now loop back to top of outer loop, and process next batch of items records	
        	
        end while	
        
        ' on exit of the outer loop vn_grand_tot holds the total of all invoices
        trace.writeln("")
        trace.writeln("Grand total of all invoices: " + ltrim(str(vn_grand_tot,12,2)))
        
        ' cleanup on the way out
        ' remember to close the open table
        
        items.close()
        
        end
        Producing this in the trace window:
        Code:
        Invoice number 000001 has   6 lines, and totals:     949.29
        Invoice number 000002 has   2 lines, and totals:     271.61
        Invoice number 000003 has   6 lines, and totals:    2629.34
        Invoice number 000004 has   4 lines, and totals:     987.17
        Invoice number 000005 has   4 lines, and totals:     306.90
        Invoice number 000006 has   4 lines, and totals:     184.84
        Invoice number 000007 has   4 lines, and totals:    1245.83
        Invoice number 000008 has   2 lines, and totals:     240.20
        Invoice number 000009 has   1 lines, and totals:     107.10
        Invoice number 000010 has   3 lines, and totals:      59.38
        Invoice number 000011 has   4 lines, and totals:     194.15
        Invoice number 000012 has   2 lines, and totals:      22.33
        Invoice number 000013 has   4 lines, and totals:     210.64
        Invoice number 000014 has   3 lines, and totals:     270.77
        Invoice number 000015 has   3 lines, and totals:     155.35
        Invoice number 000016 has   2 lines, and totals:      56.42
        Invoice number 000017 has   2 lines, and totals:     100.08
        Invoice number 000018 has   5 lines, and totals:     640.06
        Invoice number 000019 has   3 lines, and totals:     329.80
        Invoice number 000020 has   3 lines, and totals:     367.54
        Invoice number 000021 has   5 lines, and totals:     422.36
        Invoice number 000022 has   2 lines, and totals:      11.76
        Invoice number 000023 has   1 lines, and totals:      45.90
        Invoice number 000024 has   3 lines, and totals:     897.76
        Invoice number 000025 has   4 lines, and totals:     602.35
        Invoice number 000026 has   6 lines, and totals:     910.31
        Invoice number 000027 has   7 lines, and totals:     343.71
        Invoice number 000028 has  11 lines, and totals:     623.78
        Invoice number 000029 has   2 lines, and totals:     400.52
        Invoice number 000030 has   4 lines, and totals:     506.50
        Invoice number 000031 has   1 lines, and totals:      11.77
        Invoice number 000034 has   6 lines, and totals:     950.28
        Invoice number 000035 has   6 lines, and totals:     950.28
        Invoice number 000036 has   4 lines, and totals:     506.50
        
        Grand total of all invoices: 16512.58
        Last edited by Tom Cone Jr; 06-08-2011, 06:49 AM. Reason: clean up line breaks

        Comment


        • #5
          Re: Programming Puzzle 5 - Nested loops

          Originally posted by Tom Cone Jr View Post
          Our fearless scorekeeper, Ignatious A. Pickypicky, thinks you broke the rules of the puzzle here. The tbl.order() method is a form of query, in his esteemed opinion. He would have preferred it if you had simply set the Inv_num index primary, since it already contains a sorted list of index keys.
          Mr. PickyPicky I was sure I put an apple on your desk when I walked into class today, did that not buy me a little leeway?

          I recently started to use <tbl>.order() in lieu of (query.filter and query.order) and <TBL>.INDEX_PRIMARY_PUT(). Index_Primary_put() can make reading the code more difficult in that you must also have the index definitions available in order to know what a particular index being applied to a table will do. Query.filter and query.order require additional lines of code to establish the settings, apply the settings, and then blank out the settings for fear that a previous query settings be incorrectly applied the next time you do a query. I find these additional lines of code muddy up the script making it more cumbersome to read than is necessary.

          <tbl>.order has the benefit of using a predefined index if it matches the settings or if no index file is present it can still operate. In Puzzle 5, the data file was originally uploaded without the index file (in all honesty I downloaded the puzzle after you added the index file). In this case since an index already existed that matched my order description <tbl>.order() used the pre-existing index.
          Code:
          vInvoice_ItemsP = table.open("invoice_items",FILE_RW_SHARED)
          vOrderC = "invoice_nu"
          
          vIndxP = vInvoice_ItemsP.order(vOrderC)
          ?vIndxP.type_get()
          = 2
          http://support.alphasoftware.com/alp..._GET%28%29.htm
          Code:
          The <INDEX>.TYPE_GET() method returns the type of the index referenced by the <INDEX> object pointer. The index type can be: 
          2 = a tag in an index file
          6 = a query list
          -1 = record number order
          http://support.alphasoftware.com/alp...RDER%28%29.htm
          Code:
          Description
          
          The <TBL>.ORDER() method orders, and optionally filters, data from the table referenced by <TBL>.
          
          An object pointer, <INDEX>, is returned. <INDEX> will reference either:
          
              an index that matches Order_Expression and Filter_Expression
          
              if no index matches, a new query list.
          
          Use this method as an alternative to <TBL>.INDEX_PRIMARY_PUT() when you want to specify order and filter expressions, rather than index tag names.
          
          An advantage of this method over <TBL>.INDEX_PRIMARY_PUT()is that <TBL>.INDEX_PRIMARY_PUT()could fail if the data dictionary file for a table is missing. The data dictionary contains the long index tag names.
          
          In addition to using an index if one is available, <TBL>.ORDER()does not use the global query variables ( query.filter, query.order, etc.).
          Andrew

          Comment


          • #6
            Re: Programming Puzzle 5 - Nested loops

            Thanks again, Andrew. Very helpful!

            Comment


            • #7
              Re: Programming Puzzle 5 - Nested loops

              This is my attempt for Puzzle #5.

              puz 5.txt

              Comment


              • #8
                Re: Programming Puzzle 5 - Nested loops

                Jeb, Prof. Pickypicky has been having trouble with your offering.

                1) not all the invoices are showing up in the trace window

                2) the "line count" for each invoice is wrong for all but the first invoice

                3) your script opens a table, but does not close it when the script has finished its work. This is a dangerous practice. Always best to leave the user's system exactly like you found it. So cleanup on the way out is the Professor's motto.

                Care to take another crack at the problem?

                -- tom

                Comment


                • #9
                  Re: Programming Puzzle 5 - Nested loops

                  Okay I went back and looked at my script. Here is my revised script!

                  puz5.txt

                  Comment


                  • #10
                    Re: Programming Puzzle 5 - Nested loops

                    Jeb, that's much better.

                    Still have an issue with the last line.

                    table.close()

                    tells Alpha to close an open instance of a table for which a pointer has previously been created. That pointer is named "table". Since that pointer is never created in your script this statement is wrong. the <tbl>.close() method should be run against the object pointer you created when you opened the table. Clear as mud? Holler if you have questions or if this explanation is as inadequate as it feels.

                    Comment


                    • #11
                      Re: Programming Puzzle 5 - Nested loops

                      Yes I understand. It slipped my mind that the table pointer was item. So in that case my last line should read item.close() instead of table.close() .

                      Comment

                      Working...
                      X