The WrapSignOff widget replaces conventional paper signatures by allowing authenticated users to approve completed work. Only a person with appropriate authority can sign the wrap. Signatures can lock tabs or freeze the whole wrap forever.
A wrap can be digitally 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 users 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 is considered an exceptional event, and unsigning 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. Using enabling cells, you can control exactly in what order the wrap needs to be signed off, and by whom.
When someone signs off a processing step, you may lock the corresponding tabs in the wrap for further updates. When the whole unit of work is finally signed off, you can freeze the entire Wrap to prevent any further editing, including automatic updates to WrapLinks, WrapLinkLists, and WrapLinkAggregates. The only fields that are always unlocked are signature fields, which have their own controlling cells and cannot be locked by any locking mechanism in ExcelWraps.
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 forever.
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 can log every time Wraps are signed or unsigned. The same help page also has an example of how to use MyWraps to list all the wraps that a particular user has signed off.
If you use roles to grant permissions to sign, you can combine the roles in various ways.
One group of users can sign.
Stored in the cell as
=@Signoff(E39,"C-TechSC+H-Supervisor+H-Manager","C-TechSC",TRUE,TRUE,TRUE,"","","")
Three groups of people can sign.
Stored in the cell as
=@Signoff(E39,"C-TechSC,H-Supervisor,H-Manager","C-TechSC",TRUE,TRUE,TRUE,"","","")
Two groups of users can sign:
Stored in the cell as
=@Signoff(E39,"C-TechSC+H-Supervisor,H-Manager","C-TechSC",TRUE,TRUE,TRUE,"","","")
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.
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.
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. In the user interface, an apostrophe before the signature indicated that the enabling logic has changed since this signature was made. Learn more about the user interface for a wrap in the Wrap user interface help page.
If you change the enabling logic, perhaps one or more already signed wraps 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 and new wraps 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. In the code for the enabling cell, you can then use an =if() statement to select different enabling logic based on the wrap creation date. Learn more on the help page about Making changes to your wraps.
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.
A wrap 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 any later steps unavailable. The Lock Sheets option allows you to lock one or more worksheets from editing when the corresponding processing step is signed off.
A frozen wrap is locked for modification by the user, but you must also prevent the wrap from using any other modified data. Designing a wrap that eventually gets frozen requires some special considerations.
Excel functions like TODAY(), NOW(), and RAND() continue to return new values even if the wrap is frozen. To avoid misunderstandings, you should always place functions like these inside an IF clause similar to this:
=IF(freeze_signature_timestamp = 0, TODAY(), freeze_signature_timestamp)
where freeze_signature_timestamp is the name of a Holder cell returning the timestamp for the signature. The IF clause above returns the current date as long as the wrap is live. After the wrap has been frozen, the freeze_signature_timestamp has been set and the IF clause always returns the date when the wrap was signed off.
=IF(freeze_signature_timestamp = 0, RAND(), 1)
The IF clause above returns a random number as long as the wrap is live. After the wrap has been frozen, the IF clause always returns 1.
When saving KPI:s automatically, it is possible to use the AdminFrozen column in WrapStack to freeze a wrap when its period has ended. It is not possible to test for this state from within the wrap. To avoid confusion, we recommend that you avoid using functions like TODAY(), NOW(), or RAND() in wraps that may become AdminFrozen.
The actual workflow during a freeze signature is:
A Wrap may contain a progress indicator, e.g. to show when work is 100% completed. Since a freeze signature usually marks the natural end of a unit of work, it is likely that you will use the signed() function on the freeze signature cell to determine when work for the wrap is 100% complete. As you can see from the workflow above, one final recalculation is always performed – after the signature has been made, but before the Wrap is actually frozen. This enables the formula in your progress indicator cell to test for the freeze signature and return a progress status of 100% before the wrap is saved in its frozen state.
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 permission to make the signature, you may leave this box unchecked. In this case, the user has to type in their username.
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.
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.
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.
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.
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.
The information you want the holder cell to return for the signature event.
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.
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:
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.
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:
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:
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 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. The signature will freeze the wrap. 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,"","","")
=@SIGNOFF(cell_name, "sign_users", "unsign_users", freeze, dropdown, password_required, "email_enabled", "email_signed", "email_unsigned", xsheets(<locked_sheets>))
Specify the name of the enabling cell that returns TRUE when the signature can be added.
Enter the user selection logic (read more below) that designates the users that can sign the wrap.
Enter the user selection logic (read more below) that designates the users that can unsign a previously signed wrap. If you don’t provide any unsign users, the wraps cannot be unsigned.
When the signature is made, and this is TRUE, the wrap is frozen and can no longer be modified. If FALSE, the signature just confirms that an important step has been completed, but the wrap is still live. A wrap can have several signatures and one or more of these may freeze the wrap.
Signature fields are unaffected by all locking mechanisms in ExcelWraps. This allows a user with the required permissions to revoke a freeze signature, if necessary.
If set to TRUE, all the usernames with permission to make the signature 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.
For the wrap to be signed with this set to TRUE, the user must enter the password for the selected username. If FALSE, the wrap 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.
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.
An e-mail is sent to users that fulfill this user selection logic (read more below) when the wrap is signed. If this parameter is empty or unset, no e-mail is sent for this event.
An e-mail is sent to users that fulfill this user selection logic (read more below) when the wrap is unsigned. If this parameter is empty or unset, no e-mail is sent for this event.
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)
The signoff() function allows you to grant sign or unsign authority using user roles. A user is considered qualified if he or she has all the roles in at least one of the groups.
Example
"Supervisor+Administrator,Manager+Administrator"
This rule selects all users that are
If you want to grant only particular users the authority to sign or unsign, you can enter a user name instead of role names into the function. Precede the username with “U:”
=Signoff(timesheet,"U:JennyK","U:LisaM", ...
In the example above, wraps based on this template can only be signed by the user JennyK, and unsigned by the user LisaM, without regard to what roles they have.
If you don’t want to grant user permissions at design time, you can use a dynamic sign or unsign role in a read-only cell in the wrap to define the rules for each signature dynamically when the wrap is created. A read-only cell cannot be modified after the wrap has been saved once, so the rules are still tamper-proof.
In most cases, you would use a function in the read-only cell to define the sign or unsign authority for the wrap, e.g.
IF(budget<1000000,"Supervisor+Administrator,Manager+Administrator","U:JennyK")
In the example above, normal role-based authority is used for smaller budgets. If the budget is 1,000,000 or more, however, only JennyK can make the signature.
In the signoff() function, you point to the read-only cell using its cell name. prefixed by “C:”.
=Signoff(timesheet,"C:signroles","C:unsignroles", ...
In the example above, the cell signroles defines who can sign, and the cell unsignroles defines who can unsign. Before the wrap is initially saved, these cells must be primed with the actual role names or a user name required for this specific wrap, according to the rules above, typically using a formula similar to the one above.
You can test the status of a WrapSignOff widget using the signed() function elsewhere in the wrap.
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.
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.
=@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")