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

Query Filter

  • Filter
  • Time
  • Show
Clear All
new posts

    Query Filter

    Is there a max size limit on a query filter definition? I have a very long query filter, 1020 characters, which works fine, but whenever I try to add to the definition it fails (goes over 1024 characters?).

    Re: Query Filter

    1024 (for any expression)

    Among the techniques to consider to shorten the filter is the use of is_one_of() or inlist() instead of multiple fieldname = somevalue tests. If variable substitution is involved in the expression then of course the variable names should be as short as they can. Long field names are a common source of length issues in expressions but it is seldom an easy task to shorten those.
    Last edited by Stan Mathews; 02-15-2017, 12:53 PM.
    There can be only one.


      Re: Query Filter

      Anything over 1024 characters xbasic treats internally as a long string. Long strings and regular strings are two different variable types that are generally interchangeable except in expressions and a few other places such as a simple check for a strings existence IF STRINGVARIABLE works if the string is under 1024 characters more than that it gives an error. If a string may possibly grow over 1024 characters use IF. NOT. ISNULL(STRINGVARABLE)


        Re: Query Filter

        Not sure how to use this info to solve the problem.


          Re: Query Filter

          It doesn't help solve your problem. Just wanted to give more background. However, Stan gave you some good suggestions to try.


            Re: Query Filter

            I don't want o hijack this thread, but I create a filter in xbasic using the inlist function to preview a report. tells me there are no pages in the layout. if I go to the report and paste the generated filter in the filter records expression, it works fine.

            anyone know why it wouldn't work with report.preview but works inside the report?
            the reason I used this method is because if I used the fieldname = x.or. for each lot number, I was over the 1024 character limit for a report filter in some cases.

            just an observation, inlist2() seems the generate the report faster than is_one_of(). I mean it's not substantial, only talking about maybe a second, but it is perceivable.
            example of the filter
            Last edited by gkeramidas; 02-18-2017, 12:31 PM. Reason: additional info


              Re: Query Filter

              I figured out my problem. I was using crlf() as the separator. I changed it to chr(13) and now it works.


                Re: Query Filter

                OK, back to my issue.

                OK, this is the filter I am using before I extend to add another search. Basically there's a complex query form that allows you to list all items in an inventory list that meet the following options:
                Manufacturer, specified or wildcard (*)
                Owner, specified or wildcard
                Location room, rack, shelf, zone
                Condition - exclude used, parts and repair (check boxes)
                Remove items not export eligible, or not available to ship (check boxes)

                query.filter = "((var->vsrchc1= "*") .or. ((var->vsrchc1 $ manufacturer))) .and. ((var->vsrchc2 = "*") .or. ((var->vsrchc2 $ owner))) .and. ((var->vsrchc3="*") .or. ((var->vsrchc3 $ description))) .and. ((var->vsrchL1=.f.) .or. (inactive=.f.)) .and.((var->vsrchL2=.f.) .or.(quantity-qty_on_sales_order>0)) .and. ((var->vsrchc4="*") .or. ((var->vsrchc4$model_no))).and. ((var->vsrchc5="*") .or. ((var->vsrchc5=location_room))).and. ((var->vsrchc6="*") .or. ((var->vsrchc6=location_rack))).and. ((var->vsrchc7="*") .or. ((var->vsrchc7=location_shelf))).and. ((var->vsrchc8="*") .or. ((var->vsrchc8=location_zone))) .and. ((var->vsrchl3=.t.) .or.( .not.("new"$condition))) .and. ((var->vsrchl4=.t.) .or.( .not.("used"$condition))) .and. ((var->vsrchl5=.t.) .or.( .not.("parts"$condition))) .and. ((var->vsrchl6=.t.) .or.( .not.("repair"$condition))) .and. ((var->vsrchl7=.t.) .or. (isnotblank ("condition"))).AND. ((var->vlog2=.t.) .or.(export_eligible=.t.)) .and. ((var->vlog1=.t.) .or.(donotship=.f.))"
                What I am trying to add is filtering on "all web categories" or "specified web categories". That makes the filter definition too long. If I leave the filter as is, and then put the extension in the form base filter, I am OK, but I can't run a report because the report uses the whole extended filter.


                  Re: Query Filter

                  I would take the inputs from the form and dynamically build the filter.
                  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.


                    Re: Query Filter

                    In "((var->vsrchc1= "*") .or. ((var->vsrchc1 $ manufacturer)))" if var equals "*", true is returned and the contains part is not necessary. In other words you are saying if they enter a "*" then it does not matter what is in the field. Just show me all the records. Therefore you don't need that in the filter. In other words, in any of your ".OR." pairs if one side is true then the other is not needed. If you can determine one of those sides directly from your inputs such as (var = "*") then it either doesn't need to be in the filter at all or only the part that deals with the record.

                    I will go through the filter you posted and work up a suggestion.
                    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.


                      Re: Query Filter

                      Ok try if this works. Add you extension to the end using the same construct.
                      You can try the below code in a new script and play with the variable values to see what the filter will look like.

                      ' these vars are just to make the code work while testing.
                      Dim filter as C = ""
                      dim vsrchc1 as c = "zzz"
                      dim vsrchc2 as c = "zzz"
                      dim vsrchc3 as c = "ccc"
                      dim vsrchc4 as c = "zzz"
                      dim vsrchc5 as c = "zzz"
                      dim vsrchc6 as c = "zzz"
                      dim vsrchc7 as c = "zzz"
                      dim vsrchc8 as c = "zzz"
                      dim vsrchL1 as L = .t.
                      dim vsrchL2 as L = .t.
                      dim vsrchL3 as L = .f.
                      dim vsrchL4 as L = .f.
                      dim vsrchL5 as L = .f.
                      dim vsrchL6 as L = .f.
                      dim vsrchL7 as L = .f.
                      dim vLog1 as L = .f.
                      dim vLog2 as L = .f.
                      'each section is tested and only added to the filter if needed. 
                      'the value itself is extracted from the variable and inserted into the filter.
                      ' watchout for the single quotes. Could also use s_quote instead.
                      'by extracting the values and not just passing in the variable name the 
                      'variables don't need to be global. They could just be local form variables.
                      'when testing logical values for true you don't need to say 'var=.t.' 
                      'because a logical variable is by definition true or false so simply
                      'writing 'var' will return true if true. This saves 4 characters.
                      ' testing for false you could write '.not. var' but that uses one more character.
                      'The code below using the test values will make a filter string 438 chars long. 
                      'This is with all parameters active. If only testing for a few the string will be much smaller
                      'and may even run quicker because it is doing less testing at each record.
                      If var->vsrchc1 <> "*" .and. var->vsrchc1 <> "" then
                      	filter = filter  + ".and. '" + var->vsrchc1 + "' $ manufacturer "
                      end if
                      If var->vsrchc2 <> "*" .and. var->vsrchc2 <> "" then
                      	filter = filter + ".and. '" + var->vsrchc2 + "' $ owner "
                      end if
                      If var->vsrchc3 <> "*" .and. var->vsrchc3 <> "" then
                      	filter = filter  + ".and. '" + var->vsrchc3 + "' $ description "
                      end if
                      If var->vsrchL1 then
                      	filter = filter + ".and. " + "inactive=.f. "
                      end if
                      If var->vsrchL2 then
                      	filter = filter + ".and. " + "(quantity-qty_on_sales_order)>0 "
                      end if
                      If var->vsrchc4 <> "*" .and. var->vsrchc4 <> "" then
                      	filter = filter  + ".and. '" + var->vsrchc4 + "' $ model_no "
                      end if
                      If var->vsrchc5 <> "*" .and. var->vsrchc5 <> "" then
                      	filter = filter  + ".and. '" + var->vsrchc5 + "' = location_room "
                      end if
                      If var->vsrchc6 <> "*" .and. var->vsrchc6 <> "" then
                      	filter = filter  + ".and. '" + var->vsrchc6 + "' = location_rack "
                      end if
                      If var->vsrchc7 <> "*" .and. var->vsrchc7 <> "" then
                      	filter = filter  + ".and. '" + var->vsrchc7 + "' = location_shelf "
                      end if
                      If var->vsrchc8 <> "*" .and. var->vsrchc8 <> "" then
                      	filter = filter  + ".and. '" + var->vsrchc8 + "' = location_zone "
                      end if
                      If .not. var->vsrchL3 then
                      	filter = filter + ".and. '" + "new' !$ condition "
                      end if
                      If .not. var->vsrchL4 then
                      	filter = filter + ".and. '" + "used' !$ condition "
                      end if
                      If .not. var->vsrchL5 then
                      	filter = filter + ".and. '" + "parts' !$ condition "
                      end if
                      If .not. var->vsrchL6 then
                      	filter = filter + ".and. '" + "repair' !$ condition "
                      end if
                      If .not. var->vsrchL7 then
                      	filter = filter + ".and. " + "isnotblank(\"condition\") "
                      end if
                      If .not. var->vLog2 then
                      	filter = filter + ".and. " + "export_eligible "
                      end if
                      If .not. var->vLog1 then
                      	filter = filter + ".and. " + "donotship=.f. "
                      end if
                      'remove the leading  '.and.'
                      filter = *word_remove(filter,1," ")
                      ' Show the resulting filter string
                      'Query.filter = filter
                      ' the filter as supplied in the message board
                      '       query.filter = "((var->vsrchc1= "*") .or. ((var->vsrchc1 $ manufacturer))) .and. 
                      '       ((var->vsrchc2 = "*") .or. ((var->vsrchc2 $ owner))) .and. 
                      '       ((var->vsrchc3="*") .or. ((var->vsrchc3 $ description))) .and. 
                      '       ((var->vsrchL1=.f.) .or. (inactive=.f.)) .and.
                      '       ((var->vsrchL2=.f.) .or.(quantity-qty_on_sales_order>0)) .and. 
                      '       ((var->vsrchc4="*") .or. ((var->vsrchc4$model_no))).and. 
                      '       ((var->vsrchc5="*") .or. ((var->vsrchc5=location_room))).and. 
                      '       ((var->vsrchc6="*") .or. ((var->vsrchc6=location_rack))).and. 
                      '       ((var->vsrchc7="*") .or. ((var->vsrchc7=location_shelf))).and. 
                      '       ((var->vsrchc8="*") .or. ((var->vsrchc8=location_zone))) .and. 
                      '       ((var->vsrchl3=.t.) .or.( .not.("new"$condition))) .and. 
                      '       ((var->vsrchl4=.t.) .or.( .not.("used"$condition))) .and. 
                      '       ((var->vsrchl5=.t.) .or.( .not.("parts"$condition))) .and. 
                      '       ((var->vsrchl6=.t.) .or.( .not.("repair"$condition))) .and. 
                      '       ((var->vsrchl7=.t.) .or. (isnotblank ("condition"))).AND. 
                      '       ((var->vlog2=.t.) .or.(export_eligible=.t.)) .and. 
                      '       ((var->vlog1=.t.) .or.(donotship=.f.))"
                      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.


                        Re: Query Filter

                        I had come to a similar conclusion and will try it tomorrow.
                        But if I call another function like a report will the functions still be considered local, was not sure...


                          Re: Query Filter

                          Tim, thanks for the input. I was just taking the simplistic approach, not thinking that of course wild card selections ( =* )don't need to be in the filter at all, so my filter was much longer (and slower) than it should be.