Approval Formula
Using Formulas With Approval Fields
If your sheet has an approval flow, you can apply the formulas below to return certain values related to the approval process.
Currently, we support two types of approval formulas:
1. Related to the whole approval flow
| Formula | Description |
|---|---|
| APPROVAL.COUNT() | Returns the number of approval steps |
| APPROVAL.STATUS() | Returns approval status.
N: New P: Processing REJ: Rejected F: Finish |
| APPROVAL.SUBMITDATE([true]) | Returns the date and time an approval process is started. Supported in Date Fields. The [true] argument is optional, the time value will be displayed using UTC+0 timezone when applied. Otherwise, the time value will display using the account timezone. (Front-end recalculation will be based on browser timezone; Back-end recalculation will be based on company timezone) |
| APPROVAL.SUBMITTER() | Returns the email address of the user who starts the approval process. Supported in Select User Fields. |
| APPROVAL.SUBMITTERNAME() | Returns the name of the user who starts the approval process. |
| APPROVAL.FINISHDATE([true]) | Returns the date and time an approval process ends. An approval ends when all of the approvers approve or when one of them rejects. Supported in Date Fields. The [true] argument is optional, the time value will be displayed using UTC+0 timezone when applied. Otherwise, the time value will be displayed using the account timezone. (Front-end recalculation will be based on browser timezone; Back-end recalculation will be based on company timezone) |
2. Related to a certain step of approval flow
| Formula | Description |
|---|---|
| APPROVAL.CURRENTSTEPINDEX | Returns the index value representing the next step in the approval process.
Index 0 means the approval process has not yet been started. Index 1 means the approval process has been started but no approver has approved yet. Whenever an approver approves, 1 will be added to the index. When the approval process ends (all approve/ 1 rejects/ canceled), the index returns to 0. |
| APPROVAL.STEP([stepIndex]) | Uses index value to point to a certain step in the approval process. The [stepIndex] argument is optional, with its default argument being APPROVAL.CURRENTSTEPINDEX.
APPROVAL.STEP(-1) : Last step APPROVAL.STEP() : Next step. Equivalent to APPROVAL.STEP(APPROVAL.CURRENTSTEPINDEX)) APPROVAL.STEP(0) : Incorrect usage APPROVAL.STEP(1) : First step APPROVAL.STEP(2) : Second step |
| APPROVAL.STEP([stepIndex]).NAME() | Returns the name of this step. |
| APPROVAL.STEP([stepIndex]).STATUS() | Returns the status of this step.
N: New F: Finish |
| APPROVAL.STEP([stepIndex]).ISMULTI() | Returns True if this step has multiple approvers. |
| APPROVAL.STEP([stepIndex]).THRESHOLD() | Returns the threshold number of this step, or 1 if this step only has a single approver or no threshold was set. |
| APPROVAL.STEP([stepIndex]).USERS() | Returns all approvers.
E.g., Jessica Jones|Nick Fury|Steve Rogers Supported in Select Multiple Users Fields. |
| APPROVAL.STEP([stepIndex]).ACTIONDATE([email],[true]) |
Returns the approve or reject time of a specific approval step. The [true] argument is optional, the time value will be displayed using UTC+0 timezone when applied. Otherwise, the time value will be displayed using the account timezone. (Front-end recalculation will be based on browser timezone; Back-end recalculation will be based on company timezone) This formula needs to be applied to a date field. Similar to the NOWTZ() and TODAYTZ() formula, the system will use the browser time zone when recalculating on the front end (entering the record’s editing mode) and use the time zone set in company settings when recalculating on the backend (Recalculations executed in the design mode, from related sheets, or by action buttons) [email] is an optional argument. It can be an email string or a select user field. This argument allows you to get a specific user’s approval or a reject time in an approval step with multiple users. One approver in an approval step (No [email] argument needed) Returns the approver’s approval or reject date and time. Multiple approvers in an approval step: If the [email] argument is not applied, the system will return the date and time when one approver has rejected this approval or when the step is completely approved (the number of approved approvers meets the approval threshold settings). If the [email] argument is applied, the system will return the respective user’s approval or reject time. (Note: The [email] argument in the formula is only applicable for approval steps created after 2021/07/13) |
| APPROVAL.STEP([stepIndex]).UNSIGNEDUSERS() | Returns the approvers who haven't approved in this step. Supported in Select Multiple Users Fields. |
| APPROVAL.STEP([stepIndex]).SIGNEDUSERS() | Returns the approvers who have already approved this step.
E.g., Jessica Jones|Nick Fury|Steve Rogers Supported in Select Multiple Users Fields. |
| APPROVAL.STEP([stepIndex]).SIGNEDCOUNT() | Returns the number of the approvers who have already approved this step. |
| APPROVAL.STEP([stepIndex]).SIG([email]) | Returns the digital signature of the approver in this step. Please note that the [email] parameter of this formula should be enclosed in " ".
E.g., base64 image URL. Supported in Upload Image Fields. The [email] argument can be an email address or a Select User Field. Single approver: The [email] argument is optional. Returns the digital signature of the approver in this step.
Multiple approvers: The [email] argument is required. Returns the digital signature of a certain approver. |
| APPROVAL.STEP([stepIndex]).SIGIMG([email], [width], [height]) | Returns the digital signature of the approver in this step in a predetermined image size. The [width] and [height] arguments are optional, with default values being 300px x 150px. This formula can be applied to field descriptions with BBCode [formula]. Please note that the [email] parameter of this formula should be enclosed in " ". |
| APPROVAL.STEP([stepIndex]).COMMENT([email]) | Returns comments of the approver(s), or null if there is no comment.
Single approver: The [email] argument is optional. Returns the comment of the approver. Multiple approvers: The [email] argument is required. Returns the comment of a certain approver. |
| APPROVAL.STEP([stepIndex]).COMMENTDATE([email], [true]) | Returns the date and time left comments of the approver(s). The [true] argument is optional, the time value will be displayed using UTC+0 timezone when applied. Otherwise, the time value will be displayed using the account timezone. (Front-end recalculation will be based on browser timezone; Back-end recalculation will be based on company timezone)
Single approver: The [email] argument is optional. Returns the date and time the comment was left by the approver. Multiple approvers: The [email] argument is required. Returns the date and time left the comment was left by a certain approver. |
Formula Recalculation
A calculation based on the formula you have entered will be done while you are first entering data into your database. This value is saved when you first save your entry.
By default, the values that are already saved in your database will not change when you change the formula while designing your sheet. This is because, in most cases, a previous calculation is still valid for older entries and should not be overwritten when you have updated the formula. A practical example would be calculating taxes after a tax hike; all previous entries would still need to reflect the older tax rate.
In some cases, you may need to recalculate a formula on all previous entries. To do so, you can choose to apply the formula change to all saved records, or, if you have modified more than one formula, to apply all formulas on this sheet to all saved records.
If you frequently change a formula or a variable that is used in a formula, you also have the option to add a script that will recalculate your formula every day.
Timezone issue in approval formulas.
Please note that if your form has already started or completed the approval process, and you subsequently add a field using an approval formula related to return time on a form that already contains date values followed by selecting the option 'Display Using Browser’s Time Zone,' this may lead to the displayed time being adjusted based on your browser's timezone. Notable examples of approval formulas pertaining to time include APPROVAL.SUBMITDATE([true]) or APPROVAL.STEP([stepIndex]).ACTIONDATE([email],[true]).
Let's consider a scenario where you have already approved a form on August 1, 2023, at 12:00 PM. At that point, the approval time field was not set to 'Display Using Browser’s Time Zone,' and the time was recorded according to the 'company timezone.' Now, if you subsequently add a 'new approval time' field and enable 'Display Using Browser’s Time Zone,' the approval formula may initially return the value based on the 'company timezone' and then add the offset defined by 'Display Using Browser’s Time Zone' (e.g., UTC+8). This could result in the 'new approval time' field displaying a time that is 8 hours ahead, such as 8:00 PM on August 1, 2023.
Hence, if you encounter timezone disparities in the approval time, as explained previously, please deselect the 'Display Using Browser’s Time Zone' option for that field. This action will ensure that the form calculates the time based on the 'company timezone'.
Please be reminded that when you choose to select or deselect the 'Display Using Browser’s Time Zone' option, the system will display a warning indicating that your field's date data will be converted. Therefore, we recommend paying special attention to this setting and avoiding toggling it for fields that already contain date values.
