2

I've got a bit of a quandary. I have a Looping Block that is supposed to parse through different column names to pull the appropriate Value from a DE. The problem is that the Variable that determines the column has the last character truncated when placed in the LookUp function, thus causing my look up to fail. When I call the Variable itself, it is not truncated. If I manually set the variable (e.g., SET @var = 'ThisValue'), it also doesn't truncate and works fine. It's only if the Variable is receiving it's Value from the Looping Block and then placed into the LookUp that it truncates the last character in the Value. Upon further testing, it seems like the issue specifically lies with whatever the last Value is.

Assumptions:

@PrimaryKey has already been set
@VarString = 'Column1Name|Column2Name|'

DataExtension Data Set:

PrimaryKey|Column1Name|Column2Name
PrimaryKey|Text for 1 |Text for 2

Code:

%%[
SET @VarString = Substring(@VarString,1,Subtract(Length(@VarString),1))
SET @rows = BuildRowsetFromString(@VarString,'|')
SET @rowCount = rowCount(@rows)

IF @rowCount > 0 THEN
  FOR @i = 1 TO @rowCount DO
    SET @row = row(@rows,@i)
    SET @Column = field(@row,1)
    SET @Display = LookUp('ent.DataExtension',@Column,'PrimaryKey',@PrimaryKey)
]%%
%%=v(@Display)=%%<br/>
%%[
NEXT @i
ENDIF
]%%

The error message I would receive in the above scenario would be:

●The Lookup function call failed because the specified lookup field does not exist
for the data extension Data Extension: ent.DataExtension
Invalid Field Name: Column2Nam
Function Call: LookUp('ent.DataExtension',@Column,'PrimaryKey',@PrimaryKey)
Message Context: Test
●Column 'Column2Nam' does not belong to table.

What I'm aiming for is this:

Text for 1
Text for 2

If I were to call on @Column by itself, it will display 'Column2Name' (assuming we already made it to the second value in the string). The error message only calls out the last Value, so I assume all the values before it worked fine, but I don't understand why it decides to truncate the last value.

Again, if I manually set the variable, it doesn't have an issue.

Example:

SET @Column = 'Column2Name'
SET @Display = LookUp('ent.DataExtension',@Column,'PrimaryKey',@PrimaryKey)

Lastly, because I know you may ask, if there's only a SINGLE value in the string, it will truncate the single value.

The string is built from reused code that is throughout an email (basically, this all is to display some copy in the footer depending on what content was displayed in the email), and the code concatenates tags together using Pipes in between. In the code example above, there is a Substring call to remove the last pipe from the string, but it doesn't itself remove the last character from the last value.

