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.
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 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.