Sandoval's SharePoint Spot

Solution:

http://office.microsoft.com/client/helppreview.aspx?AssetID=HA101191111033&ns=SPD&lcid=1033

You configure profile imports in the Central Admin by going to Site Settings > Manage profile database > Configure profile import.

Added this LDAP query to MOSS’ profile import:

“(&(objectCategory=Person)(objectClass=Contact)(mail=*)(showInAddressBook=*))”

Per Dave L.

Create a calculated column.

=IF(ISNUMBER(FIND(“/Members/”,[Site Link])),”Yes”,”No”)

The following code was used to create an Easy To Read email notice generated by SharePoint Designer Workflow.

<table style=”border-collapse: collapse; width: 100%” dir=”ltr” cellspacing=”0″><tr style=”background-color: #f8f8f9; border: thin #e8eaec solid”><td style=”font-family: Verdana; font-size: 16pt; background-color: #f8f8f9; border: thin #e8eaec solid; padding: 20px 20px 20px 20px”>The Review workflow has started on <strong>[**********LOOKUP Name**********]</strong>.</td></tr>

<tr><td>&nbsp;</td></tr><tr style=”background-color: #f8f8f9; border: thin #e8eaec solid”><td style=”font-family: Verdana; font-size: 14pt; background-color: #f8f8f9; border: thin #e8eaec solid; padding: 10px 40px 10px 40px”>Dates</td></tr>

<tr><td style=”font-family: Calibri; font-size: 12pt; padding: 5px 60px 5px 60px”><p style=”margin-bottom: 5px; margin-top: 5px”><strong style=”color: #E84037; font-variant: small-caps”>[**********LOOKUP Document Owner**********]</strong> has requested <strong style=”color: #E84037; font-variant: small-caps”>[**********LOOKUP Priority**********]</strong>.</p>

<p style=”margin-bottom: 5px; margin-top: 5px”>The initial reviewers must complete their review by <strong style=”color: #E84037; font-variant: small-caps”>[**********LOOKUP Initial Review Due Date**********]</strong>.</p></td></tr>

<tr style=”background-color: #f8f8f9; border: thin #e8eaec solid”><td style=”font-family: Verdana; font-size: 14pt; background-color: #f8f8f9; border: thin #e8eaec solid; padding: 10px 40px 10px 40px”>Reviewers</td></tr><tr><td style=”font-family: Calibri; font-size: 12pt; padding: 5px 60px 5px 60px”><p style=”margin-bottom: 5px; margin-top: 5px”>The initial reviewers are:</p>

<ul style=”margin-top: 5px; margin-bottom: 5px”><li style=”margin-top: 1px; margin-bottom: 1px”><strong style=”color: #E84037; font-variant: small-caps”>[**********LOOKUP Reviewer 1**********]</strong></li>

<li style=”margin-top: 1px; margin-bottom: 1px”><strong style=”color: #E84037; font-variant: small-caps”>[**********LOOKUP Reviewer 2**********]</strong></li>

<li style=”margin-top: 1px; margin-bottom: 1px”><strong style=”color: #E84037; font-variant: small-caps”>[**********LOOKUP Reviewer 3**********]</strong></li></ul>

<p style=”margin-bottom: 5px; margin-top: 5px”>The backup reviewer is:</p><ul style=”margin-top: 5px; margin-bottom: 5px”>

<li style=”margin-top: 1px; margin-bottom: 1px”><strong style=”color: #E84037; font-variant: small-caps”>[**********LOOKUP Backup Reviewer**********]</strong></li></ul></td></tr>

<tr style=”background-color: #f8f8f9; border: thin #e8eaec solid”><td style=”font-family: Verdana; font-size: 14pt; background-color: #f8f8f9; border: thin #e8eaec solid; padding: 10px 40px 10px 40px”>Links</td></tr>

<tr><td style=”font-family: Calibri; font-size: 12pt; padding: 5px 60px 5px 60px”><ul style=”margin-top: 5px; margin-bottom: 5px”><li style=”margin-top: 1px; margin-bottom: 1px”>

