8/09/2010

SharePoint: Import Spreadsheet – Which column is the edit column?

 

Why does Import Speadsheet pick column “A” as the edit column?

 

I saw something odd in an import recently where the import picked a column way out to the right of the list. So I put together a test with columns in all different orders, and one that only had numeric columns.

 

 

Import #1 – second column used as the edit column

image

 

Import #2 – fourth column used as the edit column

image

 

Import #3 – first column used as the edit column

image

 

Import #4 – no imported column used! New one added!

image

 

 

So the answer is…

 

SharePoint uses the first “text” column it finds, and if it can’t find one, it creates one!

I opened each of the views above in SharePoint Designer, and found the the Edit Menu column in each one has an internal name of LinkTitle.

.

8/07/2010

SharePoint 2010: Change the “Add New Item” (and other) messages for a web part

 

This a SharePoint 2010 update to the SharePoint 2007 article found here: http://techtrainingnotes.blogspot.com/2009/07/sharepoint-change-add-new-message-for.html

 

The Goal

To change from “Add new item”:

    AddNew1

to “Click here to add team tasks”:

    AddNew2

 

ID’s used by web parts

Each web part has a different ID and some share a common ID. You will need to modify the following code examples with the ID used by your list. Note that some lists share the same ID, like Tasks and KPIs.

Note: Many of these IDs have changed in 2010!

If the ID you need is not in this table, display the web page with the web part, use the browser’s View Page Source option and search for the message text. Then look backward in the <A> tag and find the ID.

Web Part Default message ID for “.getElementById” in the code below
Announcements Add new announcement “idHomePageNewAnnouncement” (changed in 2010)
Links Add new link "idHomePageNewLink" 
Calendar Add new event "idHomePageNewEvent"
Picture Library Add new picture “idHomePageNewItem”  *  (changed in 2010)
KPI List Add new item "idHomePageNewItem"  *  (changed in 2010)
Tasks Add new item "idHomePageNewItem"  *  (changed in 2010)
Project Tasks Add new item "idHomePageNewItem"  *  (changed in 2010)
Document Library Add new document "idHomePageNewDocument"  (changed in 2010)
Wiki Add new Wiki page "idHomePageNewWikiPage"   (changed in 2010)
Discussion Add new discussion "idHomePageNewDiscussion"  (changed in 2010)
Custom List Add new item “idHomePageNewItem” *   (changed in 2010)

* = Shared with another list

 

If there is only one “Add new” message on a page

I.e. there is only one web part on the page that uses that ID then we can use this simple code:

<script>
  document.getElementById("idHomePageNewItem").innerHTML="your new text goes here"
</script> 

 

But the IDs won’t always work!

SharePoint can build pages with multiple elements with the same ID. This is not proper HTML, but, they do it…

So we will need something a little more complex:

<script>
var ttnA = document.getElementsByTagName('A');
for (var j=0; j<ttnA.length; j++)
{
  if (ttnA[j].id == 'idHomePageNewItem')
  {
    ttnA[j].innerHTML='your new text goes here'
  }
}
</script>

This code runs through all of the Anchor (<A>) tags in the page looking for the ones with the ID we need and then changes the text as needed.

What if you only wanted to change the second web part that uses that ID to a new message? Then try this:

<script>
var ttnA = document.getElementsByTagName('A');
var ttnCounter = 0;
for (var j=0; j<ttnA.length; j++)
{
  if (ttnA[j].id == 'idHomePageNewItem')
  {
    ttnCounter ++;
    if (ttnCounter == 2)
      { ttnA[j].innerHTML='your new text goes here' }
  }
}
</script>

 

Where should you add the code?

Add the code to the page using SharePoint Designer

  1. Edit the page using SharePoint Designer
  2. Find the “<asp:content” tag for PlaceHolderMain and then find the matching end tag (“</asp:content>”)
  3. Just before the end tag, add the JavaScript listed above

Or add a Content Editor Web Part to the page

This is the easiest for testing, and does not require SharePoint Designer. In SharePoint 2007 this worked equally well for web part pages and View pages. In SharePoint 2010, adding a web part to a View page causes the View selector dropdown to be hidden, so the SharePoint Designer approach might be better for View pages. The CEWP works just fine for web part pages and the new “wiki style” home pages. The only got’ya is that they have removed the Source Editor button from the CEWP. If you use the Edit HTML button in the Ribbon you may find that your HTML and JavaScript gets modified by their editor. Best practice with the 2010 CEWP is to store your HTML and JavaScript in a text file in a library and then use the CEWP’s link to a file option.

  1. Create a text file with the JavaScript code and upload to a library
  2. In the library right-click the file, click Properties and copy the URL of the file
  3. Add an Content Editor Web Part (CEWP) below the web part you want to customize
  4. From the CEWP click web part’s dropdown arrow and click Edit Web Part
  5. Paste the URL you copied into the Content Link box

 

