Applies to:
For information on Service Pack 1 updates to working with secondary data sources, see About secondary data sources in the InfoPath Developer's Reference.
Secondary data sources allow you to retrieve information from other XML files, database connections, and Web services for use in your Microsoft Office InfoPath form. Secondary data sources can be used to provide the choices for a list box or drop-down list box, or you can write script to add data from a secondary data source to your form. To work with secondary data sources in your form, click Secondary Data Sources on the Tools menu in design mode.
The Secondary Data Sources dialog box allows you to modify an existing secondary data source or create a new one. To use an existing secondary data source, select the secondary data source you want in the Secondary data sources for your form list, and then click Modify. To create a new secondary data source, click Add to open the Data Source Setup Wizard.
Note Secondary data sources in an InfoPath form do not appear in the Data Source task pane, and controls in a view cannot be bound to fields in a secondary data source.
XML file data sources
When you open the Data Source Setup Wizard, you are prompted to select a source of data that you want to use for the form. To create a secondary data source that retrieves data from an XML file, select XML data file on the first page of the wizard, and then click Next. Browse to or type the full path of the file you want to base your form on, and then click Next. InfoPath suggests a name for the data source. If you select the Connect to this secondary data source when the form is opened check box, the data in the secondary data source is available immediately when the form is opened. If you clear this option, you must use script to call the Query method of the DataObject object to connect to the data source:
//Refresh the sales summary data stored in the secondary data source. XDocument.DataObjects.Item("SalesSummaryData").Query(); Click Finish to add the new secondary data source. InfoPath warns you that the file is not part of the form and therefore might not be accessible from other computers. You can click Yes to add the XML file to the form as a resource, or No to use it from its original location. This option can be useful if the data in the XML file will change frequently and you don't want to have to rebuild the form each time it changes. Just make sure the file is in a location that all your users can access.
The Shipping Companies Secondary Data Source
The Shipping Companies secondary data source is an XML file data source that retrieves data from the ShippingCompanies.xml file. InfoPath connects to the Shipping Companies secondary data source when the form is opened.
The ShippingCompanies.xml file is placed on a Web server (by default, http://localhost/InfoPathSDK/Information Lookup/ShippingCompanies.xml) where users filling out the form can access it. This allows the ShippingCompanies.xml file, which stores the list of shipping companies and their rates, to be updated easily.
The OnLoad Event Handler
The OnLoad event occurs when a new or existing form is opened. Code to handle this event can perform any setup actions the form might need before a user starts to fill out the form. The OnLoad event handler performs two functions in the Information Lookup developer sample form. First, it takes the FileURL property of the QueryAdapter object, which is used to access the Shipping Companies secondary data source, and uses a regular expression in a Microsoft JScript replace function to remove the ShippingCompanies.xml file name from the end. The resulting path can be used to locate the company logo image files, because the logo files are installed in the same directory.
// Get the base URL for the Shipping Companies secondary // data source. This is used by shipping.xsl to build the // URL to the company logo. var objXmlFileAdapter = XDocument.DataObjects("Shipping Companies").QueryAdapter; gstrShippingCompanyBaseURL = objXmlFileAdapter.FileURL.replace(/[^\\\/]+$/, ""); After calculating the install directory of the company logo image files for the form, the OnLoad event handler accesses the XML DOM of the Shipping Methods secondary data source and uses the loadXML method to fill in the data source with choices:
// Load shipping methods into the Shipping Methods auxiliary DOM. XDocument.GetDOM("Shipping Methods").loadXML('' + '' + '' + '' + ' '); Database data sources
When you start the Data Source Setup Wizard, you are prompted to select a source of data to use for the form. To create a secondary data source from a database, select Database (Microsoft SQL Server or Microsoft Office Access only). On the next page of the wizard, click Select Database to choose an existing data source or connect directly to a specific database file.
After you select a database, the wizard prompts you to select a table or query from the database to use as the data source for the form. You must select one table or query to begin with, but you can select additional tables later. After you've selected a table or query, the wizard allows you to select the fields you want to use in the Data source structure list. By default, all the fields of the table are selected, but you can remove fields if they aren't necessary for your form. You can also control how the records returned from the table are sorted, and whether multiple records are allowed. To do so, click Modify Table, and then select up to three sorting criteria in the Sort Order dialog box. When you're satisfied with the configuration, click Finish.
Note Primary key fields from each table are always selected and cannot be removed.
InfoPath also allows you to retrieve data from multiple tables or queries at the same time. When you retrieve data from multiple tables or queries, you must be able to establish a relationship between all of the tables or queries involved withthe original table or query you selected in the Data Source Setup Wizard. For example, if you were retrieving data from the Customers table of the Northwind database, you could add the Orders table to retrieve data about all the orders for that customer, and you could add the Order Details table to retrieve the details of each order.
To add an additional table to the data source, select the table you want to add a child table to in the Data source structure list, and then click Add Table. Select the table or query you want to add, and then click Next. InfoPath prompts you to select the relationship or relationships you want to use. If fields in the two tables have the same name, InfoPath automatically adds those fields as a relationship, but if not, or if you want to use a custom relationship, you can click Add Relationship to specify which fields in the parent table correspond to fields in the child table. You can also remove existing relationships by clicking Remove Relationship in the Edit Relationship dialog box.
When you're satisfied with the relationships, click Finish. As with the main table, you can specify which fields are returned from the child table. You cannot, however, use the Modify Table button to edit the order in which the records are returned.
When the tables, relationships, and fields have been specified, you can click Edit SQL to view the SQL statement that will be used to establish the data source for the form. In the Edit SQL dialog box, you can click Test SQL Statement to verify that InfoPath will be able to create the data source from the information provided. You can also use the Edit SQL dialog box to modify the SQL statement to create more complex queries.
Note The SQL statements used by InfoPath are data shaping queries. Data shaping queries allow the building of hierarchical relationships between two or more logical entities in a query. It is possible to use SQL JOIN statements, but this is not recommended, because doing so will disable form submission. For more information on data shaping queries, see the documentation on the Microsoft Developer Network (MSDN).
The last page of the Data Source Setup Wizard displays summary information about the data source, including the name and file location of the data source, the name of the primary parent table, the number of tables used, and the submit status.
The Customers (USA) Secondary Data Source
The Customers (USA) secondary data source was created by adding a database secondary data source. The Northwind database in Microsoft SQL Server was selected as the database for the secondary data source by clicking the Select Database button in the Data Source Setup Wizard. The Customers table was chosen to provide the data for the Customers (USA) secondary data source, and all the columns of the table, except for ContactTitle and Fax, were selected for use. Finally, a WHERE "Country" = 'USA' clause was added to the SQL statement to filter out any customers not located in the United States.
The ProductCatalog Secondary Data Source
The ProductCatalog secondary data source was created in much the same way as the Customers (USA) secondary data source. A database secondary data source was added, and the Northwind database in Microsoft SQL Server was selected as the database. For the ProductCatalog secondary data source, the Categories table was selected as the primary parent table, and the CategoryID and CategoryName columns were selected.
The ProductCatalog secondary data source also uses two secondary tables in its data. The Products table was added to the data source by selecting the Categories table in the Data source structure list and clicking Add Table to open the Add Table or Query dialog box. The Products table was selected from the list of available tables. Secondary tables in a data source must have a relationship to the primary parent table, and InfoPath suggested the CategoryID column as the appropriate relationship. This suggestion was accepted by clicking Finish. The ProductName, QuantityPerUnit, and UnitPrice columns were selected in the Data source structure list, in addition to the required ProductID, SupplierID, and CategoryID columns.
The same process was used to add the Suppliers table to the Products table. After selecting the Suppliers table from the Add Table or Query dialog box, the SupplierID column was chosen as the relationship between the Products table and the Suppliers table. Only the CompanyName column was selected from the Suppliers table.
No comments:
Post a Comment