How to use the Query Data Source

Overview

What is the Query Data Source?

Query Mode is a feature that allows you to craft queries directly within the components. This eliminates the limitations of Salesforce's traditional 'Get Records' element.

Exploring the Differences Between 'Get Records' Collection and Query Mode

'Get Records' collection and 'Query Mode' are two methods that provide effective data management, but they're used in different situations and have their unique characteristics. This guide will clearly explain how each method works and help you determine the best suited for your requirements.

Description	Get Records	Query Mode
Records Limits	50,000 records	No limits
Data Search restriction	None	Long text field and rich text area not supported
Data Batching	May need more time to process and retrieve results, especially for extensive datasets.	100 records at a time, can enhance performance when navigating large datasets.
Query Complexity	No support for sub-queries or complex query structures.	Allows for more sophisticated querying capabilities.
Field Limitations	Restricted to fields on the directly queried object.	Capability to choose fields directly from the parent object for filtering purposes.

Description

Get Records

Query Mode

Records Limits

50,000 records

No limits

Data Search restriction

None

Long text field and rich text area not supported

Data Batching

May need more time to process and retrieve results, especially for extensive datasets.

100 records at a time, can enhance performance when navigating large datasets.

Query Complexity

No support for sub-queries or complex query structures.

Allows for more sophisticated querying capabilities.

Field Limitations

Restricted to fields on the directly queried object.

Capability to choose fields directly from the parent object for filtering purposes.

How to Use Query Mode

Open any supported component (Data Table, List, Carousel, Map) in the Flow Builder.
Navigate to the 'Data Source' section and select 'Query'.
A query editor will appear, enabling you to input your query directly.

Configuring Query Filters

In Avonni Components, you can customize your data queries with specific conditions akin to the 'WHERE' clause in a SOQL (Salesforce Object Query Language) query. This feature lets you define precise criteria for the data you want to retrieve.

Example of Setting Filter Conditions

Imagine you're working with Salesforce data and want to retrieve contact records in 'New York.' Here's how you can set this up:

Specify the Field: Choose the field to which you want to apply the filter, such as 'Location'.
Add the Filter Condition: In the filter condition, specify 'equals' and then 'New York'. This is analogous to writing a SOQL query where you would include WHERE Location = 'New York'.

This setup ensures that your query will only return contacts located in New York, filtering out all other records.

Grouping Conditions in Queries

Avonni Query Data Source also allow grouping multiple conditions, enhancing the query's precision.

Suppose you want to retrieve data of contacts in 'New York' or 'San Francisco' and have an 'Active' status. Here's how to group these conditions:

Click on the "Add Groups" link
Set the First Condition: As before, set a condition for the 'Location' field to 'New York'.
Add Another Condition: Add a second condition for 'Location' set to 'San Francisco'.
Add Status Condition: Now add another condition outside this group for the 'Status' field set to 'Primary'.

In this setup, the query will return contacts in New York or San Francisco and simultaneously have an 'Active' status.

Understanding Query Operators

Mastering query operators is essential for precise data retrieval in the Avonni Query Data Source. This section covers various operators, including Logical, Comparison, Set, String, and Null. Each plays a crucial role in filtering and sorting data, enabling more efficient and targeted queries.

Operators can further refine your data queries. These operators include:

Logical Operators: AND, OR, NOT
Comparison Operators: =, <>, <, >, <=, >=
Set Operators: IN, NOT IN
String Operators: LIKE, NOT LIKE
Null Operators: NULL, NOT NULL

Significations

LIKE: Use LIKE for string pattern matching. % represents zero or more characters, and _ represents a single character.
IN: The IN operator allows you to specify multiple values in a WHERE clause, making your queries more concise.
NOT IN: Use NOT IN when you want to exclude records that match any of the specified values.
ASC NULLS LAST: Sorts the queried data in ascending order while placing NULL values at the end.
DESC NULLS LAST: Sorts the queried data in descending order while placing NULL values at the end.

Enabling Dynamic Filters with Reactive Screens in Salesforce

Reactive Screens lets you create Salesforce interfaces that update instantly in response to user input. To make your filters work seamlessly with these screens, follow these steps:

Guided Steps

Create a Formula Resource: Create a new variable to store your filter formula in your Salesforce environment. Give it a descriptive name (e.g., "accountOpportunitiesCondition")

Craft Your Formula: Write a formula that defines your filtering logic. For example, this formula might filter by the ID of the row selected on the Data Table: "Account.Id = '"&{!<<YOUR DATA TABLE API NAME>>.firstSelectedRow.Id}&"'"

Connect this formula as you mapped value on the Data Table

Explanation

The variable acts like a bridge between your filter logic (the formula) and the actual customer data in Salesforce. This ensures your Reactive Screen displays accurate results, updating in real-time as the underlying data changes.

