Making changes to your wraps

When a spreadsheet is converted into a wrap, a database table is created to store the wrap instances. If you later modify the source spreadsheet – the wrap definition – the database may also need to be updated. This help page documents what you can and cannot do to a wrap after it has gone live.

ExcelWraps allows you to make changes very easily. Simply make the required changes to the source Excel file and “rewrap” – convert the spreadsheet again. As all wrap instance data is stored in the database using the Excel cell name, you need to proceed with caution to ensure that all old instances can be loaded by any newer version of the wrap.

As a rule of thumb, you can always add new fields, widgets, wrap functions or signatures to a wrap. If the new content doesn’t have a default value, it will just be empty or zero for existing instances. Removing content is far more complicated and something you really should avoid. Old wraps may have been signed and then it’s not really proper to change their content in any way, not even to remove data you later consider redundant.

Terminology

To understand ExcelWraps, you need to be fluent in our terminology:

  • A wrap may be more accurately referred to as a wrap definition. It defines what is in the client web app and what goes into the server wrapsite, including cell names and all calculations. Each wrap definition has an installation date in the wrapsite.
  • When a wrap is saved in the wrapsite database, this creates a new wrap instance identified by its unique key combination. A wrap instance contains a collection of cell names, each with an associated cell value. This collection is held in the database. Each wrap instance has its own last-modified date.
  • When you reload a wrap instance, we read data from the database and insert it into the wrap definition so that you can see the information nicely formatted in a web browser.
  • You can ask the server to Recalculate Instances. This is a special procedure that updates the database values by recalculating each instance in accordance with the most recent version of the wrap definition.

Basic checklist

Renaming cells

  • The names for input cells can never be changed. These names are directly mapped to the database. All the existing values from the database need to know their place in the wrap.
  • Calculated cells may be renamed in the wrap definition spreadsheet. Reloading an existing wrap instance will update its names in the database.

Changing formulas

  • Formulas in calculated cells may be changed. Reloading an existing wrap instance will update its calculated values in the database.

Adding cells

  • You may define additional input cells. All input cells must be named. If an input cell contains a default value, this will be inserted into existing wrap instances when you reload them.
  • New calculated cells can be introduced. Reloading an existing wrap instance will calculate the new value for the instance.

Previous signatures

  • Changing a wrap definition will compromise the signature integrity of existing wrap instances. If the signature date is older than the wrap definition, someone must have signed the wrap instance before the wrap definition was updated. We show this by prefixing the signature with an apostrophe (‘).
    Screnshot of a signature that was made before the most recent update of the wrap and is prefixed by an apostrophe
  • Your changes may break the enabling logic for signatures. If the signature date is older than the wrap definition, someone must have signed the wrap instance before the wrap definition was updated. What if the new signature enabling logic wouldn’t have enabled the signature box? In this case, it wouldn’t have been possible to sign the wrap instance with the new wrap definition.  This situation is indicated by a red outline around the signature.
    Screnshot of a signature that was made invalid by the most recent update of the wrap and is surrounded by a red border

Deleting things

You can delete anything in a wrap that is not saved in the database. This includes all content on hidden worksheets. You can remove any widget that is not used for input, like Hide Rows/Sheets and Responsive Blocks. Feel free to delete any static text like headings, captions or help text. All static images can be removed.

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. One reason for this is that one or more instances may already have been signed and then it’s not really proper to change their content in any way, not even to remove data you later consider redundant for the signature. Instead of deleting fields that you will no longer use, you can often just hide them. Otherwise, our client services team may be able to help you if you feel a wrap needs cleaning up.

Recalculate instances

From Wrap Instance view in the site dashboard, you may select the action Recalculate all Marked Instances to bring all existing instances up to date with the latest version of the wrap.

A lock is placed on the wrap whilst Recalculate Instances is running and this will prevent any users from using the wrap. After Recalculate Instances has finished successfully, the Wrap is unlocked and available for use again. For this reason, Recalculate Instances is best run out of normal hours to prevent disruption.

The following principles guide the recalculate instances (recalc) process:

  • A wrap instance is marked for recalculation when the wrap definition installation date is greater than the wrap instance last modified date.
  • During import, the Recalculate instances feature may be switched off by selecting the Unmark for Recalculate instances check box. This changes the last modified date of all instances to equal the wrap definition installation date, thus removing the mark for recalculation.
  • In the wrap instances listing, individual wrap instances may be selected and marked for recalculation.
  • In the wrap instance listing view, the action Recalculation all marked instances is available if one or more wrap instances have been marked for recalculation.

The table below shows the common recalculation procedures when updating a wrap definition.

Import Option Mark for Recalculation Action Action
Recalc all Instances None (thus all marked for recalculation) Not required Recalculate all marked instances
Recalc Live Instances Unmark for Recalc Select all with ‘Live’ filter active, then ‘Mark selected instances for Recalculation’ Recalculate all marked instances
Recalc Selected Instances Unmark for Recalc ‘Mark selected instances for Recalculation’ Recalculate all marked instances

Live and Frozen Wrap Instance Behaviour

The table below shows the behaviour of a live and frozen wrap when loaded and when subject to Recalculate Instances (recalc).

