The MyWraps2 function

The MyWraps2 function uses a MyWraps link to pull a filtered list of instances into a Wrap instance.

In ExcelWraps, the MyWraps reporting tool provides the most powerful means of selecting “rows” from the database by filtering exactly the instances that are affected in a particular situation. It also allows the developer to zoom in on exactly the “columns” or fields in these instances that are interesting.

The MyWraps2() function offers an easy way to use the MyWraps extraction mechanism from inside a Wrap. This is very useful when:

  • You are interested in engines with a certain property, e.g. “diesel-powered” and you wish to create a dashboard showing all the engines that have this property. You can do this with a lot of individual WrapLinks, but with the MyWraps2 function, you get them all in one request – which is faster, requires less resources, and is much less complicated.
  • You have the identity of a train, and wish to extract numerous fields from the instance that describes that train. Again, you can always create a pile of WrapLinks pulling all the fields one by one, but with MyWraps2 you can get them all in one go.

The MyWraps2 does not provide the additional features that the WrapLink function provides. If you use MyWraps2() to pull information from an instance,

  • No link is created to the source instance. If you click on a value returned by MyWraps2, nothing happens.
  • Users will not be able to add new instances to the source Wrap using the + sign that they get for WrapLinks.
  • No holder cell is available for testing in Excel. Holder cells are only used with basic WrapLinks. If you want to test a MyWraps2 function in Excel, you can provide a dummy value in the link like you do with WrapLink2.

Example: Incoming carriages

In a process at a railway depot, it is important to know what carriages are approaching the depot. A Wrap is created to request the ID and type for all these carriages.Screenshot of the MyWraps link in the example of the MyWraps2 function

MyWraps link and dummy value

In cell B3, a MyWraps link is created that requests the data. In this example, this link could filter the list of incoming carriages by date, sort the filtered carriages in descending order by time and select the two columns for ID and type.

To save time during development, ExcelWraps function calls work already in Excel, returning a dummy value that in this case is provided in B4. The dummy value is formatted using the same row and column separators that will be used when the live instances and their columns are returned by MyWraps.

The MyWraps2 function call

Screenshot of a MyWraps2 function call

In cell B8 we execute the MyWraps link inside a MyWraps2 function call.

Most of the ExcelWraps function calls need to be at the top level and cannot be nested within other functions, including IF. To make a MyWraps request conditional, you place an IF statement around its link. MyWraps2 does not submit empty MyWraps links for execution, and instead returns an empty string. We included a condition in this example just as a demonstration of this; if your request is unconditional you just omit the IF clause in the example.

Since the MyWraps2 function is executing inside Excel, it returns the dummy value. When uploaded to a Wrapsite, it would execute the MyWraps link and then return the actual rows and columns that were returned by MyWraps, using the requested row and column separators.

Parsing the data from MyWraps2

Screenshot of an example using TextSplit to parse data from MyWraps

The last section of the example is just showing you one way to parse the data returned by MyWraps. Here, we use the TextSplit function to directly access field 2 for instance 2 in the result. You can access the Wrap data in any way you like in the Wrap, e.g. to populate a dynamic dropdown menu, use it in lookup operations, or display it on a dashboard.

You control exactly what fields (columns) are returned, from what instances (rows), and in what order, in your MyWraps link. You should code your spreadsheet to handle all possible situations, e.g. when there are no incoming carriages – the filter settings in your MyWraps link can naturally result in no data being returned.

You must also verify that you have selected row and column separators that are not already present in the field data from the instances.

Results

Normal completion

If the source Wrap instances have been signed off and frozen, they always have the same contents as when they were signed off. Live source Wrap instances are first recalculated. (If Options.openmode=fast is in effect, cached data is used also for live instances, read more below.)

All instances and fields designated by the mywraps_link parameter are copied into the target MyWraps2 cell.

If you are testing the function within Excel, the dummy_value in the MyWraps2 function call is returned instead of live data.

Using cached data with Options.openmode=fast

