SharePoint – Retrive the internal column name for lists/libraries using PowerShell

I have been using this method for some time now and decided to make a slight change to it.  I have been using the PowerShell method found here to find the internal column names for my lists and libraries.  It works great.  The only problem with this is that I have to modify the script each time I need to change the value of the list or site.  This is usually not a big deal since I generally am only looking at one list.  This was not the case the other day.  I needed to look at many different lists in the same site.  Instead of having to modify the script each time, I added a set of parameters to prompt me at the command line.  The code looks like this:

  1. Download Get Column Names Script
  2. Save the script to a directory on your SharePoint server
  3. Open the SharePoint Management Shell for PowerShell
  4. Navigate to the directory where you downloaded the script
    example:  d:\scripts
  5. Run the script: getColumnName.ps1
  6. Enter in the variables
    ***To include hidden fields, change {$_Hidden -eq $false} to {$_Hidden -eq $true}***

Your screen should resemble the following before the command runs

psGetCN

Mike Smith has a lot of great tips on his site.  I suggest that you check it out:  http://techtrainingnotes.blogspot.com/

Stay Salty!

Cannot publish InfoPath custom list form

Today, I ran across an issue when I went to customize a list with InfoPath.  I clicked on the ‘Customize Form’ button and it opened the item view in InfoPath as expected.  After I made my changes I went to publish my form.  I received the following error message:

“InfoPath cannot connect to the server.  The server may be offline, your computer might not be connected to the network, or InfoPath Forms Services 2010 might not be enabled on the server.  To fix this problem, start by checking your network connection, and then try again.”

Bummer!

After going through my normal rounds of troubleshooting (permissions, InfoPath, SharePoint logs, Event Viewer), I came across another blog that had the solution (at least, it worked for my issue)

Here is the link to the blog: http://brenthafnersblog.wordpress.com/2013/01/15/cannot-publish-infopath-forms-or-cutomized-list-forms-in-sharepoint-lists-2010/

Basically, all that needs to happen is that you need to reset InfoPath Form Services for that particular site collection.  To do that, just run the following PowerShell scripts from your SharePoint server:

Make sure to update the urls for the above commands

 

Enjoy and remember to Stay Salty!

InfoPath 2007 – Populating from data/dropdown info from SharePoint lists using XML form data

This article applies to InfoPath 2007 only as 2010 and 2013 no longer have this limitation.

At some point during your career, you will need to connect to a SharePoint list to retrieve information for a drop down list.  This process has changed since 2007 but I thought that I would go ahead and share this.  InfoPath 2007 only reads the data in the list on how it was entered in to SharePoint.  For example, if you uploaded a list of addresses in no particular order, this is the way that InfoPath reads it.  Now, what if you would like to see your selection sorted alphabetically?  You will first need to either modify your existing view of  the list you will be using as a data source or create a new view.

***Note: In order to see the entire list, the view must be changed to show more than the default 100 records

