The returned records for any List Endpoint can be filtered by passing parameters in a query string.
A Filter is pulled from query parameters (instead of being passed as part of the URL, as in versions prior to EventPro 20, although endpoints existing before Version 20 will be backward compatible).
Filters for the endpoints are optional, and can be omitted to return all records available to the Security User who owns the API Key used for the request.
In this topic, we will learn how to create a filter, define filter conditions, and set rtfMode. After that, some filter examples have been provided.
Create a Filter
To create an endpoint filter, you add the parameters to the end of the endpoint after the delimiter question mark ? in this format:
•Base URL/Object Type Identifier?parameter
If you are adding multiple parameters to the endpoint, use an ampersand & to separate the parameters:
•Base URL/Object Type Identifier?parameter¶meter
There are two different parameters: filter and rtfMode.
They are formatted as follows:
•filter=value
•rtfMode=value
Note that the value is entered directly after the equal sign =. Do not leave a space after the equal sign =.
If you use both the filter and rtfMode parameters, the endpoint would be formatted as follows:
•Base URL/Object Type Identifier?filter=value&rtfMode=value
(Remember, you have the option to use one parameter or both, or none at all.)
See the instructions below to learn how to define the values for the filter parameter and rtfMode parameter.
Define Filter Conditions
Set Value of Filter Parameter
The value of the Filter parameter is set using filter conditions.
Any List endpoint can use a filter as a parameter. For example:
•https://connect.eventpro.net/Api/DataService.svc/events?filter=BookNo = 10
You can filter on fields that are not present in the result, just as you can in the main application.
Formatting
The formatting in the filter can handle most of the same filters as those created with EventPro's Filter Editor.
Review the topic Common Procedures | Construct Filters.
Case Sensitivity
Filter conditions are case sensitive - the operators, fields, and values - so ensure that you use uppercase or lowercase accordingly.
Spaces Around Operator
In a filter condition, there must be a space before and after an operator.
BookNo = 10
Date/Time
Date/Time fields are the text representations of that date and time, and are formatted as a sortable date/time pattern (yyyy-MM-ddTHH:mm:ss).
You can use any valid date format that does not include time zones; the time zone of the API User will be used.
Because the date/time is in text, remember that it must be enclosed in single quotation marks.
BookedDate >= '2018-08-20'
Text Values in Quotation Marks
Text values must be in single quotation marks.
VenueLocation.Name = 'Grand Bravura Theater'
Multiple Values for a Field
If you want to filter on multiple values in the same field, there are two important differences to note:
•Instead of the equal sign (=), the filter condition operator is the word in
•The filter condition needs to list the values in parenthesis, separated by comma and space.
Remember that each value must still be in single quotation marks.
For example, if you are filtering for two event locations, the filter condition would look like this:
VenueLocation.Name in ('Grand Bravura Theater', 'Exhibition Hall')
In another example, if you are filtering for two event statuses, the filter condition would look like this:
Status.StatusType in ('Confirmed', 'Tentative')
Multiple Conditions
Multiple filter conditions are joined with logical operators: and or not
Remember that there is a space before and after each logical operator.
VenueLocation.Name = 'Grand Bravura Theater' and Status.StatusType = 'Confirmed'
If you are joining filter conditions with multiple values for the same field, it would look something like this:
VenueLocation.Name in ('Grand Bravura Theater', 'Exhibition Hall') and Status.StatusType in ('Confirmed', 'Tentative')
Using Correct Field/Property Path
It is important that you use the correct field or property path when creating a filter condition.
For example, if filtering for Event Status, you cannot simply use Status = 'Confirmed', because the Event Status field directs to an Object Type, and you can't use the Object Type directly.
You need to specify the lowest-level field in the Event Status Object.
•So, for example, to filter by Event Status Item Code, you would use: Status.ItemCode = 'Confirmed'
•To filter by Event Status Type, you would use: Status.StatusType = 'Confirmed'
Text, DateTime, Integer, Boolean, or Number
If the Field's Type is Text, DateTime, Integer, Boolean, or Number, you can simply use that Field for the filter condition, in the format Field Operator Value.
•To filter for an Event Name, you would simply use: EventName = 'Company Banquet'
•To filter for a Booking Number, you would use: BookNo = 12
Type or Object
If the Field directs you to a Type or Object, you have to look into the sub-set of fields.
Object
Under the Object name, there is subset of Fields and their Types. You have to specify one of those sub-set fields in the filter, in the format Field.Field Operator Value.
For example, to filter for an Event Category, you have to include the Item Code field from the SetupEventCategoryObject:
EventCategory.ItemCode = 'Business'
Type
Under the Type name, there is a subset of Names and Values. You need to use one of those specified Values in the filter.
For example, to filter on Deposit Status, you use the specified Values as described under DepositStatus Type.
DepositStatus = 2
Fields Available for Filters
For the fields that can be used in filters, please refer to the field definitions in the various Endpoints described below.
Note, however, that the field definitions below only describe the fields exported for a request; they do not describe every single field in the objects in EventPro.
You can still filter on any field in a given object, even if it is not among the field definitions included here in the manual.
Filter Parameter Example:
As noted above, the filter parameter added to the endpoint is formatted as follows:
•Base URL/Object Type Identifier?filter=value
A sample endpoint with the filter parameter would look something like this:
•https://connect.eventpro.net/Api/DataService.svc/eventlocations?filter=VenueLocation.Name = 'Grand Bravura Theater' and not Status.StatusType = 'Confirmed'
Set rtfMode
Rich text fields are returned in whatever format was passed as the rtfMode in the endpoint. Any List endpoint can use this as a parameter to the endpoint.
rtfMode Values
•rtfMode=0 (The Default. Rich text fields will not be included in the results.)
•rtfMode=1 (Rich text fields will be displayed as plain text.)
•rtfMode=2 (Rich text fields will be formatted in RTF.)
•rtfMode=3 (Rich text fields will be formatted in HTML.)
If you do not specify the rtfMode, the default is null, which assumes the Mode is 0 (zero), meaning that rich text fields will not be included in the response.
Therefore, if you want rich text fields to be included in the response, you must add the rtfMode parameter, and set its value to 1, 2, or 3.
As noted above the rtfMode is added to the end of the endpoint as a parameter.
rtfMode Parameter Example
•Base URL/Object Type Identifier?rtfMode=value
•https://connect.eventpro.net/Api/DataService.svc/events?rtfMode=3
Filter Examples
If you add both the filter parameter and rtfMode parameters to an endpoint, use an ampersand & to separate the parameters.
Base URL/Object Type Identifier?filter=value&rtfMode=value
https://server/EventProApi/DataService.svc/eventlocations?filter=VenueLocation.Name = 'Grand Bravura Theater'&rtfMode=2
Also remember that your Base URL may be different than what appears in these examples.
List Events set to a Status of Status Type 'Confirmed':
http://localhost/EventProApi/DataService.svc/events?filter=Status.StatusType = 'Confirmed'
List Events set to a Status of Status Type 'Confirmed', with rich text fields formatted in RTF:
http://localhost/EventProApi/DataService.svc/events?filter=Status.StatusType = 'Confirmed'&rtfMode=2
List Events set to the Status (Item Code) 'Confirmed':
https://server/EventProApi/DataService.svc/events?filter=Status.ItemCode = 'Confirmed'
List Events set to the Status (Item Code) 'Confirmed' or 'Tentative':
https://server/EventProApi/DataService.svc/events?filter=Status.ItemCode in ('Confirmed', 'Tentative')
List Events with Overdue Deposits:
https://server/EventProApi/DataService.svc/events?filter=DepositStatus = 3
List Accounts that are Flagged:
https://server/EventProApi/DataService.svc/accounts?filter=Flagged = true
List Invoices billed to a specific account, e.g. GRM Corporation:
https://server/EventProApi/DataService.svc/invoices?filter=BillToAccount.Name = 'GRM Corporation'
List Invoices billed to Events, i.e. Bill To Type of Event:
https://server/EventProApi/DataService.svc/invoices?filter=BillToType = 2
List Payments received from a certain account, e.g. GRM Corporation:
https://server/EventProApi/DataService.svc/payments?filter=ReceivedFromAccount.Name = 'GRM Corporation'
List Booked Event Locations in a certain Venue Location, e.g. Grand Bravura Theater, with rich text fields displayed as plain text:
https://server/EventProApi/DataService.svc/eventlocations?filter=VenueLocation.Name = 'Grand Bravura Theater'&rtfMode=2
List Booked Event Locations of Status Type 'Confirmed' in a Venue Location (Grand Bravura Theater) with rich text fields formatted as HTML:
https://server/EventProApi/DataService.svc/eventlocations?filter=VenueLocation.Name = 'Grand Bravura Theater' and Status.StatusType = 'Confirmed'&rtfMode=3
List Booked Event Locations held in the 'Grand Bravura Theater', but not including Event Locations of Status Type 'Confirmed', with rich text fields formatted as RTF:
https://server/EventProApi/DataService.svc/eventlocations?filter=VenueLocation.Name = 'Grand Bravura Theater' and not Status.StatusType = 'Confirmed'&rtfMode=2
https://server/EventProApi/DataService.svc/eventlocations?filter=not Status.StatusType = 'Confirmed' and VenueLocation.Name = 'Grand Bravura Theater'&rtfMode=2
https://server/EventProApi/DataService.svc/eventlocations?filter=VenueLocation.Name = 'Grand Bravura Theater' and Status.StatusType != 'Confirmed'&rtfMode=2
List Booked Event Locations of Status Type 'Confirmed' or 'Tentative', in either of two Venue Locations (Grand Bravura Theater or Exhibition Hall):
https://server/EventProApi/DataService.svc/eventlocations?filter=VenueLocation.Name in ('Grand Bravura Theater', 'Exhibition Hall') and Status.StatusType in ('Confirmed', 'Tentative')
Using 'and', 'or', and 'not', and a date format:
http://localhost/EventProApi/DataService.svc/eventlocations?filter=BookedDate >= '2018-08-20' and BookedDate <= '2018-08-27'
Check if a text or object field is empty:
http://localhost/EventProApi/DataService.svc/events?filter=Description is null
http://localhost/EventProApi/DataService.svc/events?filter=EventCategory is null
