PowerClerk Support Center

Program Design
Automations
Questions to Ask
Locating the Automations feature
How to create an Automation
Automation Triggers
Automation Action Rules
Validation Rules
Scheduled Triggers
Troubleshooting Automations
FAQs
Channels
Questions to ask yourself
Locating the Channels feature
What are Channels?
Types of Channels
Channel Type: Mark as Child
Channel Type: Make Successor
Channel Type: Create Related Project
Channel Type: Submit Related Project
Channel Type: Project Lookup
How to create a Channel
Channel Signals and Automations
FAQs
Communications
Questions to ask yourself
Locating the Communications feature
Creating Communication templates
Finding Data Tags
Bulk Communications via Data Import
Images in Communications
Project Attachments and Content Library Items
Smart Templates
Upgrading to Smart Templates
Upgrading when a Test Environment Exists
Broken Template Tags
Examples of Broken Template Tags
PowerClerk Bulk Email Communication Policy
FAQs
Connections
Questions to ask yourself
Locating the Connections feature
What are Connections
How to Create a Standard Web Adapter
Define Input and Output Fields
Connecting, Testing, and Enabling the Web Adapter
Creating a Standard Web Adapter Configuration
Utilizing Connections on PowerClerk
Maintaining and Editing the Web Adapter
Web Adapter Message Type Format
Supported Message Versions
Error Handling
Custom List Lookup
Create a Custom List Lookup
Utilizing Custom List Lookup
FAQs
Content Library
Questions to ask yourself
Locating the Content Library feature
Uploading content to the Content Library
Use with Communication Templates
Use for Front Page content
FAQs
Custom API IDs
Questions to Ask
Locating the Custom API IDs feature
How to edit a Custom API ID
FAQs
Data Fields
Questions to ask yourself
Locating the Data Fields feature
How to work with Data Fields
Custom Lists and Data Field Groups
Table form element
PV System + batteries element
FAQs
Deadlines
Questions to ask yourself
Locating the Deadlines feature
What are Deadlines
How to Create a Deadline
Deadline Automation Action Rules
Utilizing Project Admin Page for Deadlines
Communication Templates for Deadlines
Deadline Set/Satisfy Options
Program-Wide Deadline Actions
Reporting on Deadlines
Deadlines in Project List Columns
FAQs
Document Templates
Questions to ask yourself
Locating the Document Templates feature
How to define a new Template
How to define a new Merged Document
eSignatures
DocuSign template tags
Mapping eSignature tags
Smart Templates
FAQs
eSignature Envelopes
Questions to Ask
Locating the eSignature Feature
What are eSignature Envelopes?
eSignature Checklist: The Prerequisites to create a new Envelope
How to set up Advanced eSignature Envelopes Step-by-Step
How to add an eSignature Envelope to a form
eSignature Automation Trigger
Viewing Completed eSignature Envelopes
Resending eSignature Notifications
Canceling eSignatures
FAQs
Forms
Questions to ask yourself
Locating the Forms feature
How to create and edit Forms
Adding data fields
Configuring Forms
VersaForms
Sensitive Data Fields
FAQs
Formulas and Calculated Fields
Questions to ask yourself
Locating the Formulas feature
How to work with Formulas and Calculated Fields
Formula Data Dictionary
Dynamic Formula References
Rules of Formula References
Advanced Visibility Rules
Video Guides: Formulas
FAQs
Front Page
Questions to ask yourself
Locating the Front Page feature
How to edit the Front Page
FAQs
Incentive Design
Questions to ask yourself
Locating the Incentive Design feature
How to create and edit Incentive Designs
Incentive Options for One-Time Incentive Type
Incentive Design Options
FAQs
Milestones
Questions to ask yourself
Locating the Milestones feature
How to define a Milestone
FAQs
Project List Columns
Questions to ask yourself
Locating the Project List Columns feature
How to use Project List Columns
FAQs
Project Summary
Questions to ask yourself
Locating the Project Summary feature
How to edit the Project Summary
FAQs
Project Views
Questions to ask yourself
Locating the Project Views feature
How to edit Project Views
FAQs
Roles
Questions to ask yourself
Locating the Roles feature
How to create and edit a Role
Access Groups
Access Groups and Automations
Access Groups and Data Imports
Access Groups and Reports
FAQs
Workflow
Questions to ask yourself
Locating the Workflow feature
How to create and edit the Workflow
Transitions
Workflow Example Overview
FAQs
Administration
Business Days
Questions to ask yourself
Locating the Business Days feature
Setting up Business Days
FAQs
Dashboards
Questions to ask yourself
Locating the Dashboards feature
How to create widgets in your Dashboard
Other Dashboard Actions
Data Import
Questions to ask yourself
Locating the Data Import feature
Steps to Complete a Data Import
Data Import Configurations
Column Header Types
Automatic Data Imports via SFTP
FAQs
Duplicate Check
Questions to ask yourself
Locating the Duplicate Check feature
How to use Duplicate Checks
FAQs
Import Projects
Questions to ask yourself
Locating the Import Projects feature
How to Import From V2
FAQs
Operation Status
Questions to ask yourself
Locating the Operation Status feature
How to use the Operation Status feature
FAQs
Program Info
Project Inquiry
Locating the Project Inquiry feature
How to edit the Project Inquiry Settings
Automating Communications for Project Inquires
Inquiry Summary
Questions to ask yourself
Locating the Program Info feature
How to edit the Program Info menu
Notification Banners
Usage Info
FAQs
Program Statistics
Questions to ask yourself
Locating the Program Statistics feature
How to use Program Statistics
FAQs
Reports
Questions to ask yourself
Locating the Reports feature
How to setup Reports
Multi-instance reports
Sharing Reports
Integrate scheduled Reports
Cross-Program Reports
FAQs
Test Environment
Questions to ask yourself
Locating the Test Environment feature
How to setup a Test Environment
FAQs
User Administration
Questions to ask yourself
Locating the User Administration feature
How to work with User Administration
FAQs
Tools Menu
My Account
Questions to Ask
Locating the My Account feature
How to use the My Account feature
Setting up Multi-Factor Authentication
Missing, lost, or stolen mobile devices: resetting Multi-Factor Authentication
Disabling Multi-Factor Authentication
Recovery Guidelines for MFA Administrators
FAQs
FormSense
Questions to Ask
Locating the FormSense feature
How to use the FormSense feature
FAQs
Grant Access
Questions to Ask
Project Grants vs Broad Grants (i.e. "Grant Access")
Locating the Project Grant feature
Locating the Grant Access feature
How to use the Grant Access feature
FAQs
Integration Guides
ePayments
Questions to ask yourself
Locating the ePayments History feature
How to add ePayments
FAQ
PowerClerk API
Questions to Ask
What is the PowerClerk API?
What can the PowerClerk API do?
Integrating with the PowerClerk API
API Documentation for Developers
Single Sign On (SSO)
Questions to Ask
PowerClerk SSO Configuration
Azure AD Configuration (SAML)
Azure AD Configuration (OIDC)
Okta IDP Configuration (SAML)
SP Configuration
SSO for multiple programs
IDP Configuration Troubleshooting
FAQs
Integration Guide 001: How to configure a Web Adapter – ArcGIS Implementation
Integration Guide 002: How to configure Electric Power Research Institute’s (EPRI) DRIVE Connect software with PowerClerk
PowerClerk Video Guides
New User Video Guide
Setting up Business Days
Dashboards
FormSense
Edit Forms - Tutorial #1
Build A Formula
Edit Forms - Tutorial #2
Automation with Formulas in Action Rules
Configuring Forms
Formulas and Advanced Visibility Rules
Calculated Fields
Milestones
Project Summary
Setting up Roll-up Reports
Roles and User Administration
Visualize Workflows
ArcGIS
API
SFTP Automatic Data Import
Standard Web Adapter Setup
PowerClerk Program Launch
PowerClerk User Group Sessions (UGS)
Learning Management System (LMS)

