The WrapStack function

The WrapStack function allows advanced users to perform bulk operations on wraps. Users can insert new wraps, update existing wraps, or add fields to a wrap template. This can save considerable time when many new wraps need to be created regularly, e.g. for daily inspection, or when the same change needs to be done to many or all wraps.

When wraps are used to document work that is repeated regularly, users may have to enter the same data over and over again. WrapStack offers a simple structure for automation of such monotonous processes, where users can easily prefill or prepopulate the database with all the necessary checklists.

WrapStack can also be used to assign a new value to one or more fields in existing wraps, or add one or more new fields to all wraps.

You can manually create a static list of the Unique Keys that are to be inserted or updated in bulk. If the operation affects several wraps, it may be easier to create a MyWraps report that contains all the Unique Keys that you want to participate in the bulk operation. Driven by the Unique Keys in the MyWraps report, WrapStack automatically inserts new or updates existing target wraps with the field contents you have provided.

All WrapStack operations are serialized through a job queue that ensures that the database is never overloaded by background operations. To further reduce the load on the system, we recommend that you retain the default option to not recalculate wraps while they are being added or updated. In most cases, it is better to save this for later.

Example: At the beginning of a day, it is known what trains will arrive at a depot for inspection. A WrapStack job is used to create a new daily inspection checklist wrap for each carriage in each train that arrives at the depot. The correct checklist is used for each type of carriage in the train.

Text manipulation

When you work with Wrapstack, you may need to manage text strings efficiently. For this purpose, WrapCreator offers the powerful TextSplit and TextGlue functions.

The Audit Trail

When WrapStack enqueues a job, you can log the event in the Audit Trail.

Definitions

Control wrap

To use WrapStack, you build a “control” wrap that contains one or more WrapStack functions. The control wrap initiates the background jobs that perform the requested bulk operations.

When you convert this spreadsheet to a wrap, each WrapStack function appears as a Create Instances button. The user presses this button to initiate the bulk operation.

Instance data table

Each WrapStack function in the control wrap has an instance data table that describes the bulk operations it will perform. In the screenshot above, the instance data table is marked with yellow. The contents of the instance data table are described in detail below.

You can only create new wraps that don’t already exist

An error in your instance data table may result in an attempt to create an instance of the Target wrap that already exists. If you have selected Create instances as the Action for the WrapStack function, this will terminate the operation.

Example: a daily inspection wrap contains the inspection date as part of the Unique Key. When using WrapStack, you can insert today’s date automatically. All the new wraps will now have a unique key, since today’s date wouldn’t have been used for any previous daily inspection. You can use an autonumber field with an asterisk “*” as the autonumber to ensure that new wraps always have a unique serial number as part of their Unique Key.

You can only update wraps that already exist

An error in your instance data table may result in an attempt to update a Target wrap that doesn’t exist. If you have selected Update instances as the Action for the WrapStack function, this will terminate the operation.

Example: a daily inspection wrap contains the inspection date as part of the Unique Key. When using WrapStack with a Source MyWraps report that doesn’t contain a date, you must insert the appropriate date using the instance data table. You can then update all the existing wraps that have the Unique Keys provided by the Source MyWraps report and the date specified in the instance data table.

Avoiding problems with existing/non-existing wraps

The WrapStack function also has an Action to both Update existing instances and create new instances. If a Unique Key already exists, the wrap will be updated, if not it will be inserted as a new wrap. This ensures that all Unique Keys provided by the Source MyWraps report and the instance data table exist for the Target wrap.

However, it also increases the risk of errors, and you may want to consider running this as two separate jobs, one Create and one Update job, to have the Unique Keys properly validated as non-existing (for the Create job)) and existing (for the Update job).

Target wrap

The TargetWrap column is the left-most column in the instance data table, and contains the name of the wrap that is subject to the bulk operation defined by the row.

If this column is empty for a row in the instance data table, the row is ignored. This makes it possible for you to design an “intelligent” data table that manipulates a variable number of wraps, e.g. for trains that appear in different fixed combinations of carriages.

To create such an intelligent table entry, you can use function calls similar to this:

=if(<condition>,<target_wrap_name>, "")

• If <condition> is False, the TargetWrap column will be empty and no wraps will be affected for this row in the instance data table.
• If <condition> is True, a wrap name will appear in the TargetWrap column, and one or more Target wraps will be affected by the bulk operation.

The column heading for this column must be TargetWrap.