State:    Live       Frozen       Live       Frozen   
Action: Load Load Recalc Recalc
WrapLink Update Y N Y Y
Use latest wrap definition Y Y Y Y
Save updated values to Database Y N Y Y

The latest version of the wrap definition is used in both cases and old versions of the wrap definition are not retained.

The load and recalc behaviour of live wrap instances is the same and we can be relatively more relaxed about changes to live data. For frozen wrap instances, however, the ‘load’ and ‘recalc’ behaviour is very different with respect to wraplinks and the values stored in the database.

  • When loading a frozen wrap instance, wraplinks do not update, and stored values in the database are never updated.
  • When recalculating instances of a frozen wrap instance, wraplinks are updated as are values stored in the database.

Users need to understand that recalculating instances is more powerful (and therefore more dangerous) than simply loading a wrap. Use ‘Recalculate Instances’ on frozen instances with caution and be fully aware of the impact any changes will make.

Wrap load behaviour is aimed at making as few changes as possible to the wrap instance, making a best attempt to preserve signature integrity. Calculated values will change on the screen in line with the new wrap definition but these changes are not committed to the data values stored in the database.

Wrap recalculation allows you to make changes to values stored in the database and is aimed at bringing old data in line with new calculation methods.

Signature integrity issues

What happens to signatures collected on a previous version of the wrap? How do we overcome the dilemma that a wrap instance was signed with an old version of the wrap definition and not the latest one?

There are two ways to handle this:

1) Change the Wrap Name to Maintain Signature Integrity: Some people who place great emphasis on signature integrity would always select this approach to avoid complications from Recalculating instances ever arising altogether. Simply change the source Excel file name, make your changes and create a new wrap with the new name to take over from the previous version. MyWraps reports allow wrap appending, so that data from many versions of the wrap can be seen in the same table/chart.

2) Keep Wrap Name but Compromise Signature Integrity: Some people place a great emphasis on reusing existing data and updating as the wrap calculation evolves. This is particularly true when wraps are first being developed and it allows you to get started immediately with a ‘quick and dirty’ approach and evolve over time to a slick and elegant approach. To achieve this keep the same source Excel file name, make the changes in Excel and install the new version of the wrap over the old. Administrators use the Recalculate Instances button to bring all existing instances up to date with the new Wrap definition. The wrap name has not changed and all the instances made with the previous version can be read by the new version and you continue to use MyWraps reports to view the results of all instances. However, signatures made on instances of a previous version of the wrap may have been signed with possibly different calculated values or the appearance of the wrap may have changed. We would recommend keeping a change log so that the implications of the change over time can be assessed.

This presents us with a signature integrity issue. Any signatures older than the current wrap installation date will be prefixed by an ‘ character to signify the signature integrity issue. If the signature enabling cell logic also has changed, the instance may show up as signed even though the signature is not enabled (which presents an interesting conundrum).

Generally, we recommend this approach, but ask users to be mindful of the consequences of the signature integrity issue. It is a good idea to maintain a version history of the wrap to fully understand any signature integrity issues. A version history control can be created by another wrap in which the relevant roles sign to accept the change introduced, thereby maintaining signature integrity.

Screenshot of a normal signature field with a signature in it

The above is an example of a valid signature.

Screnshot of a signature that was made before the most recent update of the wrap and is prefixed by an apostrophe

The above signature was made before the latest version of the wrap was installed.

Screnshot of a signature that was made invalid by the most recent update of the wrap and is surrounded by a red border

Here, a signature was made before the latest version of the wrap was installed and the new logic for the enabling cell would prevent the instance from being signed.

You can use simple logic for the signature enabling cell that ensures that both old instances and new instances always satisfy the appropriate cell enabling logic. The easiest way to do this is to use a Holder widget containing the creation date for the Wrap instance. In the enabling cell, you can then use an =if() statement to use different enabling logic based on the creation date for the Wrap instance.

Additional notes

Redefine Cell Formulas to Update Wraps

Imagine if your labour rate changes after a particular date. In a wrap, you may update the calculation method using an Excel IF() formula. In these circumstances, you would install the wrap selecting the Unmark for Recalculate instances check box. From the wrap instances listing, select only those wrap instances which will be affected by the change and recalculate only them (as these are the only wraps that will change). If these controls were not in place and there were many thousands of wrap instances, recalculating all the instances would be very time consuming – and in fact unnecessary. This approach should only be undertaken with care.

Changing Input Cells to dropdowns or WrapLinkLists

Any input cell (including any with data validation drop-down selections) can become a dropdown menu or WrapLinkList in a new version of a Wrap. Data collected with the former input cell definition will be displayed in the dropdown or WrapLinkList with the Wrap definition, even if it is not included in the current list of options.

Example: A train operating company has three depots called A, B, and C. After some time, depot B is closed permanently, and the B choice is therefore removed from the dropdown for the “carriage belongs to” property. If you open the Wrap instances for carriages registered on the B depot, they will still have this field set to B – and can still be saved with that setting. However, if a new selection is made, B is no longer one of the choices in the list.

If the data held in an old wrap instance is not available in the current Wrap definition, it will be highlighted with a red border around the dropdown.