Formulas and Calculated Fields

Use Formulas to perform calculations in Forms that assist applicants and increase data accuracy.

Formulas feature


Questions to ask yourself about Formulas:

Are there parts of the program application where providing in-form calculations would increase the accuracy of data inputs (like showing an applicant their long-term average PV energy output)?

Are there Data Fields that need to be calculated or created for certain Reports or Dashboards?

Which Forms do require calculations that can be handled by Formulas?
Should a Formula result trigger an Automation?
Do you have elements on forms that should only be displayed based on a Formula result?

Locating the Formulas feature

Anyone in a Role with the Edit Formulas privilege can edit Formulas by opening the PROGRAM DESIGN menu and clicking on Formulas.
 

Locating the Formulas feature

Figure 1: PROGRAM DESIGN >> Formulas

 

How to work with Formulas and Calculated Fields

The Formulas feature has been designed to enable operations to be performed automatically by PowerClerk by using references to Data Fields and constants. These can be used to create logical conditions such as to selectively run Automation action rules, create Validation rules for Automations, create Conditional Visibility rules, and much more. An intuitive drag-and-drop Formula editor is used to configure Formulas.

Calculated Fields are different than Formulas in that they are discreet Data Fields. This means that unlike Formulas, they house the data result of the Formula as a part of the project record. This allows the calculated results to be displayed real-time on Forms, to be used in Project List Columns, or to run local calculations for the purpose of more detailed Reporting.
 
