The WrapSignOff widget

The WrapSignOff widget replaces conventional paper signatures by allowing authenticated users to approve completed work. It integrates with user roles so that only a person with appropriate authority can sign the wrap. Signatures can lock tabs or freeze the whole wrap instance forever.

A wrap instance can be signed off, indicating that the corresponding unit of work has been verified as complete. Users can only add their own signature as determined by their login. To enter their signature, users must have the required authority. Each signoff widget designates the user roles for which it is enabled. Once a signature has been made, the same unit of work cannot be signed again.

As the developer, you may provide an unsign authority for each signature. This allows authorized users to unsign units of work that have previously been signed. It’s not common to unsign wraps but without this possibility, making a signature can be a bit scary – it’s always nice to be able to go back and fix things. Unsigning a wrap instance is considered an exceptional event, and unsigning therefore often requires a higher authority than the signature itself.

There can be more than one signature in a wrap, e.g. one for each step in a process. A signature cannot be added until a special enabling cell becomes true, indicating that all the related work is complete and that all the required documentation has been provided.

When someone signs off a processing step, you may lock one or more tabs in the wrap instance for further updates. When the whole wrap instance is finally signed off, you can freeze the entire wrap to prevent any further editing or even automatic updates to WrapLinks, WrapLinkLists and WrapLinkAggregates. A freeze signature inactivates a wrap instance to preserve the state it was in when it was signed off.

Example: A train arrives at the depot for inspection. It has different checklists for mechanical, electrical, and hydraulic components. One worker with each competence signs off the corresponding checklist, thereby locking the checklist from further changes.  When all work is complete, a supervisor signs off the entire carriage, which freezes the inspection Wrap instance forever.

Screenshot of the dialog box that is used when a wrap is signed off

The WrapSignOff widget is a visual guide that helps you insert a signoff() function into the current cell.

You can test the status of a signature using the signed() function elsewhere in the wrap.

The Audit Trail

The Audit Trail can log every time Wrap instances are signed or unsigned.

Insert a WrapSignOff widget

  1. Select the cell where you want the signature field to appear.
  2. Switch to the WrapCreator ribbon and click on Insert Widget.
  3. Select the WrapSignOff widget on the Widget tab in the task pane.
  4. Enter the required parameters and press Apply.
  5. Verify that a =@signoff() function has been inserted into the cell.

Parameters

Screenshot of a SignOff widget that does not freeze the wrap

Cell Name

You must always name all input fields. It adds clarity to the spreadsheet but more importantly names the database column where each input field is saved.

Enabling cell

Usually, a wrap cannot be signed off until it fulfills certain criteria. This is controlled by a special enabling cell that usually contains a formula that unlocks the signature widget by returning TRUE when the wrap can be signed. To select an enabling cell for the signoff widget, click on the small pen, then select the enabling cell.

Use intelligent enabling logic

You may have to change the rules for when a signoff widget is enabled after a wrap definition has gone live. Perhaps you add a step to a checklist that must be ticked off before the wrap can be signed.

If you change the enabling logic, perhaps one or more already signed wrap instances would not have been possible to sign because the signature field wouldn’t have been enabled. ExcelWraps detects this and signals these “impossible” signatures with a red border around the signature field.

To avoid this, we recommend that you use simple logic for each enabling cell to ensure that both old instances and new instances always satisfy the applicable cell enabling logic. The easiest way to do this is to add a hidden Holder widget that contains the creation date for the wrap instance. In the code for the enabling cell, you can then use an =if() statement to select different enabling logic based on the wrap instance creation date. Learn more on the help page about Making changes to your wraps.

Sign and Unsign roles

Your wrap may require the user to have certain combinations of roles to enable signing. For the Sign Role, select the combination of roles that a user must have to be able to sign. If it is possible to revoke a previous signature, specify the required user authority for the Unsign role.

For each event, select the first role that users must have. If additional roles are required for a user to be eligible, add them to the same group. A user must have all the roles in the group to qualify. In the example above, a user must be both T-Service and H-Manager to unsign the wrap.

To add another combination of roles that also make users eligible, click on New Group and add all the rules to the group that a user must alternatively have. A user must have all the roles in at least one of the groups to qualify.

If you request a Username Dropdown below, it will contain all the users that have all the roles in at least one of the groups of roles that you have defined.

Freeze

A wrap instance is usually frozen when it is signed off and there is no more work pending for it. Freezing the wrap means that it cannot be edited, and that WrapLinks, WrapLinkLists and WrapLinkAggregates no longer are updated automatically. When a freeze signature is initiated, the wrap warns the user that signing the wrap will lead to it being frozen.

