0

We are trying to pass through an xml file (example xml feed) via an API triggered send. The API call can be seen in the triggered send status as in Queue, but then after a few minutes the in Queue status goes back to empty and neither the complete or failed status update. Could it be that the way we are setting the values are causing an issue?

%%[

Set @xml = XML_Feed
/*Set @xml = "<Record AppointmentID = '27168205' PatientID='11904' FamilyID='473' HOHID='473' PatientLastName='Terry' PatientFirstName='Jibreal' AppointmentDateTime='6/13/15 14:45' ClinicID='1'></Record><Record AppointmentID = '27168230' PatientID='11905' FamilyID='473' HOHID='473' PatientLastName='Terry' PatientFirstName='Jasmine' AppointmentDateTime='6/13/15 15:30' ClinicID='1'>   </Record>"*/

/*Function to build rowset*/
Set @rowset = BuildRowsetFromXml(@xml, "//Record", 1)

Set @FamilyTotal = Rowcount(@rowset)

If not empty(@FamilyTotal) then

   for @i = 1 to Rowcount(@rowset) do

        Set @row = Row(@rowset,@i)

        /*Set @FamilyAppt = Field(Row(@rowset, 1), 'AppointmentID_att')*/
        /*Set @familyid = Field(Row(@rowset, 1), 'FamilyID_att')*/
        /*Set @ApptSubscriberKey = Field(Row(@rowset, 1), 'PatientID_att')*/
        /*Set @HOHid = Field(Row(@rowset, 1), 'HOHID_att')*/

        Set @FamilyAppt = Field(Row(@rowset,@i),"AppointmentID_att")
        Set @ApptSubscriberKey = Field(Row(@rowset,@i),"PatientID_att")
        Set @familyid = Field(Row(@rowset,@i),"FamilyID_att")
        Set @HOHid = Field(Row(@rowset,@i),"HOHID_att")
        Set @DoNotText = LookUp("b_patient_data","DoNotText","HOHid",@HOHid)

        If @i == 1 THEN

            /*Set @familyid = Field(Row(@rowset, 1), 'FamilyID_att')*/
            Set @familyid = Field(Row(@rowset,@i),"FamilyID_att")
            Set @FamilyFirstNames = Propercase(Field(@row,"FirstName"))
            /*Set @FamilyApptTime = Field(Row(@rowset, 1), 'AppointmentDateTime_att')*/
            Set @FamilyApptTime = Field(Row(@rowset,@i), 'AppointmentDateTime_att')
            Set @FamilyApptDay = Format(@FamilyApptTime,"dddd")
            Set @FamilyApptMonth = Format(@FamilyApptTime,"MMM")
            Set @FamilyApptDate = Format(@FamilyApptTime,"dd")
            Set @StartTime = Format(@FamilyApptTime,"h:mm tt")
            Set @ApptDisplay = Concat(@FamilyApptDay,", ",@FamilyApptMonth," ",@FamilyApptDate," at ",@StartTime,"")
            Set @SubjectLineDate = Concat(Format(@FamilyApptTime,"MMMM"),' ',Replace(@FamilyApptDate,"0",""),'')
            /*Set @ApptClinicID = Field(Row(@rowset, 1), 'ClinicID_att')*/
            Set @ApptClinicID = Field(Row(@rowset,@i), 'ClinicID_att')
            Set @FriendlyClinicName = Lookup("ll_clinic_data","FacilityName","CRMFacilityID",@ApptClinicID)
            Set @ClinicWifi = Lookup("ll_clinic_data","wifi","CRMFacilityID",@ApptClinicID) 
            Set @ClinicLocalURL = Lookup("ll_clinic_data","localURL","CRMFacilityID",@ApptClinicID) 
            Set @msc_data_id = Lookup("ll_clinic_data","msc_data_id","CRMFacilityID",@ApptClinicID) 
            Set @ClinicDirectionsURL = Concat("http://www.MyKoolSmiles.com/directions/",@msc_data_id,'')
            Set @ClinicInfoURL = Concat("http://www.MyKoolSmiles.com",@ClinicLocalURL,'') 
            Set @ClinicPhoneNumber = format(Lookup("ll_clinic_data","WebSiteNumber","CRMFacilityID",@ApptClinicID) ,"###-###-####")
            Set @ClinicStreetNumber = Lookup("b_clinic_data","ClinicStreetNumber","ClinicID",@ApptClinicID)
            Set @ClinicPreDirection = Lookup("b_clinic_data","ClinicPreDirection","ClinicID",@ApptClinicID)
            Set @ClinicStreetName = Lookup("b_clinic_data","ClinicStreetName","ClinicID",@ApptClinicID)
            Set @ClinicStreetSuffix = Lookup("b_clinic_data","ClinicStreetSuffix","ClinicID",@ApptClinicID)
            Set @ClinicPostDirection = Lookup("b_clinic_data","ClinicPostDirection","ClinicID",@ApptClinicID)
            Set @ClinicStreetUnitType = Lookup("b_clinic_data","ClinicStreetUnitType","ClinicID",@ApptClinicID)
            Set @ClinicStreetUnitNumber = Lookup("b_clinic_data","ClinicStreetUnitNumber","ClinicID",@ApptClinicID)
            Set @ClinicCity = Lookup("b_clinic_data","ClinicCity","ClinicID",@ApptClinicID)
            Set @ClinicStateCode = Lookup("b_clinic_data","ClinicStateCode","ClinicID",@ApptClinicID)
            Set @ClinicStreetZip = Lookup("b_clinic_data","ClinicStreetZip","ClinicID",@ApptClinicID)
            Set @ClinicStreetZip4 = Lookup("b_clinic_data","ClinicStreetZip4","ClinicID",@ApptClinicID)
            Set @ClinicDMAURL = Concat("http://www.MyKoolSmiles.com/dentist/",Lookup("ll_clinic_data","region_url","CRMFacilityID",@ApptClinicID),'') 
            Set @H_PrimaryInsuranceType = Lookup("b_patient_data","PrimaryInsuranceType","Subscriber Key",@ApptSubscriberKey)
            Set @H_InsuranceVerificationStatus = Lookup("b_patient_data", "InsuranceVerificationStatus", "Subscriber Key", @ApptSubscriberKey)
        ENDIF

        Set @ApptType = Uppercase(Lookup("b_appointment_data","AppointmentTypeDescription","AppointmentID",@FamilyAppt))
        Set @ListAppType = Concat(@ListAppType,Uppercase(Lookup("b_appointment_data","AppointmentTypeDescription","AppointmentID",@FamilyAppt)),'|')
        Set @ApptHistory = LookupOrderedRows("b_appointment_data",0,"AppointmentDateTime","Subscriber Key",@ApptSubscriberKey)

   Next 
Else
    RaiseError("Missing subscriber information.",true)
ENDIF

]%%
4
  • I'd contact SFMC support and have them tell you the reason why the email isn't being sent or contact a partner that has access to the error log. Jun 21 '15 at 22:29
  • Also, what do you get when you do a Send Preview of the email with the XML variable uncommented? How does the XML payload differ from your test vs what's being sent over with the triggered send? Jun 21 '15 at 22:32
  • @AdamSpriggs I get the following error: Invalid index (parameter 2) passed to Row function. Index must be less than or equal to the row count. Index Value: 1 Row Count: 0 Function: Row((at)rowset, 1)
    – Realrx7
    Jun 22 '15 at 12:17
  • @AdamSpriggs also received this from support: A Triggered Send in your Interactions tab has been STOPPED due to the AMPScript code is processing a lookup and/or buildrowset function that reports rows without checking the count first. When the process comes back with no rows at all, the AMPScript fails because it is still accessing the 'first' row in an empty row set.
    – Realrx7
    Jun 22 '15 at 12:24
