1

I am trying to build a dynamic From Name using amp script. In our business case customers can book multiple tours with various reservation agents; in this case we want to send a dynamic From Name as coming from the Reservation agent that they booked most with.

amp Script code in my emails is the following:

%%[var @BookerEmail, @ResAgent, @ProductName, @row,@rows,@rowCount
SET @rows=LookupOrderedRows('Example_Past_Pax_Bozi',0,'Res Agent ASC','Contact ID',[Contact ID])

SET @rowCount=RowCount(@rows)

if @rowCount>0 then]%% 


<table>
<tr>
  <td>Res Agent</td>
  <td>Tour</td>
  <td>Tour count per res agent</td>
</tr>%%[
for @i=1 to RowCount(@rows) do
SET @row=ROW(@rows,@i)]%%
<tr>
<td>%%=field(@row,"Res Agent")=%%</td>
<td>%%=field(@row,"Product Name")=%%</td>
<td>%%=RowCount(LookupRows('Example_Past_Pax_Bozi','Res Agent',field(@row,"Res Agent")))=%%</td>
</tr>%%[Next @i]%%
</table>%%[else]%%
%%[endif]%%

I am also attaching a print screen when we do preview.

I am not sure how I can incorporate the RowCount into building a variable that i can then use in the Sender Profile.

I have tried doing the count via SQL and add it into a new data extension but it feels like circling back and forth.

enter image description here

enter image description here

  • Maybe developer.salesforce.com/docs/… is something for you. You can order rows based on count per agent and take the first one and use that value as fromname. Would be my first guess – Johannes Schapdick Jul 17 '18 at 14:23
  • How many unique agenst are there and is there a DE storing all of them and their Names like in 'Res Agent' field? – Mateusz Bartkowiak Jul 17 '18 at 14:40
  • @MateuszBartkowiak in this particular example I have 2 Res Agents; so in this instance the From name should appear as the Res Agent that has the value 2 in the tour count. – Giulietta Jul 17 '18 at 14:49
  • @Giulietta I understand, but I'm talking about the end use case. If you have a table with Agents you can start by doing a LookupRows on it, get all Agents, loop through them and check how many Trips they Booked for this customer and output one with the highest number out of the loop. If there is a lot of them it will work very slow, so in that case I would recommend using queries and store Agent name in relation to customer in separated DE. – Mateusz Bartkowiak Jul 17 '18 at 14:55
  • @MateuszBartkowiak thanks. Exactly what I have done this morning. i have attached the pic that is the result after the query. – Giulietta Jul 17 '18 at 15:02
2

You actually were very close to what you needed.

Rather than collect all of the rows for that person, you can use LOOKUPBYORDEREDROW() to only pull the top agent.

%%[var @BookerEmail, @ResAgent, @ProductName, @row,@rows,@rowCount
SET @rows=LookupOrderedRows('Example_Past_Pax_Bozi',1,'Res AgentNR DESC','Contact ID',[Contact ID])

SET @row = ROW(@rows, 1)
SET @ResAgent = FIELD(@row,"Res Agent")
]%%

%%=v(@ResAgent)=%% 

This should only pull 1 row, and it will pull the row with the highest Res AgentNR because it will sort based on descending values.

Then using ROW() and FIELD() you can get the Res Agent Value into an AMPScript variable.

You would then output or use v() to push the value into the Sender Profile.

Usually when I deal with Sender profiles, I like to place these inside of a Code Snippet content block and then calling this block inside of the Sender Profile - using %%=ContentBlockbyKey("myContentBlock")=%% where "myContentBlock" is the external key of your Code Snippet.

This way you don't have a giant blob of AMPScript in the Sender Profile and it makes editing and maintaining the code much easier.

  • 1
    One important point in the answer is that the script needs to be in the sender profile itself. Those are evaluated independently of the email body (actually before the email) and therefore can't read any variables from ampscript in the email itself. – Stephan de Paly Jul 18 '18 at 19:16
  • @Giulietta - I am glad to hear that! If my answer solved your problem, please be sure to mark it as correct. If not, please create an answer yourself that shows how you solved it - so that others may find the solution if they have the same issues. – Gortonington Jul 19 '18 at 12:43

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.