Stuart Dawson
going with the flow
Posts:35
 |
| 02-03-2010 05:55 AM |
|
Hi
I need help! Upon completion of my form I need to generate a reference number that will be stored against the new record and displayed in a confirmation email to the person filling in the form.
Therefore I want to:
1) call a stored procedure that returns the reference number
2) store this against the new record
3) Fire off a confirmation email to the user containing the reference number.
Step 3 is fine, but I can't see how I can achieve steps 1and 2. Can anyone tell me how to get the results I want?
Thanks,
Stuart |
|
|
|
|
David To
river guide
Posts:2719
|
| 02-03-2010 06:24 AM |
|
HI, you can use the token $(UniqueCompletionID) as a reference number for that job submittal. -- David |
|
|
|
|
Stuart Dawson
going with the flow
Posts:35
|
| 02-03-2010 06:31 AM |
|
Hi David
Thanks for the speedy reply! Unfortunately my client wants the reference number to be in a specific format, hence I need to get the next reference number from the database and store this against my record.
Is there any way you can think of doing this?
Stuart |
|
|
|
|
David To
river guide
Posts:2719
|
| 02-03-2010 06:33 AM |
|
Where is the reference number from? Is it an integer datatype? Also, what is the table name and column name of the SQL table? -- David |
|
|
|
|
Stuart Dawson
going with the flow
Posts:35
|
| 02-03-2010 06:41 AM |
|
Basically, the reference number is based on region code (custom regions based on the nationlaity that the user enters) and an integer. An example would be "AF/00001".
What I was planning was to have a stored procedure that returns the the next available integer part which would then be concatenated with the region code and applied to the form response record. |
|
|
|
|
David To
river guide
Posts:2719
|
| 02-03-2010 06:48 AM |
|
In that case, you can create a textbox field or a hidden field on your form and use an SQL default option like something:
select (region + max(lastinteger)+1) as defaultvalue from tablename
Try something like this using SQL management studio or host/ sql first to see what value it returns to verify your SQL statement is correct. -- David
Region, lastinteger are column names and tablename is the tablename. |
|
|
|
|
Stuart Dawson
going with the flow
Posts:35
|
| 02-03-2010 06:50 AM |
|
Ah, great idea. Will give this a go. Again, thanks for the swift response! |
|
|
|
|
Stuart Dawson
going with the flow
Posts:35
|
| 02-15-2010 04:34 AM |
|
Although this solution works, it means that every time the page is accessed, the reference number in the database gets incremented.
Is there a way to only retrieve and increment the reference number at the point of submission? |
|
|
|
|
David To
river guide
Posts:2719
|
| 02-15-2010 05:37 AM |
|
HI, the SQL statement above will retrieve the max number that is in the database table + 1, but it should not have been written yet until the page is submitted. Another words, you would call the stored procedure in the SQL form completion event, which only gets executed when the page is submitted. How is it possible right now that the reference number is being incremented when you access the page without submitting? -- David |
|
|
|
|
Stuart Dawson
going with the flow
Posts:35
|
| 02-15-2010 06:16 AM |
|
Hi. The problem with this is what happens if User A opens the page and gets the max number that is in the database table + 1 but before he submits the form (and updates the response table), User B goes to the page and also gets the max number that is in the database table + 1? |
|
|
|
|
David To
river guide
Posts:2719
|
| 02-15-2010 06:27 AM |
|
Well, that's the problem with getting the MAX from the database and displaying it to the user on the form. There's no way for you to email the reference number to the user in an email unless you retrieve it from the database beforehand and use that token in the email event.
The next option is not to retrieve the reference number from the database to begin with. But then you won't be able to use dynamic forms email completion event. You will need to modify your stored procedure to retrieve the scope_identity (the reference number that just got entered in the insert statement portion of the stored procedure) and use the xp_sendmail or some equivalent SQL send mail command instead. -- David |
|
|
|
|
Stuart Dawson
going with the flow
Posts:35
|
| 02-15-2010 06:35 AM |
|
Hhmmm...I was hoping for a far simpler solution from Dynamic Forms. i.e. a SQL completion event to retrieve the next reference number and then and email completion event to send this number to the user. It doesn't seem such a big ask but never mind. I will investigate sending the mail outside of Dynamic forms. |
|
|
|
|
David To
river guide
Posts:2719
|
| 02-15-2010 06:45 AM |
|
Well, note that completion events are performed based on the sort order but they act independently of each other. So an SQL completion event to retrieve the next reference number can be done but it does not interact with an email completion event so the email completion event will not be able to retrieve the value from the SQL completion event. The shortname tokens must be on the DF form for it to be used in an email event, not passed from another completion event. I hope this better explains what the problem is. -- David |
|
|
|
|