SharePoint List – using [Me] filter on a column with AD user information populated from InfoPath (without code)

November 10, 2009 in Uncategorized | Leave a comment

Issue

I needed to create an InfoPath 2003 form that pulled in AD user information (without code). This form was then to be published to a SharePoint 2007 library where a view was required that only displayed forms for the direct reports of line managers.

Resolution

I created the InfoPath form to pull in AD user information such as username, department and manager using this article. This works fine but the manager field is populated with DOMAIN\Username.

To strip out the “DOMAIN\” bit before the username use the substring-after command as detailed here. This gets the form looking neater, but when it is saved to the document library, the Manager field is created as ‘single line of text’.

I was going to use the filter [Me] on the manager field to show forms only where I was the manager. Thus showing me all forms relating to my direct reports. However you cannot filter using [Me] on this field type.

The workaround involves 2 steps;

1. create a new field in the library (call it say ‘line manager’) of the person or group type.

2. create a workflow in SharePoint designer to fire when a form is created to set ‘field’ [line manager] to ‘value’ [manager in the current item]. This is also described in greater depth in this article.

Now when a new InfoPath form is created, it is populated with the correct looking AD user information. This information is then used to populate an additional column in the list (this could also be hidden) which can be filtered using the [Me] switch.

Although this information is written elsewhere, it is in different places and this article pulls it together. Hope it helps…

Cooliris & SharePoint Picture Libraries

July 29, 2009 in MOSS | 2 comments

cooliris

Did some playing around with Cooliris after seeing it in action on EndUserSharePoint.com. Trying to get it working with SharePoint took a little playing around but I eventually got it working by using PicLens Publisher on their site.

After installing this and running it on a set of photos, I uploaded the output content from this into a new document library on my SharePoint site.

I then inserted a Content Editor web part onto a .aspx page and edited the source code (highlighted in red below) to point to the .rss file in the document library.

<object id=”o” classid=”clsid:D27CDB6E-AE6D-11cf-96B8-444553540000″ width=”600″ height=”370″> <param name=”movie” value=”http://apps.cooliris.com/embed/cooliris.swf” /> <param name=”allowFullScreen” value=”true” /> <param name=”allowScriptAccess” value=”always” /> <param name=”flashvars” value=”feed=http://cooliris.com/static/images/feeds/home/media.rss” /> <embed type=”application/x-shockwave-flash” src=”http://apps.cooliris.com/embed/cooliris.swf” flashvars=”feed=http://cooliris.com/static/images/feeds/home/media.rss” width=”600″ height=”370″ allowFullScreen=”true” allowScriptAccess=”always”> </embed> </object>

I finished off by creating a crossdomain.xml file, then inserted this into the root of the site via SharePoint designer. I then refreshed the page and the document library content was displayed.

Very nice, although as a lot of my users access SharePoint remotely (via Citrix Currently Presentation server 4.0) I won’t be putting it into action soon. Let’s hope it works better with XenApp 5.0!

SharePoint & Google Maps – Your Options

June 1, 2009 in MOSS | Leave a comment

I’ve was asked recently about adding mapping information onto our intranet, making it easier for staff to locate company properties. Although there is a lot of information out there connected with using Google maps on SharePoint, there is a lot of variation in it’s use. Summarized below are really the 3 (free) options you have got.

Option 1 – Static map

Follow the step by step instructions here. This will display mapping information on the web page, but the location is fixed. For a slightly better option insert a content editor web part and copy across the code from here. This can be made a little more dynamic if you apply for a Google API key – which is fairly easy to do! and follow some of the instructions.

image

You will have to manually insert the location (lat/long) in the code, but it only requires modify access to the web page not MOSS server.


Option 2 – Map displayed by selecting location from a list.

But what if you want to display a map by clicking on an address in a SharePoint list. This requires a more complex solution than option 1. There may be other examples of this, but the best solution I found was here at AMREIN ENGINEERING AG. Screenshot of my solution below. Check out their other free web parts – some of which are particularly awesome.

image 

