0

I'm looking at implementing an interactive form as part of a campaign and would like to run a daily response extract via an extract file automation. Before extracting the users, I wanted to check if they had submitted the form within the last 24hrs to send the internal team only responses within the last day.

At present, I'm using a NOW() function within a hidden field but I was wondering if there was a more eloquent way of getting the users form submission date and using this as a condition within the extract automation.

Any help would be greatly appreciated.

Improve this question

1 Answer 1

Reset to default
1

There's nothing wrong with what you're doing if it works ;). The "simple" way of doing it would be to add a field onto the data extension collecting the form data called InsertDate (or something like that) and giving it a default value of now. Then you don't have to deal with the field at all...whenever a row is entered it will automatically set the time for you.

From the point of view of your data extract, I would run the query to be along the lines of:

SELECT * FROM [YourDataExtension] WHERE CAST([InsertDate] AS DATE) = CAST(GETDATE()-1 AS DATE)

This will select everyone that filled out the form yesterday (based on Central Standard Time). Main reason for doing this is the results will be exact - using something like WHERE InsertDate > GETDATE()-1 could potentially miss records as the query won't run at exactly the same time each day if it is part of an automation...it'll be close, but not exact.

Improve this answer
2
  • Ahh, that's a great point! Thanks for that, Jarrett and for the query above. I'm populating form data back into the send DE via the interactive form and a CloudPages landing page (for SMS recipients). I'm guessing it won't update the date column when the responses are captured in the DE?
    – Benjamin Penketh
    Commented Jan 11, 2021 at 21:00
  • @BenjaminPenketh - Yes, you're correct. If you're going back into the sending DE the row is already created so it won't set that value on update. In that case (and without getting into writing AMPscript - I assume you prefer not to do that given you're using Interactive Forms), what you're doing is the simplest option.
    – Jarrett Bush
    Commented Jan 12, 2021 at 5:51

You must log in to answer this question.

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