I can probably figure out a solution by not removing the final pipe and stopping one Loop short (so it doesn't try looping through a black value), but I just want to know WHY it's truncating and any solutions to not get it to truncate.

4
  • The way the code that you posted looks, it would error because @PrimaryKey is not being set. Also, there is a % missing on the delimiter before the NEXT. Please include the entire block of code if this is not it. Also, there should be a @ on your "rows" inside the row() function. So update SET @row = row(rows,@i) to SET @row = row(@rows,@i)
    – jake314159
    Jan 14 '20 at 21:22
  • Hey @jake314159, I edited the original post for clarity per your comment. The problem still stands. Thanks!
    – Brian C
    Jan 14 '20 at 21:28
  • Have you verified that each of the input strings will end with a pipe? because it sounds like your issue is coming from it taking the last char (via the substring) which works if there is an extra pipe, but would break it if that pipe was not there. Jan 14 '20 at 22:43
  • Correct. @VarString will always ends in pipe which is why I remove the final pipe since we are not adding any more to the string. If I call @Column outside of the lookup, it displays the value without being truncated. That is, if I use %%=v(@Column)=%% it will display "Column2Name" but when I put it in the LookUp, the error states that "Column2Nam" (missing the "e") does not belong to the table. If I hardcode "Column2Name" SET @Column = 'Column2Name', the lookup works fine. Basically if it's used in a lookup and specifically if it was fed a value from the looping block.
    – Brian C
    Jan 15 '20 at 17:09
4

I get the feeling that your pipe delimited string is not always ending with a pipe - meaning you are cutting off the last char of your final column.

To take out the potential issue of the string not ending in a pipe (meaning that your current ampscript will delete the final char of your string) I would look at doing the following, which basically just checks to make sure @Column has a value before running the lookup, etc.

%%[
Set @VarString = 'FieldA|FieldB|'
SET @rows = BuildRowsetFromString(@VarString,'|')
SET @rowCount = rowCount(@rows)
]%%
VarString: %%=v(@VarString)=%%<br><br>
%%[
IF @rowCount > 0 THEN
  FOR @i = 1 TO @rowCount DO
    SET @Column = ''
    SET @Display = ''
    SET @row = row(@rows,@i)
    SET @Column = field(@row,1)
    if NOT EMPTY(@Column) THEN
      SET @Display = LookUp('gg_testParse',@Column,'Email',"ggifford@degdigital.com")
]%%
Column %%=v(@i)=%%: %%=v(@Display)=%%<br/>
%%[
    endif

NEXT @i
ENDIF
]%%

If you want to keep your current way, I would put a conditional around it to verify that it is a pipe before deleting it. Something like:

Set @VarString = 'FieldA|FieldB|'
Set @LastChar = Substring(@VarString,Length(@VarString),1)
IF @LastChar == "|" THEN
  SET @VarString = Substring(@VarString,1,Subtract(Length(@VarString),1))
ENDIF

The @LastChar variable will pull the final char in the string. Then the conditional below it verifies it is a pipe. If that is true then it does your Substring() to remove the last pipe. BUT if the last char is not a pipe, then it will not remove the char.

5
  • Hey there! Yep, the string will always end with a pipe because the concat process is always SET @VarString = CONCAT(@VarString,@NewAddition,'|'), so it always ends in a pipe in anticipation of what the next addition will be. I can also verify that the value itself is not being truncated because as I mentioned, if I call @Column by itself %%=v(@Column)=%%, it is not truncated, but only truncates once I put it in the LookUp. Additionally, only truncates in the lookup if it's feed by the looping block--doesn't truncate if I hardcoded @Column's value.
    – Brian C
    Jan 15 '20 at 16:59
  • @BrianC - Without the full process, I am not sure I can help you further. What you currently displayed, I have run and it finishes successfully - meaning I have not been able to replicate your issue. I think the issue may stem then from some syntax or logic issue in a different part of your code that is affecting this lookup. Have you tried my code to verify it does not resolve the issue? Jan 15 '20 at 19:21
  • the only thing that you're "missing" is the creation of the string, which I added in my last comment. SET @NewAddition = 'Column1Name' @VarString = CONCAT(@VarString,@NewAddition,'|') and that's just repeated over and over, with @NewAddition being changed each time to create the string that is then used in the code block we've been discussion. There is no other HTML or ampscript since I'm developing this on it's own before placing it in an email context.
    – Brian C
    Jan 15 '20 at 19:37
  • I did make an edit to my code (not shown above) where I remove the substring/subtract from the rowset and then reduce my RowCount by 1 since there is always an ending pipe that would make it seem like there is an additional Null value at the end, and this "solves" the problem since the last "value" (null) would be the one that gets truncated, but the loop stops 1 short of the null value due to the reduction in the rowcount. Regardless, this doesn't answer why the final value is consistently getting truncated only when put in as a variable in the lookup function. I'm truly baffled.
    – Brian C
    Jan 15 '20 at 19:43
  • @BrianC - Unfortunately without me being able to replicate it, I cannot do much to help further debug it. At least it sounds like you have a solution, so you can continue forward. I will continue to fiddle around on this when I can and see if I can get more info on it. Jan 15 '20 at 21:29

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.