Using TODAY As a calculated column
Important things too know about using TODAY in a calculated column!
http://www.linkedin.com/news?viewArticle=&articleID=185992482&gid=93094&type=member&item=28659782&articleURL=http%3A%2F%2Fblog%2Epentalogic%2Enet%2F2010%2F09%2Ftoday-sharepoint-calculated-default-values%2F&urlhash=q1qF&goback=%2Egde_93094_member_28659782
How to Repair/Replace a broken SharePoint List Form
A life saver of an article from Ben. I used these steps to recreate the default dispform.aspx page!
Creating Single Server Sign On from Central Admin
SSO Single Server Sign On
Add Application Services in Central Admin
- Operations Tab
- Manage Settings for SSO
Ensure SSO Service is Running (If you get a “Failed to Connect…”)
- Start/ Admin Tools/ Services
- Open Microsoft Single Sign On Services
- Log On Tab
- Best Practice to use to Server Admin Account (from A.D.) not the Local System Account
- Provide Name Password
- General Tab
- Start Service/ OK
- Manage Settings for SSO
- Manage Server Settings
- Provide an SP SSO Admin Account
- Provide Enterprise Application Definition Admin Account (usually the same as SSO Admin Account)
- Creates a SSO DB (Server Name, DB name)
- Click OK
- Manage Settings for Enterprise Application Definitions (link)
- Create a New Item for Each Back end system that will be used to with each application requiring connection.
- Display Name for SSO Admin
- Application Name that Site Admins will use to connect (No Spaces)
- Contact e-mail for back end db
- Account Type (best practice GROUP)
- Authentication Type (Leave blank so that no authentication is required). This will allow for the passing of credentials
- Create a SP Name and Password for this Application Definition. Ok to leave defaults (“Username” [mask NO], “Password” [mask YES]). Not a best practice!
- Manage Settings for SSO
- Manage Account Info for Enterprise Application Definitions
- Provide account information that is going to be passed.
- Pick Enterprise App Definition created in Step 3.
- Provide Group name (See step 6 below)
- Click SET to provide User Name and Password for Enterprise Application. (Best Practice to not provide Enterpise App Sys Admin. Use a Enterpise App Read Only Account),
- Click OK/ Done
Active Directory (use to find AD Groups)
-
- Start/ Admin Tools/ AD Users and Computers
- Get name and verify membership
- Manage Encryption Key
- Click Create Encryption Key
- Click OK/ OK
-
- NOW TO USE SSO!
- You will be using the Application Name (not Display Name) to make a connection to the db)
In SP Designer
-
- Go to Data View Menu
- Click Insert Data View
- From Task Pane Connect to a Data Base
- Click Configure Database Connection
- Server Info is where (SQL Server is running)
- Authentication (USE SSO)
- Click Settings
- Provide Application Name (Step 3c, no spaces) and
Application Username and Password (Step 3g) - Click Next/ Select Database/ Select Table
- Click Finish/ Ok to make the connection
Nested If Then Statement with 15 Conditions
Another GREAT tip from Christophe at Path to SharePoint
=IF([Status]=””,”Black”,IF([Status]=”Choice1?,”Red”,IF([Status]=”Choice2?,”Gold”,IF([Status]=”Choice3?,”Green”,IF([Status]=”Choice4?,”DarkBlue”,IF([Status]=”Choice5?,”DarkCyan”,IF([Status]=”Choice6?,”DarkRed”,IF([Status]=”Choice7?,”Gray”,””))))))))&IF([Status]=”Choice8?,”MediumSlateBlue”,IF([Status]=”Choice9?,”SpringGreen”,IF([Status]=”Choice10?,”MidnightBlue”,IF([Status]=”Choice11?,”Sienna”,IF([Status]=”Choice12?,”SlateGray”,IF([Status]=”Choice13?,”OliveDrab”,IF([Status]=”Choice14?,”Gray”,””)))))))
Excel Services Does Not Support…
Published Excel Spreadsheets do NOT support
- VBA macros
- Toolbox controls
- XLM Sheets
- Embedded Clip art
- Cell and Sheet BG Pictures
- IRM Enabled Workbooks
- Forms Controls
- MS 5.0 Dialogues
- Active X Controls
- DDE Links
- Query Tables
- Split Panes
- Headers Footers
- Page Layout View
- Cell Patterns
- Zoom
How to Modify the Layout of a Default.aspx Page in MS SharePoint Designer
http://www.5min.com/Video/How-to-Modify-the-Layout-of-a-Defaultaspx-Page-in-MS-SharePoint-Designer-80729198?
How to Customize List View Pages in MS SharePoint Designer
Good demo for SPD beginners.
http://www.5min.com/Video/How-to-Customize-List-View-Pages-in-MS-SharePoint-Designer-80729533
Custom Group Order in a SharePoint List: Dynamic Grouping
Haven’t tried this, but posting it here for future reference. The one shortcome in converting a list into XSLT in SPD is the fact that the grouping will not count the number of items for its group. This SharePoint Designer trick looks to be the solution.
Thanks Tudor!
Visio Documents in a SP Library
See discussion at microsoft.public.sharepoint.portalserver
Creating a Multiple selection List Box in browser enabled InfoPath form.
Solution provided by Mehul Bhuva at SharePoint Fix.
Getting the word count of a document?
In Word 2003, go to File > Properties > Statistics
In Word 2007, go to Review > Proofing > Word Count
Create a Mapped Drive to a SP Library
Got this from a blog. To be tested.
- On the Start menu, click My Computer.
- On the Tools menu, click Map Network Drive.
- In Drive, select a drive letter.
- In Folder, type the name of the Web server and document library in the form \\Server_Name\Site_Name\Document_Library\
Just basically copy the url from the browser and change the slashes and remove any %20 so that they are spaces again.
Create a custom list form
Solution:
http://office.microsoft.com/client/helppreview.aspx?AssetID=HA101191111033&ns=SPD&lcid=1033
Check if a cell contains a string of characters.
Create a calculated column.
=IF(ISNUMBER(FIND(“/Members/”,[Site Link])),”Yes”,”No”)
Calculating Work Days, Weekdays and Holidays
SOURCE> Friday, November 21st, 2008 | SharePoint Ideas | Ryan
AcmeCorp aim to reply to customer complaints within 3 working days. They plan to use SharePoint Reminder to alert managers when these targets are not being met.
We have a list with the following information
|
ID |
Title |
Date Received |
Reply Due |
Reply Sent |
|
1 |
Order hasn’t arrived |
Friday 7/Nov/08 |
Wed 12/Nov/08 |
|
|
2 |
Order missing items |
Monday 10/Nov/08 |
Thursday 11/Nov/08 |
|
Once we have this table we could make a View with a filter to only show records that don’t yet have a Reply Sent date (i.e. they have not been dealt with) and then configure a Reminder web part to watch this view and send out emails when the Reply Due date is overdue.
If we didn’t care about working days we could set Reply Due to be a calculated column with a formula of
=[Date Received]+3
The question is – how do we work out the Reply Due date being 3 working days from the received date? (In AcmeCorps case a working week is Monday through Friday)
One of the calculated column functions you can use in SharePoint is Weekday which returns 1 for Sunday, 2 for Monday and through to 7 for Saturday.
So from here we can work out if the day is
|
Received |
Weekday |
Days to add |
Reply Due |
|
Sunday |
1 |
+4 |
Thursday |
|
Monday |
2 |
+3 |
Thursday |
|
Tuesday |
3 |
+3 |
Friday |
|
Wednesday |
4 |
+5 |
Monday |
|
Thursday |
5 |
+5 |
Tuesday |
|
Friday |
6 |
+5 |
Wednesday |
|
Saturday |
7 |
+5 |
Thursday |
Or writing this another way
Weekday > 3 then +5
Weekday = 2 or 3 then +3
Weekday = 1 then +4
or yet another way
if weekday > 3
add 5
else if weekday > 1
add 3
else
add 4
The final ninja move we need is the IF statement
=IF(some expression, do this if true, do this if false)
and these can be nested and combined with AND/OR or even nested with other IF’s – see the documentation.
We can put this into a formula for SharePoint calculated columns like this
=IF(Weekday([Date Received])>3, [Date Received]+5,
IF(Weekday([Date Received])>1,[Date Received]+3,
[Date Received]+4))
Be very careful when entering this sort of formula, if you get the field names wrong or put a space or a bracket in the wrong place then you will see an error.
You can use the above method if your due date is 4,6,7,8,9 etc days on, just adjust the number of days you add as appropriate.
If your due dates are always a multiple of 5 (e.g. 5,10,15) working days then your job is easier as you will always have 2 weekend days involved so you can just add 7,14,21 etc using =[Field]+7
(FREE BONUS! This also works in Excel, remember to change the Date Received to the column/row reference and don’t use square brackets [] and also format the column as a Date)
But what about holidays?
Ahhh, that’s the problem with this method and there is no easy solution without some complex custom workflow code.
One thing that may work for your circumstance is to add a Reply Due column, set it to Date Time type and set the default value to a Calculated Value with the following formula
=IF(WEEKDAY(Today)>3,Today+5,IF(WEEKDAY(Today)>1,Today+3,Today+4))
This will set the Due Date as 3 working days from when the record was entered but it could be overridden manually if there are holiday days between Today and the due date.
Want to omit the first 4 characters in a string.
=RIGHT(LEFT([field], 7), 3)
=RIGHT(LEFT([%List:field%], 7), 3)
================================
Truncate From Left
=RIGHT(LEFT([field], 7), 3)
=RIGHT(LEFT([%List:field%], 7), 3)
=(Left(txt,3))
How do I create a calculated column using [Today]?
http://pathtosharepoint.wordpress.com/?s=%5Btoday%5D
http://abstractspaces.wordpress.com/2008/05/19/use-today-and-me-in-calculated-column/
If you try to create column with field type : calculated and want to use current date as part of formula , the first thing that comes up in mind is to use [Today] as SharePoint gives it as filter option while creating views. But unfortunately if you try to add ‘[Today]‘ in formula, it gives error : “Calculated columns cannot contain volatile functions like Today and Me”
Same error goes when trying to use “[Me]” too.
Well, a nice trick can allow you to do this.
Open up the List Settings page where you want this column.
Create column named “Today”. The type doesn’t matter here, so let type as “Single Line of Text” and just click “Ok”
Now create the calculated column where you need to use current date within formula and add “Today” from available columns
Once this is done, we no longer need that our own generated column “Today” so you can delete it (Although when you need to edit formula you will need this again, so either recreate this dummy Today field or make it hidden using code behind. Otherwise SharePoint will put you again on that error screen).
This will trick the SharePoint to use [Today] (current date) as part of formula.
You can apply same trick for the current user i.e. [Me] too. Create a dummy field named “Me” use it in your formula, save the calculated column, and delete that dummy “Me” field.
In Part 2 we’ll see how to use Object Model to do this, as well as using the Computed Field to create more advance formulas
How do I compare a date value to TODAY?
You can trick sharepoint into creating a column for today’s date which you can then compare today’s date to another date (in this case revised date).
I created a column for “Today”, with single line of text and then created another calculated column “CalcToday” and set it equal to the “Today” column.
Once I was done I deleted the “Today” column and voila! You have a column which is set to the current date!
Then I just created the “Days Overdue” column which compares date information from certain items to be addressed/completed (a.k.a”Revised Date”) to “CalcToday”.
![Use [Today] and [Me] in Calculated Column](http://rbsandoval.com/wp-content/uploads/CalcColumn-sm.jpg)
