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

Count Blank Fields

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

    Count Blank Fields

    I'm creating a report that needs to count blank instances in a particular field, as well as specific criteria. I know you can use isblank as a query but can't figure out how to get a count of those records that have nothing in that field and put it on a report. The syntax I am currently using on the report that works for totaling those fields that contain "3 Other" is as follows:

    total(office->Casetype="3 Other".AND.Office->Casetype="3 OTHER")

    This gives me a total result of 26 records. When I run a query on that same table to find those fields that are blank using "isblank", there are 27 records. What I am trying to accomplish is for the report to show both criteria, or 53 records. I tried adding "+total(Office->Casetype="") but it only results in 30 records.

    #2
    Re: Count Blank Fields

    Code:
    tablecount("your_table_name","isblank(\"case_type\")") + total(office->Casetype="3 Other".AND.Office->Casetype="3 OTHER")
    There can be only one.

    Comment


      #3
      Re: Count Blank Fields

      Hi Stan! Used your syntax, it just returned 27 records. I called the office admin, explained the situation to her as that field should NEVER be blank so I'm going to run a report on those that are blank and we're going to update them with a value. I'm also going to make that field mandatory. I'm really concerned though because I keep getting different values when I run a filter. It took me three times to get the report to run properly and I know the criteria was correct (go figure ;-) ) For instance, when I run a query on the OFFICE table for "isblank(Casetype)" I get 27 hits, but some of those hits do have a valid value in the Casetype field. However, when the report did finally run I only had 8 hits and each of those were correct. I'm really wondering about the stablity of V9.....

      Comment


        #4
        Re: Count Blank Fields

        Question on the syntax you provided:

        tablecount("your_table_name","isblank(\"case_type\")") + total(office->Casetype="3 Other".AND.Office->Casetype="3 OTHER")

        In the first line, after isblank( ,why is the second \ inside the quote marks?

        Comment


          #5
          Re: Count Blank Fields

          Don't know where I got case_type for casetype.

          The filter is isblank("casetype")

          but you have to enclose the filter in quotes

          "isblank("casetype")"

          so you have two strings

          "isblank(" and ")"

          separated by

          casetype.

          To tell Alpha not to end the whole at the first quote you "escape" it with the backslash, to tell Alpha not to end the whole at the second quote, ditto.

          "isblank("casetype")"

          becomes

          "isblank(\"casetype\")"


          Try

          Code:
          tablecount("office","isblank(\"casetype\")") + tablecount("office","Casetype=\"3 Other\"")
          There can be only one.

          Comment


            #6
            Re: Count Blank Fields

            Gotcha. However, this is getting more bizarre. When I build a report and try to set the filter expression for the Case Source using the Expression Builder (under Report->Properties->Band->Detail Properties), and I click on Field Values to complete the syntax, it only shows 10 of the 12 values that are defined in the Field Rules for that table. I deleted all the CDX files so the indexes would rebuild. This kind of stuff really makes me crazy. I attached an image. As you can see, numbers 5 and 6 are missing.

            Comment


              #7
              Re: Count Blank Fields

              Diana,
              There are multiple things that could be causing your issues. Give us a sample and most likely we will be able to figure it out for you. Could be something as simple as your having group filters and such at the same time as set filters---the group normally supercedes the set filter but can see where it could confuse Alpha (just one of several possibilities I would look at).
              Mike
              __________________________________________
              It is only when we forget all our learning that we begin to know.
              It's not what you look at that matters, it's what you see.
              Henry David Thoreau
              __________________________________________



              Comment


                #8
                Re: Count Blank Fields

                Hi Mike, thanks for jumping in. I don't have any Group or Set filters defined for these reports, they're pretty simple. I can complete the filter by typing in the values for 5 and 6 and it does run, just don't understand why those values don't show when you try to use the expression builder. If you want, I can send you a copy of the db via personal email.

                Comment


                  #9
                  Re: Count Blank Fields

                  OK.. I am confused and am pretty sure it's not the hangover!
                  What exactly is this?:
                  total(office->Casetype="3 Other".AND.Office->Casetype="3 OTHER")

                  Comment


                    #10
                    Re: Count Blank Fields

                    That is a result of allowing additions to the lookup table and if you don't put both filters in, the report will not pick up both instances.

                    Comment


                      #11
                      Re: Count Blank Fields

                      I am still confused.
                      What additions?
                      What lookup table?
                      It's the same field, same values.

                      Comment


                        #12
                        Re: Count Blank Fields

                        Now I'm really confused as well. In the field rules it is set to "force closest match". However, when I use the Expression Builder and choose Field Values for Casetype, it gives both lowercase and uppercase as options (see attached pix). I checked the lookup table in Field Rules and only one value is there, but I know if I do not put both values in the expression the report is not correct.

                        Comment


                          #13
                          Re: Count Blank Fields

                          Why don't you start out by eliminating one or the other (convert OTHER to Other or the other way around) and retry.

                          This sentence had way too many other's in it, but I am sure you get the meaning.

                          Comment


                            #14
                            Re: Count Blank Fields

                            LOL, I get your drift....but how do I eliminate what I can't find? Where are those values (in the pix) to be found? The lookup table only has the one value.

                            Comment


                              #15
                              Re: Count Blank Fields

                              Mike and I figured it out - Alpha is pulling from what has actually been input as a value, NOT what is in the Field Rule lookup list. The reason I wasn't seeing 5 or 6 was because those values hadn't been used. I found this by changing a record to use "5 - YP McKeesport" and now it shows as a value when using expression builder. This at least solves that mystery! Thank you Mike!!

                              Also, Mike discovered that I had a case convert on the field rule to change everything to uppercase, thus explaining why "3 Other" and "3 OTHER" appeared. The lowercase must have come about before the case convert was implemented. Easy fix to just change the 2 records that have "3 Other" so we have consistency in the values. Whew!

                              Comment

                              Working...
                              X