Ampscript FOR Loops results to retrieve product using a second Loop

Question

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.

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 ]%%

Answer 1

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
]%%

Answer 2

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]%%