It is common to sign off intermediate processing steps before the entire wrap is signed off. A cleaner can sign off one part of a wrap, and an electrician another part. Intermediate signatures must not freeze the wrap since this makes its later steps inevitable. The Lock Sheets option allows you to lock one or more worksheets from editing when the corresponding processing step is signed off.

Username Dropdown

Tick the Username Dropdown check box if you want to automatically generate a dropdown list with all the users that are eligible to sign or unsign. If too many users have a qualifying combination of roles, you may leave this box unchecked. In this case, the user has to type in the username.

Password Required

Some signatures don’t require user authentication. In this case, you may untick the Password Required check box. A signature will be added for the selected user.

E-mail Alerts

Screenshot of the Email Alerts group of settings for the SignOff widget

You can send alerts when a signature field is enabled (the enabling cell returns TRUE) and when a wrap is signed or unsigned. Add user roles into each box. All users that have all the roles in at least one of the groups will receive an e-mail alert for the event.

Lock Sheets

Screenshot of the Lock Sheets setting for the SignOff widget

When a process step is signed off, you may want to lock the corresponding tab of the wrap from further changes. In the sign off widget, tick the tabs that you want to lock when a certain signature is added.

Locked tabs cannot be edited, but aren’t frozen. WrapLinks, WrapLinkLists and WrapLinkAggregates are still updated automatically also on locked tabs.

Holder cell

While you test the wrap in Excel, you can provide a test value in a special holder cell to verify that other formulas in the wrap return the correct results. When you upload the wrap, live data automatically replaces the test value in the holder cell.

For a WrapSignOff widget, the holder cell can be used to save information about the signature event, e.g. when the signature was made. If you need to retrieve more than one value you need to add additional holder cells using the =@HOLDER() function, see below.

Location

If you enable a holder cell for the widget, the cell immediately to the right of the WrapLink widget is chosen by default. You can change the location of the holder cell by clicking on the “pen” symbol and then selecting a different cell as the holder cell.

Qualifier

The information you want the holder cell to return for the signature event.

  • oadate – returns a timestamp for the signature in the OADate format, ie. the way Excel stores date and time.
  • username – the name of the user that signed.
  • nickname – the nickname (initials) of the user that signed.
  • firstname – the given name or forename of the user that signed.
  • lastname – the surname or family name of the user that signed.
  • company – the company name of the user that signed.
  • location – the location of the user that signed.
  • workgroup – the workgroup of the user that signed.

Hidden

Holder cells sometimes contain information that you don’t want to show to the user of the wrap. In this case, tick this box to make the contents of the holder cell invisible in the wrap.

Dummy value

This is the value that the holder cell will contain during testing in Excel. When the wrap is running on the server, real values from the database are used, and the dummy value has no meaning. There are three possible types of values you can put here, depending on the situation:

  • If the server will return a number at runtime, the holder cell must contain a numeric value also during testing in Excel. Example: 100
  • If the server will return a text string, you must enter a text string here, and it must be delimited with double-quotes. Example: “JaneDoe”
  • If the server will return a timestamp, you must enter a timestamp in the OADate (or OADateTime) format used by OLE Automation, i.e. as a decimal number.

Special rules for timestamps

The OADate format is easy to handle for Excel developers because it is the same as an ordinary Excel serial number, i.e. the standard decimal number that Excel uses for timestamps internally.  The integer part counts the number of days and the decimal part tells the time of day. When you use an OADate in a holder cell, it must be formatted as a decimal number, not using a date or time format.

Read more about testing Wraps in Excel.

Remove a WrapSignOff widget

To delete a field, widget, wrap function or signature that has been stored at least once in the live production database is not a trivial task. The instructions below are only applicable during the initial development of a wrap, before it has ever been used in production. Read more about making changes to your wraps.

To remove a WrapSignOff widget:

  1. Ensure that the task pane is visible.
  2. Select the widget’s cell.
  3. The task pane should now show the WrapSignOff widget’s settings on the Cell tab. If it doesn’t, you may have selected the wrong cell, or already removed the widget by mistake.
  4. If the WrapSignOff widget designates a holder cell, right-click on this cell and select Clear contents.
  5. Note the location of the Enabling Cell.
  6. Locate the Remove button at the bottom of the widget settings and click on it.

If the enabling cell was used only to lock/unlock the signature field, you may now want to remove it. Before you do, you may want to make certain that it has no other function within the wrap. To remove the enabling cell:

  • Select the enabling cell and press the Del key on the keyboard, or right-click on the enabling cell and select Clear contents.

