[SalesForce] How to create loop for subject line using AMPscript

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"

Best Answer

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.

Related Topic