The WrapLink widget

A WrapLink pulls a cell value from another wrap. When you click on the value in a WrapLink, it links to the source wrap that the value was pulled from. This allows the user to quickly traverse a web of complex information just by clicking on the most interesting linked values.

A wrap can fetch data from other wraps by using WrapLinks. As an example, this makes it easy to create dashboards that summarize information from many wraps.

Each WrapLink identifies the source wrap to be used for the lookup, the unique key that uniquely designates the right wrap, and the filter, naming the cell in the source wrap that you want the lookup operation to return. If you click on the linked value, the source wrap is opened, with the cell that provided the linked data highlighted. This makes it easy to analyze complex data just by clicking on whatever you find interesting.

Example: a dashboard cell shows an out-of-range measurement for a railway car bogie. If you click on this cell in the dashboard, you open the bogie detail wrap, with a highlight on the measurement that was shown in the dashboard. You can then return to the dashboard or continue zooming in on the problem.

The WrapLink widget can be used to collect key performance indicators. KPI data per day, week, month, or other periods can be automatically saved using a scheduling agent. Live data is updated on a regular basis so that your analysis is always based on fresh information. Historical data is securely preserved for long-term trend analysis.

With the basic format WrapLink, you can use a Holder Cell to provide a dummy value to test the wrap already in Excel. When the wrap is run on the server, WrapLink extracts live data from the cloud database and returns it in the holder cell. With the extended format WrapLink2, a default value can be assigned directly in the function call, removing the need for a separate holder cell.

The WrapLink widget is a visual guide that helps you insert a basic WrapLink() function into the current cell. If you want to link to a Wrap that doesn’t yet exist on the Wrapsite, you need to insert the =wraplink() function manually into the cell using the Function Reference section below. This is because the visual guide relies on the Wrapsite properties to provide a definition of the Wrap you want to link to, including its Unique Keys and the field you want the link to return. Without this information, the guide cannot give you much help anyway.

If you wish to use the extended WrapLink2() function, you also need to insert the function manually into the cell using the Function Reference section below.

Extracting large amounts of data using MyWraps

Requesting a lot of data using many WrapLinks or WrapLinkAggregates, e.g. for a complex dashboard showing hundreds of entries, can be very time-consuming because of all the database operations required to provide the data one cell at a time. Using the MyWraps2 function, you can request a single data extract containing all the data you need by defining a single MyWraps link and having all the data returned with item separators in a single cell of the dashboard Wrap.

Insert a WrapLink widget

  1. Select the cell where you want the WrapLink widget to appear.
  2. Switch to the WrapCreator ribbon and click on Insert Widget.
  3. Select the WrapLink widget on the Widget tab in the task pane.
  4. Enter the required parameters on the Cell tab and press Apply.
  5. Verify that a default value has been inserted into the cell.

Parameters

Screenshot of a WrapLink widget

Cell name

We encourage you to assign cell names to calculated cells, so that they get correct headings in MyWraps reports. A convenient MapToNames tool can save time by automatically using the field label as the cell name.

Wrap Selector

Select the source wrap from which you want to pull data. If you have a database background, you can think of this as selecting a table.

Formula Builder

The first part of this section lets you specify all the keys and values that together form the Unique key that designates exactly the one source wrap that you want to pull data from. To continue the database analogy, a valid key will select exactly one row in the source table. As a developer, you may want to construct the key dynamically using runtime values from other cells in the wrap. You can insert cell references here like in the screenshot above, or by editing the inserted wraplink() function call as described below.

The second part of the formula builder lets you select the “filter” cell in the source wrap that you want the WrapLink to retrieve (the column, if you think of the wrap as a table). Special keywords allow you to request information about a signature (username, date etc), read more below. After the call has been made, the returned value will be inserted in the cell where the =wraplink() function call resides, and in the holder cell, if there is one.

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 basic WrapLink widget, the holder cell contains the data pulled from the linked wrap.

If you prefer to assign a default value in the function call instead, insert the function call manually using the extended WrapLink2 format as described 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 by clicking on the “pen” symbol and then selecting a different cell as the holder cell.

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.

Openmode=fast

Wraps often link to each other. To provide the most recent information, linked wraps must be recalculated for every access to ensure that they in turn inherit the current values from their own linked wraps and return the most recent values to their caller.

Recalculation is normally instantaneous, but when a large number of wraps are affected by the same operation, e.g. a dashboard-style wrap that retrieves information from numerous other wraps, recalculating all the affected live wraps in a cascading fashion can have a noticeable effect on the wrap’s response time. Users must always wait for all the initiated recalculations to complete before the updated wrap appears on the screen.

