Discussion between Nicholas Dixon and Kelly J Andrews

and only Set @FamilyAppt = Field(@row,"AppointmentID")
Set @ApptSubscriberKey = Field(@row,"Subscriber Key")
Set @PatientID = Field(@row,"Subscriber Key") are supposed to be unique?

Nicholas Dixon

the appointment times should be different. In the email it shows multiple appointments at a single time and allows them to confirm all of them with one call to action

Yes supposed to be unique

Subscriber = Patient ID

Kelly J Andrews

1:15 PM

your data is being set here

If @i == 1 THEN
Set @FamilyFirstNames = Propercase(Field(@row,"PatientFirstName"))
Set @FamilyApptTime = Field(@row,"AppointmentDateTime")
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",""),'')

Nicholas Dixon

Correct

Kelly J Andrews

but only on the first time

so if there are three rows, it will take the first rows data, and use it all three times

Nicholas Dixon

Tbats what we are seeing now in our data extension

Kelly J Andrews

try removing that if statment

that should get you each row's data

also make sure to change "next" to "next @i"

Nicholas Dixon

Ok let me do that now and run a quick test. only will take a minute

Kelly J Andrews

1:18 PM

I'm not sure if that makes a difference, but it might

ok - ill be back in a second - need coffee :)

Nicholas Dixon

thanks

The change of next to next @i, are you referring to the set or the upsert?

Kelly J Andrews

hang on - there is a lot more code... :/

Nicholas Dixon

ok

Kelly J Andrews

ok, what is the top ampscript vs next ampscript doing?

sorry - i was only looking at the top section - my bad

Nicholas Dixon

no problem

Kelly J Andrews

1:26 PM

you also have the same code multiple times it looks like

Nicholas Dixon

are you referring to the multiple ways i tried to get the confirmation insert working?

Kelly J Andrews

yeah

so let's start at the top - was that something you built?

or already there

Nicholas Dixon

it was already there

Kelly J Andrews

ok - so it's already setting variables for you

Nicholas Dixon

yes

Kelly J Andrews

1:32 PM

ok - so the top portion gets you all set up - let me look at the middle portion

and the primary key of - LL_TEST_ET_Appointment_Action is?

Nicholas Dixon

We don't have a primary key because it would only let us insert a single record when we did

It would be AppointmentID

Kelly J Andrews

ok - that's what I was mentioning yesterday - you had just appt ID?

Nicholas Dixon

yes

Kelly J Andrews

yeah - so it would need to be more than one column to make up the key - apptid and patientid

that should be unique

Nicholas Dixon

there is both appointmentid and patientid in the DE

Kelly J Andrews

1:36 PM

I assume this table should be add only, and never an update?

Nicholas Dixon

it can be an update

Kelly J Andrews

right - they both would need to be checked as primary key

Nicholas Dixon

they get multiple confirmations so they can confirm more than once

ok

Kelly J Andrews

they are confirming the appt by patient?

Nicholas Dixon

Yes. each patients appointment is shown in the email with a sinlge confirmation button

Kelly J Andrews

1:38 PM

would a patient ever duplicate an apptointmentID?

Nicholas Dixon

they shouldnt

each appointmentid should be distinct

Kelly J Andrews

gotcha

so apptID should be distinct enough on its on

Nicholas Dixon

yes

So we wouldnt need multiple primary key

s

Kelly J Andrews

nope - im also trying to understand :)

did you add all the ampscript in the middle section, or was something else already there?

Nicholas Dixon

This was all there was at first

%%[
SET @ActionType = "Confirm"
INSERTDE(
    "ET_Appointment_Action",
    "AppointmentID",@FamilyAppt,
    "FamilyId",@familyid,
    "PatientID",@PatientID,
    "AppointmentDateTime",@FamilyApptTime,
    "AppointmentClinicID",@ApptClinicID,
    "ActionDateTime",@ActionDateTime,
    "ActionType",@ActionType,
    "Campaign_Name",@CampaignName,
    "Send_id",@JobId,
    "EventDate", RequestParameter('ed')
   )
   ]%%

