Internal notifications for workflow queues currently cannot be sent using system emails in Workflows. However, notifications can be accomplished with a Deliver mailing, and a recipient list built on a Configurable Joins enabled query.

This procedure will send one message per user daily, provided the user meets the filter criteria, rather than one message per application assigned in the queue. It uses a recipient list query on the Configurable Joins enabled User base.

As with a typical recipient list query, include standard exports to be used as merge fields in our mailing, such as User First Name, User Last Name, User Email, and any other values that should be included in the message. For this notification mailing, use an export that returns the number of records a user currently has assigned in their Workflow queue to let the mailing recipients know how many applications are awaiting their review. Filter this recipient list to include only users with applications assigned in their Workflow queue so that users are not sent notifications for applications that aren't assigned to them. Neither the export nor the filter are standard, so they must be created using the tools available in Configurable Joins.

Building Configurable Joins

When creating the export and filter, consider how relationships work in Slate. The query has a base of User, meaning that it returns one row per user. One user may have many applications assigned to them in the default Reader or in a Workflow. This is a one-to-many relationship. The relationship in the opposite direction is generally (but not always) a one-to-one relationship - one application is only ever associated with a single person.

When building joins, think about these relationships and the intention with the desired data because joins created at the base of a query will join in a way that matches the query base rows. This means that these joins are forced to one-to-one relationships, even if the relationship in Slate is one-to-many. (One person has many applications. However, a join added at the base of a query can only join in a single application at a time, such as only the rank 1 application or only the most recently updated application.)

This is important because the users who receive this mailing may have multiple applications assigned to them in the specified Workflow Bins. Since the intent is to export information about all of these (potentially many) applications, building a join at our query's base will not work. Instead, use a subquery export. Use a subquery filter to filter on information about all of these (potentially many) applications. Subquery exports and subquery filters permit looking across the many items in a one-to-many relationship in Slate to return data about the many or to filter on information about the many.

Creating a Subquery Filter

To create a subquery filter to return only users who have applications assigned to them in the specified Workflow Bin:

1. Edit the desired query and click the subquery icon next to the Filter button.

2. Give the filter a descriptive Name to indicate the intention of the filter.

3. On the configuration window for the subquery filter, locate and click the Join button.

Note: This button works differently from the Join button on the base of the query because this button does not create a forced one-to-one relationship (only joining a single item of a one-to-many relationship). Instead, it joins all items in a one-to-many relationship in the filter.

Join from User to Workflow Bin User Queue to access the queue of each individual user in the query.

4. Since each queue assignment is associated with the application in the user’s queue (one-to-one relationship), create a second join from Workflow Bin User Queue to Application.

The applications in a Workflow and assigned to the user for review are now joined.

5. Make sure that the query is only selecting applications in the intended workflow for sending the notification by including a nested subquery filter (which means a subquery filter in a subquery filter) to ensure only applications in the desired Workflow Bins are selected.

To do this, select the Subquery icon next to the Filter button in the Subquery Filter. This creates a nested subquery filter, which appears in a pop-up box over the initial one to indicate that it is one level deeper.

6. Give the nested subquery filter a descriptive Name.

7. Create a third join from application to Workflow Bin and filter for the desired Workflow Bins.

8. With the Aggregate of the subquery filter already set to Exists, save this filter to return to the original subquery filter.

The original top-level subquery filter will return only users with an application in their Workflow Bin User Queue, where the application is currently in the selected Workflow Bins.

Creating a Subquery Export

The last step to complete our recipient list is to create a subquery export to return the count of applications assigned to each User in the specified Workflow Bins. This process is similar to the previous steps' subquery filter. To create the subquery export:

1. Select the Subquery icon next to the Export button.

2. Give the subquery export a descriptive name to indicate the data intended to be returned.

3. A subquery export looks a lot like a subquery filter, but instead of having an Aggregate option, the export has Output options. The intention is to return a count, so select Aggregate from the Output list (to return numerical data about the potentially many applications assigned to each user) and select Count from the Aggregate list.

4. Create the same joins as previously done for the subquery filter, first from User to Workflow Bin User Queue and then from Workflow Bin User Queue to Application to return a count of applications.

5. To filter the applications to count only those in the specified Workflow Bin, create a nested subquery filter to join from Application to Workflow Bin, filter for bins previously selected in the subquery export , and set an Aggregate of Exists.

6. Once saved, the subquery export is now configured to return the count of applications in the user’s Workflow Bins User Queue that are in the selected Workflow Bins.

7. This subquery export can now be used, along with user email and any others you have selected, in the Deliver mailing.

With the recipient list set up, edit the Deliver message to set the User Email merge field as the recipient and include the App Count in Workflow Queue in the Example Bin merge field in the mailing content along with any other text the recipients should see. Sending the Mailing

To send this mailing once per day to any users with assigned applications in the Portfolio Review Workflow Bin, schedule the mailing to be ongoing and set the deduping option to one of the following, depending on the preferred outcome:

• Do not dedupe messages: This sends the mailing only once per record as new applications are assigned to the user. Once the message is sent for an application, that application will not be able to trigger it again the next day if it is still assigned to the same user in that Workflow Bin. With this setting, the user will only receive the email when new applications are assigned.

• Allow recurring delivery of this message to the same recipient: This allows an application to trigger the mailing as many times as needed. For example, if you set this to 1 day, the user will receive the mailing every day as long as at least one application is assigned to them in the Portfolio Review Workflow Bin.

Dealing with Deleted Applications

Slate stores all applications on the application table. That table has a column that stores the GUID of the person record each application belongs to, [application].[person]. That column allows Slate to join between the person and application tables. When a user deletes an application, Slate unsets the value from [application].[person] and sets it in the [application].[deleted_person] column. 

Deleting an application does not, however, remove applications from Workflow queues. If you want to exclude these records from your notifications, you would want to take the following steps for both your subquery export and subquery filter from above:

1. In the subquery export/filter, add the "Person" join from "Applications" and a new subquery filter.

2. Name the subquery filter, set the Aggregate setting to "Exists", and add an export for the person GUID.