Source MyWraps

The SourceMyWraps column is the right-most column in the instance data table. It allows you to finely designate what wraps you want to manipulate for each row in the instance data table, by using a MyWraps report to provide their Unique Keys.

• If you don’t provide a MyWraps report as the source (by leaving the column empty), exactly one Target wrap is affected by this row in the instance data table. In this case, you must provide the entire Unique Key for the wrap using separate columns in the instance data table, see Providing static content below.
• If you provide a Source MyWraps report, the number of wraps in this report controls the number of wraps that are manipulated for the Target wrap.
  • If the source MyWraps report doesn’t return any data, the corresponding row in the instance data table is ignored.
  • If a component of the Unique Key for the Target, e.g. a unit identifier, is available also for the corresponding Source MyWraps instance, it is used also for the Target instance.
  • If a Unique Key for the Target is not available for the Source instance, you must provide it using a separate column in the instance data table, see Providing static content below.
  • If you are creating new wraps and the Source MyWraps report has more detail than the Target, you risk attempting to create duplicate instances of the Target wrap. If the Target has one wrap per city, you must ensure that you have only one wrap per city also in the Source MyWraps report, even if the source wrap contains one wrap per postal code. If the Target is weekly but the Source is daily, you must limit the MyWraps report to only one Source wrap per week.

Create the MyWraps link

The Source MyWraps report link is created just like any other report in MyWraps. You will spend most of the time tweaking the filters in the report link to ensure that it generates exactly the Unique Keys that you want to create in the Target Wrap. Remember that you can use filters also on columns that are not included in the report.

The output of a MyWraps link is partially controlled by the MaxRows, pageSize, and page parameters. WrapStack respects all three parameters. Read more in the MyWraps help for MaxRows.

If the source data becomes huge, it is easy to remove the columns in the source wrap that are redundant because they don’t exist in the target wrap. With the source wrap open in MyWraps, request the Intersect Names action to select only the relevant columns. This will insert the following into the MyWraps link:

...&Options.IntersectNames=<target-wrap>

Copy the MyWraps link to the instance data table

When the MyWraps report fulfills your requirements, you copy the second half of the MyWraps link (everything after /MyWraps/, in blue below) from the browser’s Address field to the SourceMyWraps column.

The SourceMyWraps report is only used to provide the Unique Keys for each wrap that is affected by the bulk operation. Any data you want to insert into the affected wraps must be provided as static content, see below.

The column heading for this column must be SourceMyWraps.

Static content for new or changed data

There are several types of static content for the new or updated wraps that you may want to provide in the instance data table:

• If there is no Source MyWraps report in a table row, exactly one instance of the Target wrap is affected, and you must provide all the Unique Keys for this new wrap as static content in the instance data table.
• If there is a Source MyWraps report, but it only provides a subset of the Unique Keys for the Target wrap, you must provide all the remaining components of the Target’s Unique Key as static data in the instance data table. An example would be to add today’s date as a Unique Key for a daily inspection checklist, or use an autonumber column with an asterisk “*” as the static value to ensure that each new wrap automatically gets a unique serial number.
• When you create new wraps, you can pre-fill additional fields in the new wraps, e.g. a depot identification, by adding one column per field to the instance data table. When you add or update fields for existing wraps, you use one column in the instance data table for each field that you want to add or update, and specify each field’s new content for each row.

A column in the instance data table that provides static content for a field in the wrap must use the field name as the column header. If you want to assign a value to a field called inspection_date in the wrap, the heading for this column in the instance data table must be inspection_date. WrapStack ignores columns in the instance data table that don’t have a field name in the heading. All field names must be in lowercase.

It is possible to manipulate two or more wraps in the Target for each instance in the Source MyWraps report. This usually happens when the Target wrap has additional Unique Keys that are provided in the instance data table. In the example below, the Target has a Projectcode key. For each instance in the Source Mywraps report, WrapStack will generate or update one instance of Target with Projectcode A, one with Projectcode B and one with Projectcode set to C. All other components of the Unique Key for the new instances of the Target are provided by the Source MyWraps report.

Null and empty values

If a cell evaluates to a null value, it will be ignored. No insert or update will be made for the corresponding field.

If a cell evaluates to an empty string, the corresponding cell in the new or updated wrap will be set to an empty string.

Checkboxes

