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

Filtering a Calculated Field

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

  • Filtering a Calculated Field

    Hi folks,

    I am trying to design a calculated field for a 1-Many link
    table to show the total value of the fields (for 1 orderno)
    where another field equals "YES".
    IE.

    If (Returns->Unit_Adj_Pl="YES") then total the value of Returns->Unit_Value (where the above is YES dont include any with NO or different).

    I know how to total the group of fields up

    Total(Returns->Unit_Value,GRP->Manufactures,GRP->Returns)

    and I know how to check if field says YES

    If (Returns->Unit_Adj_Pl="YES")

    But I dont know how to join the 2 into 1 script/expression
    to only total up the values where the other field shows YES?

    Any help would be much appreciated.

    Mike

  • #2
    RE: Filtering a Calculated Field

    As a shot in the dark:

    Why don't you change your group expression to

    Total(Returns->Unit_Value,GRP->Manufactures,Returns->Unit_Adj_Pl="YES") )
    There can be only one.

    Comment


    • #3
      RE: Filtering a Calculated Field

      Sorry, cut and paste problem:

      Total(Returns->Unit_Value,GRP->Manufactures,GRP->Returns->Unit_Adj_Pl="YES")
      There can be only one.

      Comment


      • #4
        RE: Filtering a Calculated Field

        Thanks for reply,
        I tried what u said stan but it comes up with
        'Argument is incorrect data type error'

        Any other suggestions?

        Thanks
        Mike

        Comment


        • #5
          RE: Filtering a Calculated Field

          Mike, what's the context for the calculated field? Is this a field rule in a table? If so, the fields in the other table will not be visible to the expression.


          Is it a calculated display variable on a form? If so, the form must be based on the set which contains the tables referenced in the expression.

          Comment


          • #6
            RE: Filtering a Calculated Field

            Is this in a report or on a form?

            In reports I use a simple 2-step calculation.

            calc1 if(Returns->Unit_Adj_Pl="YES",Returns->Unit_Value,0)
            calctot total(calc1,GRP->Manufactures,GRP->Returns)

            Basically, if the indicator is on, use the value, else set to 0
            total up the calc.

            Hope this helps.

            Tom

            Comment


            • #7
              RE: Filtering a Calculated Field

              Sounds like your Returns->Unit_Adj_Pl might be a logical field (Yes/No). If so you can't test for "Yes" (character value). You have to text for Returns->Unit_Adj_Pl = .T.

              At least that's what it seems from the error message.
              There can be only one.

              Comment


              • #8
                RE: Filtering a Calculated Field

                Thanks again everyone for replying,

                Sorry it is a character field length 3 chars

                The selection for Returns_Unit_Adj_Pl is done by a drop down list box with 2 option YES or NO (via field rules).

                The Calculated field is on a form (generated by form) which is in a set covering all the relevant tables of which 'returns' is the child of 'Manufactures' on a 1-Many link.

                Hope this points to the solution?

                Many thanks
                Mike

                Comment


                • #9
                  RE: Filtering a Calculated Field

                  Mike, is the link in your set a simple one field expression, or is it a compound expression, designed for example to order to the display of child table records?

                  Next, have you considered using Dbsum() or TableSum() instead of Total(). They would not depend on current grouping or ordering arrangements in your set... or so it seems to me.

                  -- tom

                  Comment


                  • #10
                    RE: Filtering a Calculated Field

                    Hi Tom,

                    Dbsum or Dbtotal works a treat except for 1 thing (yes theres always one) it works out the value correctly but for the whole
                    table not just the current Quote_No.

                    To make dbsum work I had to build an index so I tried to filter out other quote nos but it doesnt allow me to cross reference another table?

                    we are almost there I feel so dont let me down.

                    Thanks mate for your help so far

                    Mike

                    Comment


                    • #11
                      RE: Filtering a Calculated Field

                      Mike, why not use a filter expression that includes both the "Yes" value you need, and the "Quote_No" ?

                      -- tom

                      Comment


                      • #12
                        RE: Filtering a Calculated Field

                        You can have dbsum summarize the child records for each parent. Create this type of calculated field at the set level. Create an index for the child table that indexes by the linking field. The calculated field will contain the sum of the values in the child table.

                        dbsum("child.dbf","quote_no_by_link",quote_no,"fee")

                        where child.dbf is the child table, quote_no_by_link is the index, quote_no is the linking field, and fee is the field you want totaled. The only drawback is all child records must be saved to the child table for the expression to calculate accurately.

                        Hope this helps.

                        Ohlen

                        Comment


                        • #13
                          RE: Filtering a Calculated Field

                          Thank you thank you Ohlen and Tom and everyone for you invaluable help in this matter the nightmare (for me and probrably you) is now solved.

                          To let you know I created a index in the child.dbf (Returns)
                          which ordered on Quote_No (linked field with set) and had a filter expression - Unit_Adj_PL="YES"
                          Then a calculated form field with the following expression -
                          DBSUM("Returns.dbf","Unit_Adj_P",QUOTE_NO,"Unit_Rtn_Value")

                          This worked a treat and have yet to find an inaccurate value
                          so fingers crossed.

                          If by any chance this does have potential problems please let me know (although you will send me to certain nervous breakdown by doing so).

                          Thanks again with this kind of support online A5 is second to none.

                          Mike

                          Comment

                          Working...
                          X