The instructions are concise and (if your knowledge of MOSS is sufficient) relatively easy to implement. It will involve amending the web.config file and adding some dll’s – so access to the MOSS server is required.

The only downside I came up with was that you have to display the latitude and longitude columns in the list view on the web page.

If like in Option 1 you apply for the Google API code, the map is dynamic.

Option 3 – Like option 2 but with more bells and whistles

Option 2 might suffice, but you might want contact information to pop up when you click on the map pin, or for directions to the location to be an option.

In this instance, I found web parts/code at Codeplex to be a good place to start with examples such as adding a map to a contacts list. To display points and locations on a Google map and get directions to and from that point you might want to consider this. Although this solution requires a pretty good knowledge of installing wsp files and stsadm configuration commands.

Again these solutions require access to the MOSS server and my advice here is to try these out on development servers beforehand and know what each stsadm command does what.

Hope the info helps…

Submit the rows of a repeating table in InfoPath to a SharePoint list

May 27, 2009 in Infopath 2007, SQL, SharePoint Designer 2007 | Tags: | Leave a comment

Issue: Using repeating tables in InfoPath is a great approach to capturing repetitive data on a form. However when you want to display the information contained in the form on a SharePoint list or web page, a few caveats come into play.

Let’s say you want to display information contained in the repeating table on a web page. When you design the InfoPath form you only have the options to display each field as either the first, last, count or merge (see image below).

image

This is not an issue if you are content to simply display the merged contents of the repeating table. But let’s say that you want to use the web page for something more than just displaying static data. For example; you might want to edit the page in SharePoint Designer and insert some conditional formatting. Having data merged into one cell/line might not work for your display requirements. In this instance you will need a unique line for each item in the table displayed on the list.

So how do you submit the rows of a repeating table in InfoPath to a SharePoint list? There is no easy method of doing this (specifically crafted my MS). I have found a few alternatives but most require code and are not for the beginner.

The Visual Basic code and the UpdateListItems method is one of the best methods I’ve come across so far. I have followed these instructions and got this to work. However a lack of time and knowledge of VB prevented me from getting more than basic fields to work.

For my requirements I tried another approach. This might seem long winded but it worked for me. I created a SQL database with all the fields I needed in the InfoPath form. I then designed the InfoPath form from the SQL database and published it to a SharePoint site. This form had 2 data connections – 1 to the SQL database and 1 to the SharePoint library.

Users go to the SharePoint library to open, complete and submit the form. The form (for record purposes) is saved in the library. A copy of the data is also submitted to the SQL database. Each item/line in the repeating table is stored as a unique line in the database.

On a new web page on the SharePoint site I inserted a dataview web part and connected it to the SQL db. I then ordered the fields as appropriate and applied some conditional formatting so that the page could be used as an overview or management page.

The immediate downside of this approach is you cannot edit the information in the SQL db directly by opening up the form again and re-submitting the data. I have not found out how to do this yet!

To be able to edit the data, open the web page with the dataview web part on it in SharePoint Designer and go into the web part properties section. Make sure to tick both the ‘show edit items links’ and ‘synchronize commands’ boxes (see below).

image

This will provide an ‘edit’ link on the dataview web part (see below). You can then edit the information in the list and have the changes synched in the SQL db.

 image

Hope this info helps…

Citrix PS 4.0 Summary Database Configuration

April 1, 2009 in Citrix | Leave a comment

To setup the Summary Database I followed the steps below;

In SQL Server Management Studio I created a new database (e.g citrix_summary) and configured/created a new SQL account (e.g. CitrixSummary) and mapped the account to the citrix_summary database as the db_owner. Note: You might want to set a default database as well.

I also found that I had to remove the ‘user must change password at next login’ check.

image

On the PS4.0 server, select data sources ODBC in admin tools. Create a new System Data Source using SQL server driver called rmsummarydatabase (make sure to have it in lower case – RMSummaryDatabase did not work for me). Check connections.

In the Pres Server Console, configure the summary database in resource manager. Enter the server you configured the ODBC setting on, and enter in the SQL user and password created on the SQL server (in my case CitrixSummary). Run a test to see if the configuration works.

image

