Inserting Random Records

[shearak]

Hi,

I have a simple form that I want to automate the case number to insert a random case number. How do I insert a random case number for each case I insert to the database?

Form:

<cfform name="editForm" action="#myself##XFA.InsertCase#" method="post">
<cfinput name="save" type="submit" value="Save" />
<cfinput name="cancel" type="button" onClick="window.location.href='#XFA.Home#';" value="Cancel" />
<table width="90%" cellpadding="5">
<tr>
<th width="11%" class="left">Bank Name:</th>
<td width="29%"><cfinput type="text" name="bank_name" size="40" message="Provide a Bank Name" validateat="onSubmit" required="yes" /></td>
<th width="12%" class="left">Case Number:</th>
<td width="16%"><cfinput type="text" name="case_number" size="20" message="Provide a Case Number" validateat="onSubmit" required="yes" /></td>
</tr>
<tr>
<th class="left">City:</th>
<td><cfinput type="text" name="city" value="" /></td>
<th class="left">State:</th>
<td><cfinput type="text" name="state" size="2" maxlength="2" /></td>
</tr>
<tr>

<th class="left">District:</th>
<td>
<select name="district" size="1" >
<option value="">
<cfoutput query="District">[Select District]</option>
<option value="#District.district_number#" >#District.district_name#</option>
</cfoutput>
</select>
</td>
<th class="left">Completed:</th>
<td><input name="completed" type="checkbox" value="yes" ></td>
<th width="9%" class="left">S.R. Withhold</th>
<td width="23%"><input name="sr_withhold" type="checkbox" value="yes"></td>
</tr>
<tr>
<th class="left" valign="top">Comments:</th>
<td colspan="7"><textarea name="comment" cols="100" rows="6" wrap="virtual" ></textarea></td>
</tr>
</table>
</cfform>

Insert statement:

<cfparam name="form.completed" default="No">
<cfparam name="form.sr_withhold" default="No">

<cfquery name="UpdateCase"
datasource="#request.app.DSN#"
username="#request.app.user#"
password="#request.app.password#">
INSERT INTO #request.app.DB2DB#.anc_case
(case_number
,bank_name
,city
,state
,district
,comment
,completed
,sr_withhold)
VALUES
('#form.case_number#',
'#form.bank_name#',
'#form.city#',
'#form.state#',
'#form.district#',
'#form.comment#',
'#form.completed#',
'#form.sr_withhold#')
</cfquery>

[chuckbeckwith]

Why not use:

[cfset case_number = #DateFormat(now(),'yyyymmdd')##TimeFormat(now(),'HHmm')#]

which will datestamp your case number

[shearak]

What I like to do is not have the case number field on the form and do all the randome case number generation in the backround? So with this method: [cfset case_number = #DateFormat(now(),'yyyymmdd')##TimeFormat(now(),'HHmm')#] will it be place on the form or the action page.

thanks again

[insuractive]

You would place it on your action page. If you don't actually need to reference the case number on the action page after you add it to your database, you can actually place that logic right in your SQL statement:

INSERT INTO #request.app.DB2DB#.anc_case
(case_number
,bank_name
,city
,state
,district
,comment
,completed
,sr_withhold)
VALUES
('#DateFormat(now(),"yyyymmdd")##TimeFormat(now(),"HHmm")#',
'#form.bank_name#',
'#form.city#',
'#form.state#',
'#form.district#',
'#form.comment#',
'#form.completed#',
'#form.sr_withhold#')

Keep in mind, the code provided creates a 12 digit number - make sure this length works with your database scheme. Also, keep in mind that this is not really a good way to generate a unique number - 2 people could submit the same form during the same minute and you would get the same case number. Probably not good for what you want.

Do your case numbers have to be unique? If so, what are the restrictions? digits only? Length Restrictions?

[shearak]

Michael, you have good point about it being a unique number. How would you go about doing it so it does generate case numbers that are unique? The only restrictions is it has to be digits and 10 to 12 in length.

Thanks in advance,

[Newsgroup_User]

a timestamp of format yymmddhhmmss will meet your requirements and be
almost unique, save for a case when 2 records are inserted at exactly
same time to a second...
depending on your database you may have an option of creating a an
autonumber zero-filled field in your table, which will generate he
number automatically in the format you specify, i.e. 000000000001,
000000000002, ...

[chuckbeckwith]

You can create random numbers with RandRange for number less than 100,000,000 but then you'd have to make sure the number was unique. If the case_number was the primary key, the database wouldn't allow duplicates.

Of course, adding seconds to the time stamp would probably prevent duplicates TimeFormat(now(),'HHmmss')

[insuractive]

Do the case numbers actually have to be random? Can you use an autonumber field in your database like Azadi suggests? The timestamp (seconds) suggestion certainly meets your requirements in terms of length but you do run into 2 issues:

1) Since the number generated is a timestamp,it isn't really random. If the whole point of generating a random number was to obscure information about the case, then that might be a problem. On the other hand, if obscuring the information is not a problem, it might be beneficial to have a case number that reveals some information (i.e. when the record was created)

2) If you are designing this for a site for which you expect a large number of traffic, you are pretty much guaranteed that at some point you are going to have 2 case numbers submitted at the same time. However, if the system is intended to be operated by a small number of operators, this is probably much less likely. You can always pre-query the database to check for the existance of a case number (not a bad idea, anyway), and alter the timestamp to make it unique (increment by 1 sec maybe?)

If you really need to have unique, totally random (or as close to it) numbers in the 10-12 character range, perhaps a better solution would be to generate the numbers outside of CF, and then store them in a table in your database. That would ensure that they are all random, unique, and reveal no information about the case they are associated with. You'd just have to make sure you set some alerts to warn you when you are nearing the end of your allotted numbers so you can generate some new ones.

[shearak]

Thanks everyone for your great advise and code help. I used the timestamp method as this application is only used by three people. The client actually does use date in the current application for case numbers, so using the timestamp is actually close to what they enter now for the case number.