Creating a Where Clause
A where clause is used to filter information from an SData feed. The SData query language is used to express filtering conditions with the where query parameter. Information from an SData feed is subject to security, so users can only see records for which they have permission to view.
The syntax for creating a where clause is very specific. If the syntax is incorrect, you may see undesirable results.
In order to create a where clause, you must know the following:
- The resource associated with the SData feed widget
- The column name that
contains the data you want to filter
Note: Not all the columns can be used to filter data.
- The values you are looking for
- What the valid SData operators are
- Text values must be between straight quotes (' ')
- Dates must be between @
symbols
Sag
- Text in a like statement must be specified between quotes (' ') and the % symbol
To create a where clause, in the Where Clause field in the SData widget Edit Widget Settings wizard, specify a where clause using the appropriate syntax. Do not include 'where='. For example, AccountName eq 'State Enterprises Co.'
Commonly Used Filters
For the following examples, assume that you selected the Account resource.
| Data Filter Condition | Where Clause Operator | Example | Result |
|---|---|---|---|
| equal | eq | CreditRating eq 'Good' | A list of accounts where the credit rating is equal to "Good". |
| compare two columns | CreateUser eq ModifyUser | A list of all accounts that were last modified by the same user who created the account. | |
| equals a date | year(CreateDate) eq 2008 and month(CreateDate) eq 06 and day(CreateDate) eq 14 | A list of all accounts created on the specified day, month, and year. | |
| not equal | ne | Industry ne 'Services/Consulting' | A list of accounts that are not in the Services/Consulting industry. |
| less than | lt | Revenue lt 5000000 | A list of accounts with revenue less than 5 million. |
| less than or equal to | le | LastHistoryDate le @2012-01-01T00:00:01@ | A list of accounts where the most recent history item occurred on or before January 1st 2013. |
| greater than | gt | Employees gt 100 | A list of accounts with more than 100 employees. |
| greater than or equal to | ge | CreateDate ge @2013-01-01T00:00:00+00:01@ | A list of all accounts created on or after January 1st 2013. |
| within a range | between | ShippingAddress.PostalCode between 60650 and 60699 | A list of all accounts with a postal code between 60650 and 60699. |
| In the set | in | CreditRating in ('Good', 'Fair', 'Poor') | A list of all accounts with a credit rating of 'Good' or 'Fair' or 'Poor'. |
| Not in the set | not in | CreditRating not in ('Good', 'Fair', 'Poor') | A list of all accounts with a credit rating that is not 'Good' or 'Fair' or 'Poor'. |
| contains | like | BusinessDescription like '%subsidiary%' | A list of accounts that contain 'subsidiary' in their description of business. |
| starts with (first character) | left(<column>,1) between | left(AccountName,1) between 'A' and 'C' | A list of all accounts with a name that starts with 'A', 'B', or 'C'. |
| starts with (first 2 characters) | left(<column>,2) eq | left(AccountName,2) eq 'Ab' | A list of all accounts with a name that starts with 'Ab'. |
| starts with (first 3 characters) | left(<column>,3) eq | left(MainPhone,3) eq 312 | A list of all accounts with main phone numbers in the 312 area code. |
| occurred within the last X days | dateAdd(<column>,X) ge | dateAdd(ModifyDate, 7) ge currentTimestamp | A list of all accounts modified in the last 7 days. |
If the SData feed widget does not return data as expected, some of the common mistakes seen are:
- Ensure that the where clause contains the vertical quotes (' '), and not the slanted quotes (‘ ’)
- Ensure that the column name is correct. Column names are case sensitive and are based on the underlying name, not the 'pretty' name that appears in Infor CloudSuite CRM views. For example, AccountName, not Account or Account Name.