Interactive Filter and Database View - View has duplicate fields & filter isn't working

[Sue Frost]

I'm trying to build an interactive filter against the Assigned To field.

When I try to run the filter against a database view it's not working. The database view joins two tables (u_case and task_sla), both of which have an Assigned To field. The filter is returning results where BOTH fields on database view match the filter - and that's not always true.

The correct field is specified on the filter. I've cleared the cache. I've tried turning off the options on the filter to 'apply to all tables in hierarchy' and 'Apply to both database views and tables' and then to specify the database view directly in the Interactive Filter References list. Nothing is working.

Am I doing something wrong or is my only option to build a separate Assigned To filter that only runs against the database view? 

Here are the results when I click through the filtered dashboard report - both fields are being used in the filter:

Here's the interactive filter with table references - I have the correct field selected in the filter (tt_assigned_to):

And here's the database view with the tables listed:

[Bert_c1]

for the duplicate fields aspect, open each 'View table' and specify the fields from each that you want. Then click 'Try it'.

https://docs.servicenow.com/search?q=Database%20views

And check the documentation on Interactive Filters:

https://docs.servicenow.com/search?q=Interactive%20Filters

There are plenty of OOB examples in your instance.

[Sue Frost · Accepted solution]

Thank you!

That pointed me towards a solution.

Going to 'View table' and selecting specific fields will definitely fix the issue.

However, for us that's not a good solution since one of the tables is quite active and has more fields added regularly. That means we'd have to remember to add the field to the database view (or not). It's maintenance load. We also have a lot of reports based on that view that could potentially be impacted by such a change. (I'm just documenting this as considerations for the next person with the same question.)

In the end, I've built a separate interactive filter specifically for the "tt_assigned_to" field on the Database view. That's very low risk. I've let the user know that they'll only run into issues if they have a dashboard tab with reports based on multiple tables and add both interactive filters. I can see that causing filtering issues.

[AndersBGS]

Hi @Sue Frost, 

Have you verified the "assigned to" field of the table "x_enig_commcase_u_ci_case_sla", if the field is extended from the task_sla? If it is, then that is the cause of your issue. 

Due to that both tables are setup up as refenrece tables of the interactive filter, the interactive filter will only show records, if both table assigned to is equal to the interactive filter. If you only want either the one or the other to be true, then you need to remove the other table as reference on the interactive filter.

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

Best regards

Anders

[Bert_c1]

looking at the 'where' clauses of your screen shot of the database view, the 'order' value of the table with the 'where' clause is before the 'order' value of the 2nd table.  but references the 2nd table. try reversing the order. or move the 'where' clause to the 'task_sla' table. Again, use the 'Try it' on the database view to see if you get expected results.