Subqueries in Views

In Flowfinity Actions, you can use subqueries in Views to help search and filter your data based on the results of querying another view in the same or a different app. To do this, you need to create a Query parameter when configuring a View.

Parameters allow users to search for data within a view, and the Query parameter can search for data within the current view for a value obtained by querying another app. A Query parameter can return three different types of values.

Return a single value from the first available record.

This will return a single value that is taken from the first available row of the view.

Return all values from a column of all view records as a set.

This will return a list of records.

Return single value by aggregating all view records value using the sum, average minimum, maximum, or count.

This will aggregate the records and only return a single value in your view. For example, the maximum value of a column in the view records.

To show you how to use query parameters, we will assume the role of a Sales Manager for the following examples. As it stands, the Sales Manager uses a CRM app with a private 'Sales Analysis' view for reporting.

The Sales Analysis view is sorted by Subtotal, and each quarter the Sales Manager must add and remove filters to create reports based on current needs.

We'll look at three ways the Sales Manager can use the Query parameter to streamline reporting for their teams.

Steps - Return a single value from the first available record.

Only the Sales Manager has access to the full CRM functionality including the Sales Analysis view. However, to motivate the team, the Sales Manager wants to create a view that displays the biggest sale by dollar value and share it with everyone as the 'Top Sale.'

To configure a view parameter, follow these quick steps:

1. Customize the application and navigate to the Views tab, then click 'Add view.'
   
2. Complete the basic view configuration, including title and selected fields, and then navigate to the Parameters tab. Then click 'Add parameter.'
3. Give the parameter a label and name, Top Sale, and then select Query from the 'type' dropdown menu.
4. Select the Customer Relationship Management as the target app and pick the appropriate view, Sales Analysis. As the Sales Manager wants to showcase the record sale, select the Subtotal money field in the 'return values from column' dropdown.
   
5. Select 'Return single value from the first available record' and click 'Ok' to proceed.
6. Navigate to the Filter tab and click 'Add condition.'
7. Configure the condition as follows:
   Subtotal < field money >, Is Greater Than or Equal To, Top Sale
   
8. Save the view and then navigate to the Permissions tab, select the appropriate roles checkboxes.
   
9. Save the view and publish the application. The largest dollar value sale is now shown in the 'Top Sale' view to everyone on the sales team.
   

Steps - Return all values from all view records as a set.

In this example, we are using a Customer Discounts application where confidential promotional discounts are recorded for VIP customers.

To ensure security, only the Sales Manager has access to the Customer Discounts app where all VIP Customers, active and inactive, are listed.

However, individual salespeople require occasional access to a list of active VIP Customers during the sales process.

Using a Query parameter, a Sales Manager can create a view in the existing Customer Relationship Management app that provides sales teams with a list of active VIP Customers returned from the Customer Discounts app.

1. Customize the CRM application and create a new view. Complete the basic view configuration, including title and selected fields.
2. Navigate to the Parameters tab. Then click 'Add parameter.'
3. Name the parameter and select Query from the 'type' dropdown menu.
   
4. Select Customer Discounts as the target app and 'View All VIP Customers' as the view. Then, ensure that the VIP Name field is the return value.
5. Select the 'return all values from all view records as a set' option and click 'Ok' to proceed.
   
6. Complete the filter as follows: Name , In, 'VIP Name
   Current state name , Equals, Constant, 'Active Customer'
   
7. Add the permissions for Regular Users to access this view in the Permissions tab and then publish the application.
8. The 'VIP Customers' view is now available to both the Sales Manager and everyone on the sales teams and displays a list of active VIP Customers.
   

Steps - Return single value by aggregating all view records value using the sum, average minimum, maximum, or count.

The Sales Manager is reviewing a trend in sales performance and wants to drill down into the data.

Without a parameter, there are too many records to manually review, so the Sales Manager decides to use a Query parameter to find the 'average' subtotal and then use a filter to show above average sales for the Year to Date (YTD).

1. Repeat the same steps to create a view and add the appropriate fields. Name the view 'Above Average Sales (YTD).'
2. Navigate to the Parameter tab and click 'Add parameter.'
3. Complete parameter as follows:
   
4. Ensure that 'return single value by aggregating all view records using' dropdown option is set to 'avg.'
5. Navigate to the Filter tab, add the following condition: Subtotal , Is Greater Than Or Equal To, Avg Sale ($)
   
6. Save the view and publish the application.
   Note: as this view is used by the Sales Manager for confidential reporting, it is not necessary to grant view permissions to other users.
7. The 'Above Average Sales (YTD)' view is now available to the Sales Manager. The Query Parameter has calculated the average subtotal within the Sales Analysis view and is now filtering out all records below that calculated average.
   

Please note, there are nearly limitless potential use cases for the subqueries in views functionality.

For example, a Sales Manager may use a Sales Targets app to set commission thresholds each quarter.

Using a query parameter, the Sales Manager can create a view that compares records in the CRM application to commission threshold values in a Sales Targets app.

The resulting view acts as a leaderboard to salespeople, showing the team members a real-time list of sales that will generate commissions at bonus rates.

By using the query parameter functionality, a Sales Manager can simply change the values in the Sales Target app without reconfiguring the view in the CRM app at the same time.