Function reference

Screenshot of a SignOff widget that freezes the wrap

Example

In this example, the wrap signoff form will appear when the cell H88 returns true. The signoff form will have a dropdown list with all eligible usernames that have the T-QA role. A user can sign the wrap instance by selecting a username in the list and entering the corresponding password. No role is assigned for unsigning the signature, which means that the signoff cannot be recalled to reopen the wrap instance. The signature will freeze the wrap instance. A password will be required. On signing the form, no email alert will be sent. This is what this signoff function call will look like:

=@Signoff(H88,"T-QA","",TRUE,TRUE,TRUE,"","","")

Format and parameters

=@SIGNOFF(cell_name, "sign_role", "unsign_role", freeze, dropdown, password_required, "email_enabled", "email_signed", "email_unsigned", xsheets(<locked_sheets>))

cell_name

Specify the name of the enabling cell that returns TRUE when the signature can be added.

“sign_role”

Enter the user selection logic (read more below) that designates the users that can sign the wrap instance.

“unsign_role”

Enter the user selection logic (read more below) that designates the users that can unsign a previously signed wrap instance. If you don’t provide an unsign role, the wrap instances cannot be unsigned.

freeze

When the signature is made, and this is TRUE, the wrap instance is frozen and can no longer be modified. If FALSE, the signature just confirms that an important step has been completed, but the wrap instance is still live. A wrap can have several signatures and one or more of these may freeze the wrap instance.

dropdown

If set to TRUE, all the usernames with the designated sign role will be presented in a dropdown to simplify the signature. When you have many eligible users, set this to FALSE to have users type in their username instead. A FALSE value could increase security slightly, since it may be harder to guess a valid username than to pick one from a list.

password_required

For the wrap instance to be signed with this set to TRUE, the user must enter the password for the selected username. If FALSE, the wrap instance can be signed without a password. If you use a dropdown for the user names, anyone will be able to sign just by selecting a random user on the list.

“email_enabled”

An e-mail is sent to users that fulfill this user selection logic (read more below) when the signature is enabled. If this parameter is empty or unset, no e-mail is sent for this event.

“email_signed”

An e-mail is sent to users that fulfill this user selection logic (read more below) when the wrap instance is signed. If this parameter is empty or unset, no e-mail is sent for this event.

“email_unsigned”

An e-mail is sent to users that fulfill this user selection logic (read more below) when the wrap instance is unsigned. If this parameter is empty or unset, no e-mail is sent for this event.

<locked_sheets>

If one or more tabs in the wrap are to be locked for editing when a certain signature is made, you include a list of these tabs wrapped in an xsheets function inside the signoff() function call for the signature.

Use the standard Excel format to refer to any cell in each worksheet that you wish to lock when the signature is made, e.g.

xsheets(Prep!A1,Work!A1)

User Selection Logic

The signoff() function allows you to select users by defining groups of user roles. A user is considered qualified if he or she has all the roles in at least one of the groups.

  • “+” is used to combine the roles of a group. A user must have all the roles in a group to qualify, so you can read the plus sign as “and”.
  • “,” is used between the role combination groups. A user only needs to have all the roles in one of the groups to qualify, so you can read the comma sign as “or”.

Example

"Supervisor+Administrator,Manager+Administrator"

This rule selects all users that are

  • either both Supervisors and Administrators
  • or both Managers and Administrators.

Testing if a signature was made

You can test the status of a WrapSignOff widget using the signed() function elsewhere in the wrap.

Screenshot of the signed function

In the example above, a signature needs to be made before the comment field below the signature is opened for input. When the signature has been made, the Signed() function in F5 returns TRUE to the Conditional Input widget in B5.

Additional holder cells

The standard holder cell provided by the widget only allows you to retrieve one value from the server related to the signoff, e.g. the company name. If you need to retrieve more values, e.g. firstname, lastname and oadate, you need to manually create additional holder cells by inserting the =@HOLDER() function into the corresponding cells.

Format

=@HOLDER(cell_name, dummy_value, hidden, holder_item)

cell_name points to the signature cell using its cell name.

dummy_value contains a value you want the cell to provide during testing in Excel – at runtime, real values from the server are used instead.

hidden if set to TRUE, makes the contents of the holder cell invisible in the wrap.

holder_item contains a key telling the server what it is you want to know – the possible values are listed in the description of the holder cell parameters above.

=@HOLDER(main_signature, "JaneDoe", TRUE, "username")