Discussion between Nicholas Dixon and Kelly J Andrews

6:41 PM

1

A: Update data extension where multiple records exist for single user

In your insertDE call, you are referencing @ variables that don't appear to be set anywhere. Also it is important to note that insertDE is only available at send time in an email. Use upsertData and that will load a new row on a landing page. You will want to modify your call a bit to account fo...

Nicholas Dixon

I receive this error with your suggested update: Invalid column name (parameter 2) passed to Field function. No column of the specified name was found. Invalid Field Name: PatientID Function: Field(@currentRow, "PatientID")

Kelly J Andrews

You would need to use the columns in the data extension you are getting the data from. The code above is just an example.

Nicholas Dixon

Getting this error: Invalid object property name. Object Type: System.Int32 Property Name: AppointmentID

Kelly J Andrews

Without knowing your full data schema, its hard to know, but id guess that the data you are passing in doesn't match what the data extension is expecting.

Try setting up the loop, and printing it to the screen to validate the data rows.

Nicholas Dixon

The AppointmentID is a number. Is there a way to set the variable being passed as a number?

Is it possible to use an IF/ELSE statement that looks to see if the patient exists in a Data extension?

Kelly J Andrews

6:41 PM

Thats built into the upsertde function

Nicholas Dixon

How does upsert execute the else portion?

Kelly J Andrews

If exists, update, else insert new.

Nicholas Dixon

Thanks! That is working for us, but it updates/inserts the appointment information identically each time. So if the email has 3 appointments and they confirm them (1 call to action) it updates/inserts the first appointment info instead of the info for each of the appointments.

Kelly J Andrews

Im not exactly sure what the issue is. Can you update the question with the current code. Might also help to see how you retrieve the data, and maybe some sample records. Hard to tell from the info i have so far.

Nicholas Dixon

Hi Kelly, thought it might be easier to communicate on here rather than go back and forth on commeints

Kelly J Andrews

6:57 PM

Hi there - this should be helpful

so how exactly is the data passed from the email to the landing page, and what data is it exactly?

im looking at the question now

is the code on the question the landing page code then?

ok - I think I know what the issue is

are you there still?

I think your issue is the key of the "b_appointment_data" table. What is your primary key there?

Nicholas Dixon

7:19 PM

sorry i was heating up some coffe

coffee

The first half is the email code and the second half is the insert on the landing page

The primary key for the b_appointment_data table is AppointmnetID

The data is being passed by a query string in the email

Var @CancelURL, @ConfirmURL, @confirm, @cancel
Set @CancelURL = ContentAreaByName("My Contents\LandingPages\AppointmentCancelPage")
/*Set @ConfirmURL = ContentAreaByName("My Contents\LandingPages\AppointmentConfirmPage")*/
Set @ConfirmURL = 'http://pages.e.mykoolsmiles.com/page.aspx?QS=c76003443ff9837dc239b153f33adc862a5a461074cc1afc476e53076182ac8f'
Set @cancel = Concat(@CancelURL,'&ed=',@EventDate,'&rd=',@ReminderDate,'&fid=',@familyid,'&hid=',@HOHid,'&jid=',@JobID)
Set @confirm = Concat(@ConfirmURL,'&ed=',@EventDate,'&rd=',@ReminderDate,'&fid=',@familyid,'&hid=',@HOHid,'&jid=',@JobID)

Kelly J Andrews

ok - so you pass to the landing page the data that this - Set @FamilyRecords = LookupOrderedRows(@DELookUp,0,"AppointmentDateTime","FamilyID",@familyid)

needs to run, right?

Nicholas Dixon

yes

I can send some sample data too

Kelly J Andrews

i think we should get it here - instead of the upsert - try printing out something, like the appointmentId for each row

do like output(@FamilyAppt)

and comment out everything else

(comment the upsert, not everything, everything)

Nicholas Dixon

ok i will try that now

Kelly J Andrews

what you need to determine is - do you have 3 distinct familyid's, or no

you may already know the answer to that based on your data

in fact, I know that - you don't have a unique row yet. The appointment id is based on the family id, which isn't unique

Nicholas Dixon

7:27 PM

the patientid is the distinct identifier here

Kelly J Andrews

so it will insert a new row the first time, but if "appointmentID = familyID" it will overwrite itself everytime you loop

you need a composite key in your appointment data extension most likely

you can use multiple primary keys if that data creates a unique row - just like you would in sql

ill be back in a minute

Nicholas Dixon

the output isnt generating anything

Kelly J Andrews

hmm - ok - I have a meeting, and ill swing back in afterward

Nicholas Dixon

ok thanks so much for your help

Nicholas Dixon

7:41 PM

Tried to print out the result and output and neither did anything

%%[
/* ExactTarget AMPscript */

SET @ActionDate = Now()
SET @ActionDateTime = SystemDateToLocalDate(@ActionDate)

set @Send_id = RequestParameter('jid')
set @JobID = RequestParameter('jid')
set @ListID = listid
set @BatchID = _JobSubscriberBatchID
set @EmailName = emailname_
/*set @EmailAddress = EmailAddress*/

Set @ReminderDate = RequestParameter('rd')

If @ReminderDate == "10" THEN
set @CampaignName = "10 Day Appt Reminder"
set @CampaignID = "100"
Set @DELookUp = "03_ApptConfirmation_Lookup"

Here is the email....

%%[

/* ExactTarget AMPscript */

Set @EventDate = Now()

Set @ReminderDate = "10"

/* Set up campaign variables for logging to et_contact_history */
set @Send_id = JobId
set @JobID = JobId
set @ListID = listid
set @BatchID = _JobSubscriberBatchID
set @EmailName = emailname_
set @EmailAddress = EmailAddress
/* End Default Campaign Set Up */

Set @DownloadForm = "FALSE"

If @ReminderDate == "10" THEN
set @CampaignName = "10 Day Appt Reminder"
set @CampaignID = "100"
Set @DELookUp = "03_ApptConfirmation_Lookup"

Kelly J Andrews

8:14 PM

try adding NEXT @i - I may have missed that in my prior answer :/

Ill review this in a bit, and try to get you some resolution in the morning

hang in there - this is solvable, just have to find the issue :)

Nicholas Dixon

sorry i keep missing your new messages

Thanks again!!

I came on to this account this week and this is the first thing they ask me to resolve!!! No background on the account or anything

Transcript for

Discussion between Nicholas Dixon and…