You are viewing limited content. For full access, please sign in.

Question

Question

Sequential Numbering in Forms

Forms
Updated December 9, 2020
asked on January 20, 2014

 I'm looking for a script for Laserfiche Forms that will query a database and pull the next number in sequence and display it as the unique identifier for this document.  Once this number has been used, the number should not be used again.  This is similar to what the Submission ID is, but from what I can gather, this number is assigned after the submission takes place.  

 

Example:  Purchase Order Request Number pulls from SQL Table, P1234, once submitted, this number is no longer valid so the next submission would be P1235.  

 

Thank you in advance for the help!  

1 0

Answer

APPROVED ANSWER
replied on January 21, 2014

The new SQL stored procedure lookup rules in Forms 9.1 make doing something like this possible. The example in the online help shows how to use a stored procedure to auto-append an incrementing number from the database to a form when it loads, which might solve some of your problems. However, the number is incremented after the form loads (not when it is submitted), so that might not exactly fit your needs. Here's the link to the correct page of the online help.

4 0
replied on January 21, 2014

That is helpful, thank you!

 

At this point it's just a choice for the customer then. We can either generate a PO number when the form loads and run the rick of having unused numbers or generate it after it's submitted and possibly have to email a "corrected" PO number. 

0 0

Replies

replied on January 20, 2014 • Show version history

I created something like this for a demo and didn't have to use a script. If I remember correctly, I leveraged Workflow. Essentially, the form submission would trigger a very simple workflow that would look at the current counter value on a database table and increment it by 1. The next form would then start with a lookup that would grab that counter and put it into a read-only (or hidden) field. Rinse and repeat.

 

You shouldn't use JavaScript. Laserfiche Forms uses it on the client-side and there are various security issues with connecting to back-end databases straight from the browser. It's generally bad practice.

2 0
replied on March 15, 2020

Hi Ege,

 

Could you guide me on how you generated the PO Number with workflow and databases. Used the Admin guide but nothing populates in my field

0 0
replied on December 9, 2020

After the fact, I found this very much appreciated post, and I wanted to add screen shots in the event it provided additional info for someone else out there.  

I Created a small table in MSSQL Management Studio that would generate unique license numbers (via the ID column).  I had forms use a Workflow Service Task to start a workflow to populate the table with very basic info (Names below are fictitious).  The ID column generates a unique number (increases by 1) for each line as forms are submitted.

I then created a Lookup Rule that pulled info from the SQL DB Table ID field back into the License number field in the Form.

The initial post even though it is 6 years old, was very helpful to me once I understood what I was doing.  Hope the addition of screenshots help someone too.

Christine

0 0
replied on January 20, 2014 • Show version history

How would you get that number back on the form? Since I'm on his team he asked me this as well. 

 

I am guessing Nate needs this for a user to print out said form with the PO number on that form for future reference. Would you allow workflow to create the PO number then add a text box, then email that as a pdf back to the user? 

 

I tested it and it's sort of kludgy. We'd have to be very careful that the size of the fields above didn't cause it to shift out of place (maybe plan on it being at the top of the document?)

 

Letting workflow create the PO number also solves a potential issue - if the counter is generated on load of the form you may run into issues with people abandoning the form and now there is a PO that has nothing attached to it. 

 

I just wanted to see if anyone else had any other ideas for this!

0 0
View 1 previous reply
replied on January 20, 2014 • Show version history 
How would you get that number back on the form? 
Since I'm on his team he asked me this as well.

You would trigger a lookup to run at the time the Form loads. Based on what you said, the order of operations would be like this:

 

  1. The form loads, looks up the "current" PO number from the table and puts it into a read-only field at the top of the form.
  2. When the user submits the form, they can get a thank you page that has the PO number on it (new in 9.1) that they can print right away, as well as an email with that information in case they need it later (via a Service Task).
  3. A second service task would trigger the Workflow that would do the following:

 

  • Look at the "current" PO number using Retrieve Form Data
  • Increment it by 1
  • Update the current PO number field in the database with the new number

 

     4. When a new PO form is opened, it would pull the incremented PO number         (which is now "current").

 

If there's something I have misunderstood, let me know. But for the scenario you provided, this should work.

1 0
replied on January 20, 2014

What happens if 2 people are starting a form at the same time though? frown

 

Just to trying to complicate things for you Ege!

 

 

 

My guess is that you check to see if that's still the correct PO number and if there has already been one submitted with that number send a "Your PO number has changed" email. 

0 0
replied on September 29, 2015

So I read through your example and it makes sense.  The one thing I can't figure out though, is how does the lookup knows to pick up the most current P.O. Number, or any P.O. number for that matter.  

I have field that's linked to a sql database column.  If I try and load that field when the form loads, it won't pull any data.  If I use a rule that a field matches a column, then fill in the P.O. number it works, but not until I input something.  

0 0
replied on September 29, 2015

The example with the auto-incrementing column in the Forms help file should give you want you need. You want to call a store procedure in your lookup, not just link to a field.

Anything else that relies on adding 1 to a value read from a table will run into duplicate values when multiple users submit at the same time as Chris pointed out above..

0 0
replied on September 26, 2018

Please tell me there is a way to accomplish this.  As stated, if two users launch the initial submission form with the lookup field populated with P1234 for example, the first user to submit get the number, and the second user now has started there purchasing with a duplicated/invalid number.  Can we increment the field on submission load?  Also, the ability to instead show the purchasing number on event completion doesn't work either, too bad.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...