InfoPath Dev

Hi,

I have a requirement to generate unique ID for each infopath form in 2007, when it is loaded. The format of ID is CX0000000001,where CX remains constant and all the number part are padded with zeroes.

For the next form loaded, the ID should be CX0000000002 and goes on like....... CX0000000010........CX0000000100...etc.Could you please help me to find a way?

Hi sbiju7,

Please try this formula below to generate a consecutive unique ID for your form:

concat("CX", concat(substring("000000000", string-length(max(ID) + 1)), max(ID) + 1))

Note that this formula needs to query the database first before it can generate the number, and then use the max ID of the returned value for determining the number of zeros (0) and the next value for the generated ID. Also, the database should have at least 1 entry in order for the formula to work, since the generated ID is based on the returned ID. So you would need to add logic when submitting data so that the first submit should work, or create a sample data instead for the first one. The only downside I can think of is when two or more users submit entries simultaneously, the generated ID will get duplicated. Hope this helps,

Regards, 

Hi donstephen,

Thanx for your reply.

When I use the formula, I got NaN error.I am new to infopath and I didnt get a logic for the first submission.Since there should be a existing ID i think this is why the error occured.Can you please elaborate the solution since i am a beginner.....

Thanks in advance

Hi,

Yes that is correct, the existing ID will determine the generated ID. A quick resolution is to add another rule which generates the value CX0000000001 for the ID, with the condition if the returned query is less than 1 or equal to 0. I hope this makes sense, if not kindly attach a sample of copy of your form so I could modify your rules a bit.

Regards, 

Hi donstepehen,

Finally i got the solution:D

Thank you so much for your valuabale time and for that formula :)

hi, donstephen I have a similar request to generate ID , but I donot have a db ,how can i get the ID ? the ID should be like 201400001,201400010,.....,20150001,201500002, every year the numbers should start from 0000 again . Thanks

Hi Lan-Huang,

May I know where you are submitting your items to? In order to generate the numbers, you will need to have a basis and this would not be possible if you do not have a database (i.e. submitting the form/s via e-mail only).

Regards,

hi donstephen, Thank you so much for your reply. I hava a idea that i can write the year and the number in a xml file , and read it ,then use the year +(number)+1 to be the ID , but I am not sure it is realizable or write a data in a datatable in db, or count the numbers of the list in a webservice then reference the webservice , for a new guy, don't know which way is better

 Hi Lan-Huang,

Generally the idea here is to query the last xml file sent and use it's ID as the basis. If you are querying a large amount let's say a thousand XMLs, then querying the items and counting the rows is not advisable. In the formula used, you will notice a MAX(ID) function which will only returns the highest number in the column, in these case the highest ID number. The count(ID) will surely affect your form's performance at one point in time when you area already querying a huge volume.

The best approach I think is to use a date range in order to just query the items submitted within the current week or current month (depending on how frequent items are submitted). From there, use the MAX(ID) approach to set the next generated number.

I'm still unsure if you not using a database or not. If so, kindly provide the type of database you are using so I would be able to provide you with a more concrete solution.

Regards,

 Thanks sir.

I have a another solution so solve  it. I create two more columns  to  get the year and the number in the list,  if the year is not equal to the year now, the value will be 0, if the year is same, then max+1,