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

Date fields and LQO

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

    Date fields and LQO

    I have indexed my tables and created my forms to utilize LQO. When i execute my query from a button it filters the browse on my form approriately.

    Any query i use that is referencing an indexed character field seems to use LQO and the response is instant. No problems there.

    However when I try to query a Date field and use the between() or between_date() commands as per what I have read in these forums and user guide, it doesn't seem to be utilizing the LQO as I notice it counting through the records in the status bar (for example 200 of 500 records 40%) and doesn't respond as quick as the indexed character fields.

    I think it is just a simple syntax problem or I am missing the point in my design

    In my table I have indexed a date field called 'completeddate' (there are no filters or unique only settings)

    On my form I have two variables (date type) called 'date1' and 'date2'. I have a browse showing all the records in my table. I have button that has the script to do the filter.

    Below are the scripts I have tried to filter my records.
    1.
    dim begin_date as D
    dim end_date as D
    begin_date = date1
    end_date = date2

    tbl = table.current()

    query.filter="between(completeddate,{"+dtoc(date1)+"},{"+dtoc(date2)+"})"
    query.order = "invert(completeddate)"
    ix = tbl.query_create()
    parentform.refresh_layout()


    2.
    dim begin_date as D
    dim end_date as D
    begin_date = date1
    end_date = date2

    tbl = table.current()
    query.filter = between_date("completeddate",var-"begin_date,var-"end_date)
    query.order = "invert(completeddate)"
    ix = tbl.query_create()
    parentform.refresh_layout()

    * I have also tried variations of these scripts that I thought might work with no luck, I even tried hard coding the dates instead of using variable

    These scripts both filter the browse correctly except I don't think it is utilizing LQO as per response being slower than with character fields.

    Is this normal or have I got it all wrong??

    Just an observation when I do a query just based on referencing the completeddate date field it seems to utilize the LQO.
    i.e. query.filter="completeddate = begin_date"
    Tags: None
    #2
    RE: Date fields and LQO

    Hummmmm ... no one answered this so I will take a wild stab at giving you an answer.

    Since you use between_date(), I am guessing the LQO wount be much help since the funtion needs to scan each record to see if it fits the criteria where as in a normal query it uses the index to quickly find the record.

    This is just a logical guess on my part for lack of anyone elses input. Rest assured that now that I answered you there will probably be others correcting me and helping you :)

    Scott

    Comment

      #3
      RE: Date fields and LQO

      Thanks for a response, your logic is easily understood. I was thinking of adding another character field to my table, converting the dates into it and going from there. Yet to do it as I am hoping for a simpler (even though that wouldn't be difficult - maybe I am lazy) solution.

      Is my logic / methodolgy worth persuing?

      Comment

        #4
        RE: Date fields and LQO

        I don't think the issue is with it being a date. I use date indexed and sometimes LQO works fine. Note that BETWEEN_DATE() returns a character string.

        "BETWEEN_DATE() returns a character string that is a quoted filter expression that uses two date values"

        I have noticed that LQO can be a little finicky (for lack of a better word) I develop on a stand alone machine and find that sometimes (I think mainly on sets) LQO doesn't work. Then I take the same app and run it on a network and LQO works fine, go figure!! I've kind of given up expecting LQO to work standalone all the time until I've tried it on the network. I know this is really odd behavior.

        I suspect others are seeing it also but maybe aren't recognizing it or maybe it's just me. I figure as long as it works on the network when deployed I can live with it not working on my standalone machine but it would sure make life easier if it worked both places.

        This may not be your issue at all but it may be related.

        Russ

        Comment

          #5
          RE: Date fields and LQO

          I am yet to try it over the network. Still in development stages of my first app. Like you, if it works over the network I can live with it acting the way it does in a stand alone environment.
          Thanks for your response

          Comment

            #6
            RE: Date fields and LQO

            I'm just wondering, and don't have time to try it, but what would happen if you didn't invert the query order? Maybe start just by leaving it at default.

            - Steve
            -Steve
            sigpic

            Comment

              #7
              RE: Date fields and LQO

              I tried your suggestion, query.order = "" and deleting it all together. Still same symptom
              Thanks

              Comment

                #8
                RE: Date fields and LQO

                Definitely .. put a calc field in the table that converts the date field via CDATE(Date_Field) and index on the calc field. Then convert all your queries to the form

                Between(Calc_Date_Character_Field,'"+Cdate(date1)
                +"','"+Cdate(date)+"')

                This will always give you LQO-speed queries.

                F.
                Finian

                Comment

                Previous template Next
                Working...
                X