Filter in Lookup to only view items that are blank

Comments

9 comments

  • Avatar
    Loren Bratzler

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

    0
    Comment actions Permalink
  • Avatar
    Casey Kolowinski

    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
  • Avatar
    Loren Bratzler

    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
  • Avatar
    Casey Kolowinski

    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
  • Avatar
    Gwen Wilson

    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
  • Avatar
    Casey Kolowinski

    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
  • Avatar
    Loren Bratzler

    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
  • 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
  • Avatar
    Casey Kolowinski

    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.