In many cases, however, the information being gathered is historical. As an example, a dashboard of KPIs for the previous year, month, or week only reflects events in the past. For historical data, the recalculation of all the instances is unnecessary because the data never changes. It is good practice to freeze all wraps that no longer can change since it tells the system that they never again have to be recalculated.

Even with live wraps, the most recent changes can be irrelevant, which makes the recalculation unnecessary. You should open such wraps using the openmode=fast parameter in the link. With this option, all database operations will reuse the results from the most recent recalculation of each instance instead of recalculating it again, drastically reducing the response time when there are many links.

So, before any results are returned from a wrap:

  • If openmode=fast is in effect, and the wrap is live, cached data from the most recent recalculation is used.
  • Without openmode=fast, a live wrap is first recalculated, and all the live wraps it links to.
  • If a wrap has been frozen, its contents are static. Frozen wraps are never recalculated.

Results

Normal completion

The value in the source cell designated by the filter parameter is retrieved for the wrap matching the link and the unique key. The value is copied into the target WrapLink cell. The value is linked to the source wrap using its unique key. If the user follows the link from the linked field to the source wrap, the filter cell is highlighted in bright yellow.

  • If you use the WrapLink function, the value in the filter cell is also copied into the holder cell, if any, to trigger any dependent calculations. If you test a WrapLink function within Excel, and a holder cell has been defined, the content of the holder cell is returned instead of live data. If no holder cell has been assigned in the function call, an empty string is returned.
  • The WrapLink2 function does not support holder cells. If you test a WrapLink2 function within Excel, the default value in the function call is returned instead of live data.

No filter specified

If you haven’t specified a filter parameter, the word Link is used as the anchor text for the link.

  • The WrapLink function returns an empty string.
  • The WrapLink2 function returns its default value.

The filter name is invalid

If the cell name in a filter parameter does not exist, the WrapLink displays a red “not available” icon. Red circle icon used for a WrapLink where the filter cell is empty

  • The WrapLink function returns an empty string.
  • The WrapLink2 function returns its default value.

The filter is empty

If the value in the source cell designated by the filter parameter is empty or null, the WrapLink displays a red “not available” icon Red circle icon used for a WrapLink where the filter cell is empty

  • The WrapLink function returns an empty string or zero depending on the data type.
  • The WrapLink2 function returns its default value.

No wrap matches the unique key

If you link to a wrap using a unique key that does not exist in the database, one of the following will happen:

  • If the user does not have a qualifying role, a grey “not permitted” icon appears. Grey X icon used for a WrapLink where the user does not have a qualifying role to add a missing wrap instance
  • If the WrapLink does not include an AutoNumber key, it will generate a button with a green “add new” plus sign. Green plus icon returned by a WrapLink that includes an AutoNumber that points to a wrap instance that does not exist Clicking on a plus button will create a new wrap using the unique key combination specified by the WrapLink. If the wrap page contains more than one green plus signs, the user can instead press the large green PrePopulate button at the bottom of the wrap page to create all the new wraps in one action.
  • If the WrapLink includes an AutoNumber key, the WrapLink will generate a button with a grey “add new” plus sign. Grey plus sign icon used for a WrapLink to create a new wrap instance using the unique key combination specified by the WrapLink Clicking on the plus button will create a new wrap using the unique key combination specified by the WrapLink. If the wrap page contains more than one grey plus signs, the user still has to click on each of them individually – the PrePopulate function does not support AutoNumber links.

If a new wrap is created as a result of a WrapLink, you can provide initial data for any number of fields, directly in the WrapLink. Just add a field/value pair like train=96529 in the link to set the train field in the new wrap to 96529. If a wrap already exists, these extra field/value pairs are ignored.

If a new wrap is created as a result of a WrapLink, you can designate the owner of the new wrap, e.g. createdby=JohnDoyle. 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. Read more on the help page about Wrapgroups and Workgroups.

If a new wrap is created, the cell value from the new wrap is returned.

If a new wrap is not created,

  • the WrapLink function returns an empty string or zero depending on the data type.
  • the WrapLink2 function returns its default value.

The wrap template does not exist

If the link points to a wrap that is not installed or where the unique key combination is invalid (does not resolve to a single wrap), a red “undefined” icon is displayed. Red X icon returned by a WrapLink that points to a wrap that is not installed or where the unique key combination is invalid

  • The WrapLink function returns an empty string or zero depending on the data type.
  • The WrapLink2 function returns its default value.

The wrap is unstable

Sometimes, the recalculation of the source wrap never gives a stable result – the filter value does not settle after repeated recalculations. It is likely that the wrap source spreadsheet contains an error, like a circular reference. In this case, a red “retry” button appears. The user can click on the button to trigger another recalculation. Red retry icon used when a WrapLink is unstable - the filter value does not settle after repeated recalculations

  • The WrapLink function returns an empty string or zero depending on the data type.
  • The WrapLink2 function returns its default value.

