When creating search forms or pre-defined filters to narrow the results in Report DataPages, it is very useful to understand the logical operators and comparison types. Knowing what these elements are and how they work can help you create more advanced and functional DataPages.
There are two logical operators available in search forms and predefined filters. They are AND and OR.
AND is by far the most common logical operator used in Report DataPages. Any piece of information that is entered into a search page with an AND operator will narrow the search results. All the pieces of information used in AND must be true for every record that is shown in the search results.
OR is used the opposite of AND. Any piece of information that is entered into a search page with OR as the logical operator will broaden the results returned.
Caspio provides you with a drag and drop user interface in the Logic tab of the search or filter configuration screen to setup a criteria using a combination or nested AND/OR logic in your criteria.
When you first open the Logic tab all your search elements are shown as grouped in one AND operator. You can drop other AND or OR operators from the left panel into the canvas and make them snap to the right spot in your criteria list. Then you can move existing search elements or new ones into the new AND and OR groups.
This interface allows you to create complex criteria without working with code. The image below is how this criteria is visualized and setup in Caspio.
First Name = “xxx” AND Last Name = “xxx” AND (Member ID = “xxx” OR Phone = “xxx-xxx-xxxx” OR Email = “email@example.com”)
The following is the list and description of comparison types in DataPages:
|A positive match occurs when the provided value is identical (or different in the case of Not Equal) to the field value. Not case sensitive. Date/Time fields must match the entire date and time.
You can use wildcards (e.g. % _ [ ] ^ – ) in your search queries.
Applies to: Text, AutoNumber, Number, Date/Time, Yes/No, File
Does Not Contain
|Keyword is searched within a text field or a file name field. If multiple keywords are entered in the search field, AND operator is assumed between them. User can also provide a desired operator by entering it in ALL CAPS (i.e. blue OR orange). Use parenthesis to group parameters. For (blue OR orange) used as keywords with Contains the following outcome will result:
Positive match: “The flower that is red.”
Greater Than or Equal
Less Than or Equal
|These comparison types can be used to return records with number values higher or lower than the provided value. Date values will be returned if the record value is after or before the provided date for “Greater Than” and “Less Than” respectively. These comparison types can also be used with text values to return strings that would appear alphabetically after or before the provided text string for “Greater Than” and “Less Than” respectively. Capitalization is not considered when comparing text strings.
Applies to: Text, AutoNumber, Number, Date/Time, File
Is Not Blank
|Is Blank creates a checkbox so users can limit their results to only records that have no data in the search field. If the checkbox is not checked, the field is ignored. Is Not Blank works the opposite way.
Applies to: Text, Number, Date/Time, File
|When comparing date values these comparison types return only records where the date value is in the provided range. Week is from Sunday to Saturday. This field will be hidden from the end-user because the current date is automatically used.
Applies to: Date/Time
|Previous X Days
Next X Days
|This comparison type limits results to only records with date values within the previous or next X number of days. The current day is not included in the range and if desired must be added as a separate criteria. Learn more here. This field will be hidden from the end-user. At design time you specify the value for X.
Applies to: Date/Time
|These comparison types limit results to only records that have date data falling before (or after) the current date/time. If time data is absent then all records with date data matching today will be included. This field will be hidden from the end-user.
Applies to: Date/Time
Note: Some Unicode characters such as emoji are ignored in the search criteria.
Combining Comparisons and Range Searches
If you would like to use more than one comparison type for a single field you can add criteria using the Insert button. This way you can include the criteria for the next X number of days, today, and the previous x number of days. You can set the logical operator to AND and create range searches. The Insert button is located on the bottom right corner of the DataPage Elements panel.
Fields of type Date/Time automatically contain both date and time values. There may be cases where you need to ignore the time and only consider the date,such as searching an entire year, or a range of months. In these situations, you can use an option from the Precision dropdown to control the precision by which the date and time values are compared against the search criteria.
- Date and time: Both date and time values are used in the search. User must search for exact values: “1/12/2007 11:20:23”.
- Date: Ignores time values and uses only the date to find matches.
- Month and Year: Both the day and time portions are ignored.
- Year: Ignores everything except the year portion of the date.