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
Devcon 2020 Goes Virtual! LEARN MORE >

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

Calculated Field Expression using Grouping in Field Rules

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
    #1

    Calculated Field Expression using Grouping in Field Rules

    I'm very new to ALpha Five and am probably asking a stupid question but:

    I have a field in a table that requires the value to be calculated from a group of other fields in the same table.

    In excel the formula would be Cell C8 =sum(C1:C20). Is it possible to do this using the Field Rules and Expression Editor? If not is there a recommended way of achieving this result? In ALPHA I wish to have the Field 'AdjustedMarketValue' = 100 divided by total(MarketValue) for a group of Day, Venue, Section.

    Any help is appreciated and will get me started down the right road.

    Regards
    GazzaT
    Tags: None
    #2
    Re: Calculated Field Expression using Grouping in Field Rules

    The two functions I would suggest to get a sum of field values at the table level are DbSum() if you have an index defined that would group your records or TableSum() if you don't. DbSum() is faster because it uses an index but TableSum() is a bit more flexible because it takes a filter.

    To achieve the value you are after for each record in the table you would enter something like this in the calculation for theAdjustedMarketValue field. Make the appropriate changes to match your field names
    Code:
    100/tablesum("YourTableName","Day+Venue+Section="+s_quote(Day+Venue+Section),"Marketvalue")
    The expression may have to be tweaked a bit if your fields are not all character.


    However, even though this expression may return the correct value in the first instance I have a feeling it is not going to behave the way you think it will behave. A calculated field is a stored value. Yes it is arrived at through calculation but once arrived at it is stored. It only gets updated when something in its own record changes. So in your case you could have four records that fit your grouping with marketvalues of 100 each. If you now add the calculation to the AdjustedMarketValue field the field will be empty unless you apply a recalculation to the table. When doing so you would get .25 in each record (100/400). If you add another record that matches the same filter with a value of 100 then the field AdjustedMarketValue would still only result in .25 (100/400) because there are only four saved records to sum up. If you now edit the newly entered record it will update to .2 (100/500). However the four previously existing records will still hold the value of .25. They won't get updated until they are edited or a recalculation applied.

    Do you really need to have this value stored in the table?

    Through the use of an unstored calculation you can have access to this value pretty much anywhere you need to see it. Such as on a form or in a report. Unstored calculations can be defined in a number of places. In a form, in a report, letter or label. They can also be defined at the table level (when editing the table structure use the xy buton on the toolbar) so that where ever the table is used the calculation will automatically be available. The actual expression will essentually be the same.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

    Comment

      #3
      Re: Calculated Field Expression using Grouping in Field Rules

      Many thanks for that well versed explanation Tim,

      It is appreciated. I think you have set me off in the right direction too. The 'unstored calculation' maybe exactly what I required to complete this excercise. I am going to go back and doing a redesign of my approach.

      Once again.

      Many thanks.

      and. good luck Saturday if you are an AFL follower. :-)

      Cheers
      GazzaT

      Comment

        #4
        Re: Calculated Field Expression using Grouping in Field Rules

        Originally posted by gazzat View Post

        Many thanks.

        and. good luck Saturday if you are an AFL follower. :-)

        Cheers
        GazzaT
        You're welcome and thanks. GO the CATS!!!! I am not a hard core fan but you can't help but get caught up in the excitement. especially as its been a while for the Cats. As luck would have it I actually topped the footy tipping at work. Yay me.

        Do I detect an aussie accent?

        And welcome to Alpha and the forum.
        Tim Kiebert
        Eagle Creek Citrus
        A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

        Comment

          #5
          Re: Calculated Field Expression using Grouping in Field Rules

          Do you really need this calculation at the table level?
          Generally, you would create this at the Form or Report level by a simple calculated field on the Form or Report designer.

          If you need it at the table level, you can create an expression for that field by Design/Edit Field Rules.

          On re-reading, I see I've recapitulated the last part of Tim's answer. Oh, well.

          Comment

          Previous template Next
          Working...
          X