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:
The MyWraps2 does not provide the additional features that the WrapLink function provides. If you use MyWraps2() to pull information from an instance,
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.
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.
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.
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.
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.
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.
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.
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(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.
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
you should only copy the part in bold, so that the function call becomes
Of course, the function can also refer to its parameters using cell references or cell names, e.g.
=MyWrap2(M14, test_unit, colsep, E3)
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:
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.
Provide an optional dummy value for the function to return during offline testing in Excel.
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
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
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.
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:
the function call above would return field 2 from instance 2, i.e. “row 2 col 2”.