InfoPath Dev

Hi,

We managed to use repeating sections on our form to hold multiple attachments and submit them to DBXL. Everything for submission works fine. However, when we try to retrieve the attachments via a data connection, there does not seem to be a way to return multiple attachments.

How we perform the retrieval is via a data connection to the table storing the attachments for a particular DocID. With the returned data, we then try to set value on a repeating section which contains a file attachment control. However, this method only returns the first attachment of that DocID. We tried dragging and dropping the secondary data source as well. This method works with repeating string content, however this method does not have an option to drop our SDS as a file attachment.

Does anyone have a way to retrieve multiple attachments that are submitted to DBXL? Thanks!

Hi Zhen,

If you are just storing the XML in it's entirety, everything will roundtrip correctly.

If on the other hand, you are using DBXL's qdFile system doctype to store images and/files and convert into links, you should be able to use our File Picker XTP to handle multiple ones. I'm going to ask someone to respond with a sample form that shows how to do this. Please attach your sample XSN so that we can see exactly what you are doing.

Thanks!

Hi Patrick,

 Attached is my sample form. I did not include my data connections but i hope this will surfice.

What happens in the top section is how I attach multiple attachments to my issue log form. The bottom half displays how I intend to be able to retrieve the multiple attachments that were submitted previously based on the issueID and revision no.(revision no portion can be ignore actually). Right now, I can only retrieve the first attachment per issueID although there are multiple attachments for that issueID.

Thanks,

Zhen Yuan

Thanks for the quick response. I'm looking at your form and have a couple questions:

• Which Web Method are you using to query DBXL for the images? Are you using QueryDB?
• How are the images stored in SQL? Are they stored as BASE64 images?
• Where do the images come from? Do they come from another InfoPath form or a separate process?
• How do you want to display them in the form?

There are lots of optiosn and we should be able to have a solution for you soon.

You can use qRules and DBXL to convert files/images to links that are stored as a separate document in qdFile doctype. This allows you to see the image or file icon in your form and click on it to open. The file/image exists in SQL as an XML object with meta data. the benefits of this approach are:

• Faster open times since the attached file/image is downloaded in the background after open
• Parallel edits of attachments - you can edit attachments without opening the forms that they are in
• Attachments can be shared across document types

More soon,

Hi,

Yes, we am using queryDB.

Images are stored as base64 in InfoPath and as varchar(max) in SQL.

Images come from user's local machines. We allow users to upload files from their desktops into this form we are developing.

We would like the attachments to be displayed just like how they were when they are first attached to the form.

Hmmm, we haven't purchased qRules yet. Hopefully the options does not involve configuring client machines like the activeX contact selector? Thanks Patrick for your help!

I think we can come up with a solution for you that doesn't involve qRules, but we can also just comp you qRules since you have a DBXL license. qRules doesn't require any client machine config and we even have Non-ActiveX Contact Selector that uses it and is specifically designed to work around that issue.

Let me search and see if we have a document or blog on your image requirement - InfoPath uses some special attributes with images that are not preserved when mapping.

If you are amenable to moving to qRules let us know and we'll send you that along with a sample.

The base64 string you are saving is the InfoPath attachment control data.  It includes a header with filename information in addition to the actual file you attached.  When you query the database for these values, the values returned will need to be copied to your fields bound with the InfoPath File Attachment control.

When you query the database for the attachments, you will need to create a new node in your repeating group to contain the attachment.

Hi Jim,

I don't quite get what you mean by creating a new node under my repeating group. I do have the Infopath file attachment control under my repeating group. But when I query the database and return values to the attachment control, only the first attachment appears. How do I pull out multiple attachments which I uploaded previously?

Hi Zhen Yuan,