Hide the “+” image?

If you want to get rid of the little icon just before the “New Item” text then just add one more line of code after where you change the message:

     ttnA[j].innerHTML='Click here to add team tasks';
     ttnA[j].previousSibling.previousSibling.style.display='none';

 

 image    image

 

Hide the entire message?

No tricks needed!  Just edit the web part and change the “Toolbar Type” to “No Toolbar”. (The “New” link we’ve been working with is the “Summary Toolbar”.)

image

 

.

8/06/2010

SharePoint: Creating Calculated Column Formulas the Easy Way Using Excel

The following works in both 2007 and 2010.

The MSDN documentation on calculated columns:

 

Calculated Columns

Below is a screen capture of where you enter the formula for a Calculated column. See that big empty space to the left… wouldn’t it have been great if they had used that space for some notes, examples, or even links on how to create formulas?

image

Built in help?

Click the the Help button and then search for “Introduction to data calculations” and “Examples of common formulas”

 

A short FAQ on calculated columns:

  • Calculated columns can only interact with data in the same “item” – an item is a single task, event, document, etc.
  • Calculated columns cannot interact with another row, or summaries (total, etc) of all of the list
  • The formulas you write are basically Excel compatible formulas – most will work! – see the links at the end of this article
  • Calculated columns can be reused by creating them as Site Columns (but this column can only reference other Site Columns!)
  • Column names with spaces or symbols must be enclosed in brackets “[Sale Amount']”
  • Nesting is only supported for eight levels – Example of three levels: =IF(a>b,1, IF(b>c, 2, IF(c>d, 3, 99)))
  • The [TODAY] and [ME] functions do not work in a calculated column, but can be used in columns referenced by a calculated column
  • RAND and NOW are not supported in SharePoint 

Ok, got that out of the way…

 

The Easy Way to Write Formulas

 

Writing complex equations in a simple text editor is not easy. Lots of trial and error, and mostly error. Instead use Excel!  Name one cell for each column you are using in your calculation.

Example: We need a little challenge in this one… we’ve got a list of computer stuff for sale… you get a discount if you buy in quantity… the discount varies based on the type of item…

  • Buy less than 10 items, no discount
  • Buy 10 or more Hardware items and get a 30% discount
  • Buy 10 or more Software items and get a 50% discount
  • Buy 10 or more “Other” items and get a 20% discount

Steps:

  1. Open Excel
     
  2. Down column A list the names of columns:
      image
     
  3. Highlight the names you entered and the empty cells in the next column
      image
     
  4. From the Formulas Ribbon tab, click “Create from Selection”, select only “Left column” and click OK
      image
     
  5. If you want to confirm that you named your cells, click one of the cells in the second column and check for the cell in the formula bar 
      image
     
  6. Enter some test data:
    image
     
  7. Now write an equation using normal Excel rules, but use cell names instead of cell addresses:
         = Qty * Price 
    image
    IntelliSense even helps by listing your cell names!
     
  8. Now add our first discount test – Less than 10 gets no discount, more than 10 gets the minimum of 20%:
      image
    IntelliSense helps again… now finish the formula:
      image  
     
  9. Do some testing now:
      image    image
     
  10. Let’s change the formula a bit to save some repetition:
      image
     
  11. If is is hardware they get 30%:   (click the image below if you don’t see all of the formula)
    image 
    And if you want a translation, it would be something like this:
    The first comma of the IF is the “THEN” and the second comma is the “ELSE”:
         IF Qty<10 THEN 100% ELSE   IF Category=”Hardware” THEN 70% ELSE …

    This is standard Excel… nothing special for SharePoint!
     
  12. And do some testing:
    image   image    image
     
  13. Now copy the equation from Excel and paste it into SharePoint:
      image
     
  14. and the result:
      image

Writing equations of this sort is often is a two part process: break the problem down into pieces and then assemble and test each piece at a time. the challenge is ending up with a single formula as a single line of text.

 

What about spaces in column names?

When you follow the steps above, but with column names with space or symbols, Excel will create cell names with underlines.  “Sales Price” becomes “Sales_Price”. When you copy and paste these into SharePoint you will need to change the underlines back to spaces and enclose the names in square brackets like this: [Sales Price].  

Another example: 
   Excel will change   Total & Tax    into  Total___Tax    and you will need to change it back to    [Total & Tax] 

 

Text “math”

Use the ampersand to combine text columns. You will usually need to add spaces and commas to the results. Literal text goes in quotes.

image

 

Functions of all kinds:

Let’s finance a car:

image

See the resource links below for more examples.

 

 

A few resources:

 

Introduction to data calculations

http://office.microsoft.com/en-us/windows-sharepoint-services-help/introduction-to-data-calculations-HA010121588.aspx

 

Examples of common formulas

http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA010105479.aspx

 

.

8/02/2010

SharePoint Saturday Columbus - August 14th 2010

 

http://www.sharepointsaturday.org/columbus/default.aspx

Register here: http://spscolumbus.eventbrite.com/

 

Finally we get a SharePoint Saturday (almost) local to Cincinnati! It’s just a little drive up the road to Columbus:

Join SharePoint architects, developers, and other professionals that work with SharePoint 2007 and 2010 for SharePoint Saturday Columbus event.  SharePoint Saturday is an educational, informative & lively day filled with sessions from respected SharePoint professionals & MVPs, covering a wide variety of SharePoint-orientated topics.  SharePoint Saturday is FREE, open to the public and is your local chance to immerse yourself in SharePoint!

 

There will be around 24 sessions, so there should be interesting topics for everyone. Go to the link above so see the complete list. 

 

 

And if you can’t find a truly interesting topic, you can stop in and see mine…

 

SharePoint 2007 and 2010 Customization for Site Owners

This session will get you, the SharePoint Site Owner, started in customizing your SharePoint site. It is not a programming session, but does include some JavaScript coding. It is not a graphics design session, but does include some Cascading Style Sheets. It is not a SharePoint Designer session, but it will make use of it. And nothing requires access to the SharePoint servers. While the session is for the SharePoint Site Owner, it's also for the developer who does not want to "reinvent the wheel". All you need to apply what you learn here are basic SharePoint skills, how to copy and paste and some puzzle solving skills.

Mike Smith is currently a Senior Instructor at MAX Technical Training in Cincinnati Ohio. He has worn many IT hats over the last thirty years as a business owner, developer, tech writer, trainer, DBA and consultant. He is a SharePoint MVP and a Microsoft Certified Trainer (MCT) and has 13 other MC* certifications. He specializes in SharePoint, SQL Server and .NET development and is a member of the Cincinnati SharePoint User Group leadership team.  If all goes well, Mike should have a new book for SharePoint Site Owners available in August.

http://www.sharepointsaturday.org/columbus/meetings/16/SharePoint2007and2010CustomizationforSiteOwners.aspx

 

And remember it’s free!  And you must register!

 

.

Are you an unemployed developer? (Project Phoenix)

 

For complete details: http://sqlblog.com/blogs/arnie_rowland/archive/2010/07/30/like-a-phoenix-rising-from-the-ashes.aspx

 

 

Project Phoenix is inviting unemployed or underemployed developers to propose a software project for a non-profit agency, school, or church. The idea is that we will provide a package of the latest software, tools, and training resources to help you improve your skills, get up to date with current technologies, gain practical experience, potentially earn a recommendation for your efforts, and in general, enjoy the feeling of accomplishing something useful for others. We are not giving out a 'free lunch', just supporting your efforts to personally gain from your own 'sweat equity'.

Project Criteria:

  • Client is a USA IRS 503(c)3 non-profit, school, or church, OR a Canadian CRA registered charity,
  • Solves a problem or satisfies a need for the client,
  • Client desires the project and is supportive,
  • Uses any combination of .NET 4.0, Windows Server 2008, SQL Server 2008, or Windows Phone 7,
  • May be a new software solution, or an upgrade to an existing software solution,
  • Additional consideration given projects that will be posted on Codeplex with a GPL license.

All 24 selected developers will receive the following award benefits:

Award timelines:

Every two weeks for the remainder of 2010, two (2) additional developers and projects will be selected.

For complete details: http://sqlblog.com/blogs/arnie_rowland/archive/2010/07/30/like-a-phoenix-rising-from-the-ashes.aspx

8/01/2010

SharePoint: Strange error message of the day…

 

‘null’ is null

And that’s not as it should be?

 

image

 

I know, this is a common JavaScript error. It just kind of pressed my “huh” button.

(only 46,000 hits when you Google "'null' is null or not an object")

 

 

.

SharePoint 2010: Missing View Dropdown

 

SharePoint 2010 has mixed three features into the title area of a page, the Site Title, the crumb trail and for lists and libraries, a View selection dropdown.

Here’s an example:

image

 

Now you want to add a custom message to a view page…

 

In 2007 we have two choices to add custom text to a view page (a web part page). One is to fire up SharePoint Designer, edit the page and then save it. This process produced a customized page (unghosted page to use the 2003 term). The other approach is to add a Content Editor Web Part (CEWP) to the page.

There are several reasons to use the CEWP: it does not unghost the page, it’s not a “customization” that might annoy our management or the next site owner, and it’s… well… just quick and easy.

 

So what’s the problem?

 

Adding a web part to a View page breaks the View selector in the crumb trail. Here’s view page with an added CEWP.  We have now lost the View selector!

image

 

But why? The did give us Edit Page and Add a Web Part! No warnings anywhere!

image image

 

I’ve been told that this is a feature, not a bug. It was done on purpose to show that this is no longer a View, but a custom application page.

 

So what works?

 

A SharePoint Designer customization. That’s right! Edit and unghost the page and you get your customization and also get to key the View selector. So here’s the page as customized in SharePoint Designer:

image

 

So which approach you recommend to your typical Site Owner?

 

Go figure…

 

 

To see one way I’ve dealt with this see: http://techtrainingnotes.blogspot.com/2010/07/sharepoint-2010-replacing-crumb-trail.html

 

.

7/26/2010

SharePoint Workflow Documentation

 

Here’s a list of the out of the box workflows and links to Microsoft’s documentation… (just some more after class documentation!)

SharePoint 2007 Workflows

More workflow documentation, examples and videos

http://office.microsoft.com/en-us/sharepoint-server-help/CH010176053.aspx?CTT=97

 

SharePoint 2010 Workflows

(looks like they have not gotten around to doing the same level of documentation for 2010 in the Office web site as they had done for 2007 – or I just can’t find it)

 

.

7/23/2010

Worst license dialog box?

 

Below is the license screen for the .NET Framework 4 Setup. Big, almost empty dialog box, and they only display three and a half lines of the license agreement at a time…  Maybe they added the print and save buttons so you could actually read it.

 

image

 

.

7/22/2010

SharePoint: Add Instructions and Color to Surveys

 

Only tested in SharePoint 2007 so far…

 

Survey Instructions

Surveys often need instructions or opening text. Here is a trick that converts a “Single line of text” question into just a page of text. There is one side effect; your “instructions question” will show up in the results as a query that everyone answered as “HIDDEN”. (But you could change the word if you like.)

 

image

Steps:

  1. For each "Instructional Text" needed in the survey add a question to the survey of type “Single line of text”, add your text as the question, and set a default value of "HIDDEN"
     
  2. If you want the instructions on their own page, then add a Page Separator after the instructions item
     
  3. Go to your survey and click “Respond to this Survey” to display the Newform.aspx page
     
  4. Edit the URL to allow access to the web parts:
      append this to the end of the URL for the NewForm.aspx URL:  
                &ToolPaneView=2 
    and press Enter  

    http://yourserver/sites/yoursite/Lists/SurveyTest/NewForm.aspx?Source=.......overview.aspx&ToolPaneView=2
  5. Click “Add a Web Part” and add a Content Editor Web Part
     
  6. Move the Content Editor Web Part below the list web part
     
  7. Edit the web part, click the Source Editor button and add the JavaScript from below
     
  8. Save your changes and go test the survey!
<script>
 // hide the input textbox for the "description" text
 var x = document.getElementsByTagName("INPUT");
 var i=0;
 for (i=0; i<x.length; i++)
 { 
  if (x[i].value=="HIDDEN")
  {
    x[i].style.display="none";
  }
 } 
</script>

Note: If you have a multipage form (you added page separators or branching logic) then you will need to add a web part with the same code as above in the EditForm.aspx page. To get to this page just respond to the survey and click Next. Then to add the web part add &ToolPaneView=2 to the end of the URL.

 

 

Add color to your surveys!

 

Just follow the steps above, but use the following JavaScript instead. Then manually embed your HTML in the text of the question: "This is a survey about <b><i>Important Stuff</i></b>. Please take your time."

<script>
 // Fix tags to allow HTML
 var t = document.getElementsByTagName('table');
 for (var i=0; i<t.length; i++)
 {
   if (t[i].className=='ms-formtable')
   {
     var td = t[i].getElementsByTagName('td');
     for (var j=0;j<td.length;j++)
     {
       if (td[j].innerHTML.indexOf('&lt;') > -1)
       {
         td[j].innerHTML = td[j].innerHTML.replace(/&lt;/g,'<').replace(/&gt;/g,'>'); 
       }
     }
   }
 }
</script>

 

 

Or add instructions and color!

Use both JavaScript routines together:

<script>
 // hide the input textbox for the "description" text
 var x = document.getElementsByTagName("INPUT");
 var i=0;
 for (i=0; i<x.length; i++)
 { 
  if (x[i].value=="HIDDEN")
  {
    x[i].style.display="none";
  }
 } 

 // Fix tags to allow HTML
 var t = document.getElementsByTagName('table');
 for (var i=0; i<t.length; i++)
 {
   if (t[i].className=='ms-formtable')
   {
     var td = t[i].getElementsByTagName('td');
     for (var j=0;j<td.length;j++)
     {
       if (td[j].innerHTML.indexOf('&lt;') > -1)
       {
         td[j].innerHTML = td[j].innerHTML.replace(/&lt;/g,'<').replace(/&gt;/g,'>'); 
       }
     }
   }
 }
</script>

 

.

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.