Important information for Reactive Screens configuration

To complete this tutorial successfully, you must enable the Reactive Screen Components Beta feature in Salesforce.

Go to Setup's Process Automation Settings page to opt into the Beta. Scroll down to the bottom and enable the Reactive Screens Beta.

If you have not enabled this setting, you must create a new flow once activated.

Alternatively, if you choose not to enable this Beta feature, ensure you use Flow version API 59 or higher.

Example 1: Dynamic Interaction Between Data Tables

This example has a main Data Table connected to two other tables. When you select an account from the main table, the other tables automatically update to show information relevant to that account. This dynamic update is made possible by a formula used as a variable to filter the data in these tables.

Step 1: Set Up the Main Data Table

Firstly, add the main Data Table to your workspace. To ensure users can select only one account at a time, configure the 'Max Row Selection' setting to 1. This setting enables single-row selection using a radio button.

Step 2: Create and Understand the Text Formula

Now, create a Text formula resource with this formula: "Account.Id = '"&{!<<YOUR DATA TABLE API NAME>>.firstSelectedRow.Id}&"'".

Let's dissect the formula:

"Account.Id = '": This formula part targets the 'Account.Id' field. Replace 'Account.Id' with the API name of the field you want to match in your specific use case.
&{!<<YOUR DATA TABLE API NAME>>.firstSelectedRow.Id}: This dynamically retrieves the ID of the selected row (the chosen account) from your main Data Table. Substitute <<YOUR DATA TABLE API NAME>> with the actual API name of your Data Table.
`&"'": This concludes the formula, ensuring it's formatted correctly.

Step 3: Configure the Reactive Data Tables

To set up the other Data Tables that will update based on the selection in the main table:

Select 'Query Data Source' for these tables.
Choose the Object you wish to query (for example, customer-related data).
In the Filter section, select 'Mapped' and apply the formula from Step 2. This setup links the tables to the account selected in the main table.
Implement any additional necessary settings for your Data Table.

Following these steps, your main Data Table will interact dynamically with the other tables, updating them to display information relevant to the selected account.

Example 2: The 'Quarterly Opportunity Tracker' component

In this example, we use the Avonni Tabs component to represent different year quarters. Each tab corresponds to a specific quarter and has an associated date range. When you select a tab, the Avonni Data Table on the right side dynamically updates. It shows opportunities relevant to that quarter.

How it Works:

Tabs for Each Quarter: Each tab is labeled for a quarter (Q1, Q2, Q3, Q4), with dates defining that quarter.
Reactive Data Table: Upon selecting a tab, the Data Table on the right becomes reactive.
The formula for Filtering: A formula is used to filter opportunities based on their close dates. This formula checks if an opportunity's close date falls within the start and end dates of the selected quarter.
Displaying Opportunities: As a result, the Data Table displays all opportunities where the close date is between the start and end dates of the selected quarter.

Breaking down the formula

"CloseDate >= '" & TEXT({!tabs.activeTabSObjectValue.Start_Date__c}) & "' AND CloseDate <= '" & TEXT({!tabs.activeTabSObjectValue.End_Date__c}) & "'"

CloseDate Comparison:
- CloseDate >= '...: This part checks if the opportunity's close date is on or after the start date of the selected quarter.
- ... AND CloseDate <= '...: This part checks if the opportunity's close date is on or before the end date of the selected quarter.
Using the Tab's Date Values:
- TEXT({!tabs.activeTabSObjectValue.Start_Date__c}): This retrieves the start date of the quarter selected in the tab. The TEXT function converts this date into text format. tabs.activeTabSObjectValue is the tab component attribute we're using.
- TEXT({!tabs.activeTabSObjectValue.End_Date__c}): Similarly, this retrieves the quarter's end date and converts it into text format.
Combining the Conditions:
- The AND in the middle combines these two conditions. The formula only selects opportunities whose close dates fall between these start and end dates.

How It Works in Practice:

When you select a quarter tab, the formula dynamically updates to use that quarter's specific start and end dates. The Data Table shows only the opportunities whose close dates fall within this selected quarter.

Guide to Using 'Custom Fields' for Enhanced Query Efficiency

The 'Custom Fields' section lets you add particular fields straight into your queries, bypassing the need to put them in the Avonni data table. This leads to a more customized querying approach, where you can include the fields needed for specific tasks or later steps.

Why use "Custom Fields":

Enhanced Flexibility: Customize your queries by including only the essential fields for specific interactions or screens.
Improved Efficiency: By allowing you to add specific fields directly, it streamlines the querying process, making it quicker and more efficient.
Focused Queries: This helps create more concentrated and precise queries by utilizing only the required fields, avoiding unnecessary clutter in the data table.

Last updated 2 months ago