You can assign static values also to checkboxes, but since these are stored as strings in the ExcelWraps database you cannot use True/False values in the Instance Data Table. When providing static content for a checkbox field,

• type the single word on into the cell for True – the box will be ticked.
• leave the cell completely empty for False – the box will be unticked.

Note that this only applies to checkboxes. Other Boolean fields in the database are set using the expected True/False values.

Reserved cell names

You can provide static content for the following reserved cell names:

• AdminFrozen – You can manually freeze wraps using WrapStack. Assign a True value in the AdminFrozen column to freeze the matching wrap. This is mainly used to freeze wraps for expired periods when saving KPIs automatically. You cannot use this option to unfreeze a frozen wrap.
• createdby – Designates the owner of the new wrap. In the ExcelWraps permissions model, there is a separate set of permissions for the owner of a wrap. This makes it possible for employees to have private access to their own sensitive information. The user of the Control Wrap must have the Create Wrap instances on behalf of others permission. Read more on the help page about Wrapgroups and Workgroups.
• WorkGroupTrust0-9 – The WorkgroupSelector function in WrapCreator allows an authorized user in one workgroup to temporarily trust another workgroup to share a wrap. You can add such trust also using WrapStack by entering the names of up to ten Workgroups that are trusted by the wrap in any of the WorkGroupTrust0 through WorkGroupTrust9 cells. You can use any of the 0-9 cells without regard to sequence. If you set WorkGroupTrust5 to WG_Installer, then WG_Installer will be a trusted workgroup for that wrap. You cannot remove workgroup trust using WrapStack – omitting a Workgroup name won’t remove any existing trust for that Workgroup. The WrapStack operation will fail if you attempt to trust a Workgroup that does not exist.

Server queue

The WrapStack function can affect many wraps, which can create a heavy extra load on the database infrastructure. WrapStack places the entire bulk operation as a wrap job on the Wrapsite’s server queue. All the database operations for one WrapStack function are run as one wrap job.

Wrap job ID

After a user press Create instances and a wrap job has been queued in order to perform the required database operations, the user gets a wrap job ID in return. The Wrap job id appears in the confirmation message

and in the Wrap at the position of the WrapStack function. You should instruct users to save this job ID for future reference.

You can follow the serialized processes by monitoring the Wrap Jobs queue on the wrapsite. Learn more on the Wrap administration help page.

Link to the job queue

If you want the control wrap to offer a link to the Wrap Jobs Queue, you can use the following formula in the source spreadsheet:

=HYPERLINK("/MoreVision.ExcelWraps/WrapJobsAdmin" ,"Wrap Jobs Queue")

WrapStack supports Undo

A WrapStack can be a complicated operation to set up. If you make a mistake that you don’t notice until the job has been run, you can use the Undo feature to roll back all the changes made by the WrapStack job.

1. Ensure that the job has finished, with a status of Completed. You cannot undo unfinished jobs. Jobs that have failed are undone automatically.
2. Tick the checkbox next to the WrapStack job you wish to undo.
3. Select Undo in the Actions box.
4. Press Apply to undo the changes made by the WrapStack job.

Function reference

Example

In the example above, the bulk action defaults to “create new instances”. New wraps will be created for the WrapStackTarget wrap. One wrap will be created for each Unique Key in the Source MyWraps report. If the Unique Key already exists, the job will be terminated.

The following cell formula is inserted in the cell.

=WrapStack(B3:C4)

Format and parameters

=WrapStack(instance_data_table, recalculate_instances, action)

instance_data_table, also known as “range”

Each WrapStack function is associated with an instance data table in a designated cell range. The format of this table is described under Definitions above.

If you don’t specify a valid cell range, the conversion will fail.

recalculate_instances, also known as “calc”

If you want to execute all the formulas, WrapLinks, etc for each wrap already during the bulk operation, set the recalculate_instances option to True. This will make the bulk operation considerably slower than if you allow this to happen the next time each wrap is opened.

The default is False.

action

Select the bulk operation to be performed using an integer 1-3:

1. Only creates new wraps. If a Unique Key already exists, the operation will be terminated.
2. Only updates existing wraps. If a Unique Key isn’t found, the operation will be terminated.
3. Wraps that don’t exist are created. Existing wraps are updated.

The default is 1.

Tracking the Wrap job

When WrapStack processing is complete, the WrapStack function returns the job id.

Known issues

• You cannot use the same wrap name for Source and Target because all the Unique Keys for the Source already exist in the Target.