PowerClerk Support Center

Program Design
Automations
Questions to ask yourself
Locating the Automations feature
Creating an Automation
FAQs
Communication Templates
Questions to ask yourself
Locating the Communications feature
Creating Communication templates
Feature reference
FAQs
Deadlines
Questions to ask yourself
Locating the Deadlines feature
How to create Deadlines
Feature reference
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
Feature reference
FAQs
Roles
Questions to ask yourself
Locating the Roles feature
How to create and edit a Role
Feature reference
FAQs
Workflow
Questions to ask yourself
Locating the Workflow feature
How to create and edit the Workflow
Transitions
Changing a project's status
Feature reference
FAQs
Advanced Program Design
Channels
Locating the Channels feature
What are Channels?
Channels Checklist
3-Step Publishing Process
Channel Type A: Mark As Child
Channel Type B: Make Successor
Channel Type C and D: Create/Submit Related Project
Sending Signals
Document Templates
Questions to ask yourself
Locating the Document Templates feature
How to define a new Template
Feature reference
eSignatures
DocuSign template tags
Mapping eSignature tags
FAQs
Formulas and Calculated Fields
Questions to ask yourself
Locating the Formulas feature
How to create a Formula
Formula Data Dictionary
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 design an Incentive
Incentive Options
FAQs
Single Sign On (SSO)
Azure AD
Okta IDP Configuration
SP Configuration
PowerClerk API
Custom API IDs
FAQ
API Documentation for Developers
Application & Process Automation
Getting Started
Common Usage Scenarios
Using Custom IDs
API Method Reference
Code Samples
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
How to validate a Data Import
FAQs
Duplicate Check
Questions to ask yourself
Locating the Duplicate Check feature
How to use Duplicate Checks
FAQs
ePayments
Questions to ask yourself
Locating the ePayments History feature
How to add ePayments
FAQ
Import From V2
Questions to ask yourself
Locating the Import From V2 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
Questions to ask yourself
Locating the Program Info feature
How to edit the Program Info menu
Notification Banners
Billing 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
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
Web Adapter Factory
Questions to ask yourself
Locating the Web Adapter Factory
What are Web Adapters
Input and Output Fields
Connecting, Testing, and Enabling
Maintenance and Alterations
Web Adapter Message Format
FAQ
Program Reporting
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
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
FAQs
How to edit the Project Summary
Project Views
Questions to ask yourself
Locating the Project Views feature
How to edit Project Views
FAQs
Settings
My Account
Questions to ask yourself
Locating the My Account feature
How to use the My Account feature
Setting up Multi-Factor Authentication
MFA Recovery Guidelines
FAQs
FormSense
Questions to ask yourself
Locating the FormSense feature
How to use the FormSense feature
FAQs
Grant Access
Questions to ask yourself
Locating the Grant Access feature
How to Grant Access to users
FAQs
Integration Guides
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
Edit Forms - Tutorial #1
Edit Forms - Tutorial #2
Configuring Forms
FormSense
Build A Formula
Automation with Formulas in Action Rules
Formulas and Advanced Visibility Rules
Calculated Fields
Milestones
Project Summary
Roles and User Administration
Visualize Workflows
PowerClerk User Group Sessions (UGS)
PowerClerk Responsive Admin View

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?

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. Please note: Formulas are available as a premium feature within PowerClerk. Please contact us, if you would like to know more about Formulas.

 

Locating the Formulas feature

Figure 1: PROGRAM DESIGN >> Formulas

 

How to work with Formulas

The Formulas feature has been designed to enable math to be performed in Forms automatically by PowerClerk using references to Data Fields and constants. An intuitive drag-and-drop Formula editor is used to configure Formulas.

Formulas can perform numeric operations (addition, subtraction, multiplication, division) as well as 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) on non-numeric fields. A few examples have been provided in Figure 2 and Figure 3 below.

 

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 to use this Field in your Forms. In the example in Figure 4 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.

 

Calculated Field element in a Form

Figure 4: Calculated Field element in a Form

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

Formulas in Automations

Figure 5: 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
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)

 
 

Boolean 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
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

 
 

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

 
 

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 True/False data 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. “foo” + “bar” = “foobar”
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
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)

 
 

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

 
 

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.
 
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 6: 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.
 

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


 

