It appears my customer and I are on the cutting edge of InfoPath development with Form Services. We are CONSTANTLY running into issues and bugs. I try my best to post what we find to help anyone else running through the same issues. This issue is a doosy.
Our scenario is this: We have 3 server farms setup, one for development, one for staging and testing and one for production. We are creating InfoPath forms in Visual Studio 2008 and each have loads of codebehind (handling loading, saving, webservices, etc). We publish the form, with several promoted fields, to Dev first and make sure it works, then publish it to Staging, then after rounds of testing it's released on the Production server. The promoted fields are almost assigned to existing site columns. This works great, in theory.
The issue is that once in a while, for no explained reason, whether publishing to any of the three servers, if I were to add a new field to a form, or modify the schema a little, that's when it hits the fan. The magic that is the InfoPath Publishing Wizard will decide to create a new site column for a promoted field that was already assigned to a site column! YES, it's true!
So for example we have a field in every InfoPath form called FX Form ID. Walking through the wizard, I make sure the FX Form ID field is bound to the correct site column. After the wizard is complete, I deactivate the current form, upload the form and the reactivate it. I take a look at Site Columns in Site Settings and I now see TWO FX Form ID FIELDS! Grrr...
I pulled up
SharePoint Manager 2007 (awesome tool for analyzing exactly what is going on in SharePoint) on the server and took a look at the list of fields for the root web. Sure enough, there are two of the same field. They are exactly the same except for the Ids and Internal Names are different, each their own unique GUID.
Unfortunately, there is no easy way to remap the existing data and fields into a single column. I'd bet there is a way in the SQL tables to do it, but that'll void any support from Microsoft, and probably make matters worse. What you can do is moving forward, make sure it doesn't happen again.
The Work Around
Working closely with a Microsoft SharePoint Architect (as soon as he get's a blog I'll point you to it), we figured out a workaround. It works great, just very tedious. Here's what you do:
1. Close the mnifest.xsf design view and open it in XML editor (right click and select Open Wth)
2. Search for the field name, in this example Form ID. You should see something similiar to this towards the end of the file.
<xsf:field name="Form ID" columnName="{81BDA8CC-A286-405F-8A3B-E5ADB18D9FFB}" node="/my:myFields/fxformstate:FormState/fusionx:FormID" type="xsd:string"></xsf:field>
3. Now search for the columnName value, the GUID, in this same file. You should find something like this.
<
xsf2:fieldExtension columnId="f765018d-6b66-4b03-8408-2d6a1f8f0060" readWrite="yes" columnName="{81BDA8CC-A286-405F-8A3B-E5ADB18D9FFB}"></xsf2:fieldExtension>
4. Note the columId value and open SharePoint Manager 2007 on the SharePoint server.
5. Expand the website then Fields (or if you want to see what existing forms are using, expand ContentTypes then the form name, then Fields).
6. Find the field and note the Id. Does it match the columnId value from the manifest file? If so, then you're fine, go get a coffee.
7. If they do not match, then InfoPath thought better of using the existing column and wanted to use another. Or if your columnId is blank, ="", then it wanted to create a new one. This here is the mystery, why did InfoPath decide the field you selected wasn't important enough, I don't know.
8. Copy the value from SPM and replace the value in the columnId in the manifest file.
9. Close the manifest file and open it normally in VS, allowing the design view to appear. You can now publish the form. When publishing, DO NOT modify the field in the Column list. This may overwrite the value you entered in. Continue through the publishing wizard and upload to MOSS.
If all goes well, the new content type or the upgrade should use the same column as selected. To verify, open SharePoint Manager on the server, expand the website then Content Types and find your form name and check the ID of the Field.
I hope this helps!