1

I have a quick question to confirm what I’m doing is possible, or if there is a simpler way to handle it.

The goal is to upload a list, which sends an automated email with the content from the list, then if the end user needs to update the date, they will go to a cloudpage with a form that pulls everything from the data extension, as prefilled in values. They can edit the fields which are submitted to a new data extension, / - All of this is working. /

On submit, it will initiate a triggered send that pulls all the data from the new DE, and send the updated fields to our data team to make sure the data is updated in our oracle database (Yes, I cannot automatically update it in the oracle, higher ups don’t want to change this process yet.)

What I’m running into is the fact I don’t know how to pull the Primary key from the original DE and match it against the new DE to make sure I’m pulling the correct content. I had it working when I was applying a timestamp on form as a hidden field but the ampscript resolves on load so the time is likely going to be different so that’s not a consistent way.

Any thoughts would be helpful, here is the ampscript from the triggered email send.

%%[ /* Modify to view AMPScript <div style="display:none"> */


var @Timestamp, @ContentFieldRows, @ContentFieldRow, @email, @SpouseID, @formalName, @Jointformalname, @birthday, @SpName, @SpBirthday,
@Nickname, @PerfClassYear, @PerfSchool, @Employer, @Title, @JointSal, @Address, @Address2, @Address3, @City, @State, @zip, @Country,
@BusAddress, @BusAddress2, @BusAddress3, @BusCity, @BusState, @BusZip,  @Telephone, @NameTag, @EnvName, @SpNameTag, @Degree, @Degree2,
@Degree3, @Degree4, @School, @School2, @School3, @School4, @SpDegree, @SpDegree2, @SpDegree3, @SpDegree4, @SpSchool, @SpSchool2,
@SpSchool3, @SpSchool4, @Wise, @SpWise, @Medallion, @Comments, @BusCountry, @Spouse_email, @SP_ID_Number, @ID_Number


set @Timestamp = format(now(),"yyyy-MM-ddThh:m")

/* retrieve rows from Lawn Society Submissions DE */
set @ContentFieldRows = LookupRows("Lawn Society Form Submissions", "Timestamp", @Timestamp)

/* check to see if row is found */
if rowcount(@ContentFieldRows) > 0 then

  /* retrieve value from row and field */
set @ContentFieldRow = row(@ContentFieldRows, 1) /* get row #1 */
set @formalName = field (@ContentFieldRow, "COMPLETE_NAME")
set @email = field(@ContentFieldRow, "EMAIL_ADDRESS")
set @Spouse_email = field(@ContentFieldRow, "SPOUSE_EMAIL_ADDRESS")
set @SpouseID = field(@ContentFieldRow, "SPOUSE_ID")
set @Jointformalname = field(@ContentFieldRow, "JOINT_FORMAL_NAME")
set @birthday = field(@ContentFieldRow, "BIRTH_DT")
set @SpName = field(@ContentFieldRow, "SPOUSE_NAME")
set @SpBirthday = field(@ContentFieldRow, "SP_BIRTH_DT")
set @Nickname = field(@ContentFieldRow, "NICKNAME")
set @PerfClassYear = field(@ContentFieldRow, "PREF_CLASS_YEAR")
set @PerfSchool = field(@ContentFieldRow, "PREF_SCHOOL_DESC")
set @Employer = field(@ContentFieldRow, "EMP_COMPANY_NAME")
set @Title = field(@ContentFieldRow, "EMP_TITLE")
set @JointSal = field(@ContentFieldRow, "JOINT_SALUTATION")
set @Address = field(@ContentFieldRow, "MAIL_ADDR_STREET1")
set @Address2 = field(@ContentFieldRow, "MAIL_ADDR_STREET2")
set @Address3 = field(@ContentFieldRow, "MAIL_ADDR_STREET3")
set @City = field(@ContentFieldRow, "MAIL_ADDR_CITY")
set @State = field(@ContentFieldRow, "MAIL_ADDR_STATE")
set @zip = field(@ContentFieldRow, "MAIL_ADDR_ZIP")
set @Country = field(@ContentFieldRow, "MAIL_ADDR_COUNTRY")
set @BusAddress = field(@ContentFieldRow, "BUS_ADDR_STREET1")
set @BusAddress2 = field(@ContentFieldRow, "BUS_ADDR_STREET2")
set @BusAddress3 = field(@ContentFieldRow, "BUS_ADDR_STREET3")
set @BusCity = field(@ContentFieldRow, "BUS_ADDR_CITY")
set @BusState = field(@ContentFieldRow, "BUS_ADDR_STATE")
set @BusCountry = field(@ContentFieldRow, "BUS_ADDR_COUNTRY")
set @BusZip = field(@ContentFieldRow, "COMPLETE_NAME")
set @Telephone = field(@ContentFieldRow, "TELEPHONE")
set @NameTag = field(@ContentFieldRow, "NAME_TAG_NAME")
set @EnvName = field(@ContentFieldRow, "ENVL_SALUTATION_NAME")
set @SpNameTag = field(@ContentFieldRow, "SP_NAME_TAG")
set @Degree = field(@ContentFieldRow, "DEGREE_YEAR_1")
set @Degree2 = field(@ContentFieldRow, "DEGREE_YEAR_2")
set @Degree3 = field(@ContentFieldRow, "DEGREE_YEAR_3")
set @Degree4 = field(@ContentFieldRow, "DEGREE_YEAR_4")
set @School = field(@ContentFieldRow, "DEGREE_SCHOOL_1")
set @School2 = field(@ContentFieldRow, "DEGREE_SCHOOL_2")
set @School3 = field(@ContentFieldRow, "DEGREE_SCHOOL_3")
set @School4 = field(@ContentFieldRow, "DEGREE_SCHOOL_4")
set @SpDegree = field(@ContentFieldRow, "SP_DEGREE_YEAR_1")
set @SpDegree2 = field(@ContentFieldRow, "SP_DEGREE_YEAR_2")
set @SpDegree3 = field(@ContentFieldRow, "SP_DEGREE_YEAR_3")
set @SpDegree4 = field(@ContentFieldRow, "SP_DEGREE_YEAR_4")
set @SpSchool = field(@ContentFieldRow, "SP_DEGREE_SCHOOL_1")
set @SpSchool2 = field(@ContentFieldRow, "SP_DEGREE_SCHOOL_2")
set @SpSchool3 = field(@ContentFieldRow, "SP_DEGREE_SCHOOL_3")
set @SpSchool4 = field(@ContentFieldRow, "SP_DEGREE_SCHOOL_4")
set @Wise = field(@ContentFieldRow, "WISE_FIRST_CLASS_YEAR")
set @SpWise = field(@ContentFieldRow, "SPOUSE_WISE_FIRST_CLASS_YEAR")
set @Comments = field(@ContentFieldRow, "comments")
set @Medallion = field(@ContentFieldRow, "lawn_medallion_requested")
set @ID_Number = field(@ContentFieldRow, "ID_NUMBER")
set @SP_ID_Number = field(@ContentFieldRow, "SPOUSE_ID")
else

  /* default value if row is not found */
  set @ContentFieldsrows = concat("Content not found for", "@ContentFieldRows")

endif

/* </div> */   
]%%

