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

Question

Question

Best way to create a sequential form number

Forms
Updated March 22, 2023
asked on August 31, 2017

Good afternoon,

I'm looking for the best way to implement a sequential form numbering system in a form field. For example, let's say that I have a form, this form should become form ABC0001 upon submit (inside of the form field that is). Then the next one should be ABC0002.

I guess one way to do it would be to implement a database table in which the number is stored and incremented by 1 when a new form is submitted.

I was just wondering if there is a different way; maybe in which the forms in a folder's repository will be counted and therefore the next available number can be assigned or something. Of course this might require a workflow as well.

I had also thought in using the instance id or a GUID if the sequential numbering isn't possible, or even the current time in seconds, but ideally would prefer something sequential.

Any ideas/posts on how to do this with database, workflow and without database or workflow?

Thank you,


Raul Gonzalez

0 0

Answer

SELECTED ANSWER
replied on August 31, 2017

Hello Raul, 

 

There is a very good solution in the Forms Admin Guide where a stored procedure is used to populate an auto incrementing number.  I have implemented this solution in 7 different forms, using the same stored procedure.  You do not have to worry about a number being duplicated if two users are completing a form at the same time as the stored procedure is called upon the loading of the page.  You can view this solution here:  https://www.laserfiche.com/support/webhelp/Laserfiche/10/en-US/administration/#../Subsystems/Forms/Content/Lookup-Rules.htm%3FTocPath%3DForms%7CCreating%2520a%2520Form%7C_____5

 

 

0 0
View 3 previous replies
replied on August 31, 2017

That is an attractive solution. 

That means that if the user keeps just refreshing the form, the auto-increment number will keep growing right?

0 0
replied on August 31, 2017 • Show version history

Yes, is there a reason numbers have to be consecutive?

1 0
replied on September 1, 2017

It doesn't necessarily have to be one after the other but as long as the numbers are not that off from each other. Maybe with a little javascript or SQL I can compare the last number with the new number and make sure it doesn't get too far or something.

mmmm. I need to think about that. Will get back to you, but please share any other ideas you may have.

0 0
replied on September 1, 2017

Although I believe that this would be the cleanest and simplest method, you could create a SQL table to contain the number which would be populated on the form + 1.  I had to do something very similar to this for an integration to our GIS system where I am updating an asset history table.  In my case I created a SQL view that was configured as ( Select top 1 [objected] + 1 as TicketNumber  From [database].[dbo].[table]  Order By [objected] desc )  When the form is submitted, I have a workflow that creates a new record in the asset table which contains the next in order objectid number.  In your case of using a table to simply contain the ticket number, you would need to have a workflow which committed the new number to the table.  To keep this table from growing and simplify the lookup of the number, your Workflow Custom SQL statement could delete the original number, then write the new number to the table for the next form submission.  The only problem I see with this solution would be if you had two users submitting a form at the same time where the number would be duplicated which is why I think the first suggestion would be the best in your case.  In my case use of this, I don't have to worry about duplicates due to the Forms business process is initiated from only a workflow where there could not be two instances of initial process occurring at the same time. 

0 0
replied on September 5, 2017

Glenn, I preferred your first suggestion of following the Forms Admin Guide and creating a Stored Procedure to update the field on load. Although I'm not too crazy about having the form assign a new number each time it reloads, it seems that if it's in the manual I can always sell it as a first-step.

I tried the tutorial and it worked like a charm.

Thanks again,

Raul Gonzalez

1 0
replied on March 15, 2020

Hello Raul,

 

I followed the Admin Guide steps but my field isn't populating with the number. Is there something else i have to do?

Ideally, The sequence i want to achieve is either alphanumeric where is state a starting constant or numeric with variables such as today() + sequential number anytime a form is submitted.

 

Has someone guide me please?

 

0 0

Replies

replied on August 31, 2017

Hi Raul,

I'll speak to the method of doing this with Workflow/a database table because its worked well for me in the past and is quick to set up.

Starting with a database table consisting of just one value in a column indicating the current forms number:

I can pass this into the Form using a lookup rule without any condition:

After the Form has submitted, I can use a service task to run a Workflow that will increment the current form number by 1.

This method works well because you can have multiple business process point to the same database table and run the same Workflow, and each form submission will consistently use the most current form number. 

replied on March 15, 2020

I've tried the instructions exactly as provided in the Laserfiche Administration Guide on creating a sequence and stored procedures but it does not populate anything in the field using Lookup Rules. Not sure why.

0 0
replied on March 16, 2020

Joseph,  can you please provide some screen shots of your setup in Forms and your stored procedure?

0 0
replied on March 18, 2020

Hi Michelle,

 

I found a good work around. Created an empty document with a field whose value is 0 / any starting number (which will be the incrementing value). Then i created a workflow in the attached to generate the initial set of characters consisting of (two static values and the year, month, day and the updated incremented number) then insert it into forms field and update the field in the empty document with the incremented number which will be incremented anytime the form runs.

1 0
replied on March 22, 2023

I had the same issue where the stored procedure was working but it wasn't populating the field in my lookup rule. The issue was that the stored procedure had an unnamed column and my lookup rule couldn't find it. It literally came up as (No Column Name) I had to create an alias for that column.  I adjusted the stored procedure text as shown below. I added AS NUMBER. Then in my lookup rule I could reference NUMBER as the column and it works.

CREATE PROCEDURE [dbo].[GetNumber]

AS

BEGIN

SELECT NEXT VALUE FOR [dbo].[CountBy1] AS NUMBER

END

GO

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

Sign in to reply to this post.

Details
Related Posts
Loading ...