Creating a Where Clause

Creating a where clause is recommended for Advanced users only.

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 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 your SData feed widget
• The column name that contains the data you want to filter

  Not all 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 contained between straight quotes (' ') and % symbols

To create a where clause

• In the Where Clause box in the SData widget Edit Widget Settings wizard, type 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 your SData feed widget does not return data as expected, here are a few common mistakes:

• Make sure that the where clause contains vertical quotes (' '), not slanted quotes (‘ ’)
• Make sure 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 CRM views. For example, AccountName, not Account or Account Name.