Explore Additional Data Filter Capabilities
Learning Objectives
After completing this unit, you’ll be able to:
- Work with different field types in data filters.
- Use AND/OR data filtering.
- Export filter results.
Set Data Filters by Data Type
Salesforce Spiff recognizes the data type of each field—string, Boolean, date and time, and so on. Data filter options change based on the data type of the field that's being used, especially in the Guided Editor.
In this unit, you learn some important considerations when working with different data types in your filters. And later, find out how to use AND/OR data filtering and export your results.
Work with String Fields
A string field functions like a label. It can contain numbers, letters, or symbols. It can be an account number, or a label like Renewals or Product. String values are never subjected to a mathematical function—you don’t add or subtract labels, even if they look like numbers.
Imagine you have a field called Product__c and you want a data filter that returns a specific product. In the Syntax Editor, your filter would look like this: Product__c = "TundraShield".Notice that the product entry used for the filter is in quotation marks. String fields need to be spelled exactly and are case-sensitive.
When using the Guided Editor select Product__c in the first picklist, is equal to in the second, and then enter TundraShield into the third. Don’t use quotation marks in the Guided Editor.
Work with Boolean Fields
A Boolean field can only contain the values true or false for each record. Common Boolean fields in Salesforce include IsClosed and IsWon. Data Filters require the field to be equal or not equal to true or false. For example, consider IsClosed = true or IsWon is not equal to false.
Work with Date and Time Fields
The Guided Editor makes it easy to work with date and time fields, but it’s important to know that you can’t mix datetime fields and date fields in the same filter. For example, you can’t use syntax like BeginningOfPeriod with a field that has a timestamp.
If you need to use a field with a timestamp, use the function beginning_of_day to create a timestamp for your statement period start: beginning_of_day(BeginningOfPeriod). This adds a timestamp and now your filter will work.
If you need to remove a timestamp from a datetime field, you can use the date function: date(datetimefield).
Just make sure you compare dates to dates, and datetime fields to datetimes.
Null Values in Fields
When working with is not equal to syntax, either through the picklist in Guided Editor or != in Syntax Editor, this syntax will include null fields.
For example, a Region__c field contains the choices East and West. If you write a filter that reads, Region__c != West, you can expect all records to contain East or null values.
Use AND/OR in Data Filters
Imagine you have a Product__c field that contains the following products: EverRoute, CellShine, TundraShield, and ClearProof.
If a data filter read Product__c = CellShine AND Product__c = ClearProof, nothing would return because no record has two product types. If you want records that are one or the other, you have to use an OR statement, such as in Product__c = CellShine OR Product__c = ClearProof.
Combining conditions with AND includes records where both conditions are met. Using OR includes records where just one or both conditions are met.
You can use AND and OR in the same data filter. This requires setting the Guided Editor to Nested mode.
Imagine you want to use your ClosedInPeriod data filter to find deals that are either CellShine or ClearProof. In Nested mode, select the data filters you want to use, and set your parameters in the Nested conditions menu.
You set ClosedWon as condition A, InPeriod as condition B, and ByRep as condition C.
Use parentheses to surround conditions B and C. This tells the filter to look for either B or C. Otherwise, the filter looks for conditions A and B to be met together, or C by itself, and it returns the incorrect data.
Caution! Switching from Nested mode to Simple mode joins all conditions with AND, eliminating all OR conditions.
Export Filter Results
When a data filter is open, an export button is visible in the top-right corner of the center pane.
This button exports the visible records as a CSV file. Use the context selectors at the top of the center window to change the visible data for export. Only synced fields are included in the export. Calculated fields that have been created in Salesforce Spiff are not included.
When you click the export button, a message appears in the lower-right corner of the screen informing you that your data is being exported. You can click through to your export, or navigate to your exports from the core platform. To see a list of exported file names, navigate to Data Sources from the Salesforce Spiff home page. Click the appropriate data object from the menu on the left, and then Export History. Download the exported file through the menu on the right of the exported file record.
Salesforce Spiff’s data filters are a powerful tool! Now you’re ready to build and use them to get helpful insights into rep statements and create automated commission plans.