Perry
going with the flow
Posts:50
 |
| 10-24-2010 11:25 PM |
|
Hello,
Is that possible to create a field that a auto generated sequential Registration number can be displayed in registration form upon registration/update? The Registration number will be a reference number only and not for login purpose like username, etc.
Thanks/ Perry |
|
|
|
|
Chad Nash
Posts:5260
|
| 10-25-2010 06:27 AM |
|
Hi Perry - This is actually something that is already build into DNN and auto increments for each user added. You can reference this within the display after message as $(UserID).
Thanks,
Chad
|
|
|
|
|
Perry
going with the flow
Posts:50
|
| 10-25-2010 08:31 PM |
|
Hi Chad, That will only be displaied on initial registration as a message, isn't it? Is that possible to make it displaied on the DR form as part of it? Is that also possible to set up a tpye of the Registration No., eg. 5 or 6 digit?
Thanks /Perry
|
|
|
|
|
Candace
river guide
Posts:2431
|
| 10-26-2010 02:29 PM |
|
Hi Perry,
You can try using a GUID that populates at page load as a default for a textbox. Then make sure you map this field to a DNN property in Advanced Field Options so you can display it as a read only for authenticated users. We have a number of similar implementations using this solution. Hope it works for you.
Thanks!
Candace
|
|
|
|
|
Perry
going with the flow
Posts:50
|
| 10-26-2010 04:25 PM |
|
Hi Candace,
Thank you for your suggestion. Is that possible to only have digit in stead of combination with characters?
Thanks/ Perry |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 10-27-2010 09:16 AM |
|
Hello Perry,
You can place this in as a default SQL driven value:
select substring(replace(newid(),'-',''),1,7) as DefaultValue
So go into your question field and under Advanced Field Options you can find a default value section, which should be at the top when expanding Advanced Field Options expandable menu. Then choose SQL Driven radio button for the default value and plug in the query that I provided above.
This will generate a unique character string which may generate letters and numbers together.
Please let me know if this helps you.
Thanks,
Ryan
|
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 10-27-2010 11:37 AM |
|
Hello Perry,
If you don't want any characters in the random generated number than please use this query for your default value:
SELECT CAST(RAND() * 1000000 AS INT) AS DefaultValue
Also be sure to perform a SQL check where if this user number already exists in the database before inserting, if it does, then use this same query again before inserting. The chances of a random number already being selected 2 times is exponentially almost impossible out of a number this large.
If you have any questions please let me know.
Thanks,
Ryan |
|
|
|
|
Perry
going with the flow
Posts:50
|
| 10-27-2010 05:30 PM |
|
Hi Ryan,
That works greatly, thanks. Another part of the operation prefers a sequential number, eg. student number. Is that possible to be automatically generated?
Thanks again/ Perry |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 10-28-2010 09:35 AM |
|
Hello Perry,
Yes this is definitely possible. There are basically two major steps:
One is to query the database like this:
Declare @variable1 nvarchar(200)
Set @variable1 = (Select Max(StudentNumber) AS StudentNum
From yourTable)
IF @variable1 is Null
BEGIN
Set @variable1 = 1
END
ELSE
BEGIN
Set @variable1 = @variable1 + 1
END
Now you have the largest student number from the database which is basically the last record added since the largest number represents the top of the stack of student number. So the reason for the IF statement is because what if there are no students? Then you will start the student number at 1.
Then the next time you're adding a student number the IF statement will fail and the ELSE will be executed and add 1 to the largest value. This will basically give you a sequential number formula.
Part two:
Now I imagine you're using a flat table and not the default storage way from Dynamic Registration correct??
So in your SQL Completion Event use the @variable1 for student number when inserting.
If you have any questions please let me know.
Thanks,
Ryan |
|
|
|
|
Perry
going with the flow
Posts:50
|
| 11-04-2010 05:46 AM |
|
Hi Ryan,
Thank you for your help. I tested the query and an error message returned as Invalid object name 'yourTable'. I have limited knowledge in SQL query. In your query, can you confirm the followings?
Student Number is value in a column
StudentNum is short name of the Question of Student Number in DR form
yourTable is a table of DynamicRegistration_Question
Thanks/ Perry |
|
|
|
|
Perry
going with the flow
Posts:50
|
| 11-05-2010 05:05 PM |
|
Hi Ryan,
What was incorrect I did in the SQL query with return in error message? Invalid object name 'yourTable'.
Thanks/ Perry |
|
|
|
|
Chad Nash
Posts:5260
|
| 11-08-2010 10:15 AM |
|
Hi Perry - The only way to really do this is to: 1. Create your own table and setup a primary key that is also an auto number / incremental number. So when you insert data into this table it will auto increment that number 2. Create a SQL insert statement that will run when the form is submmited to add a record to that. It could just include the data time etc... So your table could simply be 'MyAutoNumberID' and 'MyDateTime' and basically the SQL event would just insert MyDateTime so that the MyAutoNumberID would increment. 3. Use the SQL Default to select the most recent item from the table (probably Select Top 1 Records ordered by the most recent date) and then add + 1 to that number. So maybe even selecting Max(MyAutoNumberID) + 1 as your SQL Default. This would be the only way that I would know of to get the results you are needing. Writing SQL or handling this isn't part of our standard support but you might google around or pick up our minimum hours of 6 for Premium Support at http://www.datasprings.com/Premium-Support. Thanks! -Chad |
|
|
|
|
Perry
going with the flow
Posts:50
|
| 11-09-2010 11:37 PM |
|
Hi Chad,
That makes a lot of sense.
Thanks/ Perry |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 11-17-2010 12:03 PM |
|
Hello Perry,
Glad to hear that you've obtained useful information for you to move forward. If you ever have any questions please let us know.
Thanks,
Ryan |
|
|
|
|