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

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

    Peter, tell us about the simple list. What is the listbox bound to?

    Comment


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

      why it doesn't work when I select the Member_Id from the simple list (to change the member_id to value what I want to calculate)
      When you select from the simple list, what are you selecting? A value for a variable?
      There can be only one.

      Comment


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

        Ira
        Thank you for your more information for tablesum() it makes me understand more clearly
        I think I use the expression tablesum() correctly but it has no result and error the variable MEMBER_ID is invalid. but I'm sure the member_id is a field in act_rec_paid.dbf . However the member_id field I use to select its value from simple list (contain : 001,002,003) and I also try to select the member_id value from Lookup table.
        tablesum() don't work for me due to I use Simple list or Lookup table to select the value for member_id field?

        Thanks
        Peter-th
        Last edited by peterth; 03-23-2010, 10:31 AM.

        Comment


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

          Tom,
          In Simple list I have list of Member id to select the value to Member_id field which a field of act_rec_paid.dbf
          I also try to use the table lookup to select the member id from member table
          I can select the member id and name field from memble table and data both member id and member name can show in act_rec_paid.dbf correctly.
          but it has an error when I user tablesum() to summarize the amount that member paid during a month it

          Thanks
          Peter-th

          Comment


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

            Originally posted by peterth View Post
            Ira
            Thank you for your more information for tablesum() it makes me understand more clearly
            I think I use the expression tablesum() correctly but it has no result and error the variable MEMBER_ID is invalid. but I'm sure the member_id is a field in act_rec_paid.dbf . However the member_id field I use to select its value from simple list (contain : 0001,0002,0003) and I also try to select the member_id value from Lookup table.
            tablesum() don't work for me due to I use Simple list or Lookup table to select the value for member_id field?

            Thanks
            Peter-th
            Here is a filter expression that will search for the member in a CRLF delimited list in variable memblist. If a comma delimited list, change to CRLF() to ","
            "wordat(alltrim(Member_Id),'"+memblist"',CRLF())>0"
            Regards,

            Ira J. Perlow
            Computer Systems Design


            CSDA A5 Products
            New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
            CSDA Barcode Functions

            CSDA Code Utility
            CSDA Screen Capture


            Comment


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

              Hi Stan,

              I select the sample list that contain the value of member id ; 001,002,003
              and I also try to select the value for member_id field ( a field of act_rec_paid.dbf) using table lookup from member table. I can select the member id from member table and fill the value of member id and also the member name to act_rec_paid.bdf to member_id and name field with no problem and it can display the member id ; 001 in member_id field and name John in name field.
              but when I user tablesum() it cannot sum the value

              For your sample works perfectly and is what I want but just the difference is I have to key in the member id manually (what I test is I select the value from simple list , and table lookup to select the value ; 001,002,or003 for each member who want to pay)

              It doesn't work because I select the value for member_id field from Simple list or from table lookup?

              I will try to create the member table and select the value of member id with table lookup
              and will let you know what happen.
              -------------------------------

              After I test to select the value for member_id from member table using table lookup.
              It works correctly ! it can sum all record with the same member_id
              I don't know why my table is simply same with your table (but no the sample to use the variable for member id)
              However I will try it again with my tablesum() that has problem.
              Is it problem when I name the table dash(-) or underscore (_) like
              g1member_name.dbf or g1member-name.dbf

              Thank you again for your sample.
              Peter-th

              Thank you again for your sample.
              Peter-th
              Last edited by peterth; 03-23-2010, 11:06 AM. Reason: after test table lookup :

              Comment


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

                Ira
                Thank you for your quick response. I will try it again.

                Thanks
                Peter-th

                Comment


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

                  Stan,

                  I knew what I miss after I compare with your sample that works well.
                  in your sample tablesum() you use tablesum() in calculate field.
                  and I use the expression below in calculate field (edit field rule of table)
                  tablesum("act_rec_paid.dbf","member_id = "+quote(member_id.value),"amount_paid")
                  and the result is "invalid or incomplete expression"

                  but after I change it with calculate field (not edit field rule of table) , it works and calculate correctly. It can sum all records of member_id

                  what is difference and why it has an error "invalid or incomplete expression"
                  I know that tablesum() can calculate the all previous records or saved records (not included entering record) and have to use more technic to calculate.

                  Why alpha don't have a command that can calculate and save into field of table (including the entering record) It may be stupid question, The back end may be difficult to do that.
                  However I know the problem now and will avoid it. Don't use tablesum() to calculate and keep the result in a field ( from edit field rule of table) but use it with calculate field to calculate the result instread.

                  Peter-th

                  Comment


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

                    what is difference and why it has an error "invalid or incomplete expression"
                    The difference is context.

                    On a form member_id.value refers to the value of the member_id object on the form.

                    In a field rule you would use

                    tablesum("act_rec_paid.dbf","member_id = "+quote(member_id),"amount_paid")

                    In this construction member_id refers to the table field as part of the filter. Member_id refers to the contents of the member_id field in the table for the current record.

                    My new attachment has the tablesum() calculation in the table as you were trying. The important thing to remember is that table calculated fields are computed when a change is made to a field used in the calculation. When you add records to the test table, the tablesum() field for the new record will be correct including the new value. The tablesum() field for existing records will not change.

                    You can force the recalculation of the tablesum() field for all records if you need to do so.

                    Don't use tablesum() to calculate and keep the result in a field
                    This is good advice. You do not need to store this result in a field. You can calculate the current result at any time. Why store it?
                    There can be only one.

                    Comment


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

                      Originally posted by Stan Mathews View Post
                      You do not need to store this result in a field. You can calculate the current result at any time. Why store it?
                      for the first time I think it's easy for me if the result can store in a field. I can double click the table and view with default browse but now I understand it's not neccessary to do that. it can calculate the current result at any time.

                      Regarding the filter, what it should be about the ' , + , and " if I have more than one condition. For example I want to compute total amount paid only within a current month , not for all record.

                      old one is tablesum("test.dbf", "member_id = "+quote(member_id.value) ,"amount_paid")

                      new condition should be ? (but it doesn't work)
                      "member_id = "+quote(member_id.value) .and. "pd_date =" +quote(date())
                      or
                      "member_id = "+quote(member_id.value) .and. "pd_date =" +quote(month(date()))

                      Thanks
                      Peter-th

                      Comment


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

                        "member_id = "+quote(member_id.value) +" .and. pd_date =" +month(date())

                        The whole of the filter must be a character string. To get an outside value into the string you must suspend and restart the literal string.

                        Code:
                        name_variable = "peterth"
                        char_string = "My name is " + quote(name_variable) +", pleased to meet you."
                        ? char_string
                        = My name is "peterth", pleased to meet you.

                        You don't quote number values. (month(date()))


                        Here is help on writing expressions.
                        Last edited by Stan Mathews; 03-24-2010, 08:47 AM.
                        There can be only one.

                        Comment


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

                          Thank you for your kind suggestion for tablesum() and the expression. It make me confidence to use the tablesum() and can find what I miss when it doesn't work.

                          Thank you very much
                          Peter-th

                          Comment


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

                            Stan,

                            May I ask more for tablesum()
                            when I user tablesum() with old condition it can calculate correctly with no problem but why it cannot calculate correctly when I change new condition of expression. The result cannot calculate the old records. It calculate only the new records. How can I let it re-calculate all records included the old records with new conditions of my filter records.

                            Thanks
                            Peter-th

                            Comment


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

                              When Calculated fields are recalculated.

                              A5_RECALC_CALC_FIELDS()

                              <TBL>.RECALC_CALCFIELDS()

                              Re-Valuating Field Rules
                              There can be only one.

                              Comment


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

                                Stan,

                                Thank you for the direction and quick response.

                                Peter-th

                                Comment

                                Working...
                                X