I was able to solve this, I changed the Timestamp to just today's day, not including minutes, then I changed the lookup to the primary key that verifies the data to pull in, I was 99% there.

Here is the updated code that's working just for others who might have improvements I can make, I am full stack developer and Ampscript is fun to play with.

%%[ /* Modify to view AMPScript <div style="display:none"> */


var @Timestamp, @ContentFieldRows, @ContentFieldRow, @email, @SpouseID, @formalName, @Jointformalname, @birthday, @SpName, @SpBirthday, 
@Nickname, @PerfClassYear, @PerfSchool, @Employer, @Title, @JointSal, @Address, @Address2, @Address3, @City, @State, @zip, @Country, 
@BusAddress, @BusAddress2, @BusAddress3, @BusCity, @BusState, @BusZip,  @Telephone, @NameTag, @EnvName, @SpNameTag, @Degree, @Degree2, 
@Degree3, @Degree4, @School, @School2, @School3, @School4, @SpDegree, @SpDegree2, @SpDegree3, @SpDegree4, @SpSchool, @SpSchool2, 
@SpSchool3, @SpSchool4, @Wise, @SpWise, @Medallion, @Comments, @BusCountry, @Spouse_email, @SP_ID_Number, @ID_Number, @key 


set @Timestamp = format(now(),"yyyy-MM-dd")
set @key = FIELD(ROW(LookupRows("Lawn Society Form Submissions","Timestamp",@Timestamp),1), "ID_Number")

/* retrieve rows from Lawn Society Submissions DE */
set @ContentFieldRows = LookupRows("Lawn Society Form Submissions", "ID_Number", @Key)

/* check to see if row is found */
if rowcount(@ContentFieldRows) > 0 then

  /* retrieve value from row and field */
 set @ContentFieldRow = row(@ContentFieldRows, 1) /* get row #1 */
 set @formalName = field (@ContentFieldRow, "COMPLETE_NAME")
 set @email = field(@ContentFieldRow, "EMAIL_ADDRESS")
 set @Spouse_email = field(@ContentFieldRow, "SPOUSE_EMAIL_ADDRESS")
 set @SpouseID = field(@ContentFieldRow, "SPOUSE_ID")
 set @Jointformalname = field(@ContentFieldRow, "JOINT_FORMAL_NAME")
 set @birthday = field(@ContentFieldRow, "BIRTH_DT")
 set @SpName = field(@ContentFieldRow, "SPOUSE_NAME")
 set @SpBirthday = field(@ContentFieldRow, "SP_BIRTH_DT")
 set @Nickname = field(@ContentFieldRow, "NICKNAME")
 set @PerfClassYear = field(@ContentFieldRow, "PREF_CLASS_YEAR")
 set @PerfSchool = field(@ContentFieldRow, "PREF_SCHOOL_DESC")
 set @Employer = field(@ContentFieldRow, "EMP_COMPANY_NAME")
 set @Title = field(@ContentFieldRow, "EMP_TITLE")
 set @JointSal = field(@ContentFieldRow, "JOINT_SALUTATION")
 set @Address = field(@ContentFieldRow, "MAIL_ADDR_STREET1")
 set @Address2 = field(@ContentFieldRow, "MAIL_ADDR_STREET2")
 set @Address3 = field(@ContentFieldRow, "MAIL_ADDR_STREET3")
 set @City = field(@ContentFieldRow, "MAIL_ADDR_CITY")
 set @State = field(@ContentFieldRow, "MAIL_ADDR_STATE")
 set @zip = field(@ContentFieldRow, "MAIL_ADDR_ZIP")
 set @Country = field(@ContentFieldRow, "MAIL_ADDR_COUNTRY")
 set @BusAddress = field(@ContentFieldRow, "BUS_ADDR_STREET1")
 set @BusAddress2 = field(@ContentFieldRow, "BUS_ADDR_STREET2")
 set @BusAddress3 = field(@ContentFieldRow, "BUS_ADDR_STREET3")
 set @BusCity = field(@ContentFieldRow, "BUS_ADDR_CITY")
 set @BusState = field(@ContentFieldRow, "BUS_ADDR_STATE")
 set @BusCountry = field(@ContentFieldRow, "BUS_ADDR_COUNTRY")
 set @BusZip = field(@ContentFieldRow, "COMPLETE_NAME")
 set @Telephone = field(@ContentFieldRow, "TELEPHONE")
 set @NameTag = field(@ContentFieldRow, "NAME_TAG_NAME")
 set @EnvName = field(@ContentFieldRow, "ENVL_SALUTATION_NAME")
 set @SpNameTag = field(@ContentFieldRow, "SP_NAME_TAG")
 set @Degree = field(@ContentFieldRow, "DEGREE_YEAR_1")
 set @Degree2 = field(@ContentFieldRow, "DEGREE_YEAR_2")
 set @Degree3 = field(@ContentFieldRow, "DEGREE_YEAR_3")
 set @Degree4 = field(@ContentFieldRow, "DEGREE_YEAR_4")
 set @School = field(@ContentFieldRow, "DEGREE_SCHOOL_1")
 set @School2 = field(@ContentFieldRow, "DEGREE_SCHOOL_2")
 set @School3 = field(@ContentFieldRow, "DEGREE_SCHOOL_3")
 set @School4 = field(@ContentFieldRow, "DEGREE_SCHOOL_4")
 set @SpDegree = field(@ContentFieldRow, "SP_DEGREE_YEAR_1")
 set @SpDegree2 = field(@ContentFieldRow, "SP_DEGREE_YEAR_2")
 set @SpDegree3 = field(@ContentFieldRow, "SP_DEGREE_YEAR_3")
 set @SpDegree4 = field(@ContentFieldRow, "SP_DEGREE_YEAR_4")
 set @SpSchool = field(@ContentFieldRow, "SP_DEGREE_SCHOOL_1")
 set @SpSchool2 = field(@ContentFieldRow, "SP_DEGREE_SCHOOL_2")
 set @SpSchool3 = field(@ContentFieldRow, "SP_DEGREE_SCHOOL_3")
 set @SpSchool4 = field(@ContentFieldRow, "SP_DEGREE_SCHOOL_4")
 set @Wise = field(@ContentFieldRow, "WISE_FIRST_CLASS_YEAR")
 set @SpWise = field(@ContentFieldRow, "SPOUSE_WISE_FIRST_CLASS_YEAR")
 set @Comments = field(@ContentFieldRow, "comments")
 set @Medallion = field(@ContentFieldRow, "lawn_medallion_requested")
 set @ID_Number = field(@ContentFieldRow, "ID_NUMBER")
 set @SP_ID_Number = field(@ContentFieldRow, "SPOUSE_ID")
else

  /* default value if row is not found */
  set @ContentFieldsrows = concat("Content not found for", @ContentFieldsrows, " and ", @Timestamp)

endif

/* </div> */    
]%%
  • To make sure I understand, the issue is that after the form is filled out, the new Email is sent using your Triggered Send DE, but you cannot find a way to then update the original DE with this info? – Gortonington Apr 8 at 14:14
  • 1
    @Gortonington Not quite, the issue is that in the triggered email, I want to pull the data for the new submission and make sure I'm pulling the correct data in, I was trying to match it against the first DE's primary key, which will match the submission DE, then I abandoned that idea for the timestamp, I THINK Adam Spriggs just helped me figure it out. – Patrick Powell Apr 8 at 14:28
  • Glad you were able to find the solution and that @AdamSpriggs was able to help solve your issue! – Gortonington Apr 8 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.