Alpha Video Training
Results 1 to 13 of 13

Thread: Filter issue

  1. #1
    Member
    Real Name
    Jo Hulsen
    Join Date
    Mar 2006
    Location
    The Netherlands
    Posts
    332

    Default Filter issue

    In an attempt to avoid hard coding I created next script that needs to save location based reports in PDF format and FTP those reports to a website.

    dim lijst as C
    dim tbl as p
    dim recs as n

    tbl = table.open("facility")
    tbl.fetch_first()
    count = tbl.records_get()
    while .not. tbl.fetch_eof()
    for i = 1 TO count
    debug(1)
    lijst = tbl.Tfc_Id
    query.filter = "tbl.Tcf_id = var->lijst"
    ' query.order = ""
    query.options="N"
    query.decription="Number Locations"
    Report.SaveAs("Booking report","PDF",query.filter,query.order,var->lijst+"."+"PDF")
    tbl.fetch_next()
    next i
    end while
    tbl.close()
    end

    There seems to be an error in the query filter and I have no idea what's wrong. Perhaps any of you could help?

    Thanks,
    Jo Hulsen
    Dommel Valley Solutions
    The Netherlands

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,313

    Default Re: Filter issue

    As others have noted a useful troubleshooting technique for query filter expressions is to strip out the variables and insert literal values. Get the report to run for a single case, then examine the expression carefully. Are quotes needed? Where are they placed? then substitute a variable for the literal, being careful to include quotes where needed.

    -- tom

    ps Your script will be easier to read if formatted with indents. Formatted code will be pushed to the left margin by the message board editor unless you surround it with CODE tags, an option available to you only if you use the advanced reply editor.

  3. #3
    Member
    Real Name
    Jo Hulsen
    Join Date
    Mar 2006
    Location
    The Netherlands
    Posts
    332

    Default Re: Filter issue

    Thanks Tom,

    I was already following the single report route without use of variable. Think I need to do a bit more research.

    Also thanks for your ps. Script was formatted but I had no clue as to how it would appear in a message.
    Jo Hulsen
    Dommel Valley Solutions
    The Netherlands

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Filter issue

    I was already following the single report route without use of variable. Think I need to do a bit more research.
    As Tom indicates, start with the filter in the simple report you have now. Since your script indicates

    dim lijst as C

    it is likely your filter in the simple report looks like

    Tcf_id = "some_value_here"

    When you change to a variable in a script you have to quote the entire filter expression. (among other things)


    "Tcf_id = "some_value_here""

    Now you have to tell Alpha how to handle the interior quotes (red). You also have to place the variable where the static value was and concatenate (+) the pieces before and after the static value.
    There can be only one.

  5. #5
    Member
    Real Name
    Jo Hulsen
    Join Date
    Mar 2006
    Location
    The Netherlands
    Posts
    332

    Default Re: Filter issue

    Thanks Stan,

    I looked at the filter in the single report, applied that to the script and it works.
    The filter is:
    query.filter = "facility->Tfc_Id=\"f1\""
    The constant value f1 needs to be replaced by the variable in "lijst".

    So the filter would need to be something like:
    query.filter = "Facility->Tfc_Id=\" + lijst + "\""
    I keep getting either an error when saving or an incorrect result in the debugger. Looks like either one " too much or not enough.

    According to your explanation one more " is needed at the beginning but that produces an error when saving.
    I don't know how to solve this.
    Your help would be much appreciated.

    Thanks
    Jo Hulsen
    Dommel Valley Solutions
    The Netherlands

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,313

    Default Re: Filter issue

    Jo, you're close. Stay curious.

    The help file topic "Writing Expressions" will be helpful in explaining what you are seeing.

    -- tom

  7. #7
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Filter issue

    query.filter = "facility->Tfc_Id=\"f1\""
    First transformation: Stop the filter string just before the literal value and restart it after.

    query.filter = "facility->Tfc_Id="\"f1\"""

    Second transformation: concatenate the three pieces.

    query.filter = "facility->Tfc_Id="+\"f1\"+""

    Third transformation: replace the literal value with the variable. Since the literal is quoted, so must the variable's value.

    query.filter = "facility->Tfc_Id="+quote(var->lijst)+""

    In this case the trailing +"" is trivial and can be removed. If we were continuing with more filter criteria you might use it such as

    query.filter = "facility->Tfc_Id="+quote(var->lijst)+" .and. myage = 59"


    So this

    query.filter = "facility->Tfc_Id="+quote(var->lijst)

    or

    query.filter = "tbl.Tfc_Id="+quote(var->lijst)

    depending on the context, should be what you want.
    There can be only one.

  8. #8
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,216

    Default Re: Filter issue

    deleted. Stan got to it.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  9. #9
    Member
    Real Name
    Jo Hulsen
    Join Date
    Mar 2006
    Location
    The Netherlands
    Posts
    332

    Default Re: Filter issue

    Stan,


    Thanks very muchs for this.
    The query filter seems to need a qualified table name, don't know why but that doesn't really matter to me.
    query.filter = "facility->Tfc_Id="+quote(var->lijst)+" .and. myage = 59+1"

    Thanks again,
    Jo Hulsen
    Dommel Valley Solutions
    The Netherlands

  10. #10
    Member
    Real Name
    Jo Hulsen
    Join Date
    Mar 2006
    Location
    The Netherlands
    Posts
    332

    Default Re: Filter issue

    Quote Originally Posted by Mike Wilson View Post
    deleted. Stan got to it.
    Mike,

    That's correct. tried your solution but the table name seems to be a requirement.

    Thanks anyway,
    Jo Hulsen
    Dommel Valley Solutions
    The Netherlands

  11. #11
    Member
    Real Name
    Jo Hulsen
    Join Date
    Mar 2006
    Location
    The Netherlands
    Posts
    332

    Default Re: Filter issue

    Quote Originally Posted by Tom Cone Jr View Post
    Jo, you're close. Stay curious.

    The help file topic "Writing Expressions" will be helpful in explaining what you are seeing.

    -- tom
    Tom,

    Thanks for your support.
    Stan has kindly provided a solution. I will out of curiousity need to have a good look at that topic anyway.
    Next challenge is to store pfd reports and upload them to a website. All in that same routine. Must be working later today!
    Simply putting a bit of pressure on myself!

    Regards,
    Jo Hulsen
    Dommel Valley Solutions
    The Netherlands

  12. #12
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Filter issue

    Glad we helped but as Tom noted, "The help file topic "Writing Expressions" will be helpful in explaining what you are seeing."

    Do you understand the process, at least in part? This topic is a vital part of employing the behind the scenes power in Alpha.

    What if your variable had been date type? Maybe a mix of date and character and logical types? I jumped the gun a bit leading you to the answer where Tom was trying to direct you to the explanation of the process.

    Aside from the cited section of the help files the important thing to remember is that you are constructing a filter string. A string is composed of characters. To construct the proper filter string using variables you must convert those variable's values to character equivalents and incorporate them in the string. Doing that means you must break apart the literal portions of the filter so you can insert references to the variables. Merely inserting the variable's name in the string doesn't tell Alpha that you don't mean the characters "myvariablename".
    There can be only one.

  13. #13
    Member
    Real Name
    Jo Hulsen
    Join Date
    Mar 2006
    Location
    The Netherlands
    Posts
    332

    Default Re: Filter issue

    Stan,

    As I wrote to Tom my curiousity hasn't ended.

    I do understand the process in general but also have to admit that more study on this topic is defenitely required to gain more knowledge about the details of writing expressions.

    Never too old to learn!

    Thanks,
    Jo Hulsen
    Dommel Valley Solutions
    The Netherlands

Similar Threads

  1. Filter Issue - Not working
    By Rachel Siegel in forum QReportBuilder
    Replies: 2
    Last Post: 02-04-2008, 03:40 PM
  2. sub report filter issue
    By richard2007 in forum Alpha Five Version 8
    Replies: 3
    Last Post: 02-01-2008, 10:50 AM
  3. stack underflow message, is this a filter issue?
    By Bill in forum Alpha Five Version 7
    Replies: 2
    Last Post: 01-18-2006, 11:12 AM
  4. Report Filter Issue
    By spain246 in forum Alpha Five Version 6
    Replies: 2
    Last Post: 08-05-2005, 10:57 AM
  5. Filter Builder issue
    By Blake in forum Alpha Five Version 6
    Replies: 2
    Last Post: 08-02-2005, 06:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •