Microsoft KB Archive/928316

= Data in a Windows SharePoint Services list is displayed as one line of text on an InfoPath 2003 form =

Article ID: 928316

Article Last Modified on 3/23/2007

-

APPLIES TO


 * Microsoft Office InfoPath 2003, when used with:
 * Microsoft Windows SharePoint Services

-



SUMMARY
This article describes a behavior in which data in a Microsoft Windows SharePoint Services list is displayed as one line of text on a Microsoft Office InfoPath 2003 form. This behavior occurs even though the columns in the SharePoint list store multiple lines of text.

This article describes three workarounds for this behavior. The first workaround is to create a custom Web service that reads data from the SharePoint list and then returns the data to an InfoPath form. The second workaround is to export the data from the SharePoint list to a Microsoft Office Excel 2003 workbook. The third workaround is to create a data source in Excel 2003 that will retrieve the data from the SharePoint list.



SYMPTOMS
Consider the following scenario. A list in a Windows SharePoint Services site has one or more columns that use the &quot;Multiple lines of text&quot; property. (That is, the columns store data that contains multiple lines of text.) You create an InfoPath 2003 form. Then, you add a data connection to retrieve the data from the SharePoint list and then display it on the form.

In this scenario, when you view the form, the data from the columns that store multiple lines of text is displayed as one line of text. For example, a column in the SharePoint list contains the following data:

123

Test

However, on the InfoPath 2003 form, the data form is displayed as follows:

123 Test

You experience these symptoms regardless of the number of line breaks that the column text contains.



CAUSE
When InfoPath 2003 reads the data from the SharePoint List Web Service, InfoPath 2003 removes any carriage returns from column text.



WORKAROUND
To work around this behavior, use one of the following methods.

