0

I have a data extension with a CustomerId(PK) that has multiple Productid's as Productid1, ProductId2, ProductId3...ProductId10.

Some of the Productid's are empty for some Customers and I'm trying to write some AMPscript if the productIdx is empty to show the next one.

I'm trying to display 6 products in an email and at the moment when I preview the email, if from the first six Products one doesn't have a value(is empty) I get the error "The subscriber preview failed to generate. Review the details, correct all issues, and try again. Product1 not found"

I want to skip the product that is NULL and go to the next one available.

%%[
SET @customerId = [customerId]

SET @customer_rows = LookupRows("PRL","customerId",@customerId)
IF Rowcount(@customer_rows) > 0 THEN
SET @customer_row = ROW(@customer_rows, 1)

SET @product_count = 0

SET @productId1 = FIELD(@customer_row, "productId1")

IF NOT EMPTY(@productId1) THEN
SET @product_rows = LookupOrderedRows("PI_UK", 1,"Normal_SalePrice Desc", "ProductCode", @productId1)

IF RowCount(@product_rows) > 0 THEN

SET @product_count = ADD(@product_count,1)
ELSE

SET @product_count = ADD(@product_count,0)
ENDIF
ELSE
SET @product_count = ADD(@product_count,0)
ENDIF



SET @productId2 = FIELD(@customer_row, "productId2")
IF NOT EMPTY(@productId2) THEN
SET @product_rows = LookupOrderedRows("PI_UK", 1,"Normal_SalePrice Desc", "ProductCode", @productId2)
IF RowCount(@product_rows) > 0 THEN
SET @product_count = ADD(@product_count,1)
ELSE
SET @product_count = ADD(@product_count,0)
ENDIF
ELSE
SET @product_count = ADD(@product_count,0)
ENDIF

SET @productId3 = FIELD(@customer_row, "productId3")
IF NOT EMPTY(@productId3) THEN
SET @product_rows = LookupOrderedRows("PI_UK", 1,"Normal_SalePrice Desc", "ProductCode", @productId3)
IF RowCount(@product_rows) > 0 THEN
SET @product_count = ADD(@product_count,1)
ELSE
SET @product_count = ADD(@product_count,0)
ENDIF
ELSE
SET @product_count = ADD(@product_count,0)
ENDIF

and so on till ProducId10...

I have uploaded a photo with the PRL Data extension

enter image description here

enter image description here

  • If you notice, the error references Product1 , but in your ampscript and DE you have productId1 - so I believe the part you have in your question is not the part that contains the issue. I would do a search on your code and find the reference to Product1 and troubleshoot there. – Gortonington Oct 28 '19 at 18:47
2

Through a bit of looping, you can probably get this fairly well automated.

First I would create a delimited string with each of the product field names in it. E.g. 'productId1|productId2|productId3|productId4|productId5|productId6|productId7|productId8|productId9|productId10' and then use the BuildRowsetFromString function to turn it into a rowset you can iterate through.

From there you can then dynamically set the variables (Product 1 - 6) via some quick IF statements and a FOR loop. You would basically just skip Product2 setting until a value is present. So if ProductId1 has a value, then Product1 = ProductId1, but if ProductId2 is null, then Product2 would equal ProductId3, etc.

After that you can just output Product1-6 to display the corresponding product, regardless if its productid1 or productid10.

See below for sample code:

%%[
SET @customerId = [customerId]
SET @customer_rows = LookupRows("PRL","customerId",@customerId)

IF Rowcount(@customer_rows) > 0 THEN
    SET @customer_row = ROW(@customer_rows, 1)
    SET @product_count = 0
    SET @productStr = 'productId1|productId2|productId3|productId4|productId5|productId6|productId7|productId8|productId9|productId10'
    SET @productRowSet = BuildRowsetFromString(@productStr)
    FOR @i = 1 TO Rowcount(@productRowSet) DO
        IF @product_count < 6 THEN /* Limit returned products to 6 total */
            SET @tempProduct = '' /* Reset prior to next iteration */
            SET @productRow = Row(@productRowSet,@i)
            SET @fieldName = Field(@productRow,1)
            SET @tempProduct = Field(@customer_row,@fieldName, 0)

            IF NOT EMPTY(@tempProduct) THEN
                SET @product_count = ADD(@product_count,1)
                SET @FieldSet = TreatAsContent(CONCAT('%','%[SET @Product', @product_count, ' = @tempProduct]%', '%'))
            ENDIF
        ENDIF
    NEXT @i 
ENDIF
]%%

%%[ IF NOT EMPTY(@Product1) THEN ]%%
<div>Product1: %%=v(@Product1)=%%</div>
%%[ ENDIF
    IF NOT EMPTY(@Product2) THEN ]%%
<div>Product2: %%=v(@Product2)=%%</div>
%%[ ENDIF
    IF NOT EMPTY(@Product3) THEN ]%%
<div>Product3: %%=v(@Product3)=%%</div>
%%[ ENDIF
    IF NOT EMPTY(@Product4) THEN ]%%
<div>Product4: %%=v(@Product4)=%%</div>
%%[ ENDIF
    IF NOT EMPTY(@Product5) THEN ]%%
<div>Product5: %%=v(@Product5)=%%</div>
%%[ ENDIF
    IF NOT EMPTY(@Product6) THEN ]%%
<div>Product6: %%=v(@Product6)=%%</div>
%%[ENDIF]%%
| improve this answer | |
1

You will have to add the 0 option for the FIELD function. More info on the Field function.

Indicates whether to return a NULL value or an error if the specified data extension field does not exist. Adding a 0 will return a NULL value if data extension field does not exist. Defaults to 1.

set @productId1 = field(@row,"productId1", 0) /* note the 0 */

if @productId1 == "NULL" then set @productId1 = "not defined or is nullable"
endif
| improve this answer | |
  • thanks but that doesn't work unfortunately – Duicug Oct 28 '19 at 15:15
  • 1
    It does work. I just tested it. – Jackson Chen Oct 28 '19 at 15:19
  • this just gives me the message "not defined or is nullable" when finds a product that is NULL. I need it to go to the next available product. – Duicug Oct 30 '19 at 11:33
  • Exactly. That's what my IF statement produced. You'll need to apply it to your code. – Jackson Chen Oct 30 '19 at 14:39

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.