1

We are getting the following error when trying to generate a preview of an email with one of our data extensions:

New Error There was an unknown error building your preview.

The LookUp is where we are running into the problem I believe. The parameters are being passed from the data extension we are using to send.

We want to LookUp the data in these Data Extensions: b_patient_data & b_appointment_data. Fields in the DE are:
b_patient_data
Subscriber Key
PatientLastName
PatientFirstName
HOHid
FamilyId

b_appointment_data
AppointmentID
Subscriber Key
AppointmentDateTime
ClinicID
PatientApptTime
Subscriber Key
ClinicID

 Set @GroupRecord = LookupOrderedRows("b_appointment_data",0,"AppointmentDateTime","Subscriber Key",@ApptSubscriberKey)    

 Set @row_validation = RowCount(@GroupRecord)
 If @row_validation < 1 THEN 
    RaiseError("No values for this subscriber.",true) 
    ELSE Set @totalCount = @row_validation 
ENDIF

For @i = 1 to @totalCount do

Set @row = Row(@GroupRecord,@i)
Set @PatientSubKey = Field(@row,"Subscriber Key")
Set @PatientAppt = Field(@row,"AppointmentID")
Set @PatientApptTime = Field(@row,"AppointmentDateTime")
Set @PatientApptDay = Format(@PatientApptTime,"dddd, MMM, d, hh, mm, tt")

NEXT


Set @GroupRecordB = LookupOrderedRows("b_patient_data",0,"HOHid DESC","Subscriber Key",@ApptSubscriberKey)

Set @row_validationB = RowCount(@GroupRecordB)
If @row_validationB < 1 THEN 
    RaiseError("No values for this subscriber.",true) 
    ELSE Set @totalCountB = @row_validationB 
ENDIF

For @i = 1 to @totalCountB do
Set @row = Row(@GroupRecordB,@i)
Set @PatientFirstName = Propercase(Field(@row,"PatientFirstName"))
Set @PatientLastName = Field(@row,"PatientLastName")

NEXT
3
  • Please post some more code, specifically where you call SET @row = /*some lookup */ and we will have a look.
    – Timothy
    Commented Aug 7, 2014 at 16:27
  • @Timothy I've updated the initial post with the row call
    – Realrx7
    Commented Aug 7, 2014 at 17:19
  • @Timothy I've updated the code with the suggestion below and now get an error saying: There was an unknown error building your preview.
    – Realrx7
    Commented Aug 7, 2014 at 19:54

2 Answers 2

1

Two things here:

1 - Set @GroupRecords = LookupOrderedRows(@DELookUp,0,"AppointmentDateTime","FamilyID",@familyid)

  • parameter for sorting is missing - if you're not going to order/sort it, you may as well just use the LookupRows function instead.
  • change to: Set @GroupRecords = LookupOrderedRows(@DELookUp,0,"AppointmentDateTime DESC","FamilyID",@familyid)

2 - when using lookup functions and loops, use RowCount as a validation step to ensure you are even getting a value. so taking your code:

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

Set @row_validation = RowCount(@GroupRecords) If @row_validation < 1 THEN RaiseError("No values for this subscriber.",true) ELSE Set @totalCount = @row_validation ENDIF

For @i = 1 to @totalCount do

Set @row = Row(@GroupRecords,@i) Set @PatientSubKey = Field(@row,"Subscriber Key") ....

2
  • 1
    Once we set each row does the NEXT need to include @i
    – Realrx7
    Commented Aug 7, 2014 at 19:08
  • correct, it does.
    – Timothy
    Commented Aug 7, 2014 at 20:13
0

Finally was able to set the needed inputs by using the code below.

 Set @family_id = FamilyID

 Set @GroupRecord = LookupOrderedRows(@DELookUp,0,"AppointmentDateTime DESC","FamilyID",@family_id)

 Set @row_validation = RowCount(@GroupRecord) 

If @row_validation < 1 THEN 
RaiseError("No values for this subscriber.",true)
ELSE Set @totalCount = @row_validation
ENDIF

For @i = 1 to @totalCount do
Set @PatientSubKey = Field(@row,"Subscriber Key")
Set @PatientFirstName = Propercase(Field(@row,"PatientFirstName"))
Set @PatientLastName = Field(@row,"PatientLastName")
Set @PatientAppt = Field(@row,"AppointmentID")
Set @PatientApptTime = Field(@row,"AppointmentDateTime")
Set @PatientApptDay = Format(@PatientApptTime,"dddd, MMM, d, hh, mm, tt")

NEXT @i

You must log in to answer this question.

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