SharePoint 2007 Tips

To display the username in a Title column

1. Choose Default value as Calculated Value;
2. Type in: =REPLACE(Me,1,FIND("\",Me),"");

For Team Discussion to work

1. Subject field is used only in the first article within the thread;
2. A web-part would mainly use the Subject and Replies fields for listing;
3. Threaded view – which displays only Threading – is the only useful view;

To sort a list by the abbreviated month

1. Create a choice type for month input ([Report Mth]). The values should be the abbreviated month (e.g. Jan, Feb, Mar, etc);
2. Create a calculated column to assign a numeric month to the list;
3. The formulae should be: =MONTH("01-"&[Report Mth]&"-"&1990);

To find the difference between 2 dates

1. Use the DATEDIF function on 2 date fields
2. DATEDIF(d1 : Date, d2: Date, “D”) : Number
3. Example: DATEDIF(dateColumn, [Today], “D”)

To convert a text value (from InfoPath) to numeric

1. Create a calculated column of numeric type
2. Apply formula =VALUE([Column Name])

To apply a filter to test for empty values

1. Use a field of the following types: Date, Text Column, Numeric
2. Leave the value empty (to test against empty/ null value)

To apply a filter with complex AND/ OR conditions

1. AND takes precedence over OR
2. For such condition: X AND (Y OR Z)
3. Use X AND Y OR Z AND Z in filter

To apply a filter on view based on user

1. Ensure a column of Person type is available
2. Apply [Me] as a filtering criteria for the View

To hide the Title column in a list

1. Allow Management of Content Type for the list
2. List Item Content Type (which contains the Title column) can now be edited
3. Change the Title column to Hidden

To create a customed calculated ID (e.g. calculated column, Create date in yyMMdd, 4 character ID prepended with 0) in a list

1. Create a Calculated Column
2. Use the following for formula:

=[My Column]&"-"&TEXT([Created],"yyMMdd")&"-"&LEFT("0000",4-LEN([ID]))&[ID]

To synchronise a list with Excel 2007 (Excel 2003 is able to do so internally)

1. Install the Excel add-in (XL2007SynchronizeWSSandExcel.exe) and follow the installation instructions (http://msdn.microsoft.com/en-us/library/bb462636(office.11).aspx)
2. Install an ActiveX (http://www.softfluent.com/wsslists.htm) to reroute all .IQY to Excel
3. From Excel, choose the Table option to Synchronize with SharePoint

To attempt to filter a lookup field

1. For example, if we need to lookup a Department list which have got items that are both active and inactive (using a Status column to define)
2. Create a filtered Lookup Column in the Department list.
1. Create a new Calculated field column
2. The formula might be something like: =IF(Status = "Active", Title, "")
3. Use the new filtered Lookup Column as the lookup field in another list
4. This works as only non-blank values from the column would be returned for the lookup

https://www.linkedin.com/pulse/20140717203332-36740435-sharepoint-filtered-lookup-column