Thursday, April 18, 2013

SharePoint 2010 Surveys & How to Fix the Rating Scale Spacing


I recently have been asked to create surveys to capture this and that and the most commonly used question in my experience is the rating scales, but this question type is also the cause of an issue.  The issue is that longer questions within the scale get smashed together like I have shown below and make it nearly unreadable.  In this article I will show you how to fix this issue.



To Fix This Issue You Will Need:
1.    SharePoint 2010
2.    A Survey
3.    A question using rating scales
4.    SharePoint Designer
5.    This Code Block:
<style>
.ms-gridT1 { padding-top: 10px; padding-bottom: 10px; }
</style>

After you have created the Survey and you have questions which utilize the rating scale, you will need to open SharePoint Designer.

From SharePoint designer navigate to your site and then open the survey under lists and libraries.



The next set of steps will need to be done for the “DispForm.aspx”, “EditForm.aspx”, & “NewForm.aspx”.

To increase the padding between questions:
!1.    Right Click the NewForm.Aspx & edit in advanced mode
2.    Set the View to either Code or Split
3.    Find the Tags <ContentTemplate></ContentTemplate>
4.    Place the provided code block within these tags like the image below:
a.    

5.    Right Click the form tab and select save.
6.    Say Yes to the following message
a.    

7.    Navigate to your site and pull view the form (whichever form you did these steps for, I happened to do the newform for this example) and it should look like the below screen shot

!!!Please Note!!!!!
You will have to do this process for each of the forms within your survey.  In this example I did the newform, but if I were to view the item using the viewform, the questions would be smushed since we have not modified that display form.

I hope this helps and enjoy the easy to read surveys!

Dan

Friday, April 12, 2013

SharePoint Calendar Overlay Remove Duplicates


I recently have been asked to create surveys to capture this and that and the most commonly used question in my experience is the rating scales, but this question type is also the cause of an issue.  The issue is that longer questions within the scale get smashed together like I have shown below and make it nearly unreadable.  In this article I will show you how to fix this issue.





To Fix This Issue You Will Need:
1.    SharePoint 2010
2.    A Survey
3.    A question using rating scales
4.    SharePoint Designer
5.    This Code Block:
<style>
.ms-gridT1 { padding-top: 10px; padding-bottom: 10px; }
</style>

After you have created the Survey and you have questions which utilize the rating scale, you will need to open SharePoint Designer.

From SharePoint designer navigate to your site and then open the survey under lists and libraries.



The next set of steps will need to be done for the “DispForm.aspx”, “EditForm.aspx”, & “NewForm.aspx”.

To increase the padding between questions:
!1.    Right Click the NewForm.Aspx & edit in advanced mode
2.    Set the View to either Code or Split
3.    Find the Tags <ContentTemplate></ContentTemplate>
4.    Place the provided code block within these tags like the image below:
a.    

5.    Right Click the form tab and select save.
6.    Say Yes to the following message
a.    

7.    Navigate to your site and pull view the form (whichever form you did these steps for, I happened to do the newform for this example) and it should look like the below screen shot

!!!Please Note!!!!!
You will have to do this process for each of the forms within your survey.  In this example I did the newform, but if I were to view the item using the viewform, the questions would be smushed since we have not modified that display form.

I hope this helps and enjoy the easy to read surveys!

Dan

Friday, April 5, 2013

How to Link Your SharePoint List to Excel!

How to Link Your SharePoint List to Excel:

SharePoint provides the capability to Link your SharePoint list data to an Excel workbook.  This link is a one way street where data updates made on SharePoint will be reflected in the Excel workbook, but any updates made within Excel will not get pushed through to the SharePoint list.

To Create The Data Connection:

1.    Navigate to your SharePoint list
2.    List Tools
3.    List
4.    Export to Excel
5.    Select Open
6.    In Excel select enable

When you use this method you are not just sending the table of data to Excel, you are actually creating the data connection within the workbook.

You can tell if workbook has a connection by doing the following:

1.    Data Tab on the ribbon
2.    Connections
3.    Connections
4.    There should be something along the lines of Owssvr

You can tell exactly where the connection is going to by inspecting the properties.  Within the properties it will say what the URL is, the list ID, & view ID.

This data will stay stagnant until you refresh the data connection.  You can refresh the data connections within your workbook by:

1.    Data Tab
2.    Connections
3.    Refresh All

Many companies would like to report weekly or monthly status based on the list and use Pivot Tables and Pivot Charts to do so.  By creating the Pivot Tables & Pivot Charts based upon the linked table will allow all of your pivots to be updated when the table is refreshed.

