Autolookup to SQL Database with multiple filter conditions, but some filters may be "blank"
Hi, I'm using AgilePoint NX 6.0. This is a 2 part question (I'll make a separate post about each one, since they are actually 2 separate issues).
I need to be able to have multiple fields that a user can optionally use in order to query the SQL database and bring the data back into a subform on the eform. I can do this if the user uses every search field, but the query doesn't work if they leave one field empty. Right now I'm only using 2 fields (trying to get this to work before adding more ... there will likely by about 8-10 search filter fields). I have verified that each filter query works independently.
Here's what the form looks like initially: 2 search filter fields (ignore the hidden trigger field). Ideally, after search fields are completed, they click Submit which runs the autolookup.
So, right now, it works if the user populates both the Lifecycle Stage and Customer field: in this example there is 1 matching record returned.
So, what I can't get to work is if the user selects only one filter field: in this example the user entered a LifeCycle stage. The below screenshot is what the results would ideally be (I had to remove the Customer filter from the SQL statement to get this screenshot).
Here's my SQL statement:
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}' and Lifecycle_Stage_Desc = '${txt_Srch_LifeCycleStage}'
How do I account for a null filter? Or is there a better way to do this? If I was only going to have a 2-3 filter fields, I guess I could build a separate autolookup for each filter field combination with some sort of trigger to determine which autolookup to run. However, since I'm going to have 8-10 or so filter fields, that's not feasible.
Thanks in advance for any direction you can help with!
-
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?
-
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.
-
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":
-
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.
Please sign in to leave a comment.
Comments
9 comments