0

I have a data extension (Data Extensions > Lifecycle Campaigns > API Tables > Patient_Records_DB) that is used to capture send event data for one of our triggered campaigns. The send event capture is happening through the statement below. When I use the UpsertDE fuction and clear the data extension out before sending, the triggered campaign works fine. When there is data in the data extension and I use the UpsertDE function with the same data set, the triggered campaign errors out in the queue. I added the SendLogCount function to attempt to override the error, but this also errors out when the data extension already has data populated that is a match. Any thoughts?

  Set @SendLog = LookupOrderedRows("Patient_Records_DB",1,"AppointmentDateTime","FamilyID",@familyid)
  Set @SendLogCount = Rowcount(@SendLog)

IF @SendLogCount > 1 THEN
UpdateDE(
"Patient_records_DB",1,
"PatientID",@ApptSubscriberKey,
"PatientID",@ApptSubscriberKey,
    "HOHid",@HOHid,
    "PatientFirstName",@PatientFirstName,
    "FamilyID",@familyid,
    "AppointmentID",@FamilyAppt,
    "AppointmentDateTime",@PatientApptTime,
    "AppointmentTypeCode",@AppointmentTypeCode,
    "RescheduleYN",@RescheduleYN,
    "EventDate",@EventDate)

ELSE

InsertDE(     
    "Patient_Records_DB",
    "PatientID",@ApptSubscriberKey,
    "HOHid",@HOHid,
    "PatientFirstName",@PatientFirstName,
    "FamilyID",@familyid,
    "AppointmentID",@FamilyAppt,
    "AppointmentDateTime",@PatientApptTime,
    "AppointmentTypeCode",@AppointmentTypeCode,
    "RescheduleYN",@RescheduleYN,
    "EventDate",@EventDate)

ENDIF

  /*UpsertDE(     
    "Patient_Records_DB",1,"PatientID",@ApptSubscriberKey,
    "PatientID",@ApptSubscriberKey,
    "HOHid",@HOHid,
    "PatientFirstName",@PatientFirstName,
    "FamilyID",@familyid,
    "AppointmentID",@FamilyAppt,
    "AppointmentDateTime",@PatientApptTime,
    "AppointmentTypeCode",@AppointmentTypeCode,
    "RescheduleYN",@RescheduleYN,
    "EventDate",@EventDate)*/
Improve this question
4
  • What is the Primary Key on Patient_Records_DB?
    – Adam Spriggs
    Commented Oct 15, 2015 at 18:12
  • @AdamSpriggs PatientID
    – Realrx7
    Commented Oct 15, 2015 at 18:27
  • 1
    Is Patient_Records_DB a TriggeredSendDataExtension? I assume it is based on your statement, "I have a data extension that is used to capture send event data for one of our triggered campaigns." TriggeredSendDataExtension cannot have a primary key. But, the UpsertDE() function requires that the specified data extention have a primary key.
    – David Devoy
    Commented Oct 16, 2015 at 22:00
  • It's not being used for the triggered send in the triggered send definition. It is only used to capture the send event data.
    – Realrx7
    Commented Oct 17, 2015 at 1:56

1 Answer 1

Reset to default
2

You do not need to specify the PatientID field twice. It looks like you are setting PatientID as the match column, but then also specifying it as a column you want to insert into. You do not need to do this, setting it as the match column automatically sets it as a column that gets inserted to.

So just remove one of these from the function call: "PatientID",@ApptSubscriberKey,

Also, you don't need to do the LookUp and conditional logic to do update vs. insert. Upsert takes care of that for you automatically. All you need is the following code: 

UpsertDE(     
"Patient_Records_DB",1,
"PatientID",@ApptSubscriberKey,
"HOHid",@HOHid,
"PatientFirstName",@PatientFirstName,
"FamilyID",@familyid,
"AppointmentID",@FamilyAppt,
"AppointmentDateTime",@PatientApptTime,
"AppointmentTypeCode",@AppointmentTypeCode,
"RescheduleYN",@RescheduleYN,
"EventDate",@EventDate)
Improve this answer
1
  • Thanks @jonsakas. We had tried that before and for some reason it wasn't working. Likely could have been another error in the template that we were thinking was the Upsert.
    – Realrx7
    Commented Oct 20, 2015 at 14:33

You must log in to answer this question.

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