Method 1
Create a custom Web service that uses the Windows SharePoint Services object model to read data from the SharePoint list. Then, return the data to an InfoPath form. To do this, follow these steps.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Step 1: Create a custom Web service
The following example is sample code that creates a custom Web service that returns all fields of all items in a SharePoint list. You can modify the sample code to return only specific fields. /**********************************************

[WebMethod] public XmlDocument MultiLineList {   return GetListData(&quot;http://ServerName&quot;, &quot;List Name &quot;); }

[WebMethod] public XmlDocument GetListData(String ServerName, String ListName) {   String str; SPList oSPList;

XmlNode oXmlNode; XmlElement oXmlElement, oXmlParent; XmlDocument oXmlDocument = new XmlDocument;

oXmlDocument.LoadXml(&quot;&quot;);

using(SPSite oSPSite = new SPSite(ServerName)) {       using(SPWeb oSPWeb = oSPSite.OpenWeb) {           oSPList     = GetList(oSPWeb, ListName);

if(oSPList != null) {               foreach(SPListItem oSPListItem in oSPList.Items) {                   oXmlParent      = oXmlDocument.CreateElement(&quot;SPListItem&quot;); oXmlNode       = oXmlDocument.DocumentElement.AppendChild(oXmlParent);

foreach(SPField oSPField in oSPListItem.Fields) {                       if(oSPField.Hidden) continue;

str            = oSPField.InternalName.Replace(&quot; &quot;, &quot;&quot;); oXmlElement    = oXmlDocument.CreateElement(str); oXmlParent.AppendChild(oXmlElement);

try {                           str                     = oSPField.Title; str                    = oSPListItem[str].ToString; }

catch(Exception e)                       { str                    = &quot;Field error: &quot; + e.Message; }

oXmlElement.InnerText  = str; }               }            }        }    }

return oXmlDocument; }

/**********************************************

private SPList GetList(SPWeb oSPWeb, String strListName) {   SPList oRet = null;

strListName = strListName.Replace(&quot;%20&quot;, &quot; &quot;); strListName = strListName.ToUpper;

foreach(SPList oSPList in oSPWeb.Lists) {       if(oSPList.Title.ToUpper == strListName) {           oRet = oSPList; break; }   }

return oRet; }

Step 2: Create an InfoPath 2003 form to display the data
 Start InfoPath 2003, and then open a new blank form. Add a repeating table to the form. To do this, click Repeating Table on the Insert menu.

Note Make sure that the number of fields in the table matches the number of fields in the SharePoint list. Add a data source to the form. To do this, follow these steps:  On the Tools menu, click Data Connections, and then click Add. Click Receive data, and then click Next. Under From where do you want to receive your data, click Web service, and then click Next.</li> Type the location of the custom Web service that you created, and then click Next. For example, type the following string:

http:// :8080/Service1.asmx?WSDL

</li> Click the name of the custom Web service, and then click Finish.</li></ol> </li> Bind every field in the table to a field that is returned by the custom Web service. To do this, follow these steps: <ol> At the bottom of the table, right-click Repeating Table, and then click Change Binding.</li> Bind the whole table to the data set. This action creates a link to the data source.</li> Right-click the field that will contain the multiline text, and then click Change Binding.</li> In the Data source box, click Main, click any other field, and then click OK.</li> Right-click the field that will contain the multiline text, click Text Box Properties, and then click the Display tab.</li> Click to select the Paragraph breaks check box, and then click OK.

Note If the Paragraph breaks check box is not selected the first time that you follow this step, repeat this step until the Paragraph breaks check box is selected.</li> Right-click the field of the table that will contain the multiline text again, and then click Change Binding.</li> In the Data source box, click Web service, and then click the field.</li></ol> </li> Click Preview to preview the form.</li></ol>

Method 2
Export the data from the SharePoint list to an Excel 2003 workbook. After you export the data to an Excel 2003 workbook, you can use scripts, macros, or other applications to retrieve and then display the data.

To do this, follow these steps.

Step 1: Export the data from the SharePoint list to an Excel 2003 workbook
<ol style="list-style-type: lower-alpha;"> In a Web browser, connect to the list on the SharePoint site.</li> Under Actions, click Export to spreadsheet, and then click OK in the File Download dialog box.</li> In the Opening Query dialog box, click Open.</li> <li>In the Import Data dialog box, specify where you want to put the data, and then click OK.</li></ol>

Step 2: Use macros, scripts, or other applications to retrieve and then display the data
To use the mail merge feature in Microsoft Office Word 2003 to display the data, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Start Word 2003, and then open a new blank document.</li> <li>On the Tools menu, point to Letters and Mailings, and then click Mail Merge.</li> <li>Under Select document type in the Mail Merge pane, click Letters, and then click Next: Starting document.</li> <li>Under Select starting document, click Use the current document, and then click Next: Select recipients.</li> <li>Under Select recipients, click Use an existing list, and then click Next: Write your letter.</li> <li>In the Select Data Source dialog box, locate and then click the Excel 2003 workbook that contains the exported data, and then click Open.</li> <li>Specify the records that you want to display, click OK, and then click Next: Write your letter.</li> <li>Under Write your letter, add the items that you want to use in the document, and then click Next: Preview your letters.</li> <li>Preview the letter, and then click Next: Complete the merge.</li> <li>Under Next: Complete the merge, click Edit individual letters to display all items in one document</li></ol>

Method 3
In Excel 2003, create a data source to retrieve data from the SharePoint list. After you do this, you can use scripts, macros, or other applications to retrieve and then display the data.

To do this, follow these steps.

Step 1: Create a data source in Excel 2003 to retrieve the data from the SharePoint list
<ol style="list-style-type: lower-alpha;"> <li>Start Excel 2003, and then open a new workbook.</li> <li>On the Data menu, point to XML, and then click XML Source.</li> <li>In the XML Source pane, click XML Maps, and then click Add.</li> <li>Take one of the following actions: <ul> <li>Click New Source. The Data Connection wizard starts. Go to step 1e, and then follow the remaining steps in this procedure.</li> <li>Click an existing XML source, click Open, and then click OK. You do not have to follow the remaining steps in this procedure.</li></ul> </li> <li>Under What kind of data source do you want to connect to in the Data Connection wizard, click Windows SharePoint Services lists, and then click Next.</li> <li>Type the URL of the SharePoint site that contains the list, and then click Next.</li> <li>Under Select the object that contains the data you want, click the name of the list, and then click Next.</li> <li>Specify the fields that you want to display, and then click Next.</li> <li>If you want to sort the data, specify the sort criteria that you want, and then click Next.</li> <li>If you want to filter data, specify the filter criteria that you want, and then click Next.</li> <li>If you want to limit the number of rows that are retrieved, specify the number of rows that you want, and then click Next.</li> <li>Type a name for the data source, and then click Finish.</li> <li>Click Open, and then click OK.</li></ol>

Step 2: Use macros, scripts, or other applications to retrieve and display the data
For example, you can use the mail merge feature in Word 2003 to display the information. For more information about how to do this, see the &quot;Step 2: Use macros, scripts or other applications to retrieve and display the data&quot; subsection of the &quot;Method 2&quot; section earlier in this article.

Additional query words: multi-line template wss CR LF linefeed single

Keywords: kbnofix kbbug kbexpertiseinter kbtshoot KB928316

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.