Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

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

why tablesum() cannot summarize data correctly

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

    #46
    Re: why tablesum() cannot summarize data correctly

    Stan,

    I create the sample set to test but it doesn't work yet.
    Where I put that code? In edit field rule of Date and Member_id of g1invoice_items?

    I attached picture and video file for more information

    Thanks
    Peter-th
    Last edited by peterth; 04-12-2010, 12:06 PM.

    Comment


      #47
      Re: why tablesum() cannot summarize data correctly

      That is the correct place. It will only post for new/changed records. If you want to post the date and mem_id for all records you will have to use a posting operation from G1invoice_header to G1invoice_items, linking on invoice_number.

      After the posting operation is done all new records will be automatically posted by the cansaverecord script.
      There can be only one.

      Comment


        #48
        Re: why tablesum() cannot summarize data correctly

        Stan,

        Thank you for your advise.
        I try it again but the data of Date and Mem_id cannot post to the child table.
        I also attached the sample (but not work yet) I also add code you give me for test. I try many times but the result not post to child table.

        Thanks
        Peter-th

        Comment


          #49
          Re: why tablesum() cannot summarize data correctly

          Attached is a working example of the field rule cansaverecord event posting the date and mem_id from the invoice_header table to the invoice_items table. The posting script depends on the form Invoice being open. If you use a different name for the form, you must use that name in the script.


          Also included is the post operation, not yet run so you can see it work.

          The operation only needs to be run once and then it can be deleted.
          There can be only one.

          Comment


            #50
            Re: why tablesum() cannot summarize data correctly

            Thank you Stan,

            I will learn and try it again.

            Many thanks
            Peter-th

            Comment


              #51
              Re: why tablesum() cannot summarize data correctly

              Stan,

              I use your sample script and it works perfect
              However may I have the meaning of

              tbl.mem_id = mem_id.value

              and how it works ?

              it refers to the mem_id field of current table (invoice_items) and post the value of mem_id field from invoice header (the right side) to mem_id field of invoice items table (on the left side) ?

              Thanks
              Peter-th

              Comment


                #52
                Re: why tablesum() cannot summarize data correctly

                Almost correct.

                table field (in invoice_items) = formobjectname.value
                tbl.mem_id = mem_id.value


                value is a property of an object on a form.

                In this usage, the form object labeled Customer ID represents the mem_id field in the invoice_header table.
                There can be only one.

                Comment


                  #53
                  Re: why tablesum() cannot summarize data correctly

                  Stan,

                  Thanks for more information
                  Peter-th

                  Comment


                    #54
                    Re: why tablesum() cannot summarize data correctly

                    Originally posted by Stan Mathews View Post
                    The grand total debt should be.

                    Code:
                    TABLESUM("G1invoice_items.dbf","Member_id="+quote(member_id.value),"Extension")
                    There are no dates in the G1invoice_items.dbf so there is no way to filter for a particular month.

                    Stan,

                    Now I have date field in G1invoice_items.dbf
                    I use tablesum() and filter the member_id and year, it works with below expression.
                    debtallyear = TABLESUM("G1invoice_items.dbf","member_id="+quote(Member_Id.value)+" .and. year(date)="+year(date()),"Extension")


                    but why it doesn't work when I have more filter for month , below expression (doesn't work)
                    debtlastmonth = TABLESUM("G1invoice_items.dbf","member_id="+quote(Member_Id.value)+" .and. month(date) ="+month(date())+" .and. year(date)="+year(date()),"Extension")

                    My purpose is I want to sum and filter for same member_id, same month, same year (member_id =1004, date is 15/4/2010 so last month = 3, year = 2010)


                    Thanks
                    Peter-th

                    Comment


                      #55
                      Re: why tablesum() cannot summarize data correctly

                      Code:
                      debtlastmonth = TABLESUM("G1invoice_items.dbf","member_id="+quote(Member_Id.value)+" .and. month(date) ="+month(date())+" .and. year(date)="+year(date()),"Extension")
                      This tests for month(date) = month(date())
                      ------------- month(date) = 4

                      My purpose is I want to sum and filter for same member_id, same month, same year (member_id =1004, date is 15/4/2010 so last month = 3, year = 2010)
                      Code:
                      debtlastmonth = TABLESUM("G1invoice_items.dbf","member_id="+quote(Member_Id.value)+" .and. month(date) ="+[COLOR="Red"](month(date())-1)[/COLOR]+" .and. year(date)="+year(date()),"Extension")
                      (month(date())-1) is 4-1 = 3
                      There can be only one.

                      Comment


                        #56
                        Re: why tablesum() cannot summarize data correctly

                        Stan,

                        Thank you for your code.
                        It works well.

                        Thanks
                        Peter-th

                        Comment


                          #57
                          Re: why tablesum() cannot summarize data correctly


                          Stan,

                          I think I understand the tablesum() 's behavior but now I confuse it again I cannot control it. After I investigate I found the result is difference.
                          1. I have act_rec_paid.dbf table and created Rec_paid form from this table and use tablesum() to sum amount_paid with calculate field (not field rule)
                          It works correctly when I press F5 or click save record button.
                          It works well within form from its table (act_rec_paid.dbf)
                          The expression is below.

                          paidthismonth = tablesum("act_rec_paid.dbf","member_id = "+quote(member_id.value)+" .and. month(paid_date) ="+month(date())+" .and. year(paid_date) = "+year(date()),"amount_paid")

                          2. In Rec_paid form the same form, I created calculate field (not field rule) to sum the Extension field from other table ; G1invoice_items.dbf
                          The result is it can calculate but not correctly
                          I found when I create new invoice(has more a record in g1invoice_items.dbf)
                          and saved it
                          and when I refresh (press F5) in Rec_paid form. The result of tablesum() for below expression ,not update new record (not sum the last record of g1invoice_items that I just saved a new record) but if I edit and save it again it can sum the result correctly. I have to do it 2 times (save a new record,come back to edit and save it again so it will sum corectly)

                          I also try this code but problem is the same
                          Dim tbl as P
                          Tbl = table.open("g1invoice_items")
                          Tbl.recalc_calcFields()
                          Tbl.close()

                          The expression in Rec_paid form that cannot sum after save a new record

                          couponlastmonth = TABLESUM("G1invoice_items.dbf","pay_method='Coupon'" ".and. member_id="+quote(Member_Id.value)+" .and. month(date) ="+(month(date())-1)+" .and. year(date)="+year(date()),"Extension")

                          How can I let it sum correctly in Rec_paid form after I add and save a new record in invoice_items form.

                          I have 2 calculate fields (not field rule) to compare in the same form

                          Many thanks for your kind advise.
                          Peter-th
                          Last edited by peterth; 04-16-2010, 11:35 AM.

                          Comment


                            #58
                            Re: why tablesum() cannot summarize data correctly

                            I think you have too many " in the expression.

                            Try

                            Code:
                            couponlastmonth = TABLESUM("G1invoice_items.dbf","pay_method='Coupon' .and. member_id="+quote(Member_Id.value)+" .and. month(date) ="+(month(date())-1)+" .and. year(date)="+year(date()),"Extension")
                            There can be only one.

                            Comment


                              #59
                              Re: why tablesum() cannot summarize data correctly

                              Stan,

                              The problem is the same. I have to go to invoice_items form edit and save it again.
                              and then go back the Rec_paid form, Press F5 so it can sum the result correctly.

                              Any technic or where I have to read or try more?

                              Thanks
                              Peter-th

                              Comment


                                #60
                                Re: why tablesum() cannot summarize data correctly

                                Stan,

                                It works now both for your new code and my old code (too many " ) after shut down computer for 2 days. ( before this I try to start/stop alphafive and reboot computer but the problem is the same.
                                But now it's ok

                                Thanks
                                Peter-th

                                Comment

                                Working...
                                X