Create a Database View that shows which users from sys_user do not have a matching instance of a specific survey.

[Scott Megargee]

Hello All,

We have a need to track users who have not submitted a response to a specific survey. The challenge in our use case is we do not "pre-assign" the survey to our users and unfortunately this is something that cannot be avoided at this time.

My goal here is to create a database view with sys_user and asmt_assessment_instance. The resulting view should show all users from sys_user who "do not" have a matching instance of the survey in asmt_assessment_intance.

1st view table is sys_user with a variable prefix of usr and an order of 100. My fields are sys_id and user_name. I do not have a where clause on this table.

2nd view table is asmt_assessment_instance with a variable prefix of asmt and an order of 200. My fields are user and metric_type.  I have a where clause on this table where i'm saying asmt_user != usr_sys_id && (asmt_metic_type = 'name of survey'). 

The result should be 600 who don't have a survey of this instance assigned but i'm getting over 100K back. 

Thanks! 

[Brad Bowman · Accepted solution]

Hi Scott,

Database Views show columns from tables that are joined on a common field, so trying to join on something that is not equal is contrary to this design and purpose.  To get the results you are after, use this as the Where clause on your 200/asmt table.

usr_sys_id=asmt_user&&asmt_metric_type='3dafc6b2db32c340d087d8965e961950'

where that last number is the sys_id of your assessment.  Next, change the Left join column of this View Table record to true.

Now your list will contain every user and the details of the assessment if they have this one.  To get the users who don't have this assessment, just filter on Metric type is (empty).  If you need to use this someplace where applying a filter to the database view after it is created is not feasible, you can create a before Query Business Rule using the Database View as the table, and enter the criteria via the Script like this.

(function executeRule(current, previous /*null when async*/) {
	current.addQuery('asmt_metric_type', '');
})(current, previous);

Now the List view or report of the database view only contains users who have not submitted this survey.

[Scott Megargee]

This is excellent. I completely forgot about the left join setting true/fals. Really appreciate the help and quick response. 

Thank you,

Scott

[Brad Bowman]

You are welcome.