Formulas can perform numeric operations (E.g. addition, subtraction, multiplication, division, etc.), Boolean operations (greater than, less than, is equal to, is same as, is not same as, etc.) on numeric data fields, and can also perform Boolean operations (is equal to, is not) on Boolean data fields (true/false). PowerClerk can also run calculations that result in a Date data field and run specific operations on Multi-Instance data fields such as tables. A few examples have been provided in Figure 2 and Figure 3 below. The top-right of the formula editor will tell you the data type that the formula result will be in.
 

Example Numeric Formula

Figure 2: Example Numeric Formula

 

Example Boolean Formula

Figure 3: Example Boolean Formula


Once a Formula has been saved, you can create a Calculated Field (Fig. 4) to use on your Forms, as Project List Columns, or in a Report. The definition of a Calculated Field cannot be edited. Additionally, any changes to the original formula after the Calculated Field’s creation will not be reflected in the Calculated Field, and a new one will need to be created. If editing a calculated field is needed, you must select “Copy to New Formula”, edit the newly created formula, and then create a new Calculated Field from the newly edited formula.
 
In the example in Figure 5 below, the Calculated Field created from the Formula comparing Estimated Annual Production to Historical Annual Consumption (kWh) designed in Figure 3 above populates the result automatically in the Form and updates automatically as data field values change.
 

Example Boolean Formula

Figure 4: Example Boolean Formula

 

Calculated Field element in a Form

Figure 5: Calculated Field element in a Form

 
Formulas can also be used within Automations to trigger Action Rules as shown in Figure 6 below:
 

Formulas in Automations

Figure 6: Formulas in Automations

 
 
 

Formula Data Dictionary

 

Supported Data Types (Input):
Color Data Type Reference
Blue accepts Numeric inputs Blue accepts Numeric inputs Numeric constant or Data Field Reference types (integer, decimal, price)
Orange accepts Boolean inputs Orange accepts Boolean inputs True, False, (Multiple) Choice, Drop-down List, Attachment Approval/Rejection or Data Field references types which return a True/False result (Checkbox, Boolean Calculated Field Result)
Green accepts Text inputs Green accepts Text inputs Text String or Text Data Field reference types (Single Line Text, Paragraph Text, Email, or Contact Data Fields)
Purple accepts Date inputs Purple accepts Date inputs Date or Date Field reference
Black accepts Dynamic inputs Black accepts Dynamic inputs Formula reference types (Numeric, Boolean/Relational, Text, Date)
Additionally, inputs can also accept formula operations that match their respective data types.

 