Kelly J Andrews

1:45 PM

ok

is all of this different per appointmentid?

Set @FamilyFirstNames = Propercase(Field(@row,"PatientFirstName"))
Set @FamilyApptTime = Field(@row,"AppointmentDateTime")
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",""),'')

Nicholas Dixon

Yes, unless it is the same patient in which the first anme would stay the same

Kelly J Andrews

but each row in the lookup table has all of that, correct?

Nicholas Dixon

yes

Kelly J Andrews

sorry for so many question - i think i have it

last question ( i think) did this ever work?

Nicholas Dixon

no problem i was just as lost

no it never worked

Kelly J Andrews

1:48 PM

ok

save a copy somewhere, cause we are going to change it a bit

copy/paste locally

Nicholas Dixon

already have a copy created

Kelly J Andrews

ok

Nicholas Dixon

not touching the original lol

Kelly J Andrews

first thing - copy the insertde function above into the top ampscript block, just under the ENDIF but before the NEXT

Nicholas Dixon

ok

Kelly J Andrews

1:51 PM

now delete all of the ampscript in the middle of the page

fwiw - you can after this go back and reduce the html on the page as well

but do that after you get this working :)

Nicholas Dixon

ok

Kelly J Andrews

so there should be in the middle of your page

</body>
</html>

%%[Else]%%

<!DOCTYPE html>
<html lang="en">
<head>

is that right?

I mean - do you have that as well?

Nicholas Dixon

yes thats what I have

Kelly J Andrews

ok awesome

lets change this

If @i == 1 THEN
Set @FamilyFirstNames = Propercase(Field(@row,"PatientFirstName"))
Set @FamilyApptTime = Field(@row,"AppointmentDateTime")
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",""),'')

to:

Set @FamilyFirstNames = Propercase(Field(@row,"PatientFirstName"))
Set @FamilyApptTime = Field(@row,"AppointmentDateTime")
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",""),'')

Nicholas Dixon

remove the if entirely?

Kelly J Andrews

1:54 PM

removing the if statement - that will give you the individual info for each appointmentid

Nicholas Dixon

ok

Kelly J Andrews

INSERTDE only works in an email message, and not on a landing page

so use insertdata instead - code.exacttarget.com/content-scripting/ampscript/sites/…

Nicholas Dixon

Done

Kelly J Andrews

I do see you are setting a bunch of variable that aren't actually used on the page - let me make sure im not missing something there

Like - FamilyFirstNames

that's never actually used

more of an fyi than anything

Nicholas Dixon

ok I will comb through an get those unused variables out

Kelly J Andrews

1:57 PM

that or actually use them

Nicholas Dixon

I don't think we will use some of those

Kelly J Andrews

ok - so they can confirm appointments more than once?

Nicholas Dixon

yes. they get a 21 day reminder, 10 day reminder, and a 3 day reminder. They can confirm in all 3

Kelly J Andrews

what if they have already confirmed?

Nicholas Dixon

dont know why they get all of them regardless of their action but thats how it is setup

they still get one

Kelly J Andrews

1:58 PM

lol

Nicholas Dixon

dumb i know

Kelly J Andrews

ok

:)

Nicholas Dixon

that was my first question when looking at this process

Kelly J Andrews

that will end up with duplicate data in the DE then

so we have to modify

set the primary key in your DE to appointmentID, and we will use upsertdata

code.exacttarget.com/content-scripting/ampscript/data-extension/…

Nicholas Dixon

Ok

Kelly J Andrews

1:59 PM

sorry for that - we are closer I think

Nicholas Dixon

no problem. Your help is much appreciated

Kelly J Andrews

UPSERTDATA(
"ET_Appointment_Action", 1,
"AppointmentID",@FamilyAppt,
"FamilyId",@familyid,
"PatientID",@PatientID,
"AppointmentDateTime",@FamilyApptTime,
"AppointmentClinicID",@ApptClinicID,
"ActionDateTime",@ActionDateTime,
"ActionType",@ActionType,
"Campaign_Name",@CampaignName,
"Send_id",@JobId,
"EventDate", RequestParameter('ed')
)

