REF: Biz Support Online .NET
by S.Y.M. Wong-A-Ton
Learn how you can use Windows SharePoint Services' (WSS) owssvr.dll and InfoPath's FileQueryConnection to write code that filters SharePoint list data displayed in dependent or cascading drop-down list boxes on an InfoPath browser form in SharePoint.
Problem
You have 3 drop-down list boxes on an InfoPath browser compatible form template.
You would like the second drop-down list box to be filled with only those items that are related to the item selected in the first drop-down list box, and you'd like the third drop-down list box to be filled with only those items that are related to the item selected in the second drop-down list box.
In essence, you want to create cascading drop-down list boxes on an InfoPath browser form.
Solution
Use the owssvr.dll of Windows SharePoint Services (WSS) to filter SharePoint list data and populate drop-down list boxes on an InfoPath browser form, thereby creating cascading lists.
Discussion
There are several ways to create cascading drop-down list boxes in InfoPath, some of which include using the Filter Data option in InfoPath if you're not creating a browser-compatible form template, using a web service, or writing code to filter items and then programmatically populate drop-down list boxes.
REF: Biz Support Online .NET
by S.Y.M. Wong-A-Ton
Showing posts with label Cascade Dropdown. Show all posts
Showing posts with label Cascade Dropdown. Show all posts
Tuesday, February 23, 2010
Tuesday, February 9, 2010
Cascading Dropdowns in Browser Forms
REF: http://blogs.msdn.com/infopath/archive/2006/10/12/cascading-dropdowns-in-browser-forms.aspx
If you are building an InfoPath client-only solution and you need to filter drop-down list boxes, you can simply use the “Filter Data” feature when you set the Entries property for the control. However, since filters are not supported in browser-compatible form templates, how can you accomplish the same functionality?
This is where .NET web services can “save the day!” By creating web methods that accept parameters, you can add those web methods as data connections and then pass the selected value from one drop-down list box to the appropriate data connection “queryField”. Once the queryField has been set, simply execute that data connection to retrieve the associated values.
To setup this sample, you will need to have access to the SQL Server Northwind sample database and Visual Studio installed on your server.
First, let’s create the web service and the two web methods we will use in this sample:
Step 1: Open the appropriate web site
Launch Visual Studio
From the File menu, select Open and choose Web Site
Select File System and then navigate to: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS
NOTE: By choosing to open the LAYOUTS folder, your web service will be available from all provisioned sites. If you want the web service only to be available from a specific site (i.e. the default site) you would want to open: C:\Inetpub\wwwroot\wss\VirtualDirectories\80
Click Open
In the Solution Explorer, right-click on the web site and choose New Folder
Rename this folder to: WebServices
Because you may have multiple web services, let’s add a sub folder here that is specific to our web service:
Right-click on WebServices and choose New Folder
Rename this folder to: NorthwindTables
Step 2: Create the web service
Right-click on NorthwindTables and choose Add New Item
From the Visual Studio installed templates list choose Web Service
In the Name box, rename this to: NorthwindTable.asmx
Uncheck the option “Place code in a separate file” and click Add
Step 3: Add the web methods
NOTE: For this sample, it is assumed the SQL Server database is installed on the same Microsoft Office SharePoint Server.
Add the following “using” declarations at the top of your code page:
using System.Data;
using System.Data.SqlClient;
Add the following web method to retrieve the CustomerID values from the Customers table in the Northwind database:
[WebMethod]
public DataSet GetCustomers() {
// Create a SQL connection to the Northwind sample database
SqlConnection cn = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind");
// Create data adapter object passing it the SELECT
// statement to retrieve the customer ID values
SqlDataAdapter da = new SqlDataAdapter("SELECT Customers.CustomerID FROM Customers Order By CustomerID", cn);
// Create a dataset object to store the data
DataSet ds = new DataSet();
// Open the connection
cn.Open();
// Fill the dataset
da.Fill(ds, "Customers");
// Clean up
cn.Close();
cn = null;
da = null;
return ds;
}
Add the following web method to retrieve the associated orders for the selected customer:
[WebMethod]
public DataSet GetOrdersForSelectedCustomer(string strCustID) {
// Create a SQL connection to the Northwind sample database
SqlConnection cn = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind");
// Create a string variable for the modified SQL statement
string strOrdersSQL = "";
// Create a string variable for the default SQL statement
string strOrdersOrigSQL = "SELECT * FROM Orders";
// Some of the customer ID values contain apostrophe's - we need
// to replace them with two single quotation marks so that all
// single quotation marks in the CustomerID are parsed correctly.
strCustID = strCustID.Replace("'", "''");
// Concatenate the default SQL statement with the "Where" clause
// and add an OrderBy clause
strOrdersSQL = strOrdersOrigSQL + " Where CustomerID Like '%" + strCustID + "%' Order By OrderID";
// Create data adapter object passing it the SELECT statement
// to retrieve the OrderID values
SqlDataAdapter daOrders = new SqlDataAdapter(strOrdersSQL, cn);
// Create a dataset object to store the data
DataSet Ds = new DataSet();
// Open the connection
cn.Open();
// Fill the DataSet
daOrders.Fill(Ds, "Orders");
// Clean up
cn.Close();
cn = null;
daOrders = null;
return Ds;
}
Build and save the project
Step 4: Test the web methods
NOTE: The Identity account of the Application Pool for the web site where this web service is published will need to have access to the SQL Server database.
Open a browser and navigate to: http:///_layouts/WebServices/NorthwindTables/NorthwindTables.asmx (replace with the name of your server)
You should see the two web methods created above along with the default HelloWorld web method:
Click the GetCustomers link and then click Invoke – this should return a list of the CustomerID values
Click the GetOrdersForSelectedCustomer link, in the strCustID box enter: BERGS and then click Invoke – this should return a list of only those OrderID values for BERGS
Step 5: Create the InfoPath form
Design a new, blank, browser-compatible InfoPath Form Template
Add a drop-down list box to the view and modify the name to: SelectCustomer
Add another drop-down list box to the view and modify the name to: SelectOrder
Add a new “receive data” data connection to the NorthwindTables web service for each of the web methods created above as follows:
GetCustomers:
Enable the option “Automatically retrieve data when the form is opened”
GetOrdersForSelectedCustomer:
Use ALFKI as the sample value for the strCustID parameter when prompted in the Data Connection Wizard
Uncheck the option “Automatically retrieve data when the form is opened”
Set the Data source for SelectCustomer to the GetCustomers data connection and use the CustomerID field for both the Value and Display name properties
Set the Data source for SelectOrder to the GetOrdersForSelectedCustomer data connection and use the OrderID field for both the Value and Display name properties
Create a Rule on SelectCustomer with the following actions:
Set a field’s value: Set the SelectOrder field to nothing (e.g. leave the Value blank)
Set a field’s value: Set the parameter value (strCustID) for the GetOrdersForSelectedCustomer data connection to the SelectCustomer field
Query the GetOrdersForSelectedCustomer data connection
Save the form locally as FilteredDrop-downs_IPFS.XSN
Step 6: Publish the form
Publish the form to a server running InfoPath Form Services
Navigate to the form library where the form was published and click the New button
From SelectCustomer choose BERGS
Click SelectOrder – only those orders for BERGS are displayed
Select a different customer – notice the orders have also changed
Scott Heim
Support Engineer
Posted: Thursday, October 12, 2006 11:24 AM by infopath
http://blogs.msdn.com/infopath/archive/2006/10/12/cascading-dropdowns-in-browser-forms.aspx
If you are building an InfoPath client-only solution and you need to filter drop-down list boxes, you can simply use the “Filter Data” feature when you set the Entries property for the control. However, since filters are not supported in browser-compatible form templates, how can you accomplish the same functionality?
This is where .NET web services can “save the day!” By creating web methods that accept parameters, you can add those web methods as data connections and then pass the selected value from one drop-down list box to the appropriate data connection “queryField”. Once the queryField has been set, simply execute that data connection to retrieve the associated values.
To setup this sample, you will need to have access to the SQL Server Northwind sample database and Visual Studio installed on your server.
First, let’s create the web service and the two web methods we will use in this sample:
Step 1: Open the appropriate web site
Launch Visual Studio
From the File menu, select Open and choose Web Site
Select File System and then navigate to: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS
NOTE: By choosing to open the LAYOUTS folder, your web service will be available from all provisioned sites. If you want the web service only to be available from a specific site (i.e. the default site) you would want to open: C:\Inetpub\wwwroot\wss\VirtualDirectories\80
Click Open
In the Solution Explorer, right-click on the web site and choose New Folder
Rename this folder to: WebServices
Because you may have multiple web services, let’s add a sub folder here that is specific to our web service:
Right-click on WebServices and choose New Folder
Rename this folder to: NorthwindTables
Step 2: Create the web service
Right-click on NorthwindTables and choose Add New Item
From the Visual Studio installed templates list choose Web Service
In the Name box, rename this to: NorthwindTable.asmx
Uncheck the option “Place code in a separate file” and click Add
Step 3: Add the web methods
NOTE: For this sample, it is assumed the SQL Server database is installed on the same Microsoft Office SharePoint Server.
Add the following “using” declarations at the top of your code page:
using System.Data;
using System.Data.SqlClient;
Add the following web method to retrieve the CustomerID values from the Customers table in the Northwind database:
[WebMethod]
public DataSet GetCustomers() {
// Create a SQL connection to the Northwind sample database
SqlConnection cn = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind");
// Create data adapter object passing it the SELECT
// statement to retrieve the customer ID values
SqlDataAdapter da = new SqlDataAdapter("SELECT Customers.CustomerID FROM Customers Order By CustomerID", cn);
// Create a dataset object to store the data
DataSet ds = new DataSet();
// Open the connection
cn.Open();
// Fill the dataset
da.Fill(ds, "Customers");
// Clean up
cn.Close();
cn = null;
da = null;
return ds;
}
Add the following web method to retrieve the associated orders for the selected customer:
[WebMethod]
public DataSet GetOrdersForSelectedCustomer(string strCustID) {
// Create a SQL connection to the Northwind sample database
SqlConnection cn = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind");
// Create a string variable for the modified SQL statement
string strOrdersSQL = "";
// Create a string variable for the default SQL statement
string strOrdersOrigSQL = "SELECT * FROM Orders";
// Some of the customer ID values contain apostrophe's - we need
// to replace them with two single quotation marks so that all
// single quotation marks in the CustomerID are parsed correctly.
strCustID = strCustID.Replace("'", "''");
// Concatenate the default SQL statement with the "Where" clause
// and add an OrderBy clause
strOrdersSQL = strOrdersOrigSQL + " Where CustomerID Like '%" + strCustID + "%' Order By OrderID";
// Create data adapter object passing it the SELECT statement
// to retrieve the OrderID values
SqlDataAdapter daOrders = new SqlDataAdapter(strOrdersSQL, cn);
// Create a dataset object to store the data
DataSet Ds = new DataSet();
// Open the connection
cn.Open();
// Fill the DataSet
daOrders.Fill(Ds, "Orders");
// Clean up
cn.Close();
cn = null;
daOrders = null;
return Ds;
}
Build and save the project
Step 4: Test the web methods
NOTE: The Identity account of the Application Pool for the web site where this web service is published will need to have access to the SQL Server database.
Open a browser and navigate to: http://
You should see the two web methods created above along with the default HelloWorld web method:
Click the GetCustomers link and then click Invoke – this should return a list of the CustomerID values
Click the GetOrdersForSelectedCustomer link, in the strCustID box enter: BERGS and then click Invoke – this should return a list of only those OrderID values for BERGS
Step 5: Create the InfoPath form
Design a new, blank, browser-compatible InfoPath Form Template
Add a drop-down list box to the view and modify the name to: SelectCustomer
Add another drop-down list box to the view and modify the name to: SelectOrder
Add a new “receive data” data connection to the NorthwindTables web service for each of the web methods created above as follows:
GetCustomers:
Enable the option “Automatically retrieve data when the form is opened”
GetOrdersForSelectedCustomer:
Use ALFKI as the sample value for the strCustID parameter when prompted in the Data Connection Wizard
Uncheck the option “Automatically retrieve data when the form is opened”
Set the Data source for SelectCustomer to the GetCustomers data connection and use the CustomerID field for both the Value and Display name properties
Set the Data source for SelectOrder to the GetOrdersForSelectedCustomer data connection and use the OrderID field for both the Value and Display name properties
Create a Rule on SelectCustomer with the following actions:
Set a field’s value: Set the SelectOrder field to nothing (e.g. leave the Value blank)
Set a field’s value: Set the parameter value (strCustID) for the GetOrdersForSelectedCustomer data connection to the SelectCustomer field
Query the GetOrdersForSelectedCustomer data connection
Save the form locally as FilteredDrop-downs_IPFS.XSN
Step 6: Publish the form
Publish the form to a server running InfoPath Form Services
Navigate to the form library where the form was published and click the New button
From SelectCustomer choose BERGS
Click SelectOrder – only those orders for BERGS are displayed
Select a different customer – notice the orders have also changed
Scott Heim
Support Engineer
Posted: Thursday, October 12, 2006 11:24 AM by infopath
http://blogs.msdn.com/infopath/archive/2006/10/12/cascading-dropdowns-in-browser-forms.aspx
InfoPath 2003 Master/Detail with SharePoint List Sources
REF: http://randomdust.com/blogs/ryan/archive/2008/07/02/infopath-master-detail-with-sharepoint-list-sources.aspx
I don’t know if this is specific to SharePoint sources or just external sources in general, but you can’t use a SharePoint source in a master/detail relationship. After dragging it onto the form, right click on the repeating table and view the properties. The master/detail section is grayed out. The second thing I ran into was trying to filter the SharePoint connection. No luck there either, it’s not available.
After some searching, I found a post on implementing master/detail for a web based form which detailed some steps to pull in SharePoint data using a different method. Instead of using the SharePoint List option when creating the connection, you use the XML option and point it to a URL which outputs the list data in XML format. I won’t cover the details of that here as he covers it his post very well, but it’s using owssvr.dll. That post solved my issues completely, but it was using code for InfoPath 2007. I needed to convert that to be 2003 compatible.
Since the 2003 compatible code is probably the only useful part of this post, here it is:
http://randomdust.com/blogs/ryan/archive/2008/07/02/infopath-master-detail-with-sharepoint-list-sources.aspx
Pretty simple, but it took some SDK searching to find the right classes. I’m using the XMLFileAdapterObject to grab the “Card Version owssvr” data connection, then I change the URL using FileURL similar to how Ishai modified the FileLocation property of the FileQueryConnection object. Like I said, nothing fancy, but I thought it may come in handy for folks looking for a solution similar to Ishai’s, but who can’t use InfoPath 2007.
I don’t know if this is specific to SharePoint sources or just external sources in general, but you can’t use a SharePoint source in a master/detail relationship. After dragging it onto the form, right click on the repeating table and view the properties. The master/detail section is grayed out. The second thing I ran into was trying to filter the SharePoint connection. No luck there either, it’s not available.
After some searching, I found a post on implementing master/detail for a web based form which detailed some steps to pull in SharePoint data using a different method. Instead of using the SharePoint List option when creating the connection, you use the XML option and point it to a URL which outputs the list data in XML format. I won’t cover the details of that here as he covers it his post very well, but it’s using owssvr.dll. That post solved my issues completely, but it was using code for InfoPath 2007. I needed to convert that to be 2003 compatible.
Since the 2003 compatible code is probably the only useful part of this post, here it is:
http://randomdust.com/blogs/ryan/archive/2008/07/02/infopath-master-detail-with-sharepoint-list-sources.aspx
Pretty simple, but it took some SDK searching to find the right classes. I’m using the XMLFileAdapterObject to grab the “Card Version owssvr” data connection, then I change the URL using FileURL similar to how Ishai modified the FileLocation property of the FileQueryConnection object. Like I said, nothing fancy, but I thought it may come in handy for folks looking for a solution similar to Ishai’s, but who can’t use InfoPath 2007.
InfoPath form Services - implementing a Master-Detail on the web
REF:http://www.sharepoint-tips.com/2007/01/infopath-form-services-implementing.html
If you ever tried publishing a form to the web using infopath form services, you probably know that one compatibility issue you will have is with master-detail (also known as "cascading dropdowns") fields.
Master-detail basically means when you have two fields (usualy dropdown boxes) and you want the options in the second field to change based on the choice the user made in the first field.
For example, I have a dropdown called "States", and a dropdown calles "Cities". Now I want when the user chooses a state, to only show cities within the chosen state.
This is easy to do in infopath - just connect the dropdowns with a little configuration. But when you try to publish the form to a web form, it will tell you that the master-detail will simply not work in the web form. It's not supported.
To the rescue!
http://www.sharepoint-tips.com/2007/01/infopath-form-services-implementing.html
NOTE: This works but you need to have your security setting to Fully Trusted!!!!
If you ever tried publishing a form to the web using infopath form services, you probably know that one compatibility issue you will have is with master-detail (also known as "cascading dropdowns") fields.
Master-detail basically means when you have two fields (usualy dropdown boxes) and you want the options in the second field to change based on the choice the user made in the first field.
For example, I have a dropdown called "States", and a dropdown calles "Cities". Now I want when the user chooses a state, to only show cities within the chosen state.
This is easy to do in infopath - just connect the dropdowns with a little configuration. But when you try to publish the form to a web form, it will tell you that the master-detail will simply not work in the web form. It's not supported.
To the rescue!
http://www.sharepoint-tips.com/2007/01/infopath-form-services-implementing.html
NOTE: This works but you need to have your security setting to Fully Trusted!!!!
Tuesday, January 19, 2010
Cascading Drop downs in InfoPath Web Forms made easy
Posted by Frederik Van Lierde
REF: http://blueinfopath.blogspot.com/search?updated-max=2008-12-08T09%3A54%3A00-08%3A00&max-results=7
To create user‐friendly web forms, cascading drop down list boxes is a must, but InfoPath Form Services doesn’t allow you to use them, like you can use them with InfoPath Client. (Filter Data on DataConnections/DataSources are not allowed with InfoPath Web Forms)
This article explains how to use Cascading Drop down list boxes within an InfoPath Web Form in a very easy and re-usable way.
Please download the PDF file containing the step by step guide HOW_TO_use_Cascading_Dropdown_ListBoxes_within_an_InfoPath_Web_Form.pdf
REF: http://blueinfopath.blogspot.com/search?updated-max=2008-12-08T09%3A54%3A00-08%3A00&max-results=7
To create user‐friendly web forms, cascading drop down list boxes is a must, but InfoPath Form Services doesn’t allow you to use them, like you can use them with InfoPath Client. (Filter Data on DataConnections/DataSources are not allowed with InfoPath Web Forms)
This article explains how to use Cascading Drop down list boxes within an InfoPath Web Form in a very easy and re-usable way.
Please download the PDF file containing the step by step guide HOW_TO_use_Cascading_Dropdown_ListBoxes_within_an_InfoPath_Web_Form.pdf
Thursday, January 14, 2010
$().SPServices.SPCascadeDropdowns
Function$().SPServices.SPCascadeDropdowns
FunctionalityThe SPCascadeDropdowns function lets you set up cascading dropdowns on SharePoint forms. What this means is that you can enforce hierarchical relationships between column values. The function uses the GetListItems operation of the Lists Web Service to refresh the allowable values based on relationships which are maintained in reference lists. By implementing this function, there are no coding requirements to manage the hierarchical relationships (once it is in place) and you can let your users manage the content in the reference lists.
This function works with any number of options in the dropdowns as well as multi-select parent and child columns, as shown in the following table. This is significant because each of the three column types are rendered significantly differently by SharePoint.
When the relationshipList contains lookup columns for both the relationshipListParentColumn and relationshipListChildColumn columns, the function uses the relationshipListParentColumn's ID rather than the relationshipList item's ID. This means that "secondary lists" are also supported.
Find out more here
REF: http://spservices.codeplex.com/wikipage?title=%24%28%29.SPServices.SPCascadeDropdowns&referringTitle=Documentation
FunctionalityThe SPCascadeDropdowns function lets you set up cascading dropdowns on SharePoint forms. What this means is that you can enforce hierarchical relationships between column values. The function uses the GetListItems operation of the Lists Web Service to refresh the allowable values based on relationships which are maintained in reference lists. By implementing this function, there are no coding requirements to manage the hierarchical relationships (once it is in place) and you can let your users manage the content in the reference lists.
This function works with any number of options in the dropdowns as well as multi-select parent and child columns, as shown in the following table. This is significant because each of the three column types are rendered significantly differently by SharePoint.
When the relationshipList contains lookup columns for both the relationshipListParentColumn and relationshipListChildColumn columns, the function uses the relationshipListParentColumn's ID rather than the relationshipList item's ID. This means that "secondary lists" are also supported.
Find out more here
REF: http://spservices.codeplex.com/wikipage?title=%24%28%29.SPServices.SPCascadeDropdowns&referringTitle=Documentation
Subscribe to:
Posts (Atom)