1

First, you'll need a well formed XML document. There's no root node in the XML sample you're using.

Here's a stripped down version of your code and the output. I took out the lookups to the external source for testing purposes. I added some other tips in the comments.

%%[
var @xml
/* use AttributeValue() to handle null values */
set @xml = AttributeValue("XML_Feed")

/* Need well-formed XML here */
Set @xml = "<root><Record AppointmentID='27168205' PatientID='11904' FamilyID='473' HOHID='473' PatientLastName='Duck' PatientFirstName='Huey' AppointmentDateTime='6/13/15 14:45' ClinicID='1'></Record><Record AppointmentID='27168230' PatientID='11905' FamilyID='473' HOHID='473' PatientLastName='Duck' PatientFirstName='Dewey' AppointmentDateTime='6/13/15 15:30' ClinicID='1'></Record></root>"

Set @rowset = BuildRowsetFromXml(@xml, "//Record", 1)
Set @FamilyTotal = Rowcount(@rowset)

outputline(concat("<br>@FamilyTotal: ",@FamilyTotal))

If not empty(@FamilyTotal) then

   for @i = 1 to Rowcount(@rowset) do

        /* use the @row variable to intead of repeating the row() function everywhere */
        Set @row = Row(@rowset,@i)
        Set @FamilyAppt = Field(@row,"AppointmentID_att")
        Set @ApptSubscriberKey = Field(@row,"PatientID_att")
        Set @familyid = Field(@row,"FamilyID_att")
        Set @HOHid = Field(@row,"HOHID_att")

        /* use outputline() for debugging */
        outputline(concat("<br><br>@i: ",@i))
        outputline(concat("<br>@FamilyAppt: ",@FamilyAppt))
        outputline(concat("<br>@ApptSubscriberKey: ",@ApptSubscriberKey))
        outputline(concat("<br>@familyid: ",@familyid))
        outputline(concat("<br>@HOHid: ",@HOHid))

        If @i == 1 THEN
            Set @familyid = Field(@row,"FamilyID_att")
            Set @FamilyFirstNames = Propercase(Field(@row,"PatientFirstName_att"))
            Set @FamilyApptTime = Field(@row, 'AppointmentDateTime_att')
            Set @FamilyApptDay = Format(@FamilyApptTime,"dddd")
            Set @FamilyApptMonth = Format(@FamilyApptTime,"MMM")
            Set @FamilyApptDate = Format(@FamilyApptTime,"dd")
            Set @StartTime = Format(@FamilyApptTime,"h:mm tt")
            Set @ApptDisplay = Concat(@FamilyApptDay,", ",@FamilyApptMonth," ",@FamilyApptDate," at ",@StartTime,"")
            Set @SubjectLineDate = Concat(Format(@FamilyApptTime,"MMMM"),' ',Replace(@FamilyApptDate,"0",""),'')
            Set @ApptClinicID = Field(@row, 'ClinicID_att')

            /* add other lookups here */

            outputline(concat("<br>@familyid: ",@familyid))
            outputline(concat("<br>@FamilyFirstNames: ",@FamilyFirstNames))
            outputline(concat("<br>@FamilyApptTime: ",@FamilyApptTime))
            outputline(concat("<br>@FamilyApptDay: ",@FamilyApptDay))
            outputline(concat("<br>@FamilyApptMonth: ",@FamilyApptMonth))
            outputline(concat("<br>@FamilyApptDate: ",@FamilyApptDate))
            outputline(concat("<br>@StartTime: ",@StartTime))
            outputline(concat("<br>@ApptDisplay: ",@ApptDisplay))
            outputline(concat("<br>@SubjectLineDate: ",@SubjectLineDate))
            outputline(concat("<br>@ApptClinicID: ",@ApptClinicID))
        ENDIF

   Next @i
Else
    RaiseError("Missing subscriber information.",true)
ENDIF

]%%

Output

@FamilyTotal: 2 

@i: 1 
@FamilyAppt: 27168205 
@ApptSubscriberKey: 11904 
@familyid: 473 
@HOHid: 473 
@familyid: 473 
@FamilyFirstNames: Huey 
@FamilyApptTime: 6/13/15 14:45 
@FamilyApptDay: Saturday 
@FamilyApptMonth: Jun 
@FamilyApptDate: 13 
@StartTime: 2:45 PM 
@ApptDisplay: Saturday, Jun 13 at 2:45 PM 
@SubjectLineDate: June 13 
@ApptClinicID: 1 

@i: 2 
@FamilyAppt: 27168230 
@ApptSubscriberKey: 11905 
@familyid: 473 
@HOHid: 473 

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.