Solved

Ensuring unique numbers in InfoPath forms

Posted on 2009-05-14
10
1,391 Views
Last Modified: 2012-05-07
I have an InfoPath form that submits to a SharePoint forms library.

The forms are for tracking invoices and include a generated reference that should be sequential and unique.  The number is based on the department and the highest number so far +1.
E.g. invoices generated for Finance, IT, Legal, HR, IT would be
FI000001, IT000002, LE000003, HR000004, IT000005 respectively.

Currently this works fine; when the form is generated it calculates max(ID)+1 from the existing forms, pads the number with zeroes and prefixes it with the department code.

Each department now needs to make their invoices visible only to members of their department (using permissions, a view on a list isn't enough).
The permissions I can do fine using item level permissions in a SharePoint workflow.

Problem is, in the above example, if Legal were to generate a new invoice then the only existing invoice they'd see would be LE000003, so InfoPath would generate LE000004.  Number 4 is in use by HR though.  The next reference should have been LE000006 but InfoPath can't see 4 or 5.

Short of making a separate number fountain I can't think of a way to get around this problem.

Points to either an answer or helpful comments :)
Thanks in advance
0
Comment
Question by:Dhope
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 25

Expert Comment

by:Robbie_Leggett
ID: 24383747
I achieved a fantastic solution to this by using an incemental numbering DB in SQL and connecting InfoPath to it through a web service.
 
I can give you some detail, but the actual solution itself is being copyrighted subject to being produced for sale.
 
:-)
0
 
LVL 14

Assisted Solution

by:NBSO_ISS
NBSO_ISS earned 100 total points
ID: 24383978
Database solution is definitely the way to go.
We have similar requirements here and using a database solution also allows us to save additional data from the forms that can easily be queried or drawn from the database for reports.
0
 
LVL 6

Expert Comment

by:ramuar1981
ID: 24392203
You need to do the increment on the server when the form is submitted and have the form requery the server, get the new ID, and set it in the form. You cna hen concat your 2 char prefix to it and save it.

This topic is the most-often asked question in this forum. I replied to another person last week. There must be at least two dozen forum threads on this. Have you actually tried to search for them?
0
 
LVL 5

Author Comment

by:Dhope
ID: 24393362
Ramuar,

Yes, I searched.  If I haven't found the right link please point me to where it is.

Can't see how simply requerying the library will work.  The form filename must be the same as its reference and InfoPath will not rename a form after it's been submitted.
Also, a SPD workflow fires on any new or modified forms and SPD workflows that update fields in a form have a habit of failing if that form is still open by the submitting user (a scenario that would almost always be the case if the form was still open and being requeried).
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 24394291
I think he is talking about re-querying the SQL database.  The increments would have to be done in SQL and filled into the form.
What you want is a web service that contains a function which goes to the database creates and saves the new ID and returns it to the form.  Once this function is created, in the form, create a connection for receiving the ID.  At submission time, set all of the variables in the connection to values from the form then "Query using data connection".  After the query, you set the ID in the form to the ID returned from the data connection.
If you require further explanation, let me know.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Author Comment

by:Dhope
ID: 24394848
Sounds a lot like the number generator described in my question :)  I've set up something using SharePoint lists that achieves a similar (if not as elegant as SQL web service) solution.

At submit time, it creates a new item in a 'NumberGenerator' list (using CAML to submit to a list), refreshes the data connection and use max(@ID)+1 for the next number.
It works with multiple users submitting at the same time and avoids the problem with permissions mentioned earlier.
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 24395007
It is very much the same.  I guess the big difference in this case is you could use SQL to easily differentiate between Finance, IT, Legal and HR.  It is actually very simple to set up.  
0
 
LVL 5

Author Comment

by:Dhope
ID: 24395085
Current system is only a stopgap, we'll be extending OOTB workflow with K2 Blackpoint in the next month or two.

I'm going to keep one list of numbers, even though they'll be spread over departments.  This is because some departments have the same prefix despite different permissions (e.g. Debt Trading and Debt Syndication will both have DBT for the prefix but wouldn't be able to see each others' invoices).
The prefixes are only really for the benefit of Accounts (who will be able to see everything) but the permissions may be applied more finely than the prefixes.
0
 
LVL 25

Accepted Solution

by:
Robbie_Leggett earned 400 total points
ID: 24410387
I decided to write an article which is a step-by-step tutorial to creating a unique, incremental numbering system for InfoPath. It can be found here:

http://www.experts-exchange.com/articles/Software/Office_Productivity/Office_Suites/MS_Office/Infopath/Incremental-sequential-numbering-in-InfoPath-forms.html

Enjoy!!

:-)
0
 
LVL 5

Author Closing Comment

by:Dhope
ID: 31581397
Got a solution using an InfoPath only number generator but will certainly use an SQL one soon.
Article will be a big help :D
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now