Sample spreadsheet filter expressions
The following table shows how you can use certain functions in filters.
Tips:
- Check the syntax of your expression to make sure it is correct.
- Validate your expressions with sample data to ensure your filter yields the results you expect.
| Function | Description | Sample expression | Result |
|---|---|---|---|
| StartsWith(str, startsWithStr) | Returns true if a string starts with the specified value | StartsWith(Description, "Studs") | Returns all items with a Description field that starts with "Studs." In a similar way, you can target any field, such as a WBS code, location or BOM description. |
| EndsWIth(str, endsWithStr) | Returns true if a string ends with the specified value | EndsWith(Description, "Studs") | Similar to StartsWith, this function returns all items with a Description field that ends with "Studs." |
| Contains(str, containsWithStr) | Returns true if a string contains the specified value | Description.Contains("Conc") | Similar to StartsWith, this function returns all items with a Description field that contains"Studs" anywhere in the string passed by the function. |
| RegexMatch(str, pattern) | Checks whether a piece of text matches a regular expression | RegexMatch(Description, "2x[1-9][0-9]") | Returns only items with a description that contains "2x" followed by a 2-digit number. |
| ToUppercase(str) | Converts a string from lower case to upper case | Contains(ToUppercase(Description), "JOIST") | Returns only items with a description that contains "2x" followed by a 2-digit number. |
| ToLowercase(str) | Converts a string from upper case to lower case | Contains(ToLowercase(Description), "joist") | Returns all items whose description contains "Joist," regardless of case. |
| Length(str) | Retrieves the length of a string | Length(LaborJCPhase)=5 | Returns all job cost phases 5 characters in length, including special characters |
| NewLine() | Incorporates a carriage return in your search | Contains(Notes, "Exclusions:" + NewLine() + "Sitework") | Finds notes that contain "Exclusions:" and then "Sitework" on a new line |
| Min(Value1, value2) | Evaluates, line by line, which of 2 parameters is smaller and returns that value | Min(LaborAmount, MaterialAmount) > 100 | — |
| Max(Value1, value2) | Evaluates, line by line, which of 2 parameters is larger and returns that value | Max(LaborAmount, MaterialAmount) > 100 | — |
| Round(fractionalDigits) | Rounds the value of the parameter to the nearest integer | Round(TakeoffQuantity)=24 | Compares quantities where precision is not necessary and you want to compare with an integer |
| Abs(value) | Takes the absolute value of the parameter | Abs(TakeoffQuantity)=24 | Use for example to calculate the distance between two points, for example from 0 |
| Ceiling(value) | Rounds the value of the parameter to the nearest integer | Ceiling(TakeoffQuantity)=25 | Use to round a fractional value up. For example, if you need 1.2 buckets of nails, but can't buy fractional quantities, you you need to buy 2 buckets |
| Floor(value) | Rounds the passed value down to the nearest integer | Floor(TakeoffQuantity)=24 | Use when you cannot buy a fractional amount and want to round down. |
| Truncate(value) | Removes trailing decimal places | Truncate(TakeoffQuantity)=24 | — |
| CurrentDateTime() | Passes the current date time | MatPriceChanged>SubtractFromDateTime(CurrentDateTime(),1,0, 0,0) | — |
| CreateDateTime(Y, M, d, h, m, s) | Creates a date/time to use in an expression | MatPriceChanged<CreateDateTime(2018,1, 1,0 ,0 ,0 ) | — |
| AddToDateTime(dateTime, d, h, m, s) | Adds a set amount of time to a date | ConvertToDateTime(WBS1) < AddToDateTime(CurrentDateTime(),30 ,0 ,0 ,0 ) | — |
| SubtractToDateTime(dateTime, d, h, m, s) | Subtracts a set amount of time from a date | MatPriceChanged>SubtractFromDateTime(CurrentDateTime(),1,0, 0,0) | — |
| WbsValueByCode(wbsCodeName) | Look up WBS Code by the WBS Name | WbsValueByCode('Bid Item')='2' | — |
| WbsCodeByIndex(wbsIndex) | Looks up WBS Code by the WBS Index name | WbsValueByCode(WbsCodeByIndex(2)) = '2' | — |
| ConvertToInteger(str) | Converts a string value to an integer | ConvertToInteger(WBS2)=2 | — |
| ConvertToFloatingPoint(str) | Converts a string value to a floating point value | ConvertToFloatingPoint(WBS2)=2.00 | — |
| ConvertToDateTime(str) | Converts a date string to a date value | ConvertToDateTime(WBS1) < AddToDateTime(CurrentDateTime(),30 ,0 ,0 ,0 ) and ConvertToDateTime(WBS1) > CurrentDateTime() | — |