Supported Formula Operations (Output):
Numeric Operations
(Returns a Numeric result)
Operator Name Description
Addition Addition Adds two values
Subtraction Subtraction Subtracts two values
Multiplication Multiplication Multiplies two values
Division Division Divides two values
Constant Contant Can contain any Numeric value (defined within formula)
Data Field Reference Can reference any Numeric Data Field
If, Then, Else Compares sets of data and tests the results:
     If the results are true, the THEN instructions are taken
     If not, the ELSE instructions are taken e.g. If (A = TRUE), Then B, Else C
Min Min Returns the smallest value from the two Numeric values provided e.g. (1,2) = 1
Max Max Returns the largest value from the two Numeric values provided e.g. (1,2) = 2
Exponent Exponent Represents how many times a Numeric value is multiplied by itself
e.g. (2^5) = 2 x 2 x 2 x 2 x 2 = 32
Log Log e.g. Log (1000) = 3 (10 x 10 x 10 = 10^3 = 1,000)

 
 

Relational Formula Operations
(Subset of Boolean Formula Operations that tests a relationship and returns a True/False result)
Operator Name Description
Equal To Equal To Accepts a Numeric or Text input
Not Equal To Not Equal To Accepts a Numeric or Text input
Greater Than Greater Than Accepts a Numeric input
Less Than Less Than Accepts a Numeric input
Greater Than or Equal To Greater Than or Equal To Accepts a Numeric input
Less Than or Equal To Less Than or Equal To Accepts a Numeric input
Is Before/After Is Before/After Accepts a Date input

 
 

Logical Formula Operations
(Returns a True/False result)
Operator Name Description
And And True if and only if both conditions are true
Or Or True if either condition is true (or if both are true)
Not Not Changes true to false, and false to true
True True Constant of true
False False Constant of false
Boolean Data Field Reference References a Checkbox Data Field or Boolean formula result
Is Attachment Approved Reference Determines if an Attachment is approved by an administrator
Is Attachment Approved Reference Determines if an Attachment is rejected by an administrator
Choice Data Field Reference == Choice References a Multiple Choice Data Field & looks to see if the Data Field is equal to the designated choice in the argument
Choice Data Field Reference != Choice References a Multiple Choice Data Field & looks for if the Data Field is not equal to the designated choice in the argument

 
 

Text Formula Operations
(Returns a Text result except for the Length of Operation, which returns a Numeric result)
Operator Description
Text References any Text value entered
Text Data Field Reference References Single Line Text, Paragraph Text, Email, or Contact Data Fields
If Then Else Compares Boolean Data field or Formula result and tests the Text results
     If the results are true, the THEN instructions are taken
     If not, the ELSE instructions are taken e.g. If (A = TRUE), Then B, Else C
Length of Determines the length of a given Text value and returns a Numeric value
e.g. Length of (PowerClerk) = 10
Concatenate Combines two Text values and returns a concatenated Text string
e.g. “potat” + “oes” = “potatoes”
Project Number Enables referencing the respective project number (e.g. “CPR-00123”) within a Formula
Text() Reference non-string Data Field types such as Numeric, Boolean,
Choice, and Date Data Fields as text representations
enabling usage of non-string Data Fields in context of any of the other Text Formula
Operators (e.g. IF/THEN/ELSE, LENGTH OF(), CONCATENATE)

 
 

Data Formula Operations
(Returns a Date result)
Operator Description
Date References a designated Date constant
Date Data Field Reference References a Date Data Field
Time before/after References a Numeric value before/after a Date or Date Data Field reference e.g. (7) days after (Date)
Time: If Then Else References a Boolean Data Field or Formula result and tests the Date results.
If the results are true, the THEN instructions are taken
If not, the ELSE instructions are taken e.g. If (A = TRUE), Then B, Else C

 
 

Formula Reference Operations
(Returns a result based on the primary operation)
Operator Description
Formula Reference Place a Formula reference within any other Formula
operator to avoid having to create multiple
layers of complex Formulas:
Formula Reference

 
 