im hoping after it's done it works :)

so what this will do is the following -

it will check the ET_Appointment_Action data extension for a row that matches @FamilyAppt to AppointmentID column

If that already exists, is will update the data on that row

if it doesn't exist, it will create a new row

Nicholas Dixon

Sweet!

Kelly J Andrews

Update/Insert

= Upsert

now make sure the Next is changed to Next @i

altogether it should be

Set @familyid = RequestParameter('fid')
Set @HOHid = RequestParameter('hid')

Set @FamilyTotal = Rowcount(Lookuprows(@DELookUp,"FamilyID",@familyid))
Set @GroupRecords = LookupOrderedRows(@DELookUp,0,"AppointmentDateTime","FamilyID",@familyid)

If @FamilyTotal == 1 THEN
Set @Plural = ""
ELSE
Set @Plural = "s"
ENDIF

If Rowcount(@GroupRecords) > 0 THEN

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

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

Set @FamilyAppt = Field(@row,"AppointmentID")
Set @ApptSubscriberKey = Field(@row,"Subscriber Key")

Nicholas Dixon

matches up

Kelly J Andrews

2:04 PM

ok - give that a try

to walk back through it

its grabbing a rowset using the query params

if the rowcount is more than 0, it will loop through the rowset, getting the data from each row, and then updating/inserting a row in the appointment action table

if the row count is 0, then it shows the bottom html (that else in the middle of the page)

Nicholas Dixon

To confirm

