Now, you may be confused by what the fudge I mean by ‘Dynamically Creating Variables’ as in its essence, a variable is itself, dynamic. Basically what I mean is I am going to show you some AMPscript that will loop through and create multiple variables without needing to write out each one. Pretty neat, right?

A great use for this is when you have an email that requires multiple variables that are very similar to each other. Now this would normally mean that you would need to write out each and every variable and its value definition with an AttributeValue() to ensure it can handle null/non-existent fields. Not only that, but if the email is used in multiple campaigns, the number of variables you need can consistently change, causing you to duplicate effort and use conditionals to differentiate.

In other words….a TON of work and code. Making things messy to read, messy to upkeep and ripe with risk. To get around this, you can instead just create the variables dynamically inside of a for loop. Another great feature of this is that if you ever need to go beyond the tracking parameters you currently use, you don’t need to change a thing in the code, only the initial rowset.

Lets take a look at the basic code parts that we will be using

%%%%[
SET @deRows = LookupRows(...)

FOR @i = 1 TO RowCount(@deRows) DO

  SET @row = Row(@deRows,@i)
  SET @tempValue = Field(@Row,'Field')

  SET @FieldSet = TreatAsContent(CONCAT('%%','%%[SET @Field', @i, ' = @tempValue]%%', '%%'))

NEXT @i 
]%%%%

Not too complex, right? Only a couple lines of code and blamo, dynamically created variables. Sweet! BUT that is just the beginning. There are so many ways to adjust this to fit multiple needs. Before we get into that, lets go over the above and make sure we are 100% solid on that first.

First we start out with a lookupRows to gather a rowset to loop through for our FOR loop. This does not need to be a lookupRows, but could be a BuildRowsetFromString or really anything that would house a rowset in AMPscript.

SET @deRows = LookupRows(...)

Next we build our FOR loop going from 1 to the max length of the rowset. Inside this loop we are then going to set the row and the ‘tempValue’.

FOR @i = 1 TO RowCount(@deRows) DO

  SET @row = Row(@deRows,@i)
  SET @tempValue = Field(@Row,'Field')

...

NEXT @i

After we set the tempvalue, we are all set to start dynamically creating the variables. This next gem of a line is the magic bullet.

SET @FieldSet = TreatAsContent(CONCAT('%%','%%[SET @Field', @i, ' = @tempValue]%%', '%%'))

Lets deconstruct this for a second. The outer layer is a ‘TreatAsContent()’ call which is being used to execute the AMPscript created inside of it. The next layer is a CONCAT() function. This is being used to create a string of AMPscript using text and AMPscript variables. Do notice that I have the %% separated inside the CONCAT(). If you keep them together this can create very odd behavior, if it even works.

So basically, above we then create the following AMPscript string after the CONCAT completes. %%[SET @Field1 = @tempValue]%% (assuming we are on the first iteration of the For loop.

This string is then executed via the TreatAsContent() function which would then set a variable (named @Field1) to the @tempValue value. So lets say that @tempValue was ‘Sausage’ (gathered via the iteration in the FOR loop from the rowset. This is then pushed in as the value of @Field1 due to our above code.

To give a bit more of a ‘real world’ example of using this, I am going to create a script based on the below scenario:

Inside this email, there is a link to a package tracking url that can contain multiple tracking numbers (up to 15). The caveat being that not each of these will be filled consecutively, so it could be that the record has tracking parameter 1, 5, 7, and 15 filled – but the rest are all null.

In order to keep this from breaking the string, you need to dynamically concat these values together so that there are no extra commas, etc. This can be done with a bunch of IF conditions and CONCAT functions, but that is a big ol’ mess.

To get around that, we are going to dynamically create not just the variables to hold the value, but also the comma delimited string that contains all the tracking numbers.

Setting up the Rowset:

set @numStr = "one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen"
set @numRowSet = BuildRowsetFromString(@numStr,',')

Basically what this is doing is taking a comma delimited string and turning it into a rowset via BuildRowsetFromString. This rowset will be used to define the names and the attributes used inside the for loop.

Next we create the for loop and inside that we set the temporary variables that we need.

FOR @i = 1 TO Rowcount(@numRowSet) DO

  SET @row = Row(@numRowSet,@i)
  SET @tempName = Field(@row,1,0)
  SET @attrName = CONCAT('track_pram_',@tempName)

....

NEXT @i

This basically takes the value inside the rowset (e.g. first iteration would be a value of ‘one’) and fills it into @tempName. We then combine this with the constant part of the variable/attribute (‘track_pram’ + ‘one’ = ‘track_pram_one’) to get the final name of the variable.

Next we create the variable:

  SET @FieldSet = TreatAsContent(CONCAT('%%','%%[SET ', CONCAT('@',@attrName), ' = ATTRIBUTEVALUE("',@attrName,'")]%%', '%%')) 

Here we push the @attrName into the variable name as well as the AttributeValue() function to define that variable to equal the value of that attribute inside the sendable data.

So for instance if inside the sendable data, ‘Tracking_Pram_one’ equaled ‘123456’ then using the above, your new variable @Tracking_Pram_one would now equal ‘123456’.

Now we need to get that value inside a static named variable to create the final string.

  SET @tempValue = TreatAsContent(CONCAT('%%','%%=v(',CONCAT('@',@attrName),')=%%','%%'))

  IF NOT EMPTY(@tempValue) THEN

    SET @finalStr = CONCAT(@finalStr,',',@tempValue))

  ENDIF

Basically what this does is use the ‘TreatAsContent()” function to output the dynamically created variable value and store it inside the @tempValue variable.

From there we then CONCAT this value onto the @finalStr to link them all together. Now, there is a conditional around it to only do this if the @tempValue is not empty to prevent any unneeded commas.

Keep in mind that in its current state, that @finalStr is going to begin with a comma on the first output. e.g. ,one,two,three. In order to address this, once the FOR loop has completed, we can use a Substring() to remove that.

SET @finalStr = SUBSTRING(@finalStr,2,LENGTH(@finalStr))

Using the above will remove the leading comma from the string, giving us the comma delimited string of tracking numbers we wanted.

From there we just concat this into our URL and we then have the completed url to push into your HTML.

Set @URL = CONCAT('https://www.fedex.com/apps/fedextrack/?action=track&tracknumbers=', @finalStr, '&cntry_code=us&locale=en_US')

Example Output:

https://www.fedex.com/apps/fedextrack/?action=track&tracknumbers=123456,234567,345678&cntry_code=us&locale=en_US’)

To put it all together…

%%%%[

set @numStr = "one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen"
set @numRowSet = BuildRowsetFromString(@numStr,',')
set @finalStr = '';

FOR @i = 1 TO Rowcount(@numRowSet) DO

  SET @row = Row(@numRowSet,@i)
  SET @tempName = Field(@row,1,0)
  SET @attrName = CONCAT('track_pram_',@tempName)

  SET @FieldSet = TreatAsContent(CONCAT('%%','%%[SET ', CONCAT('@',@attrName), ' = ATTRIBUTEVALUE("',@attrName,'")]%%', '%%')) 
  
  SET @tempValue = TreatAsContent(CONCAT('%%','%%=v(',CONCAT('@',@attrName),')=%%','%%'))

  IF NOT EMPTY(@tempValue) THEN

    SET @finalStr = CONCAT(@finalStr,',',@tempValue))

  ENDIF

NEXT @i

SET @finalStr = SUBSTRING(@finalStr,2,LENGTH(@finalStr))

Set @URL = CONCAT('https://www.fedex.com/apps/fedextrack/?action=track&tracknumbers=', @finalStr, '&cntry_code=us&locale=en_US')

]%%%% 
Tags: , , , , ,
Subscribe
Notify of
guest
8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Ed Gray
Ed Gray
3 years ago