Multi-Instance Formula Operators
(valdation of multi-instance fields to ensure all field instances adhere to the desired criteria)
Operator Description
All Match Returns Boolean True if all instance values across the entire multi-instance field match, or False if not.
Value of All Match If all values across the multi-instance field match, returns that exact single value (whether that value is Numeric, Text, Boolean, or Date).
Sum All Returns the summation of all values across a multi-instance field of Numeric data type.
Count True Provides an integer value (e.g. 1, 2, or 3, etc.) based on the number of Boolean (e.g. Checkbox) “True” values are found across the multi-instance field.
Count Matching Text Provides an integer value (e.g. 1, 2, or 3, etc.) based on the number of matching Text (e.g. Single Line Text) values that are found across the multi-instance field.

 
 

Dynamic Formula References

Use Dynamic Formula References to simplify complex Formula expressions by referencing existing Formulas as components (or Formula segments) of the larger Formula. My defining simple formulas first, these can then be “nested” into a larger formula to create more complex definitions.
 
To reference a Formula within another Formula, please use the Formula Reference operand that is listed under the “Dynamic” section in the Formula editor:
 

Dynamic Formula References

Figure 7: Dynamic Formula References

 
The Dynamic Formula Reference operator itself is not designated to return a Numerical, Boolean, Text, or Date result. Once a Dynamic Formula Reference operator is placed into a Formula container, the operator will turn into the respective data type. Then, a list of the available Formula Reference Expressions (matching the respective data type) will be displayed under the Properties tab on the right-hand side of the editor.
 
Please Note: Formulas can also reference Calculated Fields. Currently only one level of reference is supported (i.e. you cannot reference Calculated Fields that reference other Calculated Fields).
 

Adding a Formula Reference

Figure 7a: Adding a Formula Reference determines data type

Formula Reference Types

Figure 7b: Dynamic Formula Reference data types


 

Rules of Formula References
  1. Incomplete Formulas cannot be referenced.
     

    Formula Reference Rule 1

    Figure 8a: Formula Reference Rule 1

     

  2. You cannot create circular Formula References.
     

    Formula Reference Rule 2

    Figure 8b: Formula Reference Rule 2

     

  3. A Formula Reference cannot be the only component of a Formula.
     

    Formula Reference Rule 3

    Figure 8c: Formula Reference Rule 3

     

 
Completed Formulas that incorporate Dynamic Formula References may be used to create Calculated Fields. Please note: Once the Calculated Field has been created, any Formula Reference is fully resolved into the Calculated Field and indistinguishable from other Formula components.
 
When the Calculated Field is included on a Form, and configured to “Show Details” (as shown in Figure 9), each individual component of the Formula will be displayed as any prior Formula References have been flattened into the Calculated Field.
 

Formula Reference

Figure 9a: Formula showing the Formula Reference

Formula Reference

Figure 9b: Calculated Field with a flattened Formula Reference

Formula Reference

Figure 9c: Calculated Field as displayed on a Form without any Formula References any longer

 

Advanced Visibility Rules

If you need to create an advanced visibility rule (see Forms), you first must start by defining a formula with a Boolean result. That formula can then be referenced to create your Advanced Visibility Rule (Fig. 10). Please note that if you change the Formula being referenced after it has been utilized for Advanced Conditional Visibility, you must remove and re-select the formula for the new conditions to take effect.
 

Advanced Visibility Rules

Figure 10: Advanced Visibility Rules

 

Video Guides

Build A Formula

This video guide will demonstrate how to build a formula through our visual design surface. Use PowerClerk to calculate key data fields that streamline your administrator workflow.

 

Automation with Formulas in Action Rules

The following video will present you the concept of triggering Automations based on a Formula’s result:

 

Formulas and Advanced Visibility Rules

To control the Conditional Visibility of From fields you can use Formula logic to adapt the display of your Forms to a vast variety of business scenarios:

 

Calculated Fields

Display the results of Formula calculations on Forms, Communications, Document Templates, and Reports. This video demonstrates how to use Calculated Fields within your program:

 
A full list of all Video Guides can also be found here.

FAQs

Have additional questions? Contact us to nominate your FAQ and help others find answers to your own questions concerning this feature.

Create A Support Ticket

Not finding your answer here?  Submit a question to our support team at the PowerClerk Ticket System and leverage the PowerClerk team’s expertise.