InfoPath Dev

Hi.  I hope someone on here can help me with this issue.

We have a Infopath 2007 procurement form in a MOSS 2007 library.  I need the form to generate a unique Quote Number every time a form is submitted for the first time.

I have read a couple of articles that said using the automatically generated ID field and then adding 1 after using max(@ID) to find the highest number in the library.

I implemented this and it worked fine.

However, while I was testing it I noticed that if you deleted a form (or 10) and even after emptying the recycling bin the used ID's are still remembered.  This is not really desirable for our purchasing department.  They would like the Quote Numbers to go up constantly and not jump by 10 or 20.

To resolve this I thought I would create another field and use that instead of the ID field.  My data connection queries it but it only seems capable of querying the field from the active form, not the whole library.

I checked my data connection settings and "Include data for the active form only" is not checked.

Does any  have any ideas why the max() function is only looking at the actvie form?  If I turn off the check to see if the form has been submitted before (which stops the Quote ID number from changing after the initial submit), then every time I re-submit it the number increments perfectly, but it is only seeing the active form.

Oh, when it's not working I get NaN in the field.  Even though there are other forms in the library with 2 or 3 for the field value.

Thanks in advance.

Hi --

The Id field from a list or library in SharePoint is only used once, so, yes, if you have an item with an id of '1', that will be the only '1' you'll ever have...

It does seem odd that your data connection is only getting data for the active form! That is very unSharePoint like -- usually it gives you way more data than you really want. Have you tried recreating the data connection, just walking through it again to make sure you've got it right?

I recreated the data connection several times to no avail.

I have now gone back to the initial idea of just using the ID field.  However, I am now experiencing issues with this.

I publish the form, clear the form cache in Infopath of the published template, open the form, fill it in, submit it and it all works fine.

Then when I try to open another one to fill in I get the following error.

The query cannot be run for the following DataObject: GetID

InfoPath cannot run the specified query.

InfoPath either cannot connect to the data source, the service has timed out, or the server has an invalid certificate.

If I clear the cache again it works, until the next time I open the form.

I have tried on different machines but to no avail, the same thing occurs.

A search on google has not turned up any answers.

Any help would be very appreciated.

Thanks.