HTML template to never change but to reference new row in lookup table

Question

Trying to figure it out a way to code a template that will be populated from a lookup table which will be updated every week with a new row. Typically I would have an unique identifier in both HTML and lookup table, e.g. @week then the script will go on the week row and get all the content to populate the email.

But, I don't want to change the week number in ampscript every week like in IF @week == "40" then @content1 is this, @content2 is that, etc

I want to be able to code the template once and with ampscript to identify automatically the current deployment row and populate properly for every deployment. I also want when I open an email from 3 weeks ago to see the content from 3 weeks ago in inbox and when I click view online and not the current content.

How can I do that? Thanks!

Answer 1

So to accomplish this, you would need to have 2 DEs that are separate from your sendable DE.

DE1: CONTENTDE

This DE would require the field RowID as the primary key and a date field named CreatedDate. This would house all the information you want in your DE to fill in for the email that week/day/month/etc.

DE2:VAWPDE

This DE would require at least a dual set of primary keys. SubscriberKey and JobID. You would then also require the field RowID to be filled in at a later date. This DE will retain all the information from at send time so that if the email is opened as a VAWP, it will display that information instead of rerunning and displaying the latest.

THE SCRIPT

From there, you would implement the below script in your email to gather the correct Row that you want to use for creating your Email:

%%[ 
SET @subkey = _subscriberkey
SET @JobID = JobID
SET @vawpDE = /* A storage DE */
SET @contentDE = /* The DE with your content stuff */

IF _MESSAGECONTEXT == "VAWP" THEN
  SET @rowID = LOOKUP(@vawpDE, "rowID", "JobID",@JobID,"Subkey",@subkey)
  SET @rowset = LookupRows(@contentDE,1,"RowID",@rowID)
  SET @row = Row(@rowset,1)
ELSE
  SET @rowset = LOOKUPORDEREDROW(@contentDE,1,"CreatedDate DESC","Active","Y")
  SET @row = Row(@rowset,1)
  SET @rowID = Field(@row,"RowID")
  Upsertde(@vawpDE,2,"SubscriberKey",@SubscriberKey,"JobID",@JobID,"RowID",@rowID)
ENDIF

SET @myValue = FIELD(@row,"myValue")
/* etc.... */

]%%

This script will look and see if it is running in a VAWP environment, if it is, it will look up the rowID from the VAWPDE and use that for the lookup to the contentDE row. If it is at send time, it will instead lookup the most recent row (CreatedDate DESC) and pull that row in. It will then upsert this RowID to the VAWP DE for use in the future.

From there you do your normal pull of data fields from rows via the FIELD() function.