1

I have 3 salesforce objects: - PersonAccount - MyWatches (Fields:PersonAccountId, ProductId, Status = Owner) - contains the list of watches bought by the PersonAccount - Product2 (Fields: Series) - contains product details

PersonAccount is linked to MyAccessories and the MyAccessories records contain a Product ID (lookup to the Products object). A PersonAccount can have multiple MyAccessories records.

I have used a loop to retrieve all the accessories purchased by the PersonAccount. So far so good.

%%[VAR @SubscriberKey 
SET @SubscriberKey= _SubscriberKey 
IF NOT EMPTY(@Subscriberkey) THEN 
SET @ContactTable = RetrieveSalesforceObjects("Account","Id","Contact_ID__pc", "=", @SubscriberKey) 
SET @ContactRow = ROW(@ContactTable,1) 
SET @PersonAccountId = FIELD(@ContactRow,"Id") 
ELSE 
ENDIF]%%
    %%[IF NOT EMPTY(@PersonAccountId) THEN 

SET @MyWatchesTable = RetrieveSalesforceObjects("My_Watches__c","Product__c","Person_Account__c", "=", @PersonAccountId,"Status__c","=", "Owner") 
SET @rowCount = RowCount(@MyWatchesTable) 
IF @rowCount > 0 THEN 
FOR @counter = 1 to @rowCount DO 
SET @MyWatchesRow = Row(@MyWatchesTable, @counter) 
SET @Product = Field(@MyWatchesRow,"Product__c") ]%%

%%=v(@counter)=%% Product: %%=v(@Product)=%% 

%%[ next @counter ]%% 
%%[ else ]%% 
No records found 
%%[ endif ]%%

%%[ENDIF]%%

As you can see from the image below it retrieves multiple product Ids but only the series associated to the last product Id.

MyWatches Loop

I need to retrieve the value in the Series field stored on the Product2 object associated with the products the PersonAccount has purchased.

I tried to use a second LOOP but it only retrieves the series for the last purchased product id. Is it possible to retrieve it for all? The end goal is to then use the series retrieved to tick multiple checkboxes on the page.

    %%[ VAR @ProductTable, @productCount, @productcount 
    SET @ProductTable = RetrieveSalesforceObjects("Product2","Series__c","Id", "=", @Product) 
    set @productCount = RowCount(@ProductTable) 
    if @productCount > 0 then 
    for @productcount = 1 to @productCount do 
    VAR @ProductRow, @Series 
    set @ProductRow = Row(@ProductTable, @productCount) 
    set @Series = Field(@ProductRow, "Series__c") ]%% 

Prod %%=v(@productCount)=%% Series: %%=v(@Series)=%%

 %%[ next @productCount ]%% 

 %%[ else ]%% 
 No product found 

 %%[ endif ]%%
3

You would need to do the lookup to get product series inside the for look for your product. The issue you are running into is that after the for loop, the @Product variable is constant (on the last value) so it will not cycle through each product.

See below, which should output correctly:

%%[
VAR @SubscriberKey 

SET @SubscriberKey= _SubscriberKey 

IF NOT EMPTY(@Subscriberkey) THEN 

    SET @ContactTable = RetrieveSalesforceObjects("Account","Id","Contact_ID__pc", "=", @SubscriberKey) 
    SET @ContactRow = ROW(@ContactTable,1) 
    SET @PersonAccountId = FIELD(@ContactRow,"Id") 

ENDIF

IF NOT EMPTY(@PersonAccountId) THEN 

    SET @MyWatchesTable = RetrieveSalesforceObjects("My_Watches__c","Product__c","Person_Account__c", "=", @PersonAccountId,"Status__c","=", "Owner") 
    SET @rowCount = RowCount(@MyWatchesTable) 
    IF @rowCount > 0 THEN 
        FOR @counter = 1 to @rowCount DO 
        SET @MyWatchesRow = Row(@MyWatchesTable, @counter) 
        SET @Product = Field(@MyWatchesRow,"Product__c")

        SET @ProductTable = RetrieveSalesforceObjects("Product2","Series__c","Id", "=", @Product) 
        set @ProductRow = Row(@ProductTable, @productCount) 
        set @Series = Field(@ProductRow, "Series__c") 

    ]%% 

        %%=v(@counter)=%% Product: %%=v(@Product)=%%
        <br>
        Product %%=v(@counter)=%% Series: %%=v(@Series)=%%

        %%[ 
        next @counter 

    else 
    ]%% 
No records found 
%%[ 
    endif 

ENDIF
]%%
| improve this answer | |
0

Thank you @Gortonington

The code almost worked, I had to set the productCount variable!

  %%[VAR @SubscriberKey set @SubscriberKey= _SubscriberKey 
     IF NOT EMPTY(@Subscriberkey) THEN 
set @ContactTable = RetrieveSalesforceObjects("Account","Id","Contact_ID__pc", "=", @SubscriberKey) 
set @ContactRow = ROW(@ContactTable,1) 
     set @PersonAccountId = FIELD(@ContactRow,"Id") 
     ELSE 
     ENDIF]%%
%%[IF NOT EMPTY(@PersonAccountId) 
       THEN set @MyWatchesTable = RetrieveSalesforceObjects("My_Watches__c","Product__c","Person_Account__c", "=", @PersonAccountId,"Status__c","=", "Owner") 
       SET @rowCount = RowCount(@MyWatchesTable) 
       IF @rowCount > 0 THEN 
       FOR @counter = 1 to @rowCount DO 
       SET @MyWatchesRow = Row(@MyWatchesTable, @counter) 
       SET @Product = Field(@MyWatchesRow,"Product__c") 
    SET @ProductTable = RetrieveSalesforceObjects("Product2","Series__c","Id", "=", @Product)
    set @productCount = RowCount(@ProductTable)
    set @ProductRow = Row(@ProductTable, @productCount) 
    set @Series = Field(@ProductRow, "Series__c") ]%%

     Product %%=v(@counter)=%%: %%=v(@Product)=%%
     Product %%=v(@productCount)=%% Series: %%=v(@Series)=%%
     %%[ next @counter ]%% %%[ else ]%% No records found %%[ endif ]%%
      %%[ENDIF]%%
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.