Wild Cards / Operators
Using Wild Cards and Operators make your searches even more powerful. Search for ranges, exclude values and even search for data in a specific position in a word or code.
Wild Card / Operators Tips
- Data used for searches are not case sensitive. The value 'civil' is equal to 'Civil' and 'CIVIL' when entering data in the Navigate Panel.
- When entering dates, use the same format that the datapp displays is a good rule to follow, however, most standard date formats will work with either dashes or slashes.
Operators
| Operator | Description |
|---|---|
| ! | The exclamation mark in front of a value will exclude that value from the results. |
| ∗ | The asterisks matches any number of characters. You can use the asterisk anywhere in a character string. |
| , | Use the comma to separate values when you are entering more than one |
| .. | Two dots are used to specify a range. This can be numeric, text, or dates. |
| ? | The question mark is used when you are specifying characters in a specific position. |
| @ | The at symbol is used in front of a dynamic parameter. |
Examples
| Description | Expression | Result |
|---|---|---|
| Equal To | civil | Finds all data where the value is exactly equal to ‘civil’. |
| List | CA,MN,TX | Finds all values equal to CA, MN, or TX. |
| Not Blank | * | Finds all data where the element is not blank. |
| Blank | !* | Finds all data where the element is blank. |
| Starts With | 1* | Finds all values that start with 1. |
| 12* | Finds all values that start with 12. | |
| Ends With | *3 | Finds all values that end with 3. |
| *34 | Finds all values that end with 34. | |
| Contains | ∗def∗ | Finds all values that contain ‘def’. |
| Contains in Specific Position | ??345 | Finds values where ‘345’ starts in the specified position. |
| @lt - Less Than | @lt 500 | Finds all values less than 500. |
| Less Than or Equal | ..100 | Finds all values less than or equal to 100. |
| @gt - Greater Than | @gt 0 | Finds all values greater than 0. |
| Greater Than or Equal | 200.. | Finds all values greater than or equal to 200. |
| Range | 100..200 | Finds all values between and including 100 and 200. |
| Not Equal, Exclusion | !10 | Finds all values not equal to 10. |
| Combination | 01,13..18,30 | Finds all values equal to 01, 13, 14, 15, 16, 17, 18, and 30 (list and range) |
| 13..18,!15 | Finds all values equal to 13, 14, 16, 17, 18 (range with exclusion). | |
| Date - Range | 7/14/2020..8/1/2020 | Finds all values between and including the dates used. |
| Date - After | 7/14/2020.. | Finds all values equal to 7/14/2020 and after. |
| Date - Before | ..7/14/2020 | Finds all values equal to 7/14/2020 and before. |
Dynamic Parameters
In the Simpler datapps, Dynamic Parameters are a shorthand for frequently used filters or functions. From the Navigate Panel, click on the field you want to filter and type or select the name of the Dynamic Parameter. By typing the '@' into the field, you will see a list of available parameters. Click on the one you want and then click Navigate. Some Dynamic Parameters will accept an argument. For example, you can add a number as an argument with date parameters for prior periods, using @prior-month or @prior-month 1 will give the same results, a date range representing the prior month. If you use @prior-month 3, it will be a date range representing three months ago. If the current date is August 2020, the results will return all dates for May 2020.
Date Parameters
The following Dynamic Parameters are available with columns that contain any Date format. Some Date Parameters can use arguments.
| Parameter | Description | Use Argument |
|---|---|---|
| @today | Returns the current date | |
| @yesterday | Returns yesterday’s date | |
| @current-month | Returns a date range representing the current month | |
| @current-month-begin | Returns the first date of the current month | |
| @current-month-end | Returns the last date of the current month | |
| @prior-month | Returns a date range representing the prior month | Yes |
| @prior-month-begin | Returns the first date of the prior month | Yes |
| @prior-month-end | Returns the last date of the prior month | Yes |
| @current-quarter-begin | Returns the first date of the current quarter | |
| @current-quarter-end | Returns the last date of the current quarter | |
| @prior-quarter-begin | Returns the first date of the prior quarter | |
| @prior-quarter-end | Returns the last date of the prior quarter | |
| @current-year | Returns a date range representing the current calendar year | |
| @current-year-begin | Returns the first date of the current calendar year | |
| @current-year-end | Returns the last date of the current calendar year | |
| @prior-year | Returns a date range representing the prior calendar year | Yes |
| @prior-year-begin | Returns the 1st date of the prior calendar year | Yes |
| @prior-year-end | Returns the last date of the prior calendar year | Yes |
Fiscal Year Parameters
The following Dynamic Parameters are available with columns that contain a Date format. Fiscal Year Parameters do not use arguments.
| Parameter | Description |
|---|---|
| @current-fiscal-year-begin | Returns the first day in the current fiscal year, determined based on today's date |
| @current-fiscal-year-end | Returns the last day in the current fiscal year, determined based on today's date |
| @prior-fiscal-year-begin | Returns the first day in the prior fiscal year, determined based on today's date |
| @prior-fiscal-year-end | Returns the last day in the prior fiscal year, determined based on today's date |
Pay Period Parameters
The following Dynamic Parameters are available with columns that contain a Pay Period, e.g., YYYY-PP format. Pay Period Parameters do not use arguments.
| Parameter | Description |
|---|---|
| @current-payperiod | Returns a date range for the current pay period |
| @last-completed-payperiod | Returns the last pay period where the Confirmation, Sheets, and Calculation Run are all equal to Yes |
| @current-cal-year-begin-payperiod | Returns the first pay period in the current calendar year, determined based on the paycheck date |
| @current-cal-year-end-payperiod | Returns the last pay period in the current calendar year, determined based on the paycheck date |
| @prior-cal-year-begin-payperiod | Returns the first pay period in the prior calendar year, determined based on the paycheck date |
| @prior-cal-year-end-payperiod | Returns the last pay period in the prior calendar year, determined based on the paycheck date |
| @current-fiscal-year-begin-payperiod | Returns the first pay period in the current fiscal year, determined based on the paycheck date |
| @current-fiscal-year-end-payperiod | Returns the last pay period in the current fiscal year, determined based on the paycheck date |
| @prior-fiscal-year-begin-payperiod | Returns the first pay period in the prior fiscal year, determined based on the paycheck date |
| @prior-fiscal-year-end-payperiod | Returns the last pay period in the prior fiscal year, determined based on the paycheck date |
| @current-quarter-begin-payperiod | Returns the first pay period in the current quarter, determined based on the paycheck date |
| @current-quarter-end-payperiod | Returns the last pay period in the current quarter, determined based on the paycheck date |
| @prior-quarter-begin-payperiod | Returns the first pay period in the prior quarter, determined based on the paycheck date |
| @prior-quarter-end-payperiod | Returns the last pay period in the prior quarter, determined based on the paycheck date |
Other Parameters
| Parameter | Description |
|---|---|
| @word-starts-with | Returns all values that begin with the text entered following the macro |
Examples
You can also extend the parameter by adding an argument to the end. For example, if you always want the datapp to default to the prior year you would use the @prior-year Dynamic Parameter. To default to 2 years ago you would use @prior-year 2. Here are a few examples of how you can use parameters.
| Parameter | Current Date | Result |
|---|---|---|
| @current-month-begin | January 15, 2020 | January 1, 2020 |
| @current-year | January 15, 2020 | January 1, 2020 - December 31, 2020 |
| @prior-year | January 15, 2020 | January 1, 2019 - December 31, 2019 |
| @prior-year 2 | January 15, 2020 | January 1, 2018 - December 31, 2018 |