%%[
/* 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"

Kelly J Andrews

that looks right to me

not sure if those loops were supposed to do something specific with the page or not

if so - that's doable - just have to move some stuff around

Nicholas Dixon

They weren't. I jsut put it in there as a placeholder

Kelly J Andrews

fire that up and let me know if it works

we can somehow figure out how to update the question/answer to reflect this :)

Nicholas Dixon

Great. Working on it now. Had to change the queries around bc we use this for our 10 day confirmation and 10 days from today is Sunday so the DE is empty. Updating so it looks for 11 days

Kelly J Andrews

2:12 PM

lol

ok

brb

more coffee :)

Nicholas Dixon

I feel ya

Kelly J Andrews

2:26 PM

let me know if you get it working

Nicholas Dixon

I get this error when I do send preview for the email

Invalid row (parameter 1) passed to Field function. Row is null or empty.
Function: Field(@row,"Subscriber Key")

To test this process I do send preview for the email and then confirm the appointment

Kelly J Andrews

does the data extension you are retrieving from have subscriber key?

and is it nullable?

Nicholas Dixon

It has subscriber key and it is set to nullable

Kelly J Andrews

comment that out for now - you aren't passing it in

Nicholas Dixon

ok

Kelly J Andrews

2:30 PM

wait...

ugh

it can't be nullable - you are using it for the patientID as well

Nicholas Dixon

ok so we need to update the DE

Kelly J Andrews

your data will require that to be in there yes

Nicholas Dixon

bc of this right...

Set @FamilyAppt = Field(@row,"AppointmentID")
Set @ApptSubscriberKey = Field(@row,"Subscriber Key")
Set @PatientID = Field(@row,"Subscriber Key")

Kelly J Andrews

correct - the ApptSubscriberKey can still go away - you dont use it

plus, the PatientID could be used more than once - no need to look up the same value twice

Nicholas Dixon

Ok i will remove that an update the DE

Kelly J Andrews

2:35 PM

if you are flipping a DE from nullable to non-nullable, you may run into trouble

can you clear the data, and reimport?

Nicholas Dixon

I created a new DE and re-ran the query

to point to the new DE

Kelly J Andrews

curious how you eneded up with a blank subkey off the query though

Nicholas Dixon

When I look at the DE it isnt blank so I dont know why the error is thrown

other than it being set as nullable in the DE

Kelly J Andrews

is it "Subscriber Key"? or SubscriberKey?

Nicholas Dixon

It is Subscriber Key

Kelly J Andrews

2:37 PM

strange

Nicholas Dixon

Ok the preview worked and I click the confirm appt. Let me check to see if the action was recorded in the LL_Test_ET_Appointment_Action table

Kelly J Andrews

fingers crossed

Nicholas Dixon

Ok this is eve more weird. The error is being thrown for some of the patients as I scroll through the DE, but not others

Thought it might be because the ones being shown the preview had multiple appointments, but there are instances of both occuring. Single and multi appt

Kelly J Andrews

sounds like some sort of data issue happening

the DE's are in multiple locations

03_ApptConfirmation_Lookup

nope, just that one

Nicholas Dixon

The action table is empty also after confirming a couple different appointments

Kelly J Andrews

2:43 PM

ok - then we need to debug some things

can you grab an hid and fid that you know should work?

and then call the url without going to through the email

Nicholas Dixon

I dont follow

I can grab an hid/fid that works

Kelly J Andrews

basically try to by pass the email for now

and call the url with the params on it

whats the landing page url?

basically add &hid=1234&fid=5678 or whatever

lets make sure we know where the issue lies

Nicholas Dixon

pages.e.mykoolsmiles.com/…

Kelly J Andrews

ok - so it doesn't fail, but it doesn't add anything

Nicholas Dixon

right

Kelly J Andrews

2:53 PM

in your code,

is it

UPSERTDATA(
"ET_Appointment_Action", 1,
"AppointmentID",@FamilyAppt,
"FamilyId",@familyid,
"PatientID",@PatientID,
"AppointmentDateTime",@FamilyApptTime,
"AppointmentClinicID",@ApptClinicID,
"ActionDateTime",@ActionDateTime,
"ActionType",@ActionType,
"Campaign_Name",@CampaignName,
"Send_id",@JobId,
"EventDate", RequestParameter('ed')
)

or is it

UPSERTDATA(
"SOMETHING ELSE HERE", 1,
"AppointmentID",@FamilyAppt,
"FamilyId",@familyid,
"PatientID",@PatientID,
"AppointmentDateTime",@FamilyApptTime,
"AppointmentClinicID",@ApptClinicID,
"ActionDateTime",@ActionDateTime,
"ActionType",@ActionType,
"Campaign_Name",@CampaignName,
"Send_id",@JobId,
"EventDate", RequestParameter('ed')
)

Nicholas Dixon

ET_Appointment_Action...palm to face! updating now

to LL_Test...

Kelly J Andrews

hahaha

awesome

Nicholas Dixon

ok let me test again

OK the action appears. Let me see if I can find a preview that works with multi appt

It works!!!!

Kelly J Andrews

hellz yeah!

awesomesauce

go forth and ampscript my friend :)

so here is what we do for the q/a

Nicholas Dixon

Awesomesauce for all

mostly or you though lol

Kelly J Andrews

2:59 PM

edit your question and use the code that was from :

Set @familyid = RequestParameter('fid')

to

Next @i

with the if statment, etc

the real issue here was that if statement

add in the original insertde call that was there as well

I will update my answer to have the code we have now

does that make sense?

Nicholas Dixon

makes sense. thanks again for everything. I can look into the data error because that is most likely in how we are passing the data in

Kelly J Andrews

yeah - should be unrelated to this

Nicholas Dixon

one thing, edit my question with the original code?

Kelly J Andrews

yeah - update your code sample with the original landing page code that had the if statemnt, and the insertDE

you basically had two items - insertde should be upsertdata, and the if statement

Nicholas Dixon

Ok will do

Updated the q/a

Thanks again so much. Let me know if I updated correctly

Nicholas Dixon

6:34 PM

Fixed the q/a. I didnt even see your post with the answer

Found out that the issue with the data is where we are setting the inputs in the email

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")

Kelly J Andrews

6:45 PM

you up and running then?

Nicholas Dixon

We are working out what is wrong with the data

We've checked the data extension and everyones records are identical (as far as configuration) but it only works for some of the patients

When we remove the code above it wont work for anyone

Could it be the way we are setting the patients info up in the email?

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

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

Set @row = Row(@FamilyRecords,@i)
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")

Transcript for

Discussion between Nicholas Dixon and…