The WrapLinkList widget

The WrapLinkList widget pulls cell values from a group of wrap instances that fulfill certain criteria. It is used to populate a dropdown list with all possible valid identities to choose from, e.g. all railway cars in a particular depot with a pending error report.

A common WrapLink uses a unique key to return a single value from a single wrap instance. If you need a list of values from all the wrap instances that match a particular condition, use the WrapLinkList widget instead, and specify a non-unique key that matches more than one wrap instance.

Example: If you use a key that designates a particular depot for rolling stock, you may retrieve the identities of all the railway cars that belong to this depot and have one or more error reports pending. Prefilling a dropdown widget with these identities makes it easy for the maintenance personnel to quickly select the right railway car. Using a dropdown also eliminates the risk of typing errors in a free-text field.

You can use a Holder Cell to provide a dummy menu choice to test the wrap already in Excel. When the wrap is run on the server, WrapLinkList extracts live data from the cloud database and creates a dropdown menu with all the live candidates in the holder cell.

The WrapLinkList widget is a visual guide that helps you insert a wraplinklist() 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 =wraplinklist() 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 to create the list from. Without this information, the guide cannot give you much help anyway.

Insert a WrapLinkList widget

  1. Select the cell where you want the WrapLinkList widget to appear.
  2. Switch to the WrapCreator ribbon and click on Insert Widget.
  3. Select the WrapLinkList 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 WrapLinkList that uses another list to set the key

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 but one of the keys and values that together form the Unique Key that designates the wrap instances that you want to pull data from. To continue the database analogy, this key will select one or more rows in the source table. You must leave one key unselected – the resulting key must not be unique if it is to return values from more than one wrap instance.

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 or by editing the inserted wraplinklist() function call as described below.

The second part of the formula builder lets you select the “filter” cell in the source wrap instances that you want the WrapLinkList to retrieve (the column, if you think of the wrap as a table). After the call has been made, the returned values will be inserted as a dropdown menu in the cell where the =wraplinklist() 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 WrapLinkList widget, the holder cell contains the item selected from the dropdown list.

Location

If you enable a holder cell for the widget, the cell immediately to the right of the WrapLinkList 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.

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.

Known issues

Saved data is not in the current WrapLinkList

A WrapLinkList is inherently dynamic and there is no guarantee that a previously selected and saved value is still eligible. As an example, a railway car may be included in a list for one particular mileage, but not when the next higher mileage is selected. ExcelWraps detects this and colors the cell background red to alert the user of the situation. To avoid locking the user out, the updated wrap instance can still be submitted also with this error.

When you develop a new version of a wrap, you may change a previously static dropdown list to a dynamic WrapLinkList. The static list may have been defined using Excel’s Data Validation, or using the static Dropdown or Dynamic Dropdown widgets. If an old wrap instance has a value that is not available in the new dynamic dropdown created with a WrapLinkList, this will be highlighted with a red border.

Frozen wraps become static

If a wrap instance has been signed with the Freeze option, any dynamic data pulled into the wrap using WrapLinks, WrapLinkLists and WrapLinkAggregates will no longer be updated. A frozen wrap always reflects exactly the conditions when the freeze signature was signed.

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.

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

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

Function reference

Screenshot of a WrapLinkList that uses another list to set the key

Example

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

=@WrapLinkList("car?unit="&J6&"&filter=car")

You can edit the formula to use cell references instead of static values. If the wrap has the unit number in a named cell you can use the following formula:

=@WrapLinkList("car?unit=" & Unit_Num & "&filter=car")

Format and parameters

=@WRAPLINKLIST("link")

link is a quoted text string that identifies the wrap you want to pull data from, provides the common key for the multiple instances you are selecting and what value you want to be returned from them

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 example above, the query string begins with a question mark and sends four parameters to the source wrap: mileage, car, end, and filter.

Wrap name

The link starts with the name of the wrap you want to link to, e.g. car in the example above.

Components of the Key, e.g. unit=

The link must contain the Key that selects the group of wrap instances you want to pull data from. The Key is often assembled from several values, e.g. for a bogie overhaul you would need to identify the car type, what bogie on the car you refer to, and the mileage interval for the overhaul. You specify the components of the Key using the field names that the wrap is using for these values. In the example above, the Key is provided by the unit parameter.

filter

The filter parameter returns a field value from the selected source wrap instances. The values in the field with this name are used to populate a dropdown menu displayed in the same cell that the =wraplinklist() formula occupies, and in the holder cell, if there is one.

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 wraplinklist() 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 This will provide 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 This provides summary info for the signature, e.g. “JTD 09/12/2019”. filter-sig-datetime=cell_name This provides the date and time of the signature, e.g. “09/12/2019 19:23”. filter-sig-date=cell_name This provides the date of the signature, e.g. “09/12/2019”. filter-sig-time=cell_name This provides the time of the signature, e.g. “19:23”.

You can only return the values of one field in a WrapLinkList, so the filter and filter-sig-xxx parameters are mutually exclusive.

The holder cell

You can use a Holder Cell to provide a dummy menu choice to test the wrap already in Excel. When the wrap is run on the server, WrapLinkList extracts live data from the cloud database and creates a dropdown menu with all the live candidates in the holder cell.

Format and parameters

=HOLDER(cell_name, dummy_value, hidden, holder_item)

cell_name points to the WrapLinkList 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 WrapLinkLists.

=HOLDER(Unit_Num, 500, TRUE, "")