Need to edit multiple items to kick off a workflow? No prob Bob!

I was faced with this task recently.  Let me paint the picture for you.

I have a document library with over 1,000 items in it.  I added a new column (Named) that needs to have the same information as an existing column (Name).  For whatever reason, the web part I am using for my list search does not recognize the existing column.  I created a SP Designer workflow to copy the contents from the ‘Name’ to the ‘Named’ on creation or edit.

To do this, there are a couple of options.

  1. The long drawn out option – Edit each file individually
  2. The best option for me – Content and Structure
  3. Power option – SharePoint Designer

I went with option 2 because I did not want to lag the server with a whole lot of who ha.  Here are the steps I followed to make this happen.

  1. Go to the site, Site Settings, Content and Structure
  2. Expand the library to perform this action
    01
  3. Place a check into the ‘Select All’ check box
    02
  4. Click on the ‘Actions’ drop down and select ‘Check Out’
    03
  5. Once checked out (you may have to do this twice), click the ‘Actions’ drop down and select ‘Check In’
    04

Make sure to check that you have performed this action for all items in your view.  Mine were in groups of 100, which is generally the standard view.  The fasted option for me would have been the SharePoint Designer route but I did not want to take a chance of checking out over 1,000 items at once and then check them back in.  We already have enough load on the servers from all of the other users accessing SharePoint.

jQuery Security….. Just hide it!

We had an issue come up about security with InfoPath forms.  One of the major issues was that we could restrict items in the view, but if you used explorer view, all of the security would be bypassed.  To counter this, we use a CEWP and the following jQuery script: HideViewsScript

Please note, you will need to change the first line in the script to reference your jQuery file:

<script src=”/PublishingImages/javascript/jquery-1.3.2.min.js” type=”text/javascript”></script>

This will disable all of those pesky views and you will be able to force your security through your main view.

Add a time duration to a list/library

Years ago, I setup an InfoPath form for an approval process.  There are around 7 status changes during the duration of this form.  We wanted to be able to see how long a request would take from creation to approval.  This was actually pretty simple.  All that you need is a calculated column.  Follow these steps and you will be able to time an event/process.

  1. Open list/library.
  2. Create Calculated Column (Form Duration)
  3. Enter this for the formula:  =TEXT(Modified-Created,”h:mm”)
  4. Click OK

That’s it!  The calculation will look at the fields provided in the formula.  The time will then calculate based on how long it took from creation to when it was last modified.  If you have a form that hides a submit button based on a status, you will then be able to lock down the time calculation.

Enjoy!

Copying files while retaining metadata

There may come a time when you will need to move data from one source to another.  You could always use Windows Explorer to do this, but you will not be able to retain any metadata in your library.

Recently, I needed to move around 1,200 files (680 MB) from a folder into a new document library.  I tried using Windows Explorer but as I stated earlier, it would not copy over the metadata.  Then, I tried using Content and Structure.  This has worked for me in the past and it will preserve your metadata.  However, this time around, I was unable to copy the files.  Not sure why, could have been file size or just the environment.

After all of this, I ended up opening my site inside of SharePoint Designer.  Within 2 minutes, I copied all of the files and preserved all of the metadata associated with the files.

To quote an old southern saying, there is always more that one way to skin a cat.  Just got to find the right one.

Adjust Width of SharePoint Columns

SharePoint lists and libraries are great.  However, you may have noticed that sometimes, the column width on certain items just looks funky.  To remedy this, all you need to do is add a little javascript.

Go to the list or library that you want to resize and add a Content Editor Web Part.

From the script below, I am referencing jQuery from my internal site.

Copy and paste this code into the CEWP.

<script src=”/PublishingImages/javascript/jquery-1.3.2.min.js” type=”text/javascript”></script><script type=”text/javascript”>$(function(){$(“TH.ms-vh2-nograd:contains(‘Column 1’)”).css(“width”, “350px”);

$(“TH.ms-vh2-nograd:contains(‘Column 2’)”).css(“width”, “350px”);

});

</script>

Column 1” and “Column 2” will be the name or your columns that you want to resize.

Here is an example I used and only resized one column:

Before:

 

 

 

__________________________________________________

After:

 

How to Export a SharePoint User List to Excel