Deploying the Office 2007 Compatibility Pack with GPO

March 24, 2009 in MS Office 2007 | Leave a comment

There are many posts that cover this, including this one and this which go into detail with specific settings. After extracting out the msi file from the MS .exe download, I made the mistake of just copying the msi file to the share.

Although it looked like it was installing on boot up, the application error logs provided that the package could not be found. Copying over all the files that I extracted from the initial exe from MS resolved the issue.

OWA Web Part in SharePoint

March 9, 2009 in MOSS | Leave a comment

I was looking to display a generic users’ inbox in a web page on my SharePoint farm. This is typically a service mailbox that people need to dip into from time to time and also send emails from. Research provided a few pointers but did not provide the entire answer I was looking for.

The standard OOTB OWA web part is fine for displaying the root inbox, but is limited when trying to display sub folders and the ‘New’ button. For this you will need to display the full OWA page.

This is simple to achieve by amending the web part and adding ?cmd=contents after the mailbox of the inbox you want to be displayed (shown below).

image

Assuming you have access to the mailbox the whole OWA web page will be displayed on the page. More information on additional commends when displaying OWA pages can be found here.

SharePoint workflow autostart on email enabled list

January 3, 2009 in SharePoint Designer 2007 | Leave a comment

Situation
Workflow created in SPD and set up to activate upon the receipt of a new item being created in an emailed enabled list. Worked fine before SP1 was installed

Issue
When the email was received in the list, the workflow did not automatically start. The workflow worked fine if started manually. I had read that this action was disabled by design in SP1, but had also read that there would be a fix in the infrastructure update. After applying this the issue remained.

Resolution
Bit of digging unearthed this link which breaks down why this all happended. Suffice to say that running the command “stsadm.exe –o setproperty –propertyname declarativeworkflowautostartonemailenabled –propertyvalue yes” worked on my farm. Check this out first on your test hardware.

Shrinking a SQL 2005 transaction log file

October 8, 2008 in SQL | 1 comment

I recently inhereted a MOSS 2007 system where the main MOSS content database had a SQL 2005 transaction log file of approx 150Gb, whereas the database was only 7Gb. Although full backups where scheduled (including shrinking of the db’s) as part of the daily maintance plan, this did not include a transaction log backup.

Inital attempts to shrink the log file failed. I was contemplating using the DETACH/ATTACH method  and then deleting the log file as a last resort due to space issues on the server. However after reading up on the procedure, I decided to persue a way to shrink the log file the ‘correct‘ way.

After reading several posts and articles I found (like many others) that shrinking the transaction log after backing it up did not shrink the overall size of the file (albeit it had 99% available free space!). Quite by acident, I shrank it again with a nominal ’shrink file to’ and hey presto! At last the file size went back to roughly the same size as the nominal value, giving me back my server disk space. I repeated this on the other MOSS db’s and had to do the same procedure – *2 shrinking of the log files.

The next step was to figure out why the log file had grown so large. This answered itself when I went to set the autogrowth file size. For some reason the Max file size ‘Restricted file growth’ setting was set to around 2,700,00 MB.

MOSS Created SQL Databases

Further investigation on the other MOSS SQL databases revealed that when MOSS automatically created it’s SQL databases, it set the max growth file size on the transaction log files to some arbitarily large figure (way bigger than the size of the hard drive). As the previous administrator had not checked these settings the log’s grew out of all proportion.

Infopath 2003 data connection to SharePoint 2007 list

June 25, 2008 in Infopath 2003, MOSS | 1 comment

Recent testing has shown a couple of querky things when attempting to setup an Infopath form to recieve data from a SharePoint 2007 list. It took a little working out but the following apply;

  1. SharePoint list items formatted as ‘Multiple lines of text’ with the column setting set to either Rich text or Enhanced Rich text, are not available in Infopath when pulled through a data connection. I only resolved this by setting the item column to Plain text.
  2. Data pulled through the data connection is related only to the SharePoint list default view. I was pulling information across from a calendar view and only a month’s worth of data was available. You’ll have to set the default view to an ‘All items’ view to pull across all the data.

a

Pages