I'm going to reply since it's still Sunday in the US where Jim is. If you are querying the values from your DB they will show up in your secondary data source as strings. The secondary data source schema won't have the msoImage attributes on it that InfoPath needs to treat it as an image and that's why Jim suggests copying the strings to your main data source. At least that's what I am thinking, but I haven't verified it. Please try to add some rules that copy the 2nd and 3rd images from your secondary data source into your main data source. Since the main data source has an image control, it should display fine after copying the strings in (since they will have the correct attributes). If that works, you can use qRules to copy a variable number of images into the main data source - use Copy Table or just Insert with a count that allows the Insert to repeat (see Jo-Anne's recent download on how to copy values from SharePoint to a form). If you can't figure it out, we'll create a quick sample tomorrow that illustrates this. I also recommend a live meeting so that we can see exactly what is happening and make sure that our solution will address it.

More soon,

Hi Patrick,

 Yup, if I use SDS, the attachment shows up as strings when I drop and drop the SDS to my form. We don't have qRules yet and so when I use the 2nd method which is to copy the SDS to my main form, only the first attachment is displayed. I was not able to make the attachment retrieval repeat. Understand that there may be a need to use qRules to go about this smoothly but meanwhile, are there any options that do not involve qRules?

MS Live meeting is fine, although we don't have the license to host meetings with external users, we would be able to join if you can host? What is your timezone? Australia, Sydney?

Thanks for helping during this christmas week, really appreciate your quick replies.

I'm in Japan until Jan 12th. Mel is in the Philippines. I have asked her to investigate a technique that would not require code and prepare a sample for you. Copying repeating section from SDS usually requires code, but there might be an way to do it if the main data source comes with default rows already inserted (but conditionally hidden). qRules doesn't require the default data rows and would be a lot simpler.  Anyway, you should hear from her soon. BTW: we also dug up an older thread you had and hope to respond to that soon. Sorry about misplacing that.

More soon, 

Hi Patrick,

 Thanks for your help, I got this feeling coding is still required. If coding is required, my form is currently using Visual Basic coding.

Thanks again Patrick and Mel.

Hi,

Please download the attached sample XSN. It uses QueryDB to retrieve attachments from SQL. Below are some guidelines on how you can try and test the form in your environment:

• Change the DBXL Submit and the QueryDB data connections to your server's.
• If you're using filters for your QueryDB connection, please change the query string in Tools > Form Options > Open and Save > Rules.
• After making changes, create a doctype in DAT attaching the XSN and setting up database mapping to allow forms to submit to your SQL database.

The Copy to Main button has a rule that uses the qRules CopyTable command. The command used is

CopyTable /dsnamesrc=QueryAttachments /tablesrc=/dfs:myFields/dfs:dataFields/ns1:GetColumnsXMLStrQueryResponse/ns1:GetColumnsXMLStrQueryResult/Rows /rowsrc=row /tabledest=/my:myFields/my:Files /rowdest=my:File /empty=yes

In your actual form, you'd want to change the command syntax to match your schema. Here are some information on the CopyTable command:

Arguments:

• dsnamesrc: Name of the source DataSource (optional). Assumes you are using the Main data source if it is not specified.
• tablesrc: XPath to the source table
• rowsrc: relative XPath from the source Table to the source Row (repeating)
• dsnamedest: Name of the destination DataSource (optional). Assumes you are using the Main data source if it is not specified
• tabledest: XPath to the destination table
• rowdest: relative XPath from the destination Table to the destination Row (repeating)
• empty: optional parameter that can have a value of yes or no. If set to yes, the destination table will be emptied before the Copy action. If unspecified, the value defaults to no.

Example: CopyTable /tablesrc=/my:myFields/my:group1 /rowsrc=my:group2 /tabledest=/my:myFields/my:group3 /rowdest=my:group4 /empty=yes

Additional Observations:

• This command now supports attributes.
• CopyTable now allows copying of rich text fields within tables
• CopyTable does not care if the column names within your source and destination tables match
• When using the CopyTable in a SharePoint library or list, it will only work if the nodes match, which also means each column has to have data in it (no blank fields). The reason your fields can't be blank is because when there is a blank field, no placeholder for that field is brought down in the XML, so it's as if that node doesn't exist.

We hope this helps. Please let us know if you encounter issues.

Hi Mel,

 We do not have qRules. Will your copytable command still work?

If we are ok with retreving some specific data like filename, file size and a url link to download the attachment, is there another solution preferably without qRules?

Thanks!

Hi Zhen,

You can't easily copy repeating nodes from one area to another unless you use code (or qRules).

Since you are already a DBXL customer, we'll give you a copy of qRules. The CopyTable command won't work without qRules. We could send you code in place of qRules, but qrules is a better value since you won't have to maintain it across all of your solutions.

Note: you can integrate your existing code with qRules. Search for Jimmy's blog on that.