0

Hi I currently have a data extension with 15 columns that are being populated by an API call. Once a record has been added any one of the 15 Attribute Values can have a tracking code in it. I need to take those 15 codes and concat them into a single FEDex tracking code with a comma separating each code, but never a comma after the last code.

I have been playing around with CONCAT and this what I have so far, but honestly this feels like a bad word puzzle that I am not approaching the right way.

%%[
Set @contact_key = AttributeValue('contact_key')
Set @tracking_pram_one = AttributeValue('track_pram_one')
Set @tracking_pram_two = AttributeValue('track_pram_two')
Set @tracking_pram_three = AttributeValue('track_pram_three')
Set @tracking_pram_four = AttributeValue('track_pram_four')
Set @tracking_pram_five = AttributeValue('track_pram_five')
Set @tracking_pram_six = AttributeValue('track_pram_six')
Set @tracking_pram_seven = AttributeValue('track_pram_seven')

Set @URL = CONCAT('https://www.fedex.com/apps/fedextrack/?action=track&tracknumbers=', @tracking_pram_one, ",", @tracking_pram_two, ",", @tracking_pram_three, ",", @tracking_pram_four, ",", @tracking_pram_five, ",", @tracking_pram_six, ",", @tracking_pram_seven, '&cntry_code=us&locale=en_US')
]%%

1 Answer 1

2

EDIT

I added in some comments in the code to better explain what it is doing. Hopefully that will alleviate some of your confusion.


The easiest and most elegant way I can think of to do this is to fill in those vars via a for loop.

Inside this loop you would dynamically set your track_pram_X via a Rowset built from a String you created. You would then push this into a finalStr variable that would house the whole query string value.

This would let you only put comma separator in those with values - but it does create a leading comma. To remove this, you just need to add in a Substring against the final result outside of the loop. Then you can do your original concat with a very slight change to use @finalStr instead of listing each param with a comma.

Something like:

%%[

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

/* numStr is a list of the number of tracking parameters. e.g. tracking_pram_one would be 'one' and tracking_pram_two would be 'two' in the list. This is not a list of the values for the tracking_prams */

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)

/* attrName is used to piece together the constant 'tracking_pram_' to the dynamic value pulled from numStr (e.g. 'one' or 'two' to create 'tracking_pram_one' as the output */

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

/* FieldSet is used to dynamically create AMPscript to set a dynamically named variable to a dynamically named attribute. It takes the attrName and creates an AMPscript variable with that name (e.g. @tracking_pram_one) and then sets that to equal the AttributeValue of that (e.g. AttributeValue("tracking_pram_one")). This is then compiled and output by the 'TreatAsContent()' function. */

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

/* tempValue is then created to house the actual value of the above set dynamic variable. Essentially meaning if in the sendable data 'tracking_pram_one is equal to '12345667' then tempValue would equal '12345667' */

  IF NOT EMPTY(@tempValue) THEN

/* If tempValue is not empty, then it gets added into the 'final string' listing all the parameters */

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

  ENDIF

NEXT @i

/* This will cut off the leading comma via a Substring */

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

/* This concats in the finalStr into your URL */

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

]%% 

EXAMPLE OUTPUT:

enter image description here

from DE:

enter image description here

5
  • Not sure I I am understanding what might be happening here, but in the email when clicked on the link returned no tracking codes, just the first and last part of the URL. Does this not skip to the next value if there are empty columns? Commented Feb 6, 2020 at 19:53
  • You verified that the sendable data you are testing on has the fields with values in them? I just want to rule out the simple solution first. I utilized this on my own test data and got results, so wanna ensure its not data issue before addressing the code. Commented Feb 6, 2020 at 21:44
  • @JakeLeffler - I posted images of my output in hopes that will help us find the issue. Commented Feb 6, 2020 at 21:54
  • Maybe that's the issue here, the code in the columns will always be a variable data of at least 100 chars, still not exceeding the max length of a url but they could look something like this Commented Feb 6, 2020 at 22:03
  • Not sure how to add images, have barely used Stack Exchange before Commented Feb 6, 2020 at 22:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .