Lookup Row for an API Object Array in Ampscript

Question

I have used the InvokeRetrieve AMPScript function on a ListSubscriber object to retrieve a list of list IDs and statuses that a subscriber belongs to.

All the documentation I can find online usually will show a for loop to display all the sets of data in the array. What I want to be able to do is do a lookup into this array to find a specific value.

For Example:

set @rr = CreateObject("RetrieveRequest")

SetObjectProperty(@rr, "ObjectType", "ListSubscriber")
 AddObjectArrayItem(@rr,"Properties","ListID") 
 AddObjectArrayItem(@rr,"Properties","SubscriberKey")
 AddObjectArrayItem(@rr,"Properties","Status")

set @sfp = CreateObject("SimpleFilterPart")
 SetObjectProperty(@sfp, "Property", "SubscriberKey")
 SetObjectProperty(@sfp, "SimpleOperator", "equals")
 AddObjectArrayItem(@sfp, "Value", @subkey)

SetObjectProperty(@rr, "Filter", @sfp)

set @listStatuses = InvokeRetrieve(@rr, @rrStatus, @rrRequestID)

Might return an API object with array

Subscriber Key: email1
ListID: 101
Status: Active

Subscriber Key: email2
ListID: 102
Status: Active

Subscriber Key: email3
ListID: 103
Status: Unsubscribed

What is the best way for me to lookup into @listStatuses by ListID to find Status?

Thanks!

Answer 1

Unfortunately you cannot do a lookup on anything that is not a DataView or Data Extension via AMPScript.

To that extent, when working in AMPScript - you are left with 2 options:

1. Adding a second Simple Filter Part (SFP), creating a complex filter. This SFP would utilize the operator 'IN' to segment the results to just the group of lists you want to look up.

e.g.

/* Create LeftOperand SimpleFilterPart */

  Set @sfp1 = CreateObject("SimpleFilterPart")
  SetObjectProperty(@sfp, "Property", "SubscriberKey")
  SetObjectProperty(@sfp, "SimpleOperator", "equals")
  AddObjectArrayItem(@sfp, "Value", @subkey)

/* Create RightOperand SimpleFilterPart */

  Set @sfp2 = CreateObject("SimpleFilterPart")
  SetObjectProperty(@sfp, "Property", "ListID")
  SetObjectProperty(@sfp, "SimpleOperator", "IN")
  AddObjectArrayItem(@sfp, "Value", @ListIDs)

/* Adding two Simple filters together to create a Complex filter */

  Set @cf1 = CreateObject("ComplexFilterPart")
  SetObjectProperty(@cf1,"LeftOperand",@sfp1)
  SetObjectProperty(@cf1,"RightOperand",@sfp2)
  SetObjectProperty(@cf1,"LogicalOperator","AND")

SetObjectProperty(@rr,"Filter",@cf1)

2. If you cannot use the filter from above, you will need to do your for loop with a conditional in it to make sure to only pull/record entries that fit your criteria.

e.g.

set @listStatuses = InvokeRetrieve(@rr, @rrStatus, @rrRequestID)

FOR @i=1 TO Rowcount(@listStatuses) DO

  SET @row = ROW(@listStatuses, @i)

  SET @SubscriberKey = Field(@row, "SubscriberKey")
  SET @ListID = Field(@row, "ListID")
  SET @Status = Field(@row, "Status")

  IF @ListID == @ListID1 THEN

    SET @doAction = 1

  ELSEIF @ListID == @ListID2 THEN

    SET @doAction = 1

  ELSEIF @ListID == @ListID3 THEN

    SET @doAction = 1
  /* Etc.... */
  ELSE

    SET @doAction = 0

  ENDIF

  IF @doAction = 1 THEN

   /* Action you wish to do */

  ENDIF

NEXT @i

If your List IDs are saved inside of another rowset, you can also have a for loop inside of your for loop to loop through and verify via conditionals again. I do not recommend this unless you do not expect high volume as this will be a large draw on resources and cause high run times (potentially causing a time-out).

(Assuming this is a non-sending environment) I would also recommend looking into using WSProxy for your API calls instead of the AMPScript method.

This will lead you into using SSJS (SFMC version) which can provide more flexibility on interaction as it will be returned in a JSON object. Feel free to look up basic JavaScript methods to pull only specific values from Arrays/Objects.