InfoPath Dev

We have a fairly complex form with a datasource that is linked to two SQL Server tables. Typically a user will "Fill Out This Form" and hit submit. We have successfully configred the form to write out the data to the two tables.

Now we want to give them the ability to copy an existing form thus creating a new one with all the same data. (eliminates re-keying) When they hit submit on this new form we want a new database record to be created in addition to the new form added to SharePoint.

Our primary key is "jobID" and it is a sequence number. I tried to blank it out and just re-submit, but it didn't like that because it new I was tampering with the PK.

E

We found a workaround, perhaps someone would comment on this approach:

We discovered that when you click the "New Record" button all datafields were blanked out. I added fields to our data source that were not tied to the db, thinking I could copy the db related fields to these variables. However the New Record button wiped those out too.

Then we discovered the Query fields do not get erased. So, we have a simple 3 button section on the form. First is called "Copy" which we copy all the "datafields" to the "queryfields". Then the user clicks the "New Record" button which wipes out all the datafields and gives us a new record. Then we click the "Paste" button which copies all the stored query fields back into the datafields and then we submit the form.

Not glamorous, but it works. I'll gladly take other suggestons or comments on "gotchas" with this approach.

This seems like it would do the trick, but it might be a little more than necessary.

A question I would like for clarity on your exact situation would be: Was the error message that InfoPath returned when you blanked out the Primary Key something to the effect of "Primary Key cannot contain blank or null value" ?

Also, I tried doing the same thing you did here and InfoPath gave me the error of "Cannot submit form because this would create duplicate entries" i.e. the PK was the same as the one before.

If so, I think your original solution will work, but instead of blanking out the PK, you need to fill it with a new one. To do this, you need to query your database to find out what the next PK is in sequence, and then use the next one on your form. If the PKs in the DB are 1,2,3,4,5,6,7 then instead of blanking out your PK in your form, you will want to set it to 8. This way the user only has to press one button. I think after you do this you will be able to submit all other duplicate data from the previous version of the record.

The only gotcha I can think of with the method you describe above is that it could drastically affect performance. I've been working on a form recently that moves a lot of nodes around and that can get VERY expensive. I'm not sure why, but InfoPath is just not very fast at moving large chunks of XML around, but that is another topic.

Anyway, I hope this information helps!

---------------
Matt Faus
InfoPath Dev Texas

Matt,

Thanks for taking the time to test our scenario and for providing feedback. To answer your questions:

The message we receive is the following:
"The value of an ID key or timestamp has been changed. ID keys and timestamp columns are automatically generated by the data source and cannot be changed."

I got this message whether I blanked out the jobID or changed it. I'm using SQL Server and the field automatically increments when a row is added.

The amount of fields we are copying to the query fields and back is around 25. Currently, I'm just doing it through rules of the buttons (using the set a field's value) option. I may switch to code if the number of fields increases.

Last thing. There is something that the "New Record" button does beyond blanking out the fields. It releases the current record connection somehow. Whatever action it does, works well. Is this code exposed anywhere? I can't seem to find it.

Don't spend too much more time. I think the 3 button approach works for what we need it to. If performance becomes a huge issue, we will look at switching things up.

Thanks again.