Recent Posts
November 22nd, 2011 in InfoPath 2010, SharePoint 2010 RTM by Kevin Pine
If you have ever used a SharePoint list containing lookup columns as a data source in your InfoPath forms, you may have been disappointed to see the value of the lookup is not displayed. Instead of the value, the list ID of the item containing the value is displayed.
My ‘Construction Line Items’ list contains three lookup columns: Project, Category, and Product.
When I connect to this list from InfoPath, my repeating table shows the lookup item IDs and not the values like I want.
This example uses the following four SharePoint lists: Line Items, Projects, Construction Category, Products and Activities.
The InfoPath form connects to the Line Items list and displays the data in a Repeating Table
So, how can you get InfoPath to display the lookup column value? One way is to add a data connection to the list (or lists) containing the lookup values and change the repeating table field controls from text boxes to calculated fields. Here are the steps:
- Create data connections to the lists containing the lookup values
- Change the repeating table field control from Text Box to Calculated Value
- Edit Formula on the calculated field control (Right-click, Calculated Value Properties or double-click, Control Properties)
- Delete the current formula and Insert Field or Group > change the Fields source to the data connection containing the lookup value (Construction Category) > select the lookup field column (Category) > click Filter Data
- Set the filter where the ID (on the Construction Category) > is equal to > Select a field or group > change fields to the Line Items data connection > select the corresponding column (Category on the repeating table connection) > OK
- Preview the form to see that the categories are now showing instead of the lookup IDs
- Following the same steps for the Item column allows the values to show there too instead of the lookup item IDs.
Let me know if you have another way of accomplishing the same thing.
October 8th, 2010 in InfoPath 2007, MOSS Help, SharePoint Designer 2007 by Kevin Pine
How-to video: Create an InfoPath form that stores form data in SQL
Part 1: Create a web-enabled InfoPath form that stores form data directly in SQL
Part 2: Create data view web parts in SharePoint Designer to display/update SQL data in SharePoint pages
Part 3: Filter SQL data with a form web part and create SharePoint list items from SQL data
This SharePoint video tutorial is based on a simple concept: Store customer suggestions for product improvement or new product ideas in SQL and create a SharePoint page to find, filter, view, and manage the task of implementing the good suggestions.
Data view/data form web parts to display/update SQL data
View part 2 of the SharePoint training video series here
October 5th, 2010 in InfoPath 2007, MOSS Help by Kevin Pine
How-to video: Create an InfoPath form that stores form data in SQL
Part 1: Create a web-enabled InfoPath form that stores form data directly in SQL
Part 2: Create data view web parts in SharePoint Designer to display/update SQL data in SharePoint pages
Part 3: Filter SQL data with a form web part and create SharePoint list items from SQL data
This SharePoint video tutorial is based on a simple concept: Store customer suggestions for product improvement or new product ideas in SQL and create a SharePoint page to find, filter, view, and manage the task of implementing the good suggestions.
Web-enabled InfoPath form
Form data stored in SQL database table
Suggestion tracking dashboard page
View part 1 of the SharePoint training video series here
September 29th, 2010 in InfoPath 2010, SharePoint 2010 RTM by Kevin Pine
How to video: Create Optional Cascading Drop-Down Filters on SharePoint List Forms
I knew I was going to like the ability to customize list item forms in SharePoint 2010 using InfoPath. The ability to filter drop down list choices based on a previous selection in the form is something many people ask for.
I have created a SharePoint 2010 tutorial/training video that shows how to customize the new item form in InfoPath, wire up drop down choice lists to information in a SharePoint list and filter and have the choices in one drop down filter the choices in the next. And yes, the use of the filters is an option for the user.
The facilities department is responsible for equipment in offices, manufacturing and retail sales locations in thirty or more buildings located in about a dozen cities in three states. This solution gives users the choice of picking directly from the entire list of buildings or filtering first by state and city (see screenshot above).
The video below will walk you through the entire process from creating a custom list, adding a lookup column that pulls back multiple columns of meta data, customizing the list item form in InfoPath and wiring up the optional cascading filtered drop down choice fields.
March 12th, 2010 in MOSS Help, Search, SharePoint Designer 2007 by Kevin Pine
Adding a Google search to a web page is pretty simple. Just drop in the search form code. Something like this:
<form id=”search-form” method=”get” action=http://search.yoursitehere.com/search>
<input type=”text” name=”q” size=”25″ maxlength=”255″ value=”"/>
<input type=”submit” id=”search-submit” name=”btnG” value=”Search”/>
<input type=”hidden” name=”site” value=”default_collection”/>
<input type=”hidden” name=”client” value=”default_frontend”/>
<input type=”hidden” name=”proxystylesheet” value=”default_frontend”/>
<input type=”hidden” name=”output” value=”xml_no_dtd”/>
</form>
The fact that the search code is in a form tag is a problem. Basically the entire body of your SharePoint page is in an ASP .NET form and you cannot have nested form tags as the inner <form> will not be processed. Some have tested ways of nesting form tags but it is pretty messy and not consistent across browsers.
I found this post that addressed the issue and his code was a great starting place. In fact it worked great on my virtual machine but was giving me issues on the production site. The problem turned out to be the code that was specifying the background image for the search box. A white background was preferred anyway so the code was simplified a bit to the following:
<div>
<input id=”q” type=”text” size=”25″ style=”border: solid 1px #999999″ onkeydown=”searchkeydown();” />
<button onclick=”googlesearch();return false;”>Search</button>
</div>
<script type=”text/javascript”>
searchkeydown = function() {
if (window.event) {
key = window.event.keyCode; //IE
}
else {
key = e.which; //firefox
}
if (key == 13) {
event.returnValue = false;
event.cancel = true;
googlesearch();
}
}
googlesearch = function() {
window.location=’http://search.yoursitehere.com/search?q=’ + escape(document.getElementById(’q').value);
}
</script>
That should get your Google Appliance (CSE) working in your SharePoint site.
February 19th, 2010 in InfoPath 2007, MOSS Help, SharePoint Designer 2007, Workflow by Kevin Pine
A question came in to the MOSS Help and How To forum about creating a form that allowed multiple approvers to be specified at the time the form is being filled out.
I went ahead and created a form in InfoPath and a simple workflow in SharePoint Designer to allow the selection of the approvers and assign tasks for the approvers. It is a simple form and workflow both. For production you would want a more robust solution. If you have ideas for a better solution, please let me know in the comments.
Instead of writing out all of the steps I created this short video to demonstrate how the form, workflow, and SharePoint library work.
February 12th, 2010 in MOSS Help by Kevin Pine
A question came up about how to go about hiding list action menu items on SharePoint lists. There were some actions that either didn’t make sense for their extranet users or they didn’t want to support.
For example they would like to hide the “Edit in Datasheet” and “Alert Me” buttons.
A number of people have posted different types of solutions. Some requiring development, some recommending editing core.js, but the solution I liked the best was a bit of Java Script that could be deployed for a single list with a content editor web part or called from a master page if the desired results were more global.
The script was written by Ayman El-Hattab and posted on his blog. When I first deployed it in a CEWP it wasn’t working. I suspected that it was not running last and therefore not overriding the script used to create the links. Talking with Ricky Spears confirmed this. He had run into the same thing when trying to add JavaScript in SharePoint pages. SharePoint has a list of JavaScripts that fire after the page loads. He showed me how to add our script to the end of that list. More info on adding JavaScript can be found in this post.
Here’s how we get ours to load last:
// Our script needs to run last
_spBodyOnLoadFunctionNames.push(”hideListActions”);
function hideListActions() {
// Add menu items by name, separated by commas
hideListViewToolbarItems(”Edit in Datasheet”, “Alert Me”);
}
Here is the entire script:
// Our script needs to run last
_spBodyOnLoadFunctionNames.push(”hideListActions”);
function hideListActions() {
// Add menu items by name, separated by commas
hideListViewToolbarItems(”Edit in Datasheet”, “Alert Me”);
}
function hideListViewToolbarItems()
{
/// <summary>
/// By : Ayman M. El-Hattab ( ayman.elhattab@gmail.com )
/// http://ayman-elhattab.blogspot.com
/// </summary>
var menuItem;
var menuItemName;
var menuItemIndex=-1;
var menuItemNames=new Array(”edit in datasheet”,”open with windows explorer”, “connect to outlook”,’export to spreadsheet’,'view rss feed’,'alert me’,”create column”,”settings:create view”,”list settings”, “document library settings”,”explorer view”,”all documents”,”all items”,”modify this view”,”view:create view”,”new document”,”new item”,”new folder”,”upload document”,”upload multiple documents”);
var menuItems = new Array(”EditInGridButton”,”OpenInExplorer”,”OfflineButton”,”ExportToSpreadsheet”,”ViewRSS”,”SubscribeButton”,”AddColumn”,”AddView”,”ListSettings”,”ListSettings”,”View1″,”DefaultView”,”DefaultView”,”ModifyView”,”CreateView”,”New0″,”New0″,”NewFolder”,”Upload”,”MultipleUpload”);
var allMenuItems = document.getElementsByTagName(’ie:menuitem’);
for(var i = 0; i < hideListViewToolbarItems.arguments.length; i++ )
{
menuItemName= hideListViewToolbarItems.arguments[i].toLowerCase();
for (j=0; j < menuItemNames.length; j++)
{
if(menuItemNames[j]==menuItemName)
{
menuItemIndex = j;
break;
}
}
menuItem=menuItems[menuItemIndex];
for (var l = 0; l < allMenuItems.length; l++)
{
if(menuItemName.indexOf(”:”)!=-1)
{
menuItemName = menuItemName.split(”:”)[1];
}
if (allMenuItems[l].id.indexOf(menuItem)!=-1 && allMenuItems[l].text.toLowerCase() == menuItemName)
{
// For FireFox Compatibility
var parentNodeOfMenuItem = allMenuItems[l].parentNode;
parentNodeOfMenuItem.removeChild(allMenuItems[l]);
break;
}
}
}
}
Here is the resulting list actions menu:
Thanks Ayman for a nice script and thank you Ricky for your help too!
I am sure there are other good ways to accomplish this. Please share them in the comments.
January 27th, 2010 in InfoPath 2007, MOSS Help, SharePoint Designer 2007, Workflow by Kevin Pine
While teaching our online class on InfoPath and SharePoint Designer Workflows (Essentials of InfoPath and SharePoint Workflows), I was asked about using SharePoint list data to populate InfoPath drop-down list controls and filtering a list control based on the selection of another drop-down.
Goal: To create an InfoPath form that includes two drop down lists populated from an external source (SharePoint lists) with the result of the first list selection filtering the second.
Scenario: Adventure Works has four types of bikes that they manufacturer:
· BMX
· Mountain
· Racing
· Touring
They have many bike models for each type of bike (Mountain bikes for example are comprised of the following: MX-100, MX-200, MX-220, MX-300, etc.)
Customer service has a form to track issues from customers. They need to select the model from a list but would like to be able to filter the list of bike models based on the type of bike.
Overview of the steps
In SharePoint:
Create a custom list to maintain the bike types
Create a custom list to maintain the current bike models (with a lookup to the Bike Types list)
In InfoPath:
Create a form
Add a drop down list control and connect it to the bike types list
Add a list (or drop down list) control and connect it to the Bike Models list
Set a filter to filter the models based on the selection from the first control
Detailed directions
1. At the top-level site in the site collection (portal.awbikes.local in the classroom environment) create a custom list named BikeTypes
2. Change the ‘Title’ column name to ‘BikeType’
3. Add the following four items to the list: BMX, Mountain, Racing, Touring
4. Create another custom list named ‘BikeModels’
5. Rename the ‘Title’ column ‘ModelNumber’
6. Add a lookup column named BikeType and lookup to the Biketypes list, BikeType column
7. Populate the list with a number of model numbers and choose a type for each
8. In InfoPath, design a new form using a blank template
9. In the Layout task pane add a Table with Title then add a two-column table in the body
10. Resize the rows and columns and hit the tab key a few times to create more rows in the two-column table as follows:
This list will contain many fields including date, customer contact info, issue type, issue description, etc. We are only going to work with the bike type and model fields here.
11. Switch to the Data Source task pane and rename the ‘myFields’ group to CustomerService
12. Under Actions, click Add a Field or Group and add a Text type Field named BikeType
13. Add another text field named BikeModel
14. Drag the two fields into the form
15. Right-click the BikeType control and change to a Drop-Down List Box
16. Right-click the BikeModel control and change to a List Box
17. Double-click the BikeType control and under the section titled List box entries, select the radio button for ‘Look up values from an external data source’ and click Add
18. Select Create a new connection to: and Receive data, Next
19. Select SharePoint library or list, Next
20. Enter the URL of your site where you created the custom lists above (in class, http://portal.awbikes.local), Next
21. Choose the BikeTypes list, Next
22. Select the BikeType field, Next
23. Select ‘Store a copy of the data…’, Next
24. Enter a name for the connection, BikeTypes, select the ‘Automatically retrieve data…’, Finish
25. Click the XPath button for Entries
26. Expand the folders and select BikeType, OK, OK again to finish
27. Preview the form to test the connection to the Bike Types list
28. Close the preview and double-click the BikeModel list control and under the section titled List box entries, select the radio button for ‘Look up values from an external data source’ and click Add
29. Select Create a new connection to: and Receive data, Next
30. Select SharePoint library or list, Next
31. Enter the URL of your site where you created the custom lists above (in class, http://portal.awbikes.local), Next
32. Choose the BikeModels list, Next
33. Select both the ModelNumber and BikeType fields, Next
(you will just display the ModelNumber but you will use the type in the filter below)
34. Select ‘Store a copy of the data…’, Next
35. Enter a name for the connection, BikeModels, select the ‘Automatically retrieve data…’, Finish
36. Click the XPath button for Entries
37. Expand the folders and select ModelNumber, click Filter Data
38. Click Add
39. Set the filter to BikeType, is equal to, Select a field or group…
40. On the Data Source drop-down, select Main, Bike Type, OK, OK, OK, OK, OK already!
41. Preview the form to test the filtering
You can create the filtering using only one list (just BikeModels) by selecting the Show only entries with unique names option. For very large lists this may impact performance.
Happy filtering!
