Using RecordFilterList function does not filter properly if there are null values

What is the expected result and what do you observe?

Hi Stefan,

I am using recordfilterlist function which fetches all the candidate names from the view and lists them in the candidate name user filter drop-down. The column candidate name has null values also. For example if I select "Josh Mathew" In the filter it simply gives some random rows with different candidate names along with null values for the candidates rows. Actual result should show only rows where the candidate name is "Josh Mathew". I am not sure if this is because of the null values or due to view definition.

Hi Stefan,

I am using recordfilterlist function which fetches all the candidate names from the view and lists them in the candidate name user filter drop-down. The column candidate name has null values also. For example if I select "Josh Mathew" In the filter it simply gives some random rows with different candidate names along with null values for the candidates rows. Actual result should show only rows where the candidate name is "Josh Mathew". I am not sure if this is because of the null values or due to view definition.

Quick question, does view has PK?

Sometimes If the primary key field contains duplicate, non-unique values or null values we may see some weird behavior!

If you filter for a name, then DB would only return lines with this value. You mentioned a view. Did you test a query against the view itself in cloud database?

Views are really the stored results of a SELECT statement.  Therefore, they have no real underlying architecture that would require a primary key.

However, you can make sure that each of your rows has a unique value by SELECT-ing one of the primary keys from the data you've joined to make the VIEW.  Of course, that only works if only one of those rows is joined per row of the VIEW.

Unknown said:

Views are really the stored results of a SELECT statement.  Therefore, they have no real underlying architecture that would require a primary key.

However from the Appian side it's important to nominate one of the CDT elements as the primary key, and make sure that column in the View will be unique (or as close as you can get), or else querying on the view *will* result in some of those funny behaviors as Gayathris mentioned.

To add, when I'm creating a view (MSSQL) which does not have a unique column, I will always add one with ROW_NUMBER() for the view PK:

SELECT TOP (100) PERCENT ROW_NUMBER() OVER (ORDER BY processInstanceID) AS Row, 
processInstanceID,
processModelID
FROM tblCOE_SAMPLE_TABLE

Hi Stefan,

Thanks for your suggestion. The view did not give the expected result when queried from cloud database.  I found that the view definition was wrong.  When joining the tables based on condition the foreign key from a different table was given.  Once I corrected that, the issue got resolved.

Thanks everyone for giving suggestions which helped me to resolve the issue.