Advanced filters allow you to apply logic to return only certain records from the server. The basic syntax for a filter is (ATTRIBUTE,OPERATOR,CRITERIA). The attribute is the name of the attribute to search, the operator provides the type of comparison, and the criteria is the search value. A sample use of advanced filters is to send a group of documents, customers or other records created before or after a certain date. When you click the Advanced Filter link, many of the main resources (e.g. Customers, and Inventory, but not Document) will display a list of columns that can be included in filters. Refer to these lists as you construct your advanced filters.
Sample Advanced Filter screen (after entering criteria)
Available Comparison Operators
The current operators you can use in a filter are as follows:
• EQ - Equal to
• NE - Not equal to
• LT - Less than
• GT - Greater than
• LE - Less than or equal to
• GE - Greater than or equal to
• NL - Is null
• NN - Not Null
• LK - Like
• NL and NN do not accept a criteria element e.g. filter=active,nn
• LK filter uses a letter as a wildcard. For example s matches all entries starting with s
String Values Require Double Quotes
If filtering on a string value, you must surround the criteria with double-quotes, as shown:
This status value is auto-inserted into updated records by the system to ensure only completed transactions are replicated This is a required parameter. The program will automatically insert the value, so you don't have to worry about adding it, but it is important to know what it is for if you see it.
The criteria you can include will depend on the type of attribute. For example, the Active column takes a 1 or a 0 for some resources, and a True or a False for other resources. The Cost attribute takes a number. The Last Name column takes a string.
Advance Filter Format Notes
Here is a sample advanced filter to find documents created after a certain date/time. Notice that there are no quotes used in the filter.
- Status=4. This status value is auto-inserted into updated records by the system to ensure only completed transactions are replicated
- Spaces are not allowed
- CamelCase column names are OK
- String values require double-quotes
- DateTime format = yyyy-mm-ddThh:mm:ss.mmm-xx:yy
- ‘T' separator must be between date-time
- Time is 24 hour time (1:00 pm = 13:00)
- Seconds must be specified to thousandths of a second (e.g. xx:12.123)
The Advanced Filter screen has a Date Generator element. This enables you to easily generate a date in the correct format that can then be cut/pasted and edited as needed for your specific parameters. This saves users time and helps avoid mistakes in date formatting. The key thing to keep in mind with the Date Generator is that the generated date/time is in Greenwich Mean Time, also known as Zulu. You will have to edit the generated date/time to include the correct offset from Zulu for your region.
Click the Calendar icon to generate a string for the date/time. Notice that the generated date/time has a Z at the end. This indicates the string is in Zulu time, otherwise known at Greenwich Mean Time and NOT the user's local time (unless the user happens to be in the GMT time zone).
Modify the date/time as needed. Replace the Z with the desired offset. For example, the West Coast of the United States is eight hours behind Greenwich Mean Time (Zulu), so replace the Z with -08:00.
Compound filters can also use AND and OR clauses with parenthesis to establish grouping. e.g. (FirstName,eq,jack)AND(LastName,eq,smith)
Filtering Documents by Subsidiary
These steps show how to create a filter to send or receive only a certain subsidiary's documents.
1. Create a new filter in Connection Manager.
2. For the filter, select the Document resource.
3. Click the Advanced Filter link.
4. Enter a filter for the subsidiary whose documents you want to send. (SbsNo,eq,2)
5. Click the Back button and save the profile.
If you make an error in the syntax (e.g. invalid columns or missing parenthesis), a message will be displayed at the bottom of the screen. Fix any errors and then the Back button will be enabled.