<a style=”color: #003399; text-decoration: underline” href=”[**********LOOKUP%20Dashboard%20URL**********]“>Workflow Dashboard</a></li>

<li style=”margin-top: 1px; margin-bottom: 1px”><a style=”color: #003399; text-decoration: underline” href=”[**********LOOKUP%20Document%20DispForm**********]“>View the document properties</a></li>

<li style=”margin-top: 1px; margin-bottom: 1px”><a style=”color: #003399; text-decoration: underline” href=”[**********LOOKUP%20Encoded%20Absolute%20URL**********]“>Open the document</a></li></ul></td></tr></table>

This is not exactly how it looks in email. But close.

The microsoft support article for WSS 2 shows us how to download attachments from a list item. Basically, the attachments for a list item are stored as SPFile objects under a hidden folder in the list where those attachments are (a folder called “Attachments”) – where each list item that has an attachment has its own folder – with the ID of the item being the folder’s name.

Here is a code sample for a function to copy attachments from one item to another.

===================================

private void CopyAttachments(SPListItem sourceItem, SPListItem targetItem)

{

  try

  {

     //get the folder with the attachments for the source item

     SPFolder sourceItemAttachmentsFolder =

         sourceItem.Web.Folders["Lists"].SubFolders[sourceItem.ParentList.Title].SubFolders["Attachments"].SubFolders[sourceItem.ID.ToString()];

      //Loop over the attachments, and add them to the target item

     foreach (SPFile file in sourceItemAttachmentsFolder.Files)

     {

        byte[] binFile = file.OpenBinary();

        targetItem.Attachments.AddNow(file.Name, binFile);

     }

  }

  catch { }

  finally

  {

     sourceItem.Web.Dispose();

  }

}

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

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

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.

=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))

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

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”.

Result = **********

=REPT(”*”,10)

=IF(LEN(Number)<10,REPT(0,(10-LEN(Number)))&Number,TEXT(Number,”0?))

This formula literally steps along as:

If the length of [Number] is less than 10, create an amount of zero’s equaling the difference between 10 and the length of [Number] then display these zero’s followed by the [Number]. If the length of [Number] is not less than 10, display the number using the “TEXT()” formatting option to display it in a number format.

Examples:

Number = 12345
Result = 0000012345

This formula was used with a CorasWorks rollup to display a custom link that says “Click Here”. It does not appear to work as a link in list’s native view.

==================================

=IF(NameCTEcorp<>”",CONCATENATE(“<a target=new href=https://xxxxx.com/sites/sitename/”,NameCTEcorp,”>click here<br>to view</a>”),”")

I often create this additional calculated date column so that I can easily sort content in a chronological order.

The following formula converts a date into a String “YYYY-MM

General Rules Are:

Create a calculated column and input this formula.  I prefer to not have slashes and find periods are easier on the eyes.  You can change the Month and Year to be MM or MMM and YY YYYY respectively.  You can even omit the day.

=TEXT(Date,”mmm.dd.yyyy”)

How do you convert a date into a day of the week?

Create a calculated column that contains…

=TEXT([End Time],”dddd”)

Found this on some blog.  My notes aren’t complete.  But the results are listed.

Q: “I would like to view all entries within the last 30 days or greater.

A: Temporarily create a text column [Today]. You will need to delete this column to see if the formula works. Every time you edit a formula that references [Today] you will have to… “  <don’t know where rest of text is>

“You can now filter a list to show any items where the [calc column] equals one.”

This was used in an Calendar.

“=IF(([Today]-30)>([Start Time]-30),1,0)”

Verify that a cell column contains a sting of text.  This formula basis calculation on cell content vs. the entire cell value.

“=ISNUMBER(FIND(“”BD”",[Column1]))”

Compare cell values to see if they are EXACT (case sensitive) or EQUAL (not case sensitive).

“Case Sensitive:
=EXACT([Text1],[Text2])
Converts to Proper Case then Compares:
=EXACT(PROPER(Text1),PROPER(Text2))”

Type in the following formula in a Calculated Column. Replace [Column] with the column you want to reference.

=CLEAN([Column])