Skip to main content

​
Function Categories

diagram

​
String Functions

FunctionArgumentsReturn TypeDescription
toupperstringStringConverts a string property value to uppercase
tolowerstringStringConverts a string property value to lowercase
trimstringStringRemoves leading and trailing whitespace
concatstring, stringStringConcatenates two string values
substringstring, Int32 (start), Int32 (length)StringExtracts a portion of a string starting at a position for a specified length
replacestring (search), string (replacement)StringReplaces occurrences of a substring with another
lengthstringInt32Returns the character count of a string property
indexofstring (source), string (search)Int32Returns the position of a substring within a string

​
toupper

Converts the property value to uppercase for case-insensitive comparisons: 
{
  "toupper(title)": { "eq": "SAFETY REQUIREMENT" }
}

​
tolower

Converts the property value to lowercase: 
{
  "tolower(status)": { "eq": "approved" }
}

​
trim

Removes whitespace from both ends of the property value: 
{
  "trim(title)": { "ne": "" }
}

​
concat

Concatenates two string values: 
{
  "concat(firstName, lastName)": { "contains": "Smith" }
}

​
substring

Extracts a substring starting at the given position: 
{
  "substring(id, 0, 3)": { "eq": "REQ" }
}
ArgumentTypeDescription
SourcestringProperty to extract from
StartInt32Zero-based start position
LengthInt32Number of characters to extract

​
replace

Replaces occurrences of a search pattern: 
{
  "replace(title, 'Draft', 'Final')": { "ne": null }
}

​
length

Returns the string length as an integer: 
{
  "length(title)": { "gt": 0 }
}

​
indexof

Returns the position of a substring (zero-based): 
{
  "indexof(title, 'Safety')": { "ge": 0 }
}
Returns -1 if the search string is not found.

​
Date Functions

FunctionArgumentsReturn TypeDescription
yearDateTimeInt32Extracts the year component from a DateTime property
monthDateTimeInt32Extracts the month component (1-12) from a DateTime property

​
year

Extract and filter by year: 
{
  "year(created)": { "eq": 2025 }
}

​
month

Extract and filter by month (1-12): 
{
  "month(updated)": { "ge": 6 }
}
Additional date functions such as day, hour, minute, and second may be available. Verify supported functions in your Powersheet version.

​
Dynamic Value Expressions

In addition to query function expressions, Powersheet supports runtime dynamic value resolution in constraint and where clause configurations:
Expression TypeSyntaxDescription
Dynamic value() => expressionArrow function syntax evaluated at runtime
Context expression$context.property.pathDot-notation access to runtime context values

​
Context Expression Examples

constraints:
  load:
    project:
      id: $context.source.project.id

where:
  document:
    id: $context.document.id
Context expressions are resolved against the current runtime context, which includes:
Context PathDescription
$context.source.project.idCurrent project ID
$context.document.idCurrent document path (folder/name)
Static constraint values are evaluated at configuration parse time. Dynamic values (prefixed with $context.) are resolved at query execution time, enabling context-sensitive filtering.

​
Function Expression in Predicate Context

Function expressions are used within the where clause of an EntityQuery or within constraint definitions. They transform property values before the comparison operator is applied. The general pattern is: 
functionName(propertyPath) operator value
This is expressed in the query JSON as: 
{
  "functionName(propertyPath)": { "operator": "value" }
}

​
Complete YAML Example

sources:
  - id: recent-requirements
    model: rtm
    query:
      from: UserNeed
      where:
        and:
          - title:
              ne: null
          - status:
              ne: rejected
    constraints:
      applyCurrentDocumentTo: UserNeed
      load:
        project:
          id: $context.source.project.id
    expand:
      - name: systemRequirements
  • Predicates — filter condition structure that uses function expressions
  • Operators — comparison operators applied after function transformation
  • EntityQuery — top-level query containing the where clause
  • Query Context — runtime context for dynamic value resolution
Sources: Code: FnExpression.java, helpers.ts (dynamic values), parseConstraints.test.ts, Operator.java
Source Code
  • FnExpression.java
  • prod-powersheet-src/com.nextedy.powersheet.client/ltc-repo/packages/common/modules/configuration/helpers.ts
  • Query.java
  • prod-powersheet-src/com.nextedy.powersheet.client/ltc-repo/cypress/e2e/Sheet/reference.spec.ts
  • QueryToLuceneTest.java