There is no link in the function call

If the link in the function call is empty, i.e. =WrapLink(“”), nothing is displayed and no link is created. This may be useful if you want to use an IF() statement inside the WrapLink formula and return an empty link if the function shouldn’t be executed.

If there is no link in the function call,

  • the WrapLink function returns an empty string.
  • The WrapLink2 function returns its default value.

Read more under Conditional request below.

Remove a WrapLink 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 WrapLink widget:

  1. Ensure that the task pane is visible.
  2. Select the widget’s cell.
  3. The task pane should now show the WrapLink 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 WrapLink widget designates a holder cell, right-click on this cell and select Clear contents.
  5. In the settings for the WrapLink widget, locate the Remove button at the bottom and click on it.

Linking to other wraps

In links, reserved characters must be encoded

One of the purposes of a WrapLink is to construct a valid hyperlink to another wrap. This link is a standard Uniform Resource Locator (URL) that contains your WrapSite’s name, a folder path, the name of the wrap, the names of the fields forming the key, and the filter name.

A URL must conform to certain rules, of which the most important is that the following characters have special meaning: ! * ‘ ( ) ; : @ & = + $ , / ? # [ ] Some of these characters are allowed in the names for Excel files, worksheets and cells, but we strongly recommend that you never use any of the reserved characters.

If you have a very special need to use one or more of the reserved characters (aside from their reserved meaning), they must be encoded. The reserved character /, for example, if used in the “folder path” component of a URL, has the special meaning of being a delimiter between folder names (path segments). If / needs to be used in a folder name, then you must percent-encode it to %2F or %2f instead of using the raw /. You can use the EncodeURL() function in Excel 2013 or later to percent-encode any names that may contain reserved characters.

Frozen wraps become static

If a wrap is frozen, any dynamic data pulled into the wrap using WrapLinks, WrapLinkLists, WrapLinkAggregates or MyWraps2 will no longer be updated. A frozen wrap always reflects exactly the conditions when it was frozen.

Avoid linking from hidden cells

Due to a design constraint, the WrapLink family of functions and MyWraps2 may not return the expected results for frozen wraps when placed in hidden cells. If you want to hide the result of a linking function, you should place it on a separate hidden sheet.

Update the Wrap Package regularly

Before you create your first wrap, you must install the Wrap Package that you downloaded from your wrapsite so that WrapCreator has all the information it needs about your wrapsite and how it is set up. The Wrap Package synchronizes Excel to your wrapsite and should be updated periodically to give WrapCreator access to the most recent wrap and user definitions.

Function reference

Screenshot of a WrapLink widget

Example

In the example above, the following cell formula is inserted in the cell.

=@WrapLink("CL334_AddSnag?snagref="&Snag!$B$37&"&Filter=IssueDefect")

You can change the cell reference directly in the function call.

If the wrap has the mileage, unit number and car type in named cells you can use the following formula:

=@WRAPLINK("Class395-Bogie-PackPredictor?mileage=" & mileage & "&unit=" & unit_id & "&car=" & car_type & "&end=2&Filter=_ht12")

Format and parameters

=@WRAPLINK("link")
=@WRAPLINK2("link", default_value)

link is a quoted text string that identifies the wrap you want to link to, provides the Unique Key for the single wrap you are selecting, and what value you want to be returned from it. Read more below.

default_value provides a default value that the function returns in special cases described in the Results section above. The default value can also be used when you test the Wrap already in Excel. If no default value has been assigned in the function call, an empty string is returned. For the basic WrapLink() function, this functionality is instead provided by a holder cell – read more below.

The different values that are returned by the function call are described in detail in the Results section above.

Link parameters

The parameters for a link are specified in the query string. The query string is appended to the wrap name following a question mark. Within the query string, parameters are separated by ampersands. In the second example above, the query string begins with a question mark and sends five parameters to the source wrap: mileage, unit, car, end, and filter.

Wrap name

The link starts with the name of the wrap you want to link to, e.g. Class395-Bogie-PackPredictor in the example above.

Components of the Unique Key, e.g. mileage=, unit=, car=, end=

The link must contain the full Unique Key that selects the single wrap you want to link to. The Unique Key is usually assembled from several values, e.g. for a bogie overhaul you may need to identify the unit number, the car type, what bogie on the car you refer to, and the mileage interval for the overhaul. You specify the components of the Unique Key using the field names that the wrap is using for these values. In the second example above, the Unique Key is provided by the mileage, unit, car, and end parameters.

Additional field/value pairs for new wrap defaults