Calculated Fields and Advanced Visibility Rules

One of the great advantages of Calculated Fields are that you can use them within your Forms without having to display the actual Field result on the Form any longer. In addition you can also reference Formulas to define Advanced Visibility Rules as shown in Figure 10:
 

Advanced Visibility Rules

Figure 10: Advanced Visibility Rules

 
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).

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

Q: What should I keep in mind when working with Formulas?
A: Please consider the following guidance when working with Formulas:
  • Formulas cannot reference another formula.
  • The Calculated Fields are dynamic. If any field included in the calculation changes, the Calculated Result field will update accordingly.
  • Calculated Fields cannot be read-only (they don’t need to be, as they are calculated based off other form fields).
Q: I am looking for a tutorial on how to get started with Formulas, can you help?
A: Please consider the following instructions as an exercise for your sandbox environment to familiarize yourself with PowerClerk's Formula functionality. With that said, let's get started!
  • STEP 1: In your sandbox environment create a form by clicking Program Design >> Forms >> New Form. Name this new form "Calculator". Add two Integer fields (labelled "Value A" and "Value B" below) and one Multiple Choice field (labelled "Operators") with the following choices as shown below: Adding Form Fields Save your new form by clicking on "Save".
  • STEP 2: Navigate now to the Formula menu by clicking on Program Design >> Formulas >> New Formula and name this new Formula "MyCalculator"
  • STEP 3: Drag each of the available operator fields onto the scratchpad as shown below: Adding Operator Fields
  • STEP 4: Drag the (Data Field Reference) data type into each of the blue operator options and assign Value A and Value B as shown below and finish the other operator blocks accordingly: Adding Data Field References
  • STEP 5a: For ADDITIONS: Drag a (IF __ THEN __ ELSE __ ) data type onto the scratch pad as well as one of the Boolean operators named ( Choice Data Field Reference == Choice ): Adding IF / THEN data type
    STEP 5b: Drag the data types into their positions as indicated below: Drag the operator block Please notice how the Boolean operator could only be placed into the orange position within the (IF __ THEN __ ELSE __ ) data type.
  • STEP 6a: Repeat these steps for SUBTRACTION, MULTIPLICATION, and DIVISION operator blocks - your scratchpad should look now similar to this screenshot below: Repeat steps for all operator blocks
    STEP 6b: Click now on each of the ( Choice Data Field Reference == Choice ) data type and assign as value "Operators" and choose the respective Choice option (i.e. "+ addition" in the screenshot below): Assign Operators to Boolean fields STEP 6c: Repeat this procedure for all other operators and your scratchpad should now look similar to this: Repeat steps for all operator blocks
  • STEP 7: Now drag the DIVISION formula block into the open blue position of the MULTIPLICATION formula block and repeat these steps until your scratchpad looks like this (Please note for the remaining blue position within the DIVISION formula block you assign data type (constant) and give it a value of 0): Repeat steps for all operator blocks
  • STEP 8: Save your formula.
  • STEP 9: In the Formula overview screen select your "MyCalculator" Formula and click on the Create Calculated Field button - you are now ready to use this Calculated Field within your original Form: Create Calculated Field
  • STEP 10: Click now on your original Form in Program Design >> Forms and click the "Edit Form" button: Edit original form
  • STEP 11: Drag a "Calculated Field Results" onto your form as shown below: Calculated Field Results
  • STEP 12: Select "MyCalculator" as the Formula and designate the visibility to be conditional on the Operators field not to be empty: Add Calculated Field Results field
  • STEP 13: Save your form now.
  • STEP 14: We now need to configure your form to display it on the HOME screen. Click on Program Design >> Forms and select your original form and click this time on "Configure Form". In the following screen select option "Use for New Project button" and hit "Save": Configuring the Form
  • CONGRATULATIONS! Click now on HOME >> New Calculator to enjoy your new calculator:
    Calculator Form
Q: What is the template tag syntax for a calculated field?
A: Calculated fields are listed in the Formulas > Calculated Fields section, but their template tags can be found in PowerClerk's Data Fields functionality as with any other data field. You can search for your Calculated Field and copy the accompanying template tag as exemplified below for {data: My Calculated Field}:  
Copying a Calculated Field's Template Tag
Copying a Calculated Field's Template Tag
 

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.