How to reference another data extension using ampscript to populate dynamic values within a CloudPage

Question

Still learning ampscript and its functions and need to ask a question as I'm going around in circles.

I have an email which is sent to a list of subscribers. Within the send data extension I have a field 'Store' which varies based on the subscriber. I've used a PURL to bring in the customers email address and then find the customers Store using a LookUp on the send data extension. This part of my ampscript works.

What I also want to be able to do is display further information not stored in the send data extension, for instance, the store address, phone number etc.

I've tried multiple ways to bring this information but keep getting a 500 error message.

Below is the latest code I tried.

    %%[/* AMPScript Email Content initialization <div style="display:none"> */

      SET @EmailAddress = RequestParameter("emailaddress")
      SET @Store = LookUp("TEST DE", "Store", "EmailAddress", @EmailAddress)

      @rowset = LookupRows('Store_Properties','Store', Store)

      @Address = field(row(@rowset,1), 'Address')
      @Phone = field(row(@rowset,1), 'Phone')
      @OpeningHours = field(row(@rowset,1), 'OpeningHours')
      @OpeningDays = field(row(@rowset,1), 'OpeningDays')
 /*</div>*/ 

]%%

Any suggestions as to how I can retrieve the information requested within the 'Store_Properties' data extension?

Thanks, Bevan

Answer 1

Change this line:

@rowset = LookupRows('Store_Properties','Store', Store)

To:

@rowset = LookupRows('Store_Properties','Store', @Store)

To reference an ampscript variable you need to add the @ symbol. Also it is not recommended to wrap your ampscript blocks in hidden div tags as it can have some bizarre effects sometimes once the code is executed and the HTML rendered.

The 500 error is probably being caused by the fact that you are trying to access rows in an empty rowset (the result of you LookupRows function). I would also recommend adding an if statement to confirm you have data for that store. Something like this:

IF RowCount(@rowset) > 0 THEN
  @Address = field(row(@rowset,1), 'Address')
  @Phone = field(row(@rowset,1), 'Phone')
  @OpeningHours = field(row(@rowset,1), 'OpeningHours')
  @OpeningDays = field(row(@rowset,1), 'OpeningDays')
ELSE THEN
  /* Some sort of default fallback */
ENDIF