There may come a time when you may need to export your user list to excel.  It’s not that difficult.

  1. Copy the following path into notepad/word pad: http://yoursite.com/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl
  2. Minimize the text editor and navigate to Site Actions à Site Setting à People and Groups à All People.
  3. Click Settings on the option bar and click List Settings
  4. Click on “List View” under views.   This will take you to the “Edit View” screen
  5. Copy the url of the list view.  It will look similar to the following:http://yoursite.com/_layouts/ViewEdit.aspx?List=%7B02ACF6F6%2D8343%2D4244%2D81B8%2D68BF95F9C6D0%7D&View=%7B56BB9BDD%2DABB0%2D4CB6%2DA946%2D59A85A8EC2F9%7D&Source=%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D02acf6f6%252D8343%2
    52D4244%252D81b8%252D68bf95f9c6d0%2526Source%253Dhttp%25253A%25252F%25252F
    spdev%25252Eprsontrack%25252Ecom%25252F%25255Flayouts%25252Fpeople%25252Easpx
    %25253FMembershipGroupId%25253D0%252526FilterField1%25253DContentType%252526
    FilterValue1%25253DPerson%252526View%25253D%25257B56BB9BDD%25252DABB0%2525
    2D4CB6%25252DA946%25252D59A85A8EC2F9%25257D%252526Filter%25253D1
  6. Copy the “List” and “View” IDs for the url that was created in step 5.  You list id will follow “List=”.  The same applies for the view id.  Here is the example from above: List=%7B02ACF6F6%2D8343%2D4244%2D81B8%2D68BF95F9C6D0%7D&View=%7B56BB9BDD%2DABB0%2D4CB6%2DA946%2D59A85A8EC2F9%7D
  7. From this example, copy %7B02ACF6F6%2D8343%2D4244%2D81B8%2D68BF95F9C6D0%7D and replace [LISTID] from step one.  This includes replacing the LISTID and the brackets.  Also, do not include the & symbol.  Follow the same steps for the “View”.
  8. Once you have finished, your completed url should look like this:http://yoursite.com/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=%7B02ACF6F6%2D8343%2D4244%2D81B8%2D68BF95F9C6D0%7D&View=%7B56BB9BDD%2DABB0%2D4CB6%2DA946%2D59A85A8EC2F9%7D&CacheControl
  9. Open a browser, and copy and paste the completed url that you completed in step 8.
  10. A window will pop up asking you to open or save the file.  Click open and the file will open in excel.
  11. A security warning will also pop up.  Click enable.
    open_excel

 

 

 

 

 

12.  After you are finished making your changes, you can save this like any other Excel file.

 

 

 

Dataview Web Part – Access Denied

The Problem

When a user that is restricted from a specific list visits the site; the view of that list displays an access denied message rather than hiding the Web Part from the user.  It appears that the security for the list is evaluated prior to the Web Part evaluating the targeted audiences.  Since the user does not have access to the list, the access denied message is shown as a result.

 


The Solution

We can remedy this issue by granting the lowest privilege on the list for the user.  As a requirement, the user must not be able to view any list items, so we will give the user the ability to see the list, but not the ability to see any items in the list. This is accomplished by creating a custom permission level that will contain ONLY the Open permission from the Site permissions group.  This will allow users possesing this permission to open the list, but will not grant them the permission to open any items in the list.  Grant the newly created permission level to the restricted users on the list and the audience targeting on the list views will now function as intended.

Happy Birthday Web Part

I wanted to be able to show all of the employees birthday’s for the current day.  Turns out, this was not a very hard task to accomplish.  I have a field in one of my lists’ that contains a birth date field.  All of the months and days are accurate, but the years are all set to 1996 for security reasons.  In order to show upcoming birthdays, I needed to change a few things.

Here’s the deal with that.  The “Birthdate” field holds the actual birthday of the user.  In my case, 24-Jul-96.  However, I want to see it as July 24 2010.

Well, since the [Today] field in calculated column will not work (all tricks will not work – the field won’t update!), here is a solution that only asks for a yearly update to the column.

First, I created a calculated column that results in the current year “Birthdate”.  This changed the view of the date from 24-Jul-96 to July 24 2010.  Note:  “Birthdate” is the name of the column in my list that contains the actual birthday.  Yours may vary.

  1. Create a calculated column to hold this year’s birth date named “Birthday”.
  2. Set the output type of the calculated value to “Date and Time”.
  3. Set the date and time format to “Date Only”.
  4. Use this formula to generate its value.

=DATE(2010,MONTH(Birthdate),DAY(Birthdate))

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5.  Create a new view of the list named “Birthday” and click on the plus to expand “Filter”.

6.  Next, enter in the following.  Once you click Ok, you will see all birthdays for the upcoming week.

 

 

 

 

 

 

 

 

 

 

 

 

7.  If you only want to see the birthdays for the current day only, simply change the filter to the following.

 

 

 

 

 

 

 

 

 

 

 

 

 

Just remember – next year you will have to update the formula of the calculated column from 2010 to 2011.

Enjoy!

Create an A-Z quick filter web part

If you have a list, like a corporate phone directory, you may start thinking “Man, I wish I could sort through these by the first letter of their name”.  If you weren’t thinking that, it’s ok.  I was so that why I decided to post this.  Follow these steps and your new web part will be up and running in no time.

  1. Open the list that you want to add the A-Z filter to (my case “Employee_Phonebook”)
  2. From the “Employee_Phonebook” tool bar, click “Settings –> Create Column”
  3. Name the column “A-Z” or whatever name you would prefer
  4. Select “Calculated” as column type
  5. Under Additional Column Settings, select “Title” from Insert Column and type =LEFT([Title],1) into Formula. Keep all default settings and click “OK”.  Note: “Title” is the name of the column you are targeting for this list (i.e. Last Name)
  6. You will see new column called “A-Z” and has the letter “A” value in them already.
  7. From the Site Actions tab, click Edit Page
  8. Click Add a Web Part and add the “Content Editor Web Part”
  9. Modify that Content Editor Web Part and click the “Source Editor” button to add the following code.
    ** Note: You will need to modify the URL’s below to reference your site.

 

  1. Click “Add a Web Part” and add “Query String (URL) Filter”
  2. Enter a name for “Filter Name” then enter “FilterBook” into “Query String Parameter Name” then click “OK”
  3. Now from Query String (URL) Filter, select Edit –> Connections –> Send Filter Values to “Employee_Phonebook” and when the Popup appears select “A-Z”
  4. Click the Finish button and your done

Your finished product will look similar to the image below