InfoPath will not recognize this view and you cannot sort lists when forms are browser based. As stated earlier, it only sees the original layout. In order for InfoPath to populate the drop down list with the correct format you will need to do the following:

  1. Double click the drop down list that you want to add the data connection to. This box will pop up. Follow the steps in the remaining screenshots to step 4. Then follow further directions.
  2. 1  
  3. 2  
  4. Go to the site that contains your list that you want to pull your data from.
    Example: http://yoursite.com/subsite/ and open the list
    Click “Settings” on the taskbar of the list then click “List Settings”
    Click on the view that you would like to use located at the bottom of the page
    When the page opens, highlight the entire address bar and press Ctrl+C
    Open a text editor (Notepad) and press Ctrl+V. Your address will look something like this:http://yoursite.com/subsite/_layouts/ViewEdit.aspx?list=%7B9C833605%2D8911%2D4B69%2D84AA%2D24DF8775735F%7D&View=%7B734A3221%2D4E03%2D4B10%2D9BE9%2D857775EBF802%7D&Source=%252Fdepartments%252Fhealth%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D%25257B9C833605%25252D8911%25252D4B69%25252D84AA%25252

    D24DF8775735F%25257D

  5. You will need to modify this address to provide the form with the correct XML values. To do so, either type or copy/paste from this address the following.
    Make sure to replace yoursite.com/subsite with your own variables:http://yoursite.com/subsite/_vti_bin/owssvr.dll?Cmd=Display&List=%7B9C833605%2D8911%2D4B69%2D84AA%2D24DF8775735F%7D&View=%7B734A3221%2D4E03%2D4B10%2D9BE9%2D857775EBF802%7D&XMLDATA=TRUE
  6. The %, letter, number string represents the {GUID} for the page. The &XMLDATA=TRUE converts the page into an XML format that the InfoPath form can read. The &View and string represents the {GUID} for the “AllItems View”
  7. Once you have modified the url, copy and paste it into the next setup page. Close the text editor and do not save when it asks. Follow the steps in the remaining screenshots.
  8. 4  
  9. 5  
  10. 6  
  11. 7  
  12. 8  
  13. 9  
  14. Save the InfoPath form and publish it to the SharePoint site.  Next, login to SharePoint and go to the form that contains this data connection. Verify that the information is displayed in the correct order.

This little trick will save you all kinds of headaches if you rely on information supplied by your SharePoint lists.

Synchronize SharePoint list using Access 2007

If you need to make changes outside of SharePoint to a list, you always have the option to use Access.  Follow these steps and you will be well on your way to using this feature.

1. Open your SharePoint list

2. Click Actions  –> Open with Access

Actions dropdown

3. A dialog box will pop up on your screen. When it does, click OK.

Access Prompt

4. After you click OK, Access 2007 will pop up on your screen. Double click on the list.

SharePoint List

5. When the list pops up you can begin to edit. Once finished, do not click the Publish to SharePoint Site button. Simply close Access 2007.

Access View
Please remember that Access 2007 is online with SharePoint and any changes you make will reflect instantly. There will be no going back.

A No-No for having Multiple SPD Workflows for Lists/Libraries

Word of warning:

If you need to have more than one workflow for a particular list/library, DO NOT copy a SPD workflow and paste back into the same library.

What happens is that even if you change all of the properties of the files (names, etc.), there is still a hidden property that SharePoint likes to hold on to and uses to identify the workflow.  Think of it as an ID.

Basically, you will have two different workflows with the same ID.  When you publish one of these to a list/library, everything works fine.  When you go to publish the second one, everything seems fine but after close evaluation, you will notice that the first workflow that you published is no longer attached to the list/library.

Moral of the story, don’t be lazy.  Create a new workflow even if all of the information is the same.

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:

 

Highlight line items in a SharePoint list

This is actually a very simple process and only requires a small amount of javascript to do the job.

Follow these steps and your will be highlighting in no time!!!

  1. Go to your list to be highlighted
  2. Edit Page
  3. Add a Content Editor Web Part
  4. Move the CEWP to the bottom of the page
  5. Click the Edit Source button
  6. Copy and paste the following lines of code into the source code section
<script src=”/PublishingImages/javascript/jquery-1.3.2.min.js” type=”text/javascript”></script><script type=”text/javascript” language=”javascript”>var x = document.getElementsByTagName(“TD”) // find all of the TDsvar i=0;for (i=0;i<x.length;i++){if (x[i].className==”ms-vb2″) //find the TDs styled for lists{

if (x[i].innerHTML==”Next Day Pickup”) //find the data to use to determine the color

{

x[i].parentNode.style.backgroundColor=’yellow’; // set the color

}

}

}

</script>

  1. Make your changes to the innerHTML section to reflect the list value for the jquery lookup
  2. Set the color of your choosing.  You can also choose to use the html value for the color: FFFFOO

Please note, I am referencing jquery inside of my SharePoint site.  This way, I do not have to add an external reference.

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!