How to: Copy a record with infopath

P

Patrick Temple

Ok heres one I'm having trouble with.

I want to copy a record on my SQL database in an infopath form. Example: I
call up a record with idNumber=607, erase idNumber, change any other data
needed, resubmit to database as a new record. Doesn't sound to hard but I
run into an error about unique IDs and Timestamps.

My guess is that Infopath is using a timestamp and the ID as identifiers for
the record. To keep track of multiple changes to the database, something
like that anyway. So is there a way to clear Infopaths values for this, so
it treats the data as a new submission and not the record you already pulled?

I have a real need for this, as my company wants to copy feature
requirements from product to product.

I'll appreciate any help I can get on this one.

--Patrick Temple
 
P

Patrick Temple

Scott,

Thanks for the example. A couple questions:

1) Looks like the example is Vbscript, is there one with Jscript?

2) I'm not using a repeating table because I only want one record. Is this
a problem?

3) I tried to get the section Id by using the steps in the example but my
Advanced Tab didn't have that information. Is there another way to get the
section ID?

Scott L. Heim said:
Hi Patrick,

One way to accomplish this is to use an XML Resource File to store field
data from the selected record and then access this resource file to
populate a new record. Here are some sample steps that I believe work as
you need:

- Create an XML file as follows called: MyID.xml

<?xml version="1.0" encoding="UTF-8"?>
<root>
<data>
<companyname></companyname>
<phone></phone>
</data>
</root>

- Create a new InfoPath solution from the Northwind Shippers table
- Add the ShipperID from the queryFields to the query section
- Add the Shippers node from the dataFields as a Repeating Table
- Add MyID.xml as a new data connection and then make sure you do include
this as a Resource file (the last screen of the wizard)
- Add a new button on the form
- Add the following code to the click event of the button:

Sub CTRL6_7_OnClick(eventObj)
Dim objShipper
Dim objNewShipper

Set objShipper =
XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Shippers")

StoreSelectedCompany objShipper.selectSingleNode("@CompanyName").text,
objShipper.selectSingleNode("@Phone").text
XDocument.View.ExecuteAction "xCollection::insert", "Shippers_1"

Set objNewShipper =
XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Shippers[pos
ition() = last()]")
SetNewInfo objNewShipper
End Sub

Sub StoreSelectedCompany(strCompany, strPhone)
Dim objResourceFile
Set objResourceFile = XDocument.GetDOM("MyID")

objResourceFile.selectSingleNode("root/data/companyname").text = strCompany
objResourceFile.selectSingleNode("root/data/phone").text = strPhone
End Sub

Sub SetNewInfo(newNode)
Dim objResourceFile
Dim strCompany
Dim strPhone

Set objResourceFile = XDocument.GetDOM("MyID")
strCompany = objResourceFile.selectSingleNode("root/data/companyname").text
strPhone = objResourceFile.selectSingleNode("root/data/phone").text

newNode.selectSingleNode("@CompanyName").text = strCompany
newNode.selectSingleNode("@Phone").text = strPhone
End Sub

** NOTE: The code assumes your data connection is MyID and that the
Repeating Section you added for Shippers has an ID of "Shippers_1" - you
can confirm this by:

- Right-clicking on the Shippers Repeating Section
- Choose Properties
- Select the Advanced tab

So how this works:
- Execute a query against shippers for an ID of 1
- This should populate one company
- Click the new button - this will take the data from the CompanyName and
Phone fields and store this in the MyID resource file. The code will then
use the "ExecuteAction" method to add a new Shippers section and then
populate the CompanyName and Phone fields in this new record with data from
the Resource file.

Let me know if you have any questions!

Best Regards,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights
Click to expand...
 
S

Scott L. Heim [MSFT]

Hi Patrick,

Can you give me a quick overview of how your form is setup? Are you bound
to a database or web service? (It sounds like you are not if you do not
have a repeating data section.) How do you get the one record back on the
form?

Thanks,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.
 
P

Patrick Temple

Scott,

Are you bound
to a database or web service?

Database, I unchecked the return multiple rows check box under the "Modify"
button in the connection wizard.

How do you get the one record back on the
form?

So all my data is in a section, I guess its repeating. I assumed sense I
could not return multiple rows, that the section would not be repeating.

Hope that helps clarify.
 
S

Scott L. Heim [MSFT]

Hi Patrick,

I see what you are saying now and the way you have this setup, my
suggestion will not work as it requires the ability to insert a new node
for the duplicate data.

Unfortunately, I don't know of a way to make this work other than with a
repeating section. In doing so, the way you get just one record back is to
enter some query criteria. For instance, I was using the Shippers table
from Northwind so my query was on the ShipperID field. As such, when you
click the Run Query button, it will only return the requested shipper.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.
 
P

Patrick Temple

Scott,

Was afraid you were going to say that. Oh well, guess I'll figure out
another way to make copies of records outside of InfoPath. Perhaps you can
make a suggestion to Microsoft to add this functionality into their next
service pack. While their at it, they should add the delete functionality to
the rules list, so we could do other things when we delete a record.

Anyway, thanks for the help scott. I'm sure I'll have other questions in a
little while. =)
 
Top