Hello,
It's fairly common in on-prem systems to set up a form that auto-increments a number so that any form can have a unique number - purchase orders, work orders, etc. This help file details how to do it. What is the best way to do this same thing in Cloud?
My first thought is a dedicated table in Cloud that has 1 row and 1 column - the number we want to increment. Forms pulls it into a hidden field, and a visible field adds 1 to it and displays it to the user. On form submission, a rule is kicked off that replaces the value in the table with the 'plus 1' value from the form. But, that would allow simultaneous submissions to share a number, which we don't want, however uncommon it may be.
Maybe I'm overcomplicating it and we can call stored procedures in Cloud and use them basically the same way we do on-prem. Wanted to see what others are doing for this kind of situation in Cloud.
Thanks!
Question
Question
Cloud - Forms - Auto increment number
Replies
Hi Jacob,
I am currently exploring a sandbox version of LF cloud as our company may move to the cloud soon. I have just arrived at this same issue. We have change control, purchase orders and other processes that all need a unique id number and I don't see an obvious replacement to the on-prem solution to this problem. I also had a similar idea to you but it opens the door to duplicate numbers which isn't ideal.
If I come up with a solution I will post it back here. If anyone else has anything they could share on this topic it would be much appreciated.
Cheers,
Neil
Hi Jacob,
I spoke with our local VAR and have been advised of this method;
Create a table with an 'id' column
Rule one, selectID (Query)
- Select the table
- Select the id
- Order by ID DEC
- This brings the highest number to the top.
- Test to make sure this works correctly
Rule two, insertData (Query)
- Select the table
- Select the id and other values you want to pass in. check the box as an input parameter.
- Test by manually entering the data
In a workflow, select the rule selectID
- Select return only the first row
This will now always bring back the highest number and one number only
Next, use a token calculator to +1 to the id's local workflow token
Now select the insertData rule (Query)
- Set the input parameter for the id field as the token calculator output
- Then add in other data already gathered earlier in the workflow
This workflow is called directly after the form is submitted. The initiator can then be emailed a copy with the number showing as it will be written back to the form field via the workflow output parameters being linked to the form's process. Every copy after submission will have the number present. Only the starting form will not.
--
I hope this helps, it has held up fine so far. I have come across another post that sounds like it using a similar method but in a high rate of submission environment and they are experiencing duplications. Our company does not have a high rate of submission on forms, so it is unlikely we will ever experience a duplication error, although this is something to be aware of.
rigorous testing is recommended.
Hi, Neil
Kindly provide more details so that I can follow... I am still fairly new to Laserfiche. If you can pliz include screen shots. Your usual cooperation is greatly appreciated
Do you need it start with a specific number, need to append or prepend something to the number, or rollover at a fiscal year? If not, does the Forms Instance ID work? That is what I have used in the past because it will always be a unique number.
In my case, we use SCR-XXX. with our current on-prem setup.
the form is submitted with no numbers visible.
A workflow is then started that will 'insert data' to SQL writing the doc title and creating a new row.
The SCR number column is auto-incrementing so the next number is generated on the entry of the doc title and date stamp.
I then query the SQL table with the doc title and retrieve the new SCR number.
I then use 'set business process variable' to write this number back to the form field.
This works fine with no duplications. I have also used a stored procedure in the past but this way seems to be the cleanest and I've never had an issue.
the problem with using the form instance ID is these numbers need to run in order with no gaps, for audit reasons.