Creating an SData 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.

Note: Creating a where clause is recommended for Advanced users only.

The syntax for creating a where clause is very specific. If the syntax is incorrect, you can see undesirable results.

In order to create a where clause, you must know these:

  • The resource associated with the SData feed widget.
  • The column name that contains the data you requied to filter
    Note: Not all columns can be used to filter data.
  • The values you are looking.
  • What are the valid SData operator.
  • Text values must be between straight quotes (' ').
  • Dates must be between @ symbols.
  • 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 widgetEdit Widget Settings window, specify a where clause using the appropriate syntax. Do not include 'where='. For example, AccountName eq 'State Enterprises Co.' See Using Welcome page widgets

For more examples, see Where clause examples

Commonly Used Filters

Class Meaning Where Clause Operator Example
Member member access x.y BillingAddress.country
Unary unary minus -x -discount
negation not x not disabled
Multiplicative multiplication x mul y price mul 1.07
division x div y price div 2
modulus x mod y index mod 2
Additive addition x+ y price + tax
subtraction x - y price - discount
Comparison equal x eq y countryCode eq ‘GB’ Or CreateUser eq ModifyUser Or year(CreateDate) eq 2021 and month(CreateDate) eq 06 and day(CreateDate) eq 14
not equal x ne y countryCode ne ‘GB’
less than x lt y price lt 100.0
less than or equal x le y price le 100.0 or LastHistoryDate le @2021-01-01T00:00:01@
greater than x gt y price gt 100.0
greater than or equal to x ge y price ge 100.0 or CreateDate ge @2021-01-01T00:00:00+00:01@
within a range (between) x between y and z Price between 100.0 and 500.0 or ShippingAddress.PostalCode between 60650 and 60699
contained in x in (y,z) CreditRating in (‘Good’, ‘Fair’, ‘Poor’)
Not in the set x not in (y,z) CreditRating not in (‘Good’, ‘Fair’, ‘Poor’)
Contains or like x like y BusinessDescription like ‘%subsidiary%'
occurred within the last X days dateAdd(<column>,X) ge dateAdd(ModifyDate, 7) ge current_timestamp
Logical logical and x and y countryCode eq ‘GB’ and amount gt 1000.0
logical or x or y countryCode eq ‘GB’ or countryCode eq ‘US’

If the SData feed widget do 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 displays in the application views. For example, AccountName, not Account or Account Name.