Alpha Video Training
Results 1 to 22 of 22

Thread: Using variable for field name in filter expression

  1. #1
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Using variable for field name in filter expression

    Hello All,

    I have a filter expression as shown below.

    Code:
    (Display_name <> Var->vc_U_1  )  .and. (Display_name <> Var->vc_U_2  ) .and. (Display_name <> Var->vc_U_3  ) .and. Llsb_plate=.T.
    I have a variable vc_qualification which contains the value Llsb_plate

    I need to use the variable in the filter expression instead of the hard coded Llsb_plate.

    Does anyone know the proper syntax for accomplishing this?

    Thanks in advance.

    Louis

  2. #2
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Using variable for field name in filter expression

    (Display_name <> Var->vc_U_1 ) .and. (Display_name <> Var->vc_U_2 ) .and. (Display_name <> Var->vc_U_3 ) .and. (var->vc_qualification=.T.)

    But this is not the actual filter is it? Usually a filter is double-quoted. And I assumed that the variable is also a logical
    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
    __________________________________________




  3. #3
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Re: Using variable for field name in filter expression

    Mike,

    Thank you for responding.

    I must not have explained well enough. Your suggestion tests to see if the variable vc_qualification = .t.

    What I need to do is create a filter with the following characteristics:

    The Field Display_Name can not match the value of Var->vc_U_1
    The Field Display_Name can not match the value of Var->vc_U_2
    The Field Display_Name can not match the value of Var->vc_U_3

    That part is easy...

    The next thing I need to test for (and this is where I'm having a tough time) is:

    The LOGICAL field named Llsb_plate must be .t.

    However, in my filter expression I can not literally type (Llsb_plate = .t.) because the name of the field I am testing against will depend on the values of a couple of other fields on the form. This time I'm testing against Llsb_plate but next time I might be testing against Llxyx_plate.

    Instead, the value "Llsb_plate" exists as the value held in the variable named vc_qualification.

    So... I need to be able to use the variable in the filter expression IN THE PLACE OF the actual field name but I need the filter expression to understand that I want to test against the field in the table not the variable itself.

    Hope I haven't completely confused you at this point.

    As always, thank you for your help.

    Louis

  4. #4
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Using variable for field name in filter expression

    What is the context of the filter expression. Ie query genie, xbasic code?
    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.

  5. #5
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Re: Using variable for field name in filter expression

    Mike,

    You asked:

    But this is not the actual filter is it? Usually a filter is double-quoted. And I assumed that the variable is also a logical

    No, I suppose it isn't. The code you saw was copied from the filter builder.

    See attached screen shot.

    Louis

  6. #6
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Re: Using variable for field name in filter expression

    Hey Tim,

    The filter is being used to limit the records displayed in a record list list box which is contained in an X-Dialog box.

    Thanks for jumping in on this one.

    Louis

  7. #7
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Using variable for field name in filter expression

    So vc_qualification actually represents a table field which can be any that it is set to prior to this filter....I have done this but cannot remember exactly how....very possible I needed either eval(var->vc_qualification) or evaluate_template(var->vc_qualification)

    Tim--you're the one who first brought evaluate_template() to my attention...am I on the right track??? :)
    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
    __________________________________________




  8. #8
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Re: Using variable for field name in filter expression

    Mike,

    Yes, that's what I need to do.

    I will check out eval() and evaluate_template() to see if I can make some headway.

    Thanks again!

    Louis

  9. #9
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Using variable for field name in filter expression

    To simplify a bit.... when testing a logical var or field you don't need to use '= .t.' as the var or field is inherently true or false. That just leaves resolving the variable to a field name. Not sure if this works in the context of the filter builder but try eval(var->vc_qualification).

    As per MikeC

    I am a bit behind you guys today. :)
    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.

  10. #10
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Re: Using variable for field name in filter expression

    Mike & Tim,

    I have tried using both eval() and evaluate_template as you suggested. I also gave evaluate_string() a shot, but no luck.

    In the Interactive Window:

    vc_field_name="ABC"

    ?eval("vc_field_name")
    = "ABC"

    ?evaluate_string("{vc_field_name}")
    = "ABC"


    Evaluate_Template() appears to be used for executing commands and probably isn't used in this context.

    Regardless, I can't seem to get eval() or evaluate_string() to work in my filter.

    Maybe I just need to call it a night and start with a fresh mind tomorrow.

    Thank you both for your help.

    Louis

  11. #11
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Using variable for field name in filter expression

    possibly
    eval(var->vc_qualification+".value")

    Would be my next guess....

    sample??? I'll have some time tomorrow to play with it if still at a dead end.
    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
    __________________________________________




  12. #12
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Using variable for field name in filter expression

    In my previous post I said that you do not need the "= .t.".
    After doing some testing in the query genie against a form I have found that if the variable is declared as shared/session then you don't need it (but no problem if you include it) but if declared as global then you do. I don't know why. Lets call it a quirk. So for now ignore my advice and leave it in.

    Have you tried eval(vc_field_name)? ie without the quotes.

    As you have seen eval("vc_field_name") evaluates to the name(value) you assigned to vc_field_name. This is because eval() is evaluating the quoted expression which resolves to vc_field_name which contains "ABC"


    Without the quotes, eval(vc_field_name), the eval() function evaluates the string contained in vc_field_name which is "ABC". Which would be the same as having eval("ABC").

    Code:
    ABC = .t.
    
    vc_field_name="ABC"
    
    ?eval("vc_field_name")
    = "ABC"
    
    ?eval(vc_field_name)
    = .T.
    
    ?eval("ABC")
    = .T.
    So I would be trying eval(var->vc_qualification) = .t.
    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.

  13. #13
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Re: Using variable for field name in filter expression

    Tim and Mike,

    No luck with your suggestions.

    I will piece together a working example and post it to this thread. I'm off to work right now so it will be later this afternoon before I have the time to put this together.

    Thank you both for your help!

    Louis

  14. #14
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,875

    Default Re: Using variable for field name in filter expression

    Maybe this seems old-school, but I am coming from a lot of code in ver 5 and ver 9, and this method works for me every time.

    If this is actually in x-basic, then the code:

    Code:
    (Display_name <> Var->vc_U_1 ) .and. (Display_name <> Var->vc_U_2 ) .and. (Display_name <> Var->vc_U_3 ) .and. (var->vc_qualification=.T.)
    should look something like this:

    Code:
    DIM filt as C
    filt = replace_parameters("Display_name <> [Var->vc_U_1] ) .and. (Display_name <> [Var->vc_U_2] ) .and. (Display_name <> [Var->vc_U_3] ) .and. ([var->vc_qualification]=.T.",local_variables())
    then use "Filt" as the filter part of the expression.

    Tom

  15. #15
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Using variable for field name in filter expression

    Tim,
    I missed that Louis had quoted the variables instead of using what I had shown (no quotes)--nice explanation.

    Tom,
    I have not used the replace_parameters function except when left as a remnant of Action Scripting when converting scripts....but the help file stipulates the var-> has to have the variable type included...does it work without this being used?
    The parameters in the expression must be enclosed in square brackets ( [ ] ) and are preceded with varC->, varN->, varD-> or varL->, depending on the type of the variable.
    Note : You must use Version 6 or higher to support varT->.

    Louis,
    Looking forward to your sample.
    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
    __________________________________________




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

    Default Re: Using variable for field name in filter expression

    I don't think you can use eval() in the context of the expression builder to evaluate the table field value. It seems to be looking for a variable. I tried a bunch of ways and could only get it to work with a table pointer. I made an example to play with if anyone wants.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  17. #17
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Re: Using variable for field name in filter expression

    Hello All,

    Attached is a sample database. When you open the one and only form, there is a detailed description of what is happening and the desired result.

    I really appreciate you guys taking a look at this. It seems that what I want to do should be fairly straight forward...I just can't seem to get past this filter issue.

    Thanks for all your help.

    Louis

  18. #18
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,875

    Default Re: Using variable for field name in filter expression

    Mike C,

    You're right, you need varC-> or varD-> etc for it to work properly.

    I'm still in the ver 5 world, so I use what I know.

    Tom

  19. #19
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Using variable for field name in filter expression

    Here ya go Louis....hope it does what you want.

    I changed the variables to global as it just did not seem to like it when I had them as shared!....at least while I was in the expression builder.

    Changed the filter in the list box xdialog.
    Also changed the table structure for one Logic field that was misnamed. (think it was Lotic instead of Logic).


    Attachment 25393
    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
    __________________________________________




  20. #20
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Re: Using variable for field name in filter expression

    Mike C,

    That works perfectly, thank you! I will study your code so that hopefully, the next time a similar situation comes up, I will be better prepared.

    Tim, Tom and Mike Wilson,

    Thank you for taking the time to look at this challenge and offer your input. I have learned from each of you and your assistance is truly appreciated.

    Now, I'm off to add some date values into this filter as well.

    Kindest Regards,

    Louis

  21. #21
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Using variable for field name in filter expression

    Glad it worked....when studying the code, I had left some in that does nothing currently--part of my experimenting....In the OnChange events of the two fields that set the filter the only code needed is

    Code:
    dim global vFld as c
    
    vFld=case(topparent:Test_Field_A.value = "North" .and. topparent:Test_Field_B.value = "Up","Logic_Field_1", topparent:Test_Field_A.value = "North" .and. topparent:Test_Field_B.value = "Down", "Logic_Field_2",topparent:Test_Field_A.value = "South" .and. topparent:Test_Field_B.value = "Up","Logic_Field_3",topparent:Test_Field_A.value = "South" .and. topparent:Test_Field_B.value = "Down","Logic_Field_4" )
    what follows it can be deleted.
    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
    __________________________________________




  22. #22
    "Certified" Alphaholic
    Real Name
    Louis Nickerson
    Join Date
    Aug 2002
    Posts
    1,039

    Default Re: Using variable for field name in filter expression

    Mike,

    When I first looked at your code, I wondered why you were opening the table but I assumed you had a good reason...and after pulling my hair out on this one, I wasn't about to screw with success.

    I have since, removed the unnecessary code and all is working well.

    Thank you again for sharing your expertise. I hope I can repay the favor some day.

    Kindest,

    Louis

Similar Threads

  1. Query Filter - Expression for Variable Charactor
    By johnkoh in forum Alpha Five Version 9 - Desktop Applications
    Replies: 6
    Last Post: 12-10-2009, 09:35 AM
  2. Variable into Field Name expression
    By Citadel in forum Alpha Five Version 10 - Desktop Applications
    Replies: 4
    Last Post: 11-30-2009, 11:42 AM
  3. branch expression sees field name as variable
    By Bob Arbuthnot in forum Alpha Five Version 8
    Replies: 3
    Last Post: 01-28-2008, 12:05 AM
  4. Using variable from MultiSelect List in a filter expression?
    By Charlain in forum Alpha Five Version 8
    Replies: 2
    Last Post: 09-26-2007, 03:33 PM
  5. Field Selection by Expression or Variable
    By Robin Hoare in forum Alpha Five Version 7
    Replies: 10
    Last Post: 01-08-2006, 02:47 PM

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
  •