0

I'm having an issue trying to combine multiple rows into an email. As an example, I want to take the below data (Policy Data) and combine data where the policy number is the same and display that information within a table in an email. Like the below...

Policy Number|First Name|Relationship
1111         |Steve     |Primary
1111         |Mary      |Spouse
1111         |Dave      |Child
1111         |Wendy     |Child

I've pasted below as well my AMPscript.

Policy_data
Policy_Number|First_Name|Relationship
1111|Steve|Primary
1111|Mary|Spouse
1111|Dave|Child
1111|Wendy|Child
2222|Morgan|Primary
2222|Tom|Child
3333|Greg|Primary
4444|Kayla|Primary
    

<html>
…
Hi, your policy has the following members:
<table>
%%[
set @rows = LookupRows(Policy_Data,Policy_Number,Policy_Number)
set @rc = RowCount(@rows)

for @i = 1 TO @rc DO
  set @row = Row(@rows, @i)
  set @fName = Field(@row, first_name)
 set @relationship = Field(@row, relationship)
]%%   
<tr><td>%%=v(@fName)=%%</td><td>%%=v(@relationship)=%%</td></tr>
%%[next @r]%%
%%[end]%%
</table>
</html>

Wondering if anyone has had any similar experience?

Thanks!

salesforce

Improve this question
4
  • 1
    Can you clarify what you mean by combine data where the policy number is the same and display that information within a table in an email? If you mean just looping through the whole thing, you are fairly close with what you have. And to that extent, what error or unexpected output are you getting from current code?
    – Gortonington
    Commented Jun 16, 2021 at 13:18
  • 1
    Yes, an AmpScript FOR loop. The Error message I get when trying to send out a test is as followed... "An error occurred when attempting to resolve a script expression. See inner exception for detail. Script Expression: LookupRows(Policy_Data,Policy_Number,Policy_Number) MemberID: 100019515 JobID: 0 The specified attribute or custom object field name was not found for this client. Function Call: LookupRows(Policy_Data,Policy_Number,Policy_Number) Attribute or Field Name: Policy_Data Invalid Content: set @rows = LookupRows(Policy_Data,Policy_Number,Policy_Number)" Thank you!
    – rom29
    Commented Jun 16, 2021 at 13:42
  • 1
    Yeah looks like you forgot the quotations around the constants - both my or @Macca's answer should get you to where you need to be.
    – Gortonington
    Commented Jun 16, 2021 at 13:48
  • Yep. @Gortonington put the quotes in the right places (it's intuitive for him). Both would work.
    – Macca
    Commented Jun 16, 2021 at 14:17

2 Answers 2

Reset to default
2

I'm making a wild assumption that you have a field in your sendable data called "Policy_Number"?

Policy_Number is a variable. "Policy_Number" is a literal. Quotations count.

LookupRows(Policy_Data,Policy_Number,Policy_Number)

You're asking to lookup a table whose name is stored in your 'Policy_Data' variable (undefined) where the value of the property whose name you have in your 'Policy_number' variable ("X123") equals, yeah, the same value ("X123"). I'm guessing your undefined data extension doesn't have a field called "X123"?

LookupRows("Policy_Data","Policy_Number",Policy_Number)

Your table is "Policy_Data" (a literal), the field in your where clause is "Policy_Number" (a literal) and the value to match against is Policy_Number (a variable).

Improve this answer
1
  • Thank you, this worked!
    – rom29
    Commented Jun 16, 2021 at 16:56
1

If you are looking for like a list of people on the account, you can likely do something like:

%%[ 
set @PolicyNumber = AttributeValue("Policy_Number")
IF NOT EMPTY(@PolicyNumber) THEN
  set @rows = LookupRows("Policy_Data","Policy_Number",@PolicyNumber)
  set @rc = RowCount(@rows)

  for @i = 1 TO @rc DO
    set @row = Row(@rows, @i)
    set @fName = Field(@row, "first_name")
    set @relationship = Field(@row, "relationship")
]%%   
<tr><td>%%=v(@fName)=%%</td><td>%%=v(@relationship)=%%</td></tr>
%%[next @i
  endif]%%

Now if you are looking for a way to combine those values, e.g. All the First Names into a delimited list, then could do it this way:

%%[ 
set @PolicyNumber = AttributeValue("Policy_Number")
set @finalStr = ""

IF NOT EMPTY(@PolicyNumber) THEN
  set @rows = LookupRows("Policy_Data","Policy_Number",@PolicyNumber)
  set @rc = RowCount(@rows)

  for @i = 1 TO @rc DO
    set @row = Row(@rows, @i)
    set @fName = Field(@row, "first_name")
    set @relationship = Field(@row, "relationship")
    set @finalStr = IIF(EMPTY(@finalStr), @fName, CONCAT(@finalStr,",",@fName))
]%%

Where @finalStr would output: Steve,Mary,Dave,Wendy

Improve this answer

You must log in to answer this question.

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