Autolookup to SQL Database with multiple filter conditions, but some filters may be "blank"

Comments

9 comments

  • Hi Gwen,

    Did you try SQL statements with "like '%${control}%'" conditions? Another way is to use Stored Procedure and check if fields are empty inside the SP. Hope it helps.

    Regards,
    Alexey

    0
    Comment actions Permalink
  • Avatar
    Gwen Wilson

    Thanks Alexey, I built this... 

    where Customer_ID like '%${txt_srchCustomer}%' and Lifecycle_Stage_Desc like '%${txt_Srch_LifeCycleStage}%'

    In the Lookup Source configuration (where the sql statement goes on the autolookup), it validated with all combinations (1) leaving the variables blank (and got all rows returned),  2) one variable populated but not the other, and 3) both populated.... so I thought it was going to work on the form. However, in the form, it doesn't (no records). 

    So the form controls are "different" values when empty, I guess. The drop downs have "Please Select" in them, but I didn't think that was the actual value. Just in case, I made 2 text boxes to use for input (instead of the drop downs) so I wouldn't have to worry about "Please select". But they behave the same way. So, I wonder if the controls are actually "Null" values, so that's why the sql doesn't work there? 

    0
    Comment actions Permalink
  • Hi Gwen,

    Then i guess you should go for stored procedures if "like" doesn't work properly. I would also create a ticket for agilepoint support team to enhance this behaviour in future releases.

    Regards,
    Alexey

    0
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    If you can figure out what default-value is stored in the fields when nothing is selected, you should be able to create a query that looks like this:

    SELECT Customer_Name as txt_resCustomerName,
    RFQ_ID as txt_resRFQID,
    Location as txt_resLocation,
    Lifecycle_Stage_Desc as txt_resLifeCycleStage
    FROM dbo.[EPE_RFQ_Quotes]
    where (Customer_ID = '${txt_srchCustomer}' or '${txt_srchCustomer}' = 'default-value')
    and (Lifecycle_Stage_Desc = '${txt_Srch_LifeCycleStage}' or '${txt_Srch_LifeCycleStage}' = 'default-value')

    Often times, drop-down fields will have a default-value of -1 when nothing is selected but you may want to run a test to find out what value is being stored in your fields.

    0
    Comment actions Permalink
  • Avatar
    Gwen Wilson

    Thank you, Loren! This worked beautifully. We had tried adding "or" statements with blank values (which of course didn't work). I never would've guessed the default for a drop down is -1.

    0
    Comment actions Permalink
  • Avatar
    Gwen Wilson

    This worked perfectly for dropdowns with Lookup lists (default = -1). However, I have one dropdown that is an inline list. The default value is not -1. Any clue what it could be? I've tried 1, 0, -1, -2, -3, blank, null.  I'm not sure how to tell what the default is.

    Thanks!

    0
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    Gwen - I believe when you have an in-line drop-down list, the default value is going to be whatever the first value is in your options list.

    For example, I created a quick test app and added a drop-down control.  The initial entry that is created for you when you specify in-line list is "Please Select".  I then added some additional options:

     

    I then published it and ran a test where I did not select anything from the drop-down.  The value stored in the field was "Please Select":

    0
    Comment actions Permalink
  • Avatar
    Gwen Wilson

    Thanks! I didn't think Please Select was actually the value. I should've tried that. I've never seen that write to my database, but maybe I just haven't had the right scenario.

    0
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    It is interesting too that if you make the drop-down a mandatory field, the system will prompt the user to select something from the list if they leave it as "Please Select".  But if you don't have a "Please Select" option like this:

    then the user does not have to make a selection and they will end up with "Option 2".

    So I guess "Please Select" is treated as no selection.

    0
    Comment actions Permalink

Please sign in to leave a comment.