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,
The examples on this page assume that the data returned by MyWraps2 has a static size with a fixed number of cells being returned. If the extent of the returned data is unpredictable – some trains have many cars, some have just a few – you can use the ShowTable function to display a varying-length result from MyWraps2 in a single wrap cell.
The MyWraps2 function is subject to the same settings and defaults as an interactive MyWraps report, including the MaxRows limit that only 500 rows are returned by default. It is also subject to the default wrapsite culture (the wrapsite locale) and uses regional formatting for numbers and dates.
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 default value that in this case is provided in B4. The default 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. Default data should be formatted according to the default wrapsite culture (the wrapsite locale).
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 default 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.
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:
All wraps and cells designated by the provided MyWraps link are copied into the target cell using the supplied wrap and cell separators.
If you are testing the function within Excel, the default value in the function call is returned instead of live data.
If you link to a group of wraps using a unique key that does not match any existing wraps in the database, the MyWraps2 function returns its default value.
If the link points to a wrap that is not installed, the MyWraps2 function returns its default value.
If the link in the function call is empty, i.e. =MyWraps2(“”), the function returns its default value. This may be useful if you want to use an IF() statement in the MyWraps2 formula and return an empty link if the function shouldn’t be executed. Read more under Conditional request below.
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.
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.
WrapCreator does not prevent a wrap from linking to itself, or including itself in a linked aggregate or list. If you create a nested design like this, you should be aware that WrapLinks don’t always return the most current value. To maintain reasonable performance, linked data is cached and refreshed only in certain situations.
Example: Wrap A has a cell todaysdate that is a WrapLink to a cell in Wrap B which fetches the value from wrap C. The cell value in a Wrap C will not be updated until the Wrap is saved, say on the Monday. The new value will not reach and be reflected by Wrap B until it is opened, which may be on the Tuesday. The linked cell in Wrap A will not be updated until Wrap B has both been opened and saved with the new value it got from Wrap C, which may be on the Wednesday. Now imagine if Wrap A links to itself for a value. The same delay will occur, so the source value (that is linked to) will often be more current, and different, from the linked value – in the same Wrap.
The circumvention is simple, instead of using a WrapLink to fetch todaysdate from the same wrap, simply use a direct cell reference like =todaysdate in the target cell.
In WraplinkLists, the list returned by a nested call will not include a cell value that hasn’t previously been saved. Example: If you use a WraplinkList in a Wrap template to return all the car numbers for which there are instances of the Wrap, then create a new instance of the Wrap for car 7013, this car number will not appear in the WraplinkList for the 7012 Wrap until you save the 7013 wrap and then open the 7012 wrap.
In WraplinkAggregates, the aggregate returned by a nested call will be calculated using the most recent values in the database. Example: A WraplinkAggregate is calculated in wrap template A as a SUM of a cell in all A wraps. There are only three A wraps with the most recently saved values for the cell being 1, 2 and 3, respectively. The sum will be returned as 6, regardless of which of the three wraps you open. If you change the source value in the third wrap to 8, the aggregate will not be recalculated and the sum will not be affected until you save the third wrap with the new value. You then have to open either of the three wraps to have its aggregate refreshed to return 11.
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.
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.
In this example, an IF clause makes the request conditional. If the enabled cell is False, the link parameter will be empty, which terminates the function call. If it is True, a MyWraps link is sent for execution on the server, and the function returns the result returned from MyWraps. When tested in Excel, the function returns the default value.
This is what this MyWraps2 function call will look like:
=MyWraps2(IF(enabled,link,""),default,colsep,rowsep)
=MyWraps2(mywraps_link, default_value, column_separator, row_separator)
A MyWraps link is used to retrieve a filtered list of instances and their cell contents.
All parameters are optional except mywraps_link. Normal MyWrap defaults and settings apply, including the MaxRows limit that only 500 rows are returned by default.
The different values that are returned by the function call are described in detail in the Results section above.
The easiest way to create the MyWraps link is to experiment with the filter settings in MyWraps until you see exactly the selection of wraps that you would like to have returned by your MyWraps2 function. After that, you click on the Copy button in the MyWraps toolbar and two useful fields appear:
You can also manually copy the MyWraps link into the function call, starting with the Wrap name.
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)
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 an IF statement around the link, and return an empty link to deactivate the function:
=MyWraps2(IF(required_field="","","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 about openmode=fast above.
Provide 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. Default data should be formatted according to the default wrapsite culture (the wrapsite locale).
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
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”.
The Copy button in MyWraps can be used to obtain example data for testing.