Hi - I have an issue coding a DynamicForm instance (v4.10) to do what I want. Here's the story.

I have a form that a user can submit multiple times. And I want to fire an eMail completion event to the submitter the first time the user submits the form; and not send an email on subsequent submissions.

Here's my approach.

First, I run an INSERT SQL form completion event that adds a new record for each submission. One of the fields on this INSERT is the email address.

Next, I added a hidden field that counts the number of times the current email address has submitted the form. I have coded this field to use the following SQL-driven default value:

SELECT COUNT(email) AS 'DefaultValue' FROM myTable WHERE email = '$(Email)'

Finally, I code the Email completion event to trigger only when the value of this hidden field is 1.

But here's the problem. The above SQL query for the hidden field searches for email the literal value of '$(Email) as opposed to the tokenized value of the email address entered during this session. So, for example, if the user entered an email address of myEmail@gmail.com I expect the WHERE clause to find these records. But it is counting records that have a literal email value of $(Email). So obviously the count is not correct, and the email process breaks.

Can you please advise what I am doing wrong?

Thank you
Mark

Do you have a hidden field that collects the email value for the user currently logged in? OR is there a field that the user inputs their email and this value is checked against your database records?

Based on that information we can determine where this is going wrong.

If you have a hidden field that collects the user's email, be sure that this field that counts the number of times this email has submitted the form has a higher sort order than the hidden field that collects the user's email.

If you have an input field that the user their email address, then you'll need to have a question event that fires after the email address is inputted. Then the SQL Default value can take the inputted email address token $(Email) and render it correctly.

Please let me know if you have any questions.

Thanks,

Ryan

Ryan, thank you very much for the response. But I still have one issue to resolve.

As it relates to your question, I am doing this as described in your 2nd alternative. Specifcially we have a text field (called $(EmailAddress) where the user enters their email address.  But I am unable to populate the hidden fiend default value through a question event - I think because the $(EmailAddress) field is the only input field on the form. Hence there is no way for the hidden field's default value to be set through a postback after the $(EmailAddress) field is updated.

Does this sound right to you? If so, what do you suggest?

Thanks in advance.

Mark

Hello Mark,

If email address is the only field on the form that the user can interact with, then I would suggest the following, which is an alternate workflow:

1.) Add a new Question(Combo Box)

2.) Create a Question Event that Executes for any response for the new Combo Box.This is a "dummy" Question Event. However this will become a Question event that you'll repurpose.

3.) Inspect the Combo Box on your form, and you should see JavaScript in the onChange section of the Combo Box. The code you find in this section is what fires a Question Event. Copy this Question Event call.

4.) Add this Question Event call to your Email textbox's Client Side event.

5.) Alter your Question Event. This will become a "Set Default Value" Question Event type. And set it to Execute for any Response based on your EmailHidden field. Honestly this will never fire, since hidden fields can't be interacted with. But since you have copy/pasted the code to call a Question Event into the Email Textbox's Client side event, the Question Event can be executed.

6.) Make sure your Question Event has a SQL Default query, probably something like the following will work:
------------------------------------------------------------------

IF (SELECT COUNT(*) FROM MyTable WHERE EMAIL = '$(EmailAddress)') > 0
BEGIN

Select 'True' As DefaultValue

END

ELSE
BEGIN

Select 'False' As DefaultValue

END

------------------------------------------------------------------

7.) Note that you're query is probably not exactly the same as the example I provided, so please make the necessary changes. Also note, that this query is what will assign a value to your hidden Email field, the value will be True if the user has submitted the form before. Value will be False if the user has never submitted the form before.

8.) Your Email Completion Event should only fire if your hidden Email field = 'False'.


NOTE: The reason we have to do all of these steps is simply because you need to query the database on after the email address is provided by the user. Well Dynamic Form Question Events can't conditionally fire based on a Textbox. Only Combo boxes, Radio Buttons, Checkbox, hidden field values, etc..


I hope these instructions help.

Let me know if you have any questions.

Thanks,

Ryan

Ryan - thank you very much for your detailed explanation. I will have a go at it and get back to you if questions.
Mark