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