Auto Populate a field ?

mar0364 · July 7th, 2004, 02:57 PM

I have a form you complete to add documents to a system. Now people are naming the documents themselves and this is causing some problems. What I would like to do is auto populate the document name. My first thought was to use the key/autonumber field but I can't change that (duh). I'd like to when some one adds a record a field in that record is automatically generated "000001.pdf" 02 etc.

I'd appreciate any help or if you can pointing me in the right direction.

Thanks!
Rich

happygv · July 7th, 2004, 03:06 PM

Hi Rich,

What happens, if the original document name is different from what is generated/prepopulated in the database?

Also what would be the extenstion name of the document? Is that just PDFs or can there be .doc / .txt / .xls / anything else.

Cheers!

_________________________
- Vijay G
Strive for Perfection

mar0364 · July 7th, 2004, 07:55 PM

A little more detail.

Lets say a claims adjuster will complete the form and add the information to the database. The adjuster would next do a fancy print screen attach that print screen to the documents and give them to a clerical worker who would scan the document and call up the record associated with the document and save the document with the document name they get from the database record.

Currently they are scanning and saving based on type of document and date created. "court_order031504.pdf" and placing each document in its own type directory. All court orders would go in a court order directory.

If I can get the first method with the auto name with numbers going it will allow us to drop all docs into one directory and allow us to easley compare what has been scanned and not indexed or indexed and not scanned. They will all be pdf docs.

Hope I didn't say to much.

WAKE UP!! LOL

Thanks!
Rich

happygv · July 8th, 2004, 03:18 PM

Not sure, if you are using SQL server or Access or any other.

Considering your Filename would have 8.3 name format

If you are using SQL DB try this.

Code:

Declare @NewVal Varchar(10)
select @NewVal=cast(Coalesce(max(cast(left(FName,len(FName)-4) as int) +1),1) as varchar(15)) from Files
Select replicate('0',7-len(@NewVal)) + @NewVal + '.pdf'

if you decide to go for a higher scope of filename generation you can increase the 7 to a higher number
(ie: if 8.3 format used it is 8-1, if N.3 it is N-1)

If you are using MSACCESS DB try this.

Code:

<% set conn=server.CreateObject("Adodb.connection")
Conn.Open "Your Connection String here."
rs=Server.CreateObject("ADODB.RecordSet")

Sql="select top 1 (left(FName,len(FName)-4) as NewVal from Files order by FName desc"

set rs = Conn.Execute(Sql)
If Not rs.EOF Then
    NewVal=cint(rs("NewVal"))+1
    gennum=""
    For Len(CStr(NewVal)) to 7 ' for higher scope increase 7 to higher number as mentioned above
        gennum = cstr(gennum) & "0"
    Next
    NewVal = CStr(gennum) & CStr(NewVal)
Else
    NewVal = "0000001"
end if
NewVal = CStr(NewVal) & ".pdf"
rs.close

Response.Write NewVal
%>

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

mar0364 · July 9th, 2004, 11:10 AM

Thanks allot man!