Views:

Description

Modifies an existing xCache collection with a supplied filter.

Signature

xCacheFilterLocal('key', 'filter', 'sort')

 

Example 1

xCacheFilterLocal('AllAccounts', 'address1_country = "USA" ', 'address1_city')

Filters the EntityCollection called AllAccounts so that it returns only rows matching address1_country equal to USA and sorts the EntityCollection by address1_city.

 

Example 2

xCacheFilterLocal(
  'AllOpportunities', 
  'new_budget_amount > 10000 and new_optionset_rating  = 217000001',
  'createdon DESC'
)

Filters the EntityCollection called AllOpportunities so that it returns only rows that have a new_budget_amount greater than 10000 and field new_optionset_rating matches to a specific value. In addition, it sorts the EntityCollection by the createdon field in descending order.

 

Example 3: Literals

String values are enclosed within double quotes "". 

xCacheFilterLocal('AllAccounts', 'name = "Oliver"')
// matches to a plain string value

 

Number values are not enclosed within any characters.

xCacheFilterLocal('AllAccounts', 'new_year = 2008')
// integer value

xCacheFilterLocal('AllAccounts', 'new_price = 1199.9')
// float value

 

Date values are enclosed within hash characters # #. 

xCacheFilterLocal('AllAccounts', 'createdon = #12/31/2008#')
// Date = #12/31/2008#

xCacheFilterLocal('AllAccounts', 'createdon = #2008-12-31#')
// also this format is supported

xCacheFilterLocal('AllAccounts', 'createdon = #12/31/2008 16:44:58#')
// date and time value

 

Alternatively, you can enclose all values within double quotes "". It means you can use string values for numbers or date-time values.

xCacheFilterLocal('AllAccounts', 'createdon = "12/31/2008 16:44:58"')
// if current culture is English

xCacheFilterLocal('AllAccounts', 'createdon = "31.12.2008 16:44:58"')
//  if current culture is German

xCacheFilterLocal('AllAccounts', 'new_price = "1199.90"')
// if current culture is English

xCacheFilterLocal('AllAccounts', 'new_price = "1199,90"')
// if current culture is German

 

Example 4: Comparison operators

Equal, not equal, less, greater operators are used to include only values that suit to a comparison expression. You can use these operators = <> < <= > >=.

xCacheFilterLocal('AllAccounts', 'creditlimit = 10')
// number is equal to 10

xCacheFilterLocal('AllAccounts', 'createdon < #1/1/2008#')
// date is less than 1/1/2008

xCacheFilterLocal('AllAccounts', 'name <> "Microsoft"')
// string is not equal to Microsoft

Operator IN is used to include only values from the list. You can use the operator for all data types, such as numbers or strings.

xCacheFilterLocal('AllAccounts', 'accountnumber IN (1, 2, 3)')
// integer values

xCacheFilterLocal('AllAccounts', 'new_price IN (1.0, 9.9, 11.5)')
// float values

xCacheFilterLocal('AllAccounts', 'name IN ("John", "Jim", 'Tom")')
// string values

xCacheFilterLocal('AllAccounts', 'createdon IN (#12/31/2008#, #1/1/2009#)')
// date time values

xCacheFilterLocal('AllAccounts', 'accountnumber NOT IN (1, 2, 3)')
// values not from the list

 

Operator LIKE is used to include only values that match a pattern with wildcards.

Wildcard character is * or %, it can be at the beginning of a pattern *value, at the end value*, or at both *value*. Wildcard in the middle of a patern va*lue is not allowed.

xCacheFilterLocal('AllAccounts', 'name LIKE "j*"')
// values that start with j

xCacheFilterLocal('AllAccounts', 'name LIKE "%jo%"')
// values that start with jo

xCacheFilterLocal('AllAccounts', 'name NOT LIKE "j*"')
// values that don't start with j

 

Example 5: Boolean operators

Boolean operators AND, OR and NOT are used to concatenate expressions. Operator NOT has precedence over AND operator and it has precedence over OR operator.

// operator AND has precedence over OR operator, parenthesis are needed, examples below:

xCacheFilterLocal('AllAccounts',
  'address1_city = "Dublin" AND (creditlimit < 20 OR creditlimit > 60)') 

xCacheFilterLocal('AllAccounts', 'address1_city <> "Dublin" AND address1_city <> "Paris"')  

xCacheFilterLocal('AllAccounts', 'NOT address1_city = "Dublin" AND NOT address1_city = "Paris"') 

xCacheFilterLocal('AllAccounts', 'NOT (address1_city = "Dublin" OR address1_city = "Paris")')

xCacheFilterLocal('AllAccounts', 'address1_city NOT IN ("Dublin","Paris")')

 

Example 6: Arithmetic and string operators

Arithmetic operators are addition +, subtraction -, multiplication *, division / and modulus %.

xCacheFilterLocal('AllAccounts', 'new_core_days - new_extended_days < 20')   

xCacheFilterLocal('AllAccounts', 'new_core_days % 10 = 0')

There is also one string operator concatenation +.

 

Example 7: Aggregate Functions

There are supported following aggregate functions SUM, COUNT, MIN, MAX, AVG (average), STDEV (statistical standard deviation) and VAR (statistical variance).

This example shows the aggregate function performed on a single table:

xCacheFilterLocal('AllAccounts', 'creditlimit > AVG(creditlimit)')
// select accounts with above-average credit limit

 

Example 8: Audit History Functions

Filter an Entity collection by its column Changed By and sort it by its Date (note the Change By column with a space is enclosed within [ ]):

xCacheFilterLocal('AuditEC',
                  '[Changed By]= "8dad0984-9667-47e1-92ed-bab591b9f9c9"',
                  'Date')

 

Example 9: Null Matching

How to only match against null values.

xCacheFilterLocal('AllAccounts', 'description is NULL')

 

Example 10: Functions

These are also supported functions:

  • CONVERT – converts particular expression to a specified .NET Framework type
    • Convert(expression, type)    e.g. Convert(new_days_outstanding, "System.Int32")

 

  • LEN – gets the length of a string
    • LEN(expression) e.g. Len("description")

 

  • ISNULL – checks an expression and either returns the checked expression or a replacement value
    • ISNULL(expression, replacementvalue)  e.g. IsNull(new_price, -1)

 

  • IIF – gets one of two values depending on the result of a logical expression
    • IIF(expr, truepart, falsepart) e.g. IIF(new_total > 500, "expensive", "cheap")

 

  • TRIM – removes all leading and trailing blank characters like \r, \n, \t, ‚ ‘
    • TRIM(expression) e.g. Trim("description")

 

  • SUBSTRING – gets a sub-string of a specified length, starting at a specified point in the string. 
    • SUBSTRING(expression, start, length) e.g. SUBSTRING(address1_main_phone, 7, 8)
Keywords: xCache, Substring, Trim, xCacheFilterLocal, Formula Manager