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



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

Reports - Using UDF for a calculated field

  • Filter
  • Time
  • Show
Clear All
new posts

  • Reports - Using UDF for a calculated field

    Hi everyone,

    I have a grid that has a Custom Field to compute a row average ignoring any values that are 0. I also have a report where I would like to do the same thing but I don't know how to do it.

    The code in the Custom Field in the grid looks like this:

    if S1 >0 then
    holdscore = HoldScore + S1
    DivideBy = DivideBy + 1
    end if

    if S2 >0 then
    holdscore = HoldScore + S2
    DivideBy = DivideBy + 1
    end if
    Custom_render = Holdscore / DivideBy

    and it works fine. I am stuck on how to achieve the same thing for the row total in my report. Any ideas?



    EDIT: I am using 7902
    Attached Files

  • #2
    Okay, I got it (I think). On the control panel, I created a function with similar code I used on the grid. In the report I created a calculated field MyCalc = MyFuntion(s1,s2,s3,s4,s5,s6) and put that field on the report and it works fine.



    • #3
      Using 7902 and MS Access.

      I spoke too soon. The row average works (see above) but the column average does not. On my report, I also need to get the average of the column. Not all columns contain a number. If the question wasn't answered it will be blank. If I drag a field [Score1] into the Break area to get an average, it gives me the average as if the missing value is 0 which in turn gives an incorrect average. The screenshot gives 2.42, but it should ignore the blank value and return 2.64.

      How do I get the average to ignore a blank score?

      Attached Files


      • #4
        EDIT: What is strange is that if I average that field on a grid then it does work. It ignores the blank value and returns 2.64


        • #5
          Long shot.
          Try => or =< or <> (<> will need a bit of a rework.)
          See our Hybrid Option here;

          There are 3 things you can do in any situation;
          Live with it, Change it or Walk away from it.


          • #6
            Hi Ted, thanks for responding. After working with this a little more I think I need to get a count of how many rows are in the group header less the rows where there has been no information recorded. At least that way I can have a total of the scores and then a count of how many lines there are that have an actual number and I can create my average from that. My problem is I don't know how to find out how many rows there are that have an actual number.

            I can get a count of the rows MyRowCount = count(SQLDATASOURCE->score1,grp->Break1ce,grp->detail) but I'm stumped on how to exclude the rows where there is no numeric number.

            Ideally, I'd have

            MyRowCount = count(SQLDATASOURCE->score1,grp->Break1ce,grp->detail) = 12
            MyRowCount2 = count(SQLDATASOURCE->score1,grp->Break1ce,grp->detail WHERE SCORE1 >0) = 11

            I'm stumped.

            Thanks for any help anyone can offer.



            • #7
              I have also tried computing the row numbers in my Access database but AA does not seem to like it when I do that and throws errors.


              • #8
                Why not move the data from access to a real database, like sqlExpress or mySQL, and let the database do the work for the row ?
       is a great site to test and share sql code


                • #9
                  Maria Db or HeidiSQL both work well.
                  I use SQL 2019 on my hosing account, but any of the local free versions are a bit better than Access.
                  This works as a Summarise operation on DT.

                  sum.db = summarize_db_name
                  sum.db_dd_delete = .F.
                  sum.result_overwrite = .F.
                  sum.order = "ID"
                  sum.filter = "VALUES<>0"
                  sum.options = "I"
                  sum.fields = 4
                  sum.field1 = "ID"
                  sum.code1 = 0
                  sum.field2 = "VALUES"
                  sum.code2 = 5
                  sum.field3 = "VALUES"
                  sum.code3 = 2
                  sum.field4 = ""Filter""
                  sum.code4 = 16
                  Last edited by Ted Giles; 07-02-2022, 12:05 PM.
                  See our Hybrid Option here;

                  There are 3 things you can do in any situation;
                  Live with it, Change it or Walk away from it.


                  • #10
                    Thanks for pitching in and sorry for the late response. I'm out of town and limited to when and where I can get online. If I could switch, I would, but sadly that call is not mine to make no matter how hard I push it. I'll see what I can do once I return.

                    There must be a way in AA reports to do this though right? I would think so at least.




                    • #11
                      The above produces a summarised table.
                      I will do a mock up.
                      Dont fuss about a tesponse until its convenient.
                      Stay safe over there!
                      See our Hybrid Option here;

                      There are 3 things you can do in any situation;
                      Live with it, Change it or Walk away from it.


                      • #12
                        ok. Time to start at the beginning.
                        Show us the code for your calculated field.
                        Based on the image you showed, should be able to set the hold value to the sum of s1,s2,s3,s4,s5,s6
                        The divideby is quite different and most likely where you need the udf. I can see several options based
                        on the reality of your project, which I cannot determine because it looks like you haven't provided enough
                        sample data.
               is a great site to test and share sql code


                        • #13
                          John, have a look at the attached. It is quite basic, but addresses the issues you may have.
                          I used to teach statistics, so I believe I know where you are going with this.
                          Not being pushy, but as Gregg says, if we know a bit more about your aims, we can possibly add some "non-technical" value.
                          The attachment shows the difference between discrete and selected data based on Alpha out of the box stuff in a DBF.
                          When you want to go through Activity Sampling (Easy and gets people excited) let me know.
                          See our Hybrid Option here;

                          There are 3 things you can do in any situation;
                          Live with it, Change it or Walk away from it.


                          • #14
                            Hello again and thank you. I have returned but have not returned to normal :)

                            I arrived back to a completely dead PC so I'm now battling (going on 4 days) trying to download all my files, passwords, etc from the cloud to my other PC along with all the other stuff a 24 hour period throws at you. But, just on an aside, this newer PC doesn't have AA installed and the dead PC still has the license number on it so I can't "de-register" it. Do I need to contact sales or someone?




                            • #15
                              Beg, borrow or steal a USB SATA Hard Drive Docking station. Mine was about £10.
                              On the dead PC, remove the hard drive (SSD or SATA)
                              Insert the drive into the Docking Station, note connections, really easy.
                              Attach USB to working PC which is already running.
                              Turn on Docking Station.

                              Important! DO NOT ALLOW THE NEW PC TO FORMAT OR CHECK THE DRIVE! (It has the opsys on it and will confuse things.)

                              The drive from the old PC should show up. Drives rarely fail nowadays so it's likely something simple on the dead PC which is causing issues.

                              If the drive is indeed wrecked, this won't work, but it's worth a shot.
                              If it works, you have a means of backing up stuff and storing it in a safe.

                              All my dead machine drives are stored and used this way.

                              When you install, you may need the activation key (in Roaming) and beware the machine naming convention for 2 copies of Alpha.
                              See our Hybrid Option here;

                              There are 3 things you can do in any situation;
                              Live with it, Change it or Walk away from it.