List Items in datamapper

I would like to take the opportunity to add to ​@Paul McCulloch’s solution.

By creating an array of items in a variable Assyst search field, you can for instance use this array to construct a table for adding to the event’s rich remark.

As an example here is how you can construct the table with hyperlinks to the item

// Construct the table fron previous datamapping
// Uses a 'variable Assyst Search' field as input that should return an array of items from the CMDB
// Assumes elements in array at minimum have both id, and shortCode

var items = variables.expiringItemsArray || []; // Set to empty array to avoid undefined behaviour if missing 'items' array 
var baseUrl = "https://assyst-OUR-DOMAIN.net/assystweb/application.do#item%2FManageItem.do?dispatch=manageParent&currentTab=searchForm&id=";

var table = "<table><tr><th>Short Code</th><th>Expiry Date</th></tr>";

if (items.length > 0) {
    for (var i = 0; i < items.length; i++) {
        var item = items[i];
        var shortCodeLink = '<a href="' + baseUrl + item.id + '" target="_blank">' + item.shortCode + '</a>';

        table += "<tr>";
        table += "<td>" + shortCodeLink  + "</td>";
        table += "<td>" + (item.expiryDate || "") + "</td>"; //Empty string catch for resuabilty in cases where expiryDate is customized as Hidden
        table += "</tr>";
    }
} else {
    table += "<tr><td colspan='2'>No expiring items found</td></tr>";
}

table += "</table>";

// Output 'table' for use in the rich remarks field
table;

This table can then be put into a rich remarks field alongside any other text you want to add.

ItemTable = variables.ItemTable || "" //Protect against undefined if the variable field 'ItemTable' is missing

"<p>The following items will expire in 6 months: <p>" + ItemTable

Also, a heads-up: depending on the ETM/IPaaS version you’re on, you might not have date ranges available in ETM. ETM 1.10 adds support for date ranges, but if you’re on 1.9 or earlier, using a variable Assyst search field on its own will probably not work, as it only accepts a single date in an expiryDate and such a query will return items with expiry after the given date…
As a workaround, you could try using common.search to do the date range lookup of items. However, I’ve not personally been able to get the syntax for date range in an common.search correct during this testing.

Lastly a small note about Paul’s idea of linking items to the event:
This is probably the preferred way for reporting and my personal favorite as it allows for later reuse of data with use of VTL in action templates, etc.
However, creating such a channel is a bit more involved, as it will likely involve three datamappers: the aforementioned event mapper, a Linked Item Group mapper, and lastly a Linked Item mapper that gets the newly created Linked Item Group and iterates on the item array from the first mapper. While not difficult in and of itself, it does add several more layers of complexity.

This is a good use for Velocity, rather than JS, in mapper expressions. Rather than lots of string concatenation we can do something like:

#set( $items = $variables.expringItems)
#set ($baseUrl = "https://assyst-OUR-DOMAIN.net/assystweb/application.do#item%2FManageItem.do?dispatch=manageParent&currentTab=searchForm&id=")
<table>
    <tr>
        <th>Short Code</th>
        <th>Expiry Date</th>
    </tr>
    #if(  $items.size != 0)
        #foreach( $item in $items)
        <tr>
            <td><a href="${baseUrl}$item.id" target="_blank">$item.shortCode</a></td>
            <td>$item.expiryDate</td>
        </tr>
        #end    
    #else
        <tr>
            <td colspan='2'>No expiring items found</td>
        </tr>
    #end

</table>

which, IMHO, is easier to read.

Thank you both that is really helpful, I have just recently upgraded to ETM 1.10 so the date ranges is really useful.

If I wanted to return an array where the expiry date is blank how would I do that now?

Thanks

My first idea here was use of something like "EMPTY_RECORD" in an expiryDate-field during data mapping (similar to how one can lookup items with an emtpy CSG, Assyst24R2 Wiki: Integrations:assystETM 1.10 - assyst Request Mappers → Empty Records).

However for the CSG example this shows the use of  “shortCode” as the lookup parameter during the field datamapping. And atleast in IPaaS 1.8 (which currently is the only I have online atm) using EMPTY_RECORD returns an error during debuging when used with an Expiry Date field. 

I’m not sure if this is a bug in ETM 1.8, something outside the scope of the feature (and needs to be requested as an idea on the community), or if its something that has been added in a later version of ETM 😅. However could you perhaps test if using EMPTY_RECORD works for you in ETM 1.10?

Thank you both that is really helpful, I have just recently upgraded to ETM 1.10 so the date ranges is really useful.

If I wanted to return an array where the expiry date is blank how would I do that now?

Thanks

My first idea here was use of something like "EMPTY_RECORD" in an expiryDate-field during data mapping (similar to how one can lookup items with an emtpy CSG, Assyst24R2 Wiki: Integrations:assystETM 1.10 - assyst Request Mappers → Empty Records).

However for the CSG example this shows the use of  “shortCode” as the lookup parameter during the field datamapping. And atleast in IPaaS 1.8 (which currently is the only I have online atm) using EMPTY_RECORD returns an error during debuging when used with an Expiry Date field. 

I’m not sure if this is a bug in ETM 1.8, something outside the scope of the feature (and needs to be requested as an idea on the community), or if its something that has been added in a later version of ETM 😅. However could you perhaps test if using EMPTY_RECORD works for you in ETM 1.10?

Thanks Richard! I get the exact same error on version 1.10 so maybe outside of the scope of this feature. Potentially an idea for the community if I cant find anything. Thanks 😊

I tried to do this the assystREST. Initially I tried to look for items with an expiry date outside a sane range:
/items?productId=2450&expiryDate[exclude]=1900-01-01T00:00:00Z,2099-01-01T00:00:00Z
but this fails with an error

What does work (though isn’t mentioned in the docs) is to use = null:
/items?productId=2450&expiryDate=null

These queries will both be possible in the next release of ETM (year end) which allows you to manually specify REST queries for those scenarios that the UI doesn’t deal with.