Most users do not know how to refresh the data connection between SharePoint and Excel so I typically like to use VBA on the workbook load to refresh the data or creating a button and using VBA there to Refresh All.  In order to create the button or On Open Code, you will need to enable the developer tools.

How to Enable Developer Tools For Excel (Developer Tab):

1.    Within Excel Select File
2.    Options
3.    Customize Ribbon
4.    Checkmark The Developer Box Under Main Tabs
5.    Select Ok

The Code You Will Need For The Button Refresh:
Button Code (Your button (Name) will replace CommandButton1)
Private Sub CommandButton1_Click()
ActiveWorkbook.RefreshAll
End Sub

&

Workbook Code
Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
End Sub

To Refresh Automatically On Workbook Open:

1.    Developer Tab
2.    Code
3.    Visual Basic
4.    This Workbook
5.    Drop The Private Sub Workbook_Open() code provided above in and save

To Create A Button To Refresh On Button Click

1.    Developer Tab
2.    Insert
3.    ActiveX controls
4.    Command Button
5.    Double Click Into The Button
6.    Insert the ActiveWorkbook.RefreshAll into the button within the sub procedure

In order for your users to use these “Macros,” you must save the workbook as a Macro Enabled Workbook through Save As.

Enjoy!
Dan


Wednesday, April 3, 2013

Event Content Types Remove Recurrence, All Day Event, & Workspace From Forms


I have had many requests to modify the fields encapsulated within the Event Type and that is typically not an issue.  There are three pieces of Meta Data which seem to be impossible to remove through the site interface, “All Day Event,” “Recurrence,” & “Workspace.”  Although these fields can’t be removed through your site interface, you can hide 1 or all of these fields through SharePoint Designer.
 

Many of the sites I have worked on have requested a Calendar which allows the user to select “All Day Event” & “Recurrence,” but would like to have Workspace Removed. (We will remove it in this example)

 

How to Hide Workspace:

 

1.    Open Designer

2.    Under the sites tab, open the site where the Event Content Type Resides

3.    Under Navigation, select “Content Types”

4.    From the Content Types Tab, select the Event Type which needs to have Workspace hidden

a.    You should now have a tab open with the name of your content type

5.    Select “Edit Content Type Columns” under Customization

 

This is where it can be a bit tricky and you start to realize some of the quirks of SharePoint Designer.

 



When you select Workspace, there is not a dropdown arrow to select the Property Type from the combo box.  This is extremely irritating, but you have to click 3-4 (typically) to the right of the Optional Dialog until you will be presented with the dropdown options

 
You can now select the Hidden Property!  Save your content type and your updates should now be reflected in SharePoint!

 
Regards,

Dan

Monday, April 1, 2013

Linking Lists & Editing The Default Display Form


This post will explain how to create a connection between two lists and then how to display the connection in the display item form. These lists are extremely simplistic, but they will help to visualize the idea. (I apologize for the small screenshots; Blogger does not seem to like my Snagit Shots)

 
Creating a connection between two or more lists can be extremely helpful and for this example I will explain it in terms of Employees and an Employer. If any of you have had to work with database schemas in the past, you will know all too well about data relationships, data integrity rules, and normalization. For those readers who have not had exposure to this yet, here are the cliff notes.


There can be many employees to one employer, so the relationship is a one to many relationship. For the linking of these lists we will want the Employer (Parent) to display all of the Employees (children) when we open the "View Item Form."

To do this we will create two custom lists (the meta data within each list does not matter for this example, but you will need the lookup field)

1.      Employer

a.       Employer Name –title

b.      Employer City – combo box

c.       Phone Number – single line of text

&

2.      Employee

a.       Employee Name - title

b.      Employed By - lookup

c.       Employee Type – radio button

d.      Emergency Contact Number – single line of text



The field which will enable this connection of the two lists is the lookup field


Within the Employees List (Child) select the Lookup field and select Employer (Parent) like I have done in the below screenshot.

 


Now that you have created the two lists, create some dummy data within Employer & Employees so there is data to select from.


I have created three list items in “Employee” linked to “Employed by" Example 1


This view is ok when viewing the data from the Employee list, but what about the view for the “Employer List?”



This view does not show us any of the employees that are linked to example 1, so we will edit the display form to show our related items.


List Tools → List→ Customize List→ Form Web Parts→ Default Display Form

 


From this page go to:

Page Tools→ Insert→ Related List→ Employee

Your View Item Form should now resemble the following



 

Congrats on creating a connection and displaying it in the “View Item Form!”