Is there a way to set up a query entity to filter on fields that are an empty st

Question

Is there a way to set up a query entity to filter on fields that are an empty string?

The following returns an error:

a!queryFilter(
field: "FIELDNAME",
operator: "<>",
value: ""
),

However, operator: "is null" seems to exclude the results that are "".

This is especially an issue because Appian will write an "" to a varchar field when a null is passed into a Write to Datastore entity. Now there is no way to query for these rows.

jarrods229 · Answer

The following seems to work in the query filter from what I can tell operator: "not in", value:{""}

abhi.jana · Answer

This may require applying a second filter on the FIELDNAME column of the retrieved datasubset to perform a length check. As to the issue where the db stores nulls as empty strings - this has been reported to Engineering (Reference no. AN-45612)

abhi.jana · Answer

I meant that this would be a two step process - 1. Store data from query into a local variable 2. Filter the datasubset Here's an example - load ( local!queryData: rule!getAllEmployees(), a!formLayout( label: "Null filter test", firstColumnContents: { a!textField( label: "All data", value: local!queryData, readOnly: true() ), a!textField( label: "Filtered data", value: index(local!queryData, where(len(local!queryData.name)>1), {}), readOnly: true() ), } ) )

Carlos Santander · Answer

Jason, in cases like yours, we've resorted to having the database do the null check (or length check, as Abhi suggests) by creating a view. Then you can just add a filter on that new boolean column from the view.

Mike Schmitt · Answer

Hey Priyanka - 
 There are a couple different issues with your response here... 
 
 this thread is over 7 years old, including the comment you're replying to 
 the comment you're replying to has nothing to do with the issue you're asking about - it probably would've been better to just create your own original post for this 
 
 Per your question: you're asking about the "showWhen" parameter on the a!gridColumn() component. For a column, you can't use fv!row data to show/hide the column, because for any given column, it'll always be shown/hidden regardless of individual row data. In fact, the fv!row data is not even accessible from the showWhen parameter, for this reason. 
 In a grid, you can't have some rows "show" a certain column and other rows "hide" it - that wouldn't really even be a grid at that point. The best you can do is show an empty value in that column for that row (which you already seem to be doing), or decide to hide the entire column based on some external factor that you use on-form logic to calculate in advance.

Is there a way to set up a query entity to filter on fields that are an empty st

Top Replies