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

Complex query.filter expression help

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

  • Complex query.filter expression help

    I have read thru Tom Cone's and Dr. Peter Wayne's articles several times trying to understand how to Concatenate an expression together, but still can't get the syntax correct. Am trying to develop a simple way to get a count of animals on an input date.
    Code:
    Inventory table:
        animal_id
        class
        dt_entered
        dt_disposed
    Am wanting to create a UDF that will take a date and return a number. Need to query for 3 things:
    Code:
    1. dt_entered <= Input date .AND
    2. (dt_disposed = ctod("") .OR. dt_disposed > Input Date) .AND.
    3. Class = "Cow"
    Question ONE - Can you not use the "\" as a line-continuation character in a function? Here's where I'm at. Had to put the query.filter all on one line, but will break it up here for better reading.
    Code:
    FUNCTION cow_numbers AS N (ndate AS D )
    	dim t as p
    	t = table.open("inventory")
    	query.description = "Cow Numbers"
    	query.filter = "t.dt_entry <= 's_quote(ndate)'  \\\
    		 .and. (t.dt_disposed = {"+dtoc("")+"} .or.   t.dt_disposed>'s_quote(ndate)'   \\\
    		  .and. textsearch(t.Class,"Cow") "
    	query.order = ""
    	indx = t.query_create()
    	cow_numbers = indx.records_get()	
    END FUNCTION
    In order to get it to pass inspection, I had dimmed two character variables to load with ndate and "Cow" to make the syntax a little easier, but this may not be a good way to do it. When I did that, I would get this error:

    Command t.query_create() expected value.

    If I have it as above I get the "Extra characters at end of line 11..."
    I know my syntax in the query.filter is not right, so would appreciate any help.
    Thanks.
    Ernie

  • #2
    Re: Complex query.filter expression help

    We can help you faster with some sample data.

    How about this:

    Code:
    dim ndate as D = {02/15/08}
    dim nclass as C = "Horse"
    
    ?a5_get_records_in_query("animals","dt_entered <= " + s_quote(ndate) + " .and. trim(class) = " + s_quote(nclass)+ " .and. (isblank(\"dt_disposed\") .or. dt_disposed > " + s_quote(ndate) + ")")
    = 2
    Last edited by Tom Cone Jr; 07-20-2008, 09:01 PM.

    Comment


    • #3
      Re: Complex query.filter expression help

      Tom,
      Still having trouble. Here's a small zip file. Open up production_form. Right now just trying to get code to work on first date field, dt_bullsin. Still trying to work it thru a UDF.
      Thanks for your help.
      Ernie

      Comment


      • #4
        Re: Complex query.filter expression help

        Hi Ernie,

        Is this what you are after?

        See attachment, run operation.

        Select July 19.
        Regards
        Keith Hubert
        Alpha Guild Member
        London.
        KHDB Management Systems
        Skype = keith.hubert


        For your day-to-day Needs, you Need an Alpha Database!

        Comment


        • #5
          Re: Complex query.filter expression help

          Ernie, perhaps this will get you closer. Changes documented on the form.

          Comment


          • #6
            Re: Complex query.filter expression help

            Keith,
            Thanks for the suggestion. Had looked at this, but, as yet, not sure if that's the route I want to take. Seem to have my mind set on trying the function route first, which will work, if I can get the syntax correct.
            Tom,
            The code does work, however, in the class, I need to find if the passed in "an_type" exists in the field. Have tried the *word() function, but still returns 0.
            (Have commented out the end of the filter, after *word()... to try to get this to work.)
            Code:
            In_Count = a5_get_records_in_query("Inventory","dt_entry <= " + s_quote(in_date) + " .and. *word(class,an_type) ")' .and. (isblank(\"dt_disposed\") .or. dt_disposed > " + s_quote(in_date) + ")")
            You'll notice that the Class field in inventory contains a value "Mature Cow". If I pass in "Cow", I need to count all the records that contain the word "Cow" in the class field. I could get it to work in the interactive window by dimming a pointer to the table (*?word(t.class,"Cow") -> .T.). I know it is also case sensitive. Couldn't use the textsearch() function in the interactive window.
            Does this make sense?
            Really appreciate the help in this.
            Ernie

            Comment


            • #7
              Re: Complex query.filter expression help

              Ernie, you keep changing the problem. I'm running out of enthusiasm and volunteer time. First it was "Cow", then it was "Bull", now it's "Mature Cow" or "Cow". I think I've shown you how to implement this. The simplest solution may be to add anothter "OR" condition to catch the "Mature Cow" records. Time to roll up your sleeves and adapt it to all the permutations you need. The key to this of course is understanding how the example I supplied does it's work. Once you understand what each term of the filter expression means, and how each is constructed, you have the tools to extend the filter to catch other types of animals.

              -- tom

              Comment


              • #8
                Re: Complex query.filter expression help

                Tom,
                Sorry I didn't make myself clear on the Class field. I just assumed you might take a peak at what was in that field and figure out that was why I've been trying to use "textsearch()" and "*word()" functions. Is there no way in a function to have it do a text search to see if a word exists? If not I'll just have to hard code what I want.
                Thanks for your time and patience.
                Ernie

                Comment


                • #9
                  Re: Complex query.filter expression help

                  Ernie, actually on my best days I try to answer the questions that are asked. Here you've changed the question several times. When time permits I'll take another look.


                  Later on...

                  Here's a revised example. Changes noted as points (6) and (7) on the form. There are other ways this could be implemented, but this one seems to meet your latest question.

                  -- tom
                  Last edited by Tom Cone Jr; 07-21-2008, 04:08 PM.

                  Comment


                  • #10
                    Re: Complex query.filter expression help

                    Tom,
                    That's one I didn't try ($). Completely overlooked it. Really appreciate your help in this. Sorry for not being clear from the beginning about the Class field.
                    Thanks again.
                    Ernie

                    Comment

                    Working...
                    X