Loading
CRM Analytics
Table of Contents
Filter by (0)Add
Select Filters

          No results
          No results
          Here are some search tips

          Check the spelling of your keywords.
          Use more general search terms.
          Select fewer filters to broaden your search.

            Search all of Salesforce Help
            Search all of Salesforce Help
            Structured Filter in sfdcDigest Transformation

            You are here:

            1. Salesforce Help
            2. Docs
            3. CRM Analytics

            Structured Filter in sfdcDigest Transformation

            You define a structured filter using JSON syntax.

            A structured filter uses the following JSON syntax for each filter condition.

            { 
"field": "<field name>", 
"operator": "<operator>", 
"value": "<value>"|"["<value 1>", "<value 2>"]", 
"isQuoted": true|false}

            The value can be a number, date, string, list of strings, or date literal. CRM Analytics automatically quotes strings unless you set isQuoted to true, which indicates that the string is already quoted.

            You can use one of the following operators in a filter condition.

            Operator Comment
            =

            Filter condition is true if the value in the field equals the specified value. String comparisons using the equals operator are case-insensitive.

            Example:

            "filterConditions": [
   {
   "field": "OwnerId",
   "operator": "=",
   "value": "0056A0000020jzDQAQ" 
   }
]
            !=

            Filter condition is true if the value in the field does not equal the specified value.

            Example (using backslashes to escape double quotes in a string value): 

            "filterConditions": [
   {
   "field": "Nickname__c",
   "operator": "!=",
   "value": "\"Sammy\"" 
   }
]
            >

            Filter condition is true if the value in the field is greater than the specified value.

            Example: 

            "filterConditions": [
   {
   "field": "Amount",
   "operator": ">",
   "value": "100000"
   }
]
            <

            Filter condition is true if the value in the field is less than the specified value.

            Example (using a date literal): 

            "filterConditions": [
   {
   "field": "CloseDate",
   "operator": "<",
   "value": "THIS_MONTH",
   "isQuoted": false 
   }
]
            >=

            Filter condition is true if the value in the field is greater than or equal to the specified value.

            Example: 

            "filterConditions": [
   {
   "field": "Amount",
   "operator": ">=",
   "value": "100000"
   }
]
            <=

            Filter condition is true if the value in the field is less than or equal to the specified value.

            Example (using a SOQL function): 

            "filterConditions": [
   {
   "field": "CALENDAR_YEAR (CreatedDate)",
   "operator": "<=",
   "value": "2015",
   "isQuoted": true
   }
]
            LIKE

            Filter condition is true if the value in the field matches the specified value. The LIKE operator is similar to the LIKE operator in SQL; it provides a mechanism for matching partial text strings and supports wildcards.

            • The % and _ wildcards are supported for the LIKE operator.
            • The % wildcard matches zero or more characters.
            • The _ wildcard matches exactly 1 character.
            • The LIKE operator is supported for string fields only.
            • The LIKE operator performs a case-insensitive match.
            • The LIKE operator supports escaping of special characters % or _. Use a backslash (\) to escape special characters.

            Example:

            "filterConditions": [
   {
   "field": "FirstName",
   "operator": "LIKE",
   "value": "Chris%" 
   }
 ]
            IN

            Filter condition is true if the value in the field equals any one of the values in the specified list. You can specify a quoted or non-quoted list of values. If the list is quoted, set isQuoted to true.

            Example: 

            "filterConditions": [
   {
   "field": "StageName",
   "operator": "IN",
   "value": ["Closed Won", "Closed Lost"] 
   }
]
            NOT IN

            Filter condition is true if the value in the field does not equal any of the values in the specified list.

            Example: 

            "filterConditions": [
   {
   "field": "BillingState",
   "operator": "NOT IN",
   "value": ["California", "New York"] 
   }
]
            INCLUDES

            For picklist or multi-select picklist fields only. Filter condition is true if the value in the picklist field includes the specified value.

            Example: 

            "filterConditions": [
   {
   "field": "BillingState",
   "operator": "INCLUDES",
   "value": ["California"] 
   }
]
            EXCLUDES

            For picklist or multi-select picklist fields only. Filter condition is true if the value in the picklist field excludes the specified value.

            Example: 

            "filterConditions": [
   {
   "field": "BillingState",
   "operator": "EXCLUDES",
   "value": ["California", "New York"] 
   }
]

            Let’s look at a few examples of structured filters.

            Example
            Example Let’s look at an example with a basic structured filter. To perform pipeline analysis on opportunities in fiscal quarter 2 of fiscal year 2015, you create this dataflow definition file to create the relevant dataset.
            {
   "Extract_Filtered_Opportunities": {        
      "action": "sfdcDigest",        
      "parameters": {            
         "object": "Opportunity",
         "fields": [                
            { "name": "Id" },                
            { "name": "Name" }, 
            { "name": "AccountId" },               
            { "name": "Amount" },                
            { "name": "StageName" },                
            { "name": "CloseDate" },                
            { "name": "OwnerId" },
            { "name": "FiscalYear" },
            { "name": "FiscalQuarter" },
            { "name": "SystemModstamp" }            
         ],
         "filterConditions": [
               {
                  "field": "FiscalYear",
                  "operator": "=",
                  "value": "2015"
               },
               {
                  "field": "FiscalQuarter",
                  "operator": "=",
                  "value": "2"
               }
         ]
      }    
   },    
   "Register_Opportunities_Dataset": {        
      "action": "sfdcRegister",        
      "parameters": {            
         "alias": "Opportunities_2015Q2",            
         "name": "Opportunities_2015Q2",            
         "source": "Extract_Filtered_Opportunities"        
      }
   } 
}
            Note
            Note If you do not specify a logical operator for multiple filter conditions—as is the case in this example—CRM Analytics applies AND between the conditions.
            Example
            Example Let's look at an example of a structured filter with a logical operator. To help forecast expected revenue, you create this dataflow to view all opportunities that have either closed or have greater than 90% probability of closing.
            {
   "Extract_Opportunities": {        
      "action": "sfdcDigest",        
      "parameters": {            
         "object": "Opportunity",
         "fields": [                
            { "name": "Id" },                
            { "name": "Name" }, 
            { "name": "AccountId" },               
            { "name": "Amount" },                
            { "name": "StageName" },                
            { "name": "CloseDate" },                
            { "name": "OwnerId" },
            { "name": "Probability" },
            { "name": "FiscalYear" },
            { "name": "FiscalQuarter" }          
         ],
         "filterConditions": [
            {
               "operator": "OR",
               "conditions": [
                  {
                     "field": "StageName",
                     "operator": "=",
                     "value": "Closed Won"
                  },
                  {
                     "field": "Probability",
                     "operator": ">=",
                     "value": "90"
                  }
               ]
            }
         ]
      }    
   },    
   "Register_Opportunities_Dataset": {        
      "action": "sfdcRegister",        
      "parameters": {            
         "alias": "OpportunitiesExpectedToWin",            
         "name": "OpportunitiesExpectedToWin",            
         "source": "Extract_Opportunities"        
      }
   } 
}
            Example
            Example Finally, let's look at an example of a structured filter with nested logical operators. You create this dataflow to view all opportunities that closed in the current fiscal quarter and are owned by either one of your two direct reports.
            {
    "Extract_Opportunities": {
        "action": "sfdcDigest",
        "parameters": {
            "object": "Opportunity",
            "fields": [
                { "name": "Id" },
                { "name": "Name" },
                { "name": "AccountId" },
                { "name": "Amount" },
                { "name": "StageName" },
                { "name": "CloseDate" },
                { "name": "OwnerId" },
                { "name": "FiscalYear" },
                { "name": "FiscalQuarter" }
            ],
            "filterConditions": [
                {
                    "operator": "AND",
                    "conditions": [
                        {
                            "field": "CloseDate",
                            "operator": "=",
                            "value": "THIS_FISCAL_QUARTER",
                            "isQuoted": false
                        },
                        {
                            "operator": "OR",
                            "conditions": [
                                {
                                    "field": "OwnerId",
                                    "operator": "=",
                                    "value": "0056A0000020jzDQAQ"
                                },
                                {
                                    "field": "OwnerId",
                                    "operator": "=",
                                    "value": "0056A0000020jzGQAQ"
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    },
    "Register_Opportunities_Dataset": {
        "action": "sfdcRegister",
        "parameters": {
            "alias": "DirectReport_Opportunities",
            "name": "DirectReport_Opportunities",
            "source": "Extract_Opportunities"
        }
    }
}
             
            Loading
            Salesforce Help | Article