Putting a unique number on each submitted form

[MaxD31415]

My department is new to Live Cycle Designer.  We created a PDF form using LCD ES that is used to approve the purchase of certain types of equipment.  I've been asked to modify it to include a unique ID number, and I need advice on where to start.

The user goes to the web and opens the PDF approval form, fills it out, and clicks a submit button (on the form) which attaches the completed PDF to an email that is sent back to our office.  Once the form reaches us, it has to be circulated to 4 or 5 other offices for review, and we would like to identify each form with a unique number so we can track its progress.

So what I've been asked to do is figure out a way to put an auto-generated unique number on the form at some point before it is emailed to us.  The number can have any number of digits and/or characters (so long as it is human-readable), and doesn't need to be sequential.  I think a 5 or 6 digit number would be fine.

I was thinking the right approach  might be to create a little Access database to generate/store the ID numbers, and then get the Form to capture/read a new number each time the submit button is clicked.  While I can connect to my test database just fine, I don't understand how to run an SQL statement from LCD, or how to do it based on events, like when the form opens or when the submit button is clicked.  I haven't had any luck finding a good, similar example so far.

Is this the right way to go, or is there a better way to do this in LCD?

Thanks for your time,

-Max

[Former Community Member · Accepted solution]

I created a sample showing the different options that are available. Note that if you connect to an outside source like a DB and your Users are using Reader then you will have to Reader Extend the form using LiveCycle to allow this.

Attached is the file I was referring to. If you need the process and the commands for the DB that I mention just ask and I will post those as well. You will need LiveCycle server for that part.

Paul

[MaxD31415]

Paul,

Thanks very much for taking time to create that excellent example PDF!  After some consideration, I think the DB connection probably isn't necessary, though you've given me a good start in case a future project comes along.  I'd be very interested to see the process & commands you mentioned if it wouldn't be much trouble for you to post them.

That's a good tip about enabling Reader Extend, too -- that wasn't on my radar at all =).

You've also shown me FormCalc (thanks =)), which I now see can be used to do almost anything a spreadsheet can do, and how to implement javascript in case we need more functionality, though it will require the user to have javascript enabled, which they might not if they are nervous about security.

I'm still struggling with the UI in LCD, though now I see better how to use the Script Editor (Window --> Script Editor) to implement functions on form objects, and that's a plus.  Ah, the power of a good example =).

In case there are other new LCD users out there, here are the references I found today that seem useful to this project:

FormCalc:

http://help.adobe.com/en_US/livecycle/es/FormCalc.pdf

JavaScript Develpment in Acrobat:

http://www.adobe.com/devnet/acrobat/pdfs/js_developer_guide.pdf

As an aside, the results of the FormCalc Time() function look a bit fishy to me.  If the epoch is the Unix epoch (1/1/1970), the number of seconds is up around 1,256,842,115, and it seems logical that the number of miliseconds would be an order of magnitude higher.  But the Time() function returns a number like 67,304,566, which, if it is miliseconds, would represent about 18 hours of time, wouldn't it?  So I'm missing something here -- any wisdom out there?

Thanks again, Paul.

-Max =)

[Jono_Moore]

FormCalc uses a different epoch. This caught me out a while ago when I was playing around between Javascript and FormCalc.

From the FormCalc User Reference:

Here's some Javascript I've used for generating a unique number based on time and date. It's on the Initialize event of the field that is displaying the number.

if (this.rawValue == null) {
    var d = new Date();
    //divide result by 1000 to get seconds and round off 
    this.rawValue = parseInt(d / 1000);
}
else {
    this.rawValue = rawValue;
}

I rounded the number down to get a more manageable string for people to work with.

The "else" statement is for re-opening a saved form - if there's already a value in the field it won't get overwritten.

[MaxD31415]

That makes a LOT more sense -- 0 GMT would have been 18 hours prior to the time I generated that number.  Thanks a lot for that tip, Jono!

Also, thanks for sharing the javascript function -- you make an extremely good point in that if I'm using the initialize event and I don't protect the initial value, the form will continue to recalculate the ID number each time it is opened...

Realizing that saves me from at least one really bad day .

-Max