1

Not sure if I am asking this correctly, but here is what we are trying to do. We want to create a subject line that will pull through each appointment's patient name and append it to the end of the subject line for each iteration. Not very far on this one so any help is appreciated.

Final outcome: If 1 appointment We are looking forward to Tom's appointment!

If more than 1... We are looking forward to Tom's and Jane's appointment!

If more than 2... We are looking forward to Tom's, Jane's, and Cindy's appointment!

Updated snippet

var @xml, @SubjectLine, @GroupRecords, @row, @FirstName, @FirstNameGroup, @FirstNameLoop, @FirstNameFinal, @tempset

set @xml = XML_Feed

Set @GroupRecords = BuildRowsetFromXml(@xml, "//Record", 1)
Set @rowcount = Rowcount(@GroupRecords)
Set @FirstNameLoop = ""

If not empty(@GroupRecords) then

Set @tempset = @rowcount

Set @FirstNameFinal = ProperCase(Field(1,"FirstName_att"))

If @tempset != 1 Then
for @i = 1 to @rowcount do

    Set @row = Row(@GroupRecords,@i)
    Set @FirstName = ProperCase(Field(@row,"FirstName_att"))

    IF @i == @tempset THEN
            SET @FirstNameFinal = CONCAT(@FirstNameLoop," and ",@FirstName,"'s ")
            ELSE
            SET @FirstNameGroup = CONCAT(@FirstNameLoop," , ", @FirstName)
            ENDIF

    SET @FirstNameLoop = @FirstNameGroup

Next @i
ENDIF
ENDIF

SET @SubjectLine = Concat("We are looking forward to ",@FirstNameFinal,"'s appointment!")

Sample XML: Root Record AppointmentConfirmedYN="N" AppointmentDateTime="Dec 21 2015 1:00PM" AppointmentId="29986792" AppointmentSubTypeCode="" AppointmentTypeCode="1" CellPhone="18036165144" ClinicId="8" EmailAddress="antoinettawhite@yahoo.com" FamilyId="2352212" FirstName="Kanika" HOHID="6034997" InsuranceStatusCode="2" LastName="White" MessageId="531111" PatientId="6034997" PayerTypeCode="1" RescheduleYN="N"

  • Is @GroupRecords a data extension lookup or a profile attribute? If it is a data extension, are you storing all of the names in individual rows? – Rob Weis Jun 9 '15 at 14:45
  • Sorry, forgot to include that bit – Realrx7 Jun 9 '15 at 15:50
1

EDIT - I have updated the AMPscript and it appears to be working 100% correct in test environments for me - solving the repeating error. Sorry for delay in edit. See below:

%%[
VAR @DELookup, @familyid, @SubjectLine, @GroupRecords, @rowcount, @row, @FirstName, @FirstNameGroup @FirstNameLoop, @FirstNameFinal

SET @DELookup = (YOURDENAME)
SET @familyid = (YOURINFO)

SET @GroupRecords = LookupOrderedRows(@DELookUp,0,"AppointmentDateTime","FamilyID",@familyid)
SET @FirstNameLoop = ""

IF NOT EMPTY(@GroupRecords) THEN

    SET @rowcount = Rowcount(@GroupRecords)
    SET @FirstNameFinal = ProperCase(Field(1,"PatientFirstName"))

    IF @rowcount != 1 THEN

        for @i = 1 to @rowcount do 

            SET @row = Row(@GroupRecords,@i)
            SET @FirstName = ProperCase(Field(@row,"PatientFirstName"))

            IF @i == @rowcount THEN
                SET @FirstNameFinal = CONCAT(@FirstNameLoop," and ",@FirstName,"'s ")
            ELSE
                SET @FirstNameGroup = CONCAT(@FirstNameLoop," , ", @FirstName)
            ENDIF

            SET @FirstNameLoop = @FirstNameGroup

        NEXT @i

    ENDIF
ENDIF

SET @SubjectLine = Concat("We are looking forward to ",@FirstNameFinal,"'s appointment!")
]%%

I have not tested it, but I think something like the below should get you going.

%%[
Set @GroupRecords = LookupOrderedRows(@DELookUp,0,"AppointmentDateTime","FamilyID",@familyid)

If not empty(@GroupRecords) then

    set @rowcount = Rowcount(@GroupRecords)

    for @i = 1 to @rowcount do 

        Set @row = Row(@GroupRecords,@i)
        Set @FirstName = ProperCase(Field(@row,"PatientFirstName)")

        IF NOT EMPTY(@FirstNameLoop) THEN

            IF @i == @rowcount AND @i > 1 THEN
                SET @FirstNameFinal = CONCAT(@FirstNameLoop," and ",@FirstName,"'s ")
            ELSE
                SET @FirstName = CONCAT(@FirstNameLoop," , ", @FirstName)
                SET @FirstNameFinal = CONCAT(@FirstNameLoop," , ", @FirstName)
            ENDIF
        ELSE

            Set @FirstNameFinal = @FirstName

        ENDIF

        SET @FirstNameLoop = @FirstName

    next @i

ENDIF

Set @SubjectLine = Concat("We are looking forward to ",@FirstNameFinal,"'s appointment!")
]%%

It will go through your data and for each row it will pull the first name in propercase and push it into a variable. As the row number goes beyond one, it will start compiling it into a string by CONCAT-ing each name with a comma between until the loop hits the final run. On the final run, if it is above 1, it will add the "and" before the final name. This final variable is then put into the CONCAT subject and all should be ready.

| improve this answer | |
  • You don't need quotes around 1 in @i > "1" -- in the innermost conditional. – Adam Spriggs Jun 9 '15 at 19:59
  • Tried a few variations of what you provided and still keep receiving the same error message: An error occurred when attempting to parse HtmlEmailBody content for HTML content. Content Begins With: set (at)rowcount = Rowcount((at)GroupRecords) for (at)i = 1 to @rowcount do Set (at)row = Row – Realrx7 Jun 9 '15 at 20:08
  • sounds like a validation error in the AMPscript. Pull the whole thing through an AMPscript editor(hubexchange.exacttarget.com/listing/exacttarget-labs/…) or plugin for Notepad++(adventureswithet.com/2014/03/04/…), and see where it may be. I think what I provided is valid (based on you inserting the necessary variables and values, etc) but the editor should be able to point out the issue fairly easy. – Gortonington Jun 9 '15 at 20:15
  • The Propercase function had a (") out of place. Here is the result from a family record that has two appointments: We are looking forward to Ricky , Yasmine and Ricky's 's appointment! – Realrx7 Jun 9 '15 at 20:30
  • With one appointment output is: We are looking forward to Shane, Shane, Shane appointment! – Realrx7 Jun 9 '15 at 21:08

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.