2

Hopefully someone might be able to help me. I'm fairly new to MC and AMPScript . What I am trying to do is send out an email that includes the Person's Business ID. The issue we come up against is that if a person is associated to more than one business. I don’t want to send an individual email per business so is there a way to write a script to output all business IDs for a specific email address or subscriber key that will populate into the email? Our setup has duplicated data based per business. So if i have 5 business i will be in the data 5 times, what i need is a script that see's those 5 email address and merges the business ID into 1 email when it de dupes on a send.

Eg:

  • Sub key = 12345 - email add= abc@def.com - BusinessID = 12345
  • Sub key = 12345 - email add= abc@def.com - BusinessID = 12346
  • Sub key = 12345 - email add= abc@def.com - BusinessID = 12347
  • Sub key = 12345 - email add= abc@def.com - BusinessID = 12348
  • Sub key = 12345 - email add= abc@def.com - BusinessID = 12349

Email output would be Hi Business owner.

We are just updating our records as we currently have the following business IDs on file for you 12345, 12346, 12347, 12348, 12349

Your help would be much appreciated.

Here is the code used based on Adam's recommendations

%%[
var @rows, @row, @rowCount, @returnRows, @Subscriber Key, @i

set @Subscriber Key = AttributeValue("_Subscriber Key") 
set @returnRows = 0 /* 0 means all, max 2000 */
set @rows = LookupOrderedRows("Master_Indiv_Business",@returnRows,"Business_ID asc","Subscriber Key", @Subscriber Key)

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

  var @Business_IDs
  set @Business_IDs = ""

  for @i = 1 to @rowCount do

    var @Business_ID

    set @row = row(@rows,@i) /* get row based on counter */
    set @Business_ID = field(@row,"Business_ID")

    if @i < @rowCount then
      set @Business_IDs = concat(@Business_IDs, @Business_ID,", ")
    else
      set @Business_IDs = concat(@Business_IDs, @Business_ID)
    endif

  next @i 

else 

    write(concat("<br>no rows found"))

endif 
]%%
Hi Business owner.
<br><br>We are just updating our records as we currently
have the following Business IDs on file for you %%=v(@Business_IDs)=%%.
  • You can't have spaces in the SubscriberKey variable name -- in the var row and in your lookup row. – Adam Spriggs Jan 31 '18 at 22:42
  • Hi Again, If i remove the space I get a – Andy Feb 1 '18 at 0:35
  • I've updated my answer below -- to illustrate my point. – Adam Spriggs Feb 1 '18 at 13:00
2

Welcome to SFSE. I'd recommend reviewing all of the Data Extension lookup functions in the official documentation. I also have some examples here on my blog.

I'd approach it like this:

%%[
var @rows, @row, @rowCount, @returnRows, @SubscriberKey, @i

set @SubscriberKey = AttributeValue("_subscriberkey") 
set @returnRows = 0 /* 0 means all, max 2000 */
set @rows = LookupOrderedRows("Master_Indiv_Business", @returnRows, "Business_ID asc", "Subscriber Key", @SubscriberKey)

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

  var @Business_IDs
  set @Business_IDs = ""

  for @i = 1 to @rowCount do

    var @Business_ID

    set @row = row(@rows,@i) /* get row based on counter */
    set @Business_ID = field(@row,"Business_ID")

    if @i < @rowCount then
      set @Business_IDs = concat(@Business_IDs, @Business_ID,", ")
    else
      set @Business_IDs = concat(@Business_IDs, @Business_ID)
    endif

  next @i 

else 

    write(concat("<br>no rows found"))

endif 
]%%
Hi Business owner.
<br><br>We are just updating our records as we currently
have the following Business IDs on file for you %%=v(@Business_IDs)=%%.
  • Hi Adam, Thanks very much for this its looks like its very close to working. Just stuck on one thing. I get an error "An expected comma is not found in the varible declaration. Script: var (at)rows, (at)row, (at)rowCount, (at)returnRows, (at)Subscriber Key, (at)i ListID: 2264 Index: 9644" Really appreciate your help with this – Andy Jan 31 '18 at 19:40
  • Please update your question with the code you're using. – Adam Spriggs Jan 31 '18 at 20:01
  • Thanks Adam, I have updated the Question with the code. Hopefully I havent done something to silly – Andy Jan 31 '18 at 21:02

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.