First off, TIA for any support or insight.
I'm a front-end user with little or no access to the backend of our corporate deployment of SP 2010.
Developing a form that will automatically increment the "submission" number on a current form. When a lot number is entered I fire a data connection to check the library and look for forms with the same lot number. If an existing form with a matching lot is found (results returned by Created, Descending to return to most current existing form) the submission number from the existing form is returned and incremented by 1 on the current form. IE - if the library has lot 12345678 and the latest submission is 7 the query will return "7" and the field on the current form is incremented to "8."
To avoid pulling down a huge query result set I have the form set the Lot Number Query field in the data connection to the lot number on the current form. Then, execute the query and return the Data value Submission Number.
The issue that I am running into is that setting the query field will only return records that were created by that same user. i.e.: if I create multiple forms with the same lot number it autoincrements correctly. However, if I enter a lot number that is on a form created by a coworker it doesn't return...
If I set the form to just run the query without setting any query fields, and the filter the results within the form it works as it should. The difficulty is that the library will get fairly large and I don't want to be pulling down thousands of entries.
Any suggestions?