If a new wrap is created as a result of a WrapLink, you can provide initial data for any number of fields, directly in the WrapLink. Just add a field/value pair like train=96529 in the link to set the train field in the new wrap to 96529. If a wrap already exists, these extra field/value pairs are ignored.

createdby

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.

If a new wrap is created as a result of a WrapLink, you can designate the owner of the new wrap, e.g. createdby=JohnDoyle. The user of the Wrap issuing the WrapLink must have the Create Wrap instancs on behalf of others permission.

Read more on the help page about Wrapgroups and Workgroups.

autonumber

If the wrap you link to uses the AutoNumber feature, you can designate any numbered wrap using the autonumber parameter in the wraplink function call, e.g. autonumber=42. You can use autonumber=first or autonumber=last to link to the first or last existing wraps. If you specify an autonumber that does not exist, ExcelWraps will offer to create the corresponding wrap for you. To explicitly request that a new wrap is created, use autonumber=*. A grey plus button will appear in the WrapLink field. If the user clicks on the button, a new instance of the linked wrap template is created. No data is pulled from the new wrap and no link to it is created. The AutoNumber must be the last part of a unique key combination.

filter

The filter parameter returns a field value from the selected source wrap. The value in the field with this name is displayed in the same cell that the =wraplink() formula occupies, and in the holder cell, if there is one. There are several special cases; read more under Results above.

Properties of a signature

Signatures contain information about the person who has signed, the company they work for, the date and time that the wrap was signed, the role required for signoff and the role required for unsigning. In the wraplink() formula, you can use some special keywords for signature cells (in the examples below, replace cell_name with your name for the signature cell).

  • filter-sig-detail=cell_name provides all the details of the signature e.g. “John T. Doe, MoreVision Limited – Stainburn 09/12/2019 19:23 Sign(Operative) Unsign(Manager)”.
  • filter-sig-summary=cell_name provides summary info for the signature, e.g. “JTD 09/12/2019”.
  • filter-sig-datetime=cell_name provides the date and time of the signature, e.g. “09/12/2019 19:23”.
  • filter-sig-date=cell_name provides the date of the signature, e.g. “09/12/2019”.
  • filter-sig-time=cell_name provides the 24-hour time of the signature, e.g. “19:23”.
  • filter-sig-username=cell_name returns the username for the user that signed the wrap, e.g. “JohnDoe”.
  • filter-sig-firstname=cell_name gives you the given name or forename for the user that made the signature, e.g. “John”, and
  • filter-sig-lastname=cell_name returns the surname or family name for the signature, e.g. “Doe”.
  • filter-sig-nickname=cell_name provides the nickname (initials) of the user that signed the wrap, e.g. “JTD”.
  • filter-sig-company=cell_name gives you the company name of the signature, e.g. “MoreVision Limited”.
  • filter-sig-location=cell_name contains the location for the user making the signature, e.g. “Stainburn”.
  • filter-sig-workgroup=cell_name returns the name of the workgroup for the user that made the signature, e.g. “Depot_3”.
  • filter-sig-roles=cell_name provides the user roles for the user that made the signature, e.g. “Supervisor, Manager”.

You can only return the value of one field with a WrapLink, so the filter and filter-sig-xxx parameters are mutually exclusive. If you wish to retrieve more than one value from a linked wrap, you must use two or more WrapLinks.

state=live | frozen | awaitingsignature | signedbyme | any

The state of a Wrap at any time is Live or Frozen. A WrapLink can be restricted to live wraps only. If you specify state=live, the wrap will only select the wraps that have not been frozen.

Some live Wraps will be AwaitingSignature (waiting for the logged-in user to sign the wrap) or SignedByMe (when the signature has already been made). The state Any includes all Wraps.

A common special case is when you want to access the last frozen wrap in a sequence with autonumber.

=@WrapLink("overhaul?unit=" & Unit_Num & "AutoNumber=Last&filter=date&state=frozen")
Conditional request

WrapLink is a top-level function and cannot be nested inside another function like IF. If you want to make the function call only if a certain condition is True, you can place an IF statement around the link, and return an empty link to deactivate the function:

=WrapLink(IF(required_field="","","link"))
Openmode=fast

When a Wrap only provides historical information, e.g. as a dashboard, you should always consider appending &openmode=fast to the link to it. This greatly increases performance. Read more under Openmode=fast above.

The holder cell

You can use a Holder Cell with the basic WrapLink() function to provide a dummy value to test the wrap already in Excel. When the wrap is run on the server, WrapLink extracts live data from the cloud database and returns it in the holder cell. The extended WrapLink2() function does not support holder cells.

Format and parameters

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

cell_name points to the WrapLink 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. The dummy value must have the same data type as the value that will later be returned by the server.

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

holder_item is always empty for WrapLinks.

=@HOLDER(error_rate, 0, TRUE, "")