When a large number of instances are affected by the same operation, e.g. a dashboard-style Wrap that pulls data from numerous other Wrap instances, recalculating all the affected instances can have a noticeable effect on system performance. If a linked instance in turn links to other instances that also need to be recalculated, the problem is further aggravated in a cascading fashion. To provide the most recent information for live Wrap instances, this recalculation is required to ensure 100% correct values, and users must wait for the recalculations to complete.

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. You should open such reports using the Options.openmode=fast parameter in the MyWraps link. With this option, all Wraplinks, WrapLinkAggregates, and WrapLinkLists will reuse the results from the most recent recalculation of each instance instead of recalculating it again, which drastically reduces the response time.

There is no link

If the link in the MyWrap2 function call is empty, nothing is returned. This may be useful if you want to use an IF() statement in the MyWraps2 formula. Read more under Conditional request below.

Function reference

Screenshot of a MyWraps2 function call

Example

In this example, the IF clause is first evaluated. If TRUE, a MyWraps link is sent for execution on the server. On the Wrapsite, the function returns the result returned from MyWraps. In Excel, it returns the dummy value for testing.

This is what this MyWraps2 function call will look like:

=MyWraps2(IF(enabled,link,""),dummy,colsep,rowsep)

Format and parameters

=MyWraps2(mywraps_link, dummy_value, column_separator, row_separator)

A MyWraps link is used to retrieve a filtered list of instances. During testing in Excel, a dummy value is returned instead.

All parameters are optional except mywraps_link.

mywraps_link

Define a MyWraps extract as you would normally do. Filter the list of instances also that it only includes the instances you will actually use. Sort the instances in the order most appropriate for your requirements. Select only the columns you will actually use. Copy the link into the function call, starting with the Wrap name.

If the mywraps_link resolves to an empty string, the MyWraps2 call does nothing and returns an empty string.

Example: If your MyWraps link is

https://toc.live.excelwraps.com/MyWraps/SAP-SerialNumber?Options.MaxRows=500&Options.Title=Wrap+Instances+for+SAP-SerialNumber&Options.Columns=serialnumber.string&Filter.material=2000000577

you should only copy the part in bold, so that the function call becomes

=MyWraps2("SAP-SerialNumber?Options.MaxRows=500&Options.Title=Wrap+Instances+for+SAP-SerialNumber&Options.Columns=serialnumber.string&Filter.material=2000000577")

Of course, the function can also refer to its parameters using cell references or cell names, e.g.

=MyWrap2(M14, test_unit, colsep, E3)
Conditional request

MyWraps2 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 the IF statement around the MyWraps link:

=MyWraps2(IF(required_field="","","mywraps_link"))
Options.openmode=fast

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

dummy_value

Provide an optional dummy value for the function to return during offline testing in Excel.

column_separator

Specify the character that should be used to separate the column values, the fields from each Wrap instance, in the result returned by the function. You can designate the column separator as a static string, e.g. “,”, or as a cell reference, e.g. C4. Changing the value in C4 during execution will not refresh the instances returned by the MyWraps link.

If you don’t specify a column separator, a semicolon, “;” (without the quotes), will be used.

row 1 col 1;row 1 col 2|row 2 col 1;row 2 col 2

row_separator

Specify the character that should be used to separate the rows representing each Wrap instance in the result returned by the function. You can designate the row separator as a static string, e.g. “^”, or as a cell reference, e.g. C5. Changing the value in C5 during execution will not refresh the instances returned by the MyWraps link.

If you don’t specify a row separator, a vertical bar or “pipe” symbol, “|” (without the quotes), will be used.

row 1 col 1;row 1 col 2|row 2 col 1;row 2 col 2

Use MyWraps2() in a hidden cell

The MyWraps2 function can be placed in a hidden row, column, or cell. You can then refer to the returned data from other cells in the spreadsheet.

Parsing data from MyWraps2

If the MyWraps2 function is in a cell called data_extract, you could use the following nested TextSplit function to extract the second field from the second extracted instance:

=TEXTSPLIT(TEXTSPLIT(data_extract, rowsep, instance_no), colsep, field_no)

Assuming you provide the following input values:

  • data_extract = “row 1 col 1;row 1 col 2|row 2 col 1;row 2 col 2”
  • rowsep = “|”
  • colsep = “;”
  • instance_no = 2
  • field_no = 2

the function call above would return field 2 from instance 2, i.e. “row 2 col 2”.