-1

Currently, I have a microsite which is being used as a form to insert / update data in a data extension.

In data extension, email is NOT a primary key so I can have multiple records for same email because I have a column called "Group". So 1@1.com can exist more than once in the data extension with GroupA, GroupB and GroupC as 3 different rows.

Here is the code / AMPScript

I open the contact / row details like this:

%%[
var @DEColumnEmail, @DEColumnFirstName, @DEColumnLastName, @DEColumnGroup
set @row = row(@rows,@i)
set @DEColumnEmail = field(@row,"Email")
set @DEColumnFirstName = field(@row,"First Name")
set @DEColumnLastName = field(@row,"Last Name")
set @DEColumnGroup = field(@row,"Group")
]%%

<input type="text" name="email" id="email" value="%%=v(@DEColumnEmail)=%%"><br/>
<input type="text" name="fname" id="fname" value="%%=v(@DEColumnFirstName)=%%"><br/>
<input type="text" name="lname" id="lname" value="%%=v(@DEColumnLastName)=%%"><br/>
<input type="text" name="Group" id="Group" value="%%=v(@DEColumnGroup)=%%">

Then to update the record, I use another page where I pass on the values and run UpsertDE AMPScript like this

SET @Email_Address = RequestParameter("email")  
SET @First_Name = RequestParameter("fname")
SET @Last_Name = RequestParameter("lname")
SET @Group = RequestParameter("Group")

IF IsEmailAddress(@Email_Address) THEN
    /* Add/update the record to the appropriate DE */   
    UpsertDE("DATA_EXTENSION_NAME",1,"Email",@Email_Address,"First Name",@First_Name,"Last Name",@Last_Name,"Group",@Group)
ENDIF

/* Redirect to the confirmation page */     
Redirect(@RedirectURL)

ELSE

    /* The text below will be displayed in case of error */ 
    SET @ErrorMessage = "There was an error in your submission. Please return to the previous page and try again."

ENDIF

The limitation of this approach is that, I am only able to update / add one row at a time. I would like to add the record and if there are more Groups then I want to insert / update record just once instead of 3 manual steps.

For group, I want to display checkbox list like this:

<input type="checkbox" name="Group" value="GroupA"> GroupA<br>
<input type="checkbox" name="Group" value="GroupB"> GroupB<br>
<input type="checkbox" name="Group" value="GroupC"> GroupB<br> 

And based on the selected values, 1, 2 or 3 rows should be added / updated in the data extension.

Any advice on what should be my approach or how my code / AMPScript should look look?

  • 1
    You would need to loop through the values passed in Group and preform 3 separate upserts. You should be getting a single comma delimited value from RequestParameter("Group"). Is that Correct? – David Devoy Mar 1 '17 at 19:18
  • Thank you - Could you please show me with an example how to do this? – kuml Mar 2 '17 at 19:56
  • Can you output the value in @Group? example: %%=v(@Group)=%% – David Devoy Mar 2 '17 at 21:53
  • @l-k, please don't expect everyone to write the code for you. Update your question as you work through it or post another specific question if you get stuck. – Adam Spriggs Mar 3 '17 at 16:26
0

This would be my approach:

  • Create a rowset with all the records that match the email.
  • Then count number of records in the rowset.
  • Create a loop that iterates from 1 to the number of records.
  • In each iteration you can update a single record.

%%[

set @Set_of_rows = Lookuprows ("Data_extension_City","State","Texas")

set @Number_of_rows= RowCount(@Set_of_rows)

FOR @i = 1 TO @Number_of_rows DO

  SET @row = Row(@Set_of_rows,@i)
  SET @id = Field(@row,"ID")
  SET @name = Field(@row,"Name")
  SET @ud =UpdateDE("Data_extension_city",1,"ID",@id,"Counter",@i)

NEXT @i

]%%

In this example the data extension is updated, the field counter is filled with a sequential number. It is supposed that the DE has a field called ID, City, State and a counter. Only rows from Texas are updated. The example is meaningless but shows the process.

  • Thank you for your response. Would you be able to help me with the code/ampscript? OR how can I update my code above to accomplish the requirement? – kuml Mar 2 '17 at 19:57

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.