Filter in Lookup to only view items that are blank

Follow

Casey Kolowinski

• June 03, 2020 18:31

Hi There,

I'm using a Lookup to reach out to a Data Entity. Works great, but when I add a Filter condition that says Column X = Blank, it's not pulling back any results. The inverse works: Column X != Blank, but I can't get this to work.

Typically just leaving the textbox empty would signify Blank, but I'm wondering if there's a different syntax that I need to use. Any help would be appreciated.

Thanks,

Casey

0

• 

  Loren Bratzler

  • June 05, 2020 17:21

  Curious - When you say Column X = blank do you really mean that the condition is Column X = ' '  

  0

  Comment actions Permalink
• 

  Casey Kolowinski

  • June 05, 2020 17:39

  That's exactly what I mean. It won't work for me when I try to use that as a condition. I'm trying to pull back all values from my Data Entity into a data grid where Column X is empty, and again, the inverse works, but I can't find a syntax that allows me to check for blanks. I would have expected to either just leave the right side of the condition blank, or use '' or "" to get the job done but neither seems to work.

  Thoughts? Loren Bratzler thank you

  0

  Comment actions Permalink
• 

  Loren Bratzler

  • June 05, 2020 17:47

  I have not done anything yet with AgilePoint Data Entities but I have done a lot of lookups against other SQL tables.  When dealing with something like this in a SQL table, you need to know if the blank values in the column are actually blanks (spaces) or if the field is NULL.  Maybe something like this might work:

  select *
  from Table_X
  where Column_X is null OR Column_X <= ' '

  0

  Comment actions Permalink
• 

  Casey Kolowinski

  • June 05, 2020 17:52

  I thought about that, but the problem is that Data Entities don't allow for Custom Queries like that. I'm restricted to the Quick Config filtering which doesn't have the IS NULL option, which is likely the problem

  0

  Comment actions Permalink
• 

  Gwen Wilson

  • June 05, 2020 18:13

  I haven't used data entities, but in other areas that I've used sql like this to compare the value of a lookup list drop down, the default value for a blank is -1  So, column = "-1". I don't know if that's what you are comparing, but worth a shot.

  0

  Comment actions Permalink
• 

  Casey Kolowinski

  • June 05, 2020 18:18

  Thanks Gwen! I really appreciate all the help. It's unfortunately not a dropdown, it's just a text field both in the Source and on the Form, so that didn't work either.

  I have done a workaround where I just make sure that all of the data in the entity is filled out with a basic _ and it doesn't look too bad, and then my filter has a value to search against to avoid the blank.

  It just seemed odd to me that the Quick Config options in a lookup don't have an "is blank" or "is null" or "is empty"

  Also very much wish I could do the Advanced Config/Query for a data entity, but I can "fake" it with an On-Premises version and just use the actual SQL tables on the backend instead of using the Data Entity in the traditional sense, since the Data Entity is just another table in the SQL schema behind the scenes. More difficult to do in the Cloud versions, but yeah, I've got a workaround for now.

  Thank you all for your inputs.

  0

  Comment actions Permalink
• 

  Loren Bratzler

  • June 05, 2020 18:33

  Gwen brings up a good point.  If you have a drop-down field and the user does not select a value in it (leaving it as "Please Select"), the drop-down field will have a value of -1 in the schema data.

  However, I don't think that is the case for a Text Box.

  0

  Comment actions Permalink
• 

  Alexey Shmelkin (sysberry-absc.com)

  • June 06, 2020 09:11

  Hi Casey,

  if you use OnPrem AgilePoint you can create a database Access Token to the Data entity and then work with the table as a normal sql table. This is sometimes neccessary as long not all options of sql tables can be done with "vanila" data entity API.

  Regards
  Alexey

  0

  Comment actions Permalink
• 

  Casey Kolowinski

  • June 08, 2020 15:18

  Thanks Alexey, we have both On-Prem and Cloud, and in this particular use case I'm restricted to the Cloud, or else I would definitely do it that way!

  Appreciate all of the inputs.

  0

  Comment actions Permalink

Please sign in to leave a comment.