Of course, I read this after just creating a reporting email with hundreds of variables…great to know for next time. Thanks!

Liz Davalos
Liz Davalos
3 years ago

Hi, I am trying and failing to adapt this to my situation, not sure what I’m missing. I have a DE where I am storing a bunch of variable (columns: code, var). I want to use the above code to output all of the variables with the code being the var name and the var being the output. Here’s what I did, I’m not great at loops yet so I’m sure I’m missing something obvious.

%%[
SET @deRows = LookupRows(‘Variables’,’Code’,@varCode)

FOR @i = 1 TO RowCount(@deRows) DO

SET @row = Row(@deRows,@i)
SET @tempValue = Field(@row,’var’)
set @tempName = Field(@row,’code’)

SET @FieldSet = TreatAsContent(CONCAT(‘%’,’%[SET @tempName’, ‘ = @tempValue]%’, ‘%’))

NEXT @i
]%%

Thank you!!!

Johannes
Johannes
3 years ago

Just out of interest: Does this really work? Without TreatAsContent the set@tempValue would look like: %%[ Set @tempValue = %%=v(@dynamicAttrName)=%% ]%%. Is it really possible to write inline Ampscript inside an AmpScript Block? Never seen this

Something must be wrong with the ending of ampscript blocks and inline ampscript.

Also you are opening an AmpScript Block inside an AmpscriptBlock in row 13, i guess this shouldn’t work either.

Last edited 3 years ago by Johannes
Jonas
Jonas
3 years ago

Hi, this technique is absolute game changer and I’ve been using it a lot throughout my last implementations.
Our rowsets typically are data extensions with content stored for dynamic messages or pages.

From this I want to report an edge case, but come to think of it, maybe not so much of an edge case after all, to save others the pain. Myself, I’ve run into it twice in the last weeks:

Suppose your @tempvalue was not ‘Sausage’ but “it’s a sausage” then the whole thing starts acting weird, because the TreatAsContent function interprets the ‘ character in the word “it’s” as “end of string”, causing ugly debug sessions.

If your dynamically created result goes into an HTML context (careful if not!), say an email, an easy solution for this is to simply escape single quotes in @tempvalue before you start the treatAsContent function.

hope this doesn’t get lost in the formatting of this comment – what the following code does is, it replaces potential single quotes in @tempvalue with the corresponding html entity & # 3 9 ; (spaces inserted so it doesn’t get interpreted)

SET @tempValue = Field(@Row,’Field’)
/* new */
SET @tempvalue = Replace(@tempvalue,”‘”,”'”)
/* end new */

hope this saves someone a grey hair or two.
cheers

George Vlahos
George Vlahos
1 year ago

hi Gortonington, thank you very much for this, extremely helpful.

One, question, is the
“SET @FieldSet =” required?

I have been testing just doing
TreatAsContent(CONCAT(‘%’,’%[SET ‘, CONCAT(‘@’,@attrName), ‘ = ATTRIBUTEVALUE(“‘,@attrName,'”)]%’, ‘%’))
and it seems to be setting the variables ok.

Vishal Kumar C V [SFMCninja]
Vishal Kumar C V [SFMCninja]
7 months ago

Thanks, Greg, this has been very useful.