The WrapStack function

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

When wrap instances 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 wrap instances, or add one or more new fields to all wrap instances.

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 instances, 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 instances in the target wrap 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 instances 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 instance of a daily inspection checklist for each carriage in each train that arrives at the depot. The correct checklist is used for each type of carriage in the train.

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.

Screenshot of the Create Instances button created by a WrapStack widget

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 instances that don’t already exist

An error in your instance data table may result in an attempt to create an instance of the Target 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 instances will now have a unique key, since today’s date wouldn’t have been used for any previous daily inspection.

You can only update instances that already exist

An error in your instance data table may result in an attempt to update an instance of the Target 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 instances 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 instances

The WrapStack function also has an Action to both Update existing instances and create new instances. If a Unique Key already exists, the instance will be updated, if not it will be inserted as a new instance. 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 instances, 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 instances 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 instances of this Target wrap 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 instances 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 instance of the Target wrap is affected by this row in the instance data table. In this case, you must provide the entire Unique Key for the instance using separate columns in the instance data table, see Providing static content below.
  • If you provide a Source MyWraps report, the number of instances in this report controls the number of instances 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 instances 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 instance per city, you must ensure that you have only one instance per city also in the Source MyWraps report, even if the source wrap contains one instance per postal code. If the Target is weekly but the Source is daily, you must limit the MyWraps report to only one Source instance per week.

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

When the 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.

Screenshot of a MyWraps link with the second half highlighted

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

The column heading for this column must be SourceMyWraps.

Providing static content

There are several types of static content for the new wrap instances 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 instance 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.
  • When you create new instances, you can pre-fill additional fields in the new wrap instances, e.g. a depot identification, by adding one column per field to the instance data table. When you add or update fields for existing instances, 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. All field names must be in lower case.

It is possible to manipulate two or more wrap instances in the Target wrap 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.

Screenshot of a WrapStack function call with an extra Unique Key for the Target wrap

Reserved field names

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

  • createdby – Designates the owner of the new instance. In the ExcelWraps permissions model, there is a separate set of permissions for the owner of an instance. This makes it possible for employees to have private access to their own sensitive information. Read more on the help page about Wrapgroups and Workgroups.

Job queue

The WrapStack function can affect many wrap instances, which can create 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

Screenshot of the wrap job tracking id returned when you press Create Instances

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")

Function reference

Screenshot of a WrapStack function call with the same Unique Keys for the Source and Target wraps

Example

In the example above, the bulk action defaults to “create new instances”. New instances will be created for the WrapStackTarget wrap. One instance 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

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

If you want to execute all the formulas, WrapLinks, etc for each instance 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 instance is opened.

The default is False.

action

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

  1. Create new instances. If a Unique Key already exists, the operation will be terminated.
  2. Update existing instances. If a Unique Key isn’t found, the operation will be terminated.

The default is 1.

Tracking the Wrap job

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

Screenshot of a WrapStack widget displaying the job id

Known issues

  • You cannot use WrapStack with AutoNumber. Neither the Source nor the Target can have an AutoNumber field.
  • You cannot use the same wrap name for Source and Target because all the Unique Keys for the Source already exist in the Target.