In my previous articles on color-coded calendars, the colors have been calculated based on a Choice field. But what if you need more functionality to, say, require approval for certain calendar entries like Personal Vacation.

Creating a Lookup Table

To add approval functionality like this (which is beyond the scope of this article), you’ll want to create a new table that has the names of your date types along with other fields you will need for a workflow, like this:

 

Create: Absence Type

A list will allow you to associate other information with your date type, for example, to use in a Workflow

Once you’ve created the list of date types, you can then add a Lookup column to your calendar so your users can choose from your list and your workflow can handle any back-end processing that needs to occur for your specific business needs.

 

Lookup column

Absence Type is getting its information from the new list by the same name

"Houston, we have a problem…"
When it comes to color-coded calendars, we run into a problem with this method. Now that our date type is a Lookup column, SharePoint won’t let us include it in our color calculations.

Error

Lookup columns are not supported in formulas

We have a couple of options:

• We can let SharePoint Designer do the calculation for us, which will keep the color choices hidden from the end user, OR
• We can give the end user control over the color as well as the types of absences

For the purpose of this article, we are going to explore the second option, which will still involve using SharePoint Designer.

Assigning Colors

Let’s start by adding two fields to our Absence Type list: Color and BGColor. Both should be a single line of text with a max length (optionally) of 7. We’re going to be storing our hexadecimal values here.

Absence Type – with two new fields

 

We will need to make some changes to our calendar columns as well. Color and BGColor will no longer be calculate fields. We can’t change them to the column type we want so we have to delete the existing ones and re-create them the way we want them.

Customize – Color, BGColor, Absence Title, and OldDateType will be filled in by a Workflow

Notice we’ve added two extra fields: Absence Title and OldDateType. Go ahead and add these, each as a Single line of text. We’ll need these when we get to our workflow.

Hiding System-calculated Fields

It would be confusing to the user for us show these fields on our edit and display forms, so let’s hide them.

• From the calendar, click on Settings -> List Settings

Settings -> List Settings

• Under General Settings, click on Advanced settings

General Settings -> Advanced settings

• On Content Types, click Yes under Allow management of content types?

Allow management of content types? – Yes

• Click OK
• You will now see a Content Types section on the List Settings page
• Click on Event

 

Content Types section – Click on Event

• For each field you want to hide, click on its Name

 

When managing Content Types, columns can be Required, Optional, or Hidden

In our example, we don’t need Location, Color, BGColor, Display, Absence Type, or OldDateType to be visible

• On Column Settings, under This column is:, choose Hidden (Will not appear in forms)

 

This column is: Hidden (Will not appear in forms)

• Click OK
• Repeat for the rest of the columns

 

Hidden columns

• Go back to Settings

 

Click on the Settings breadcrumb

For simplicity, you can now turn the management of content types off and your hidden columns will remain hidden.

• Under General Settings, click on Advanced settings
• On Content Types, click No under Allow management of content types?
• Click OK

 

Absences – New Item

Color and Display columns are hidden, as well as the unused column, Location

Checking Our Progress

Once we’ve entered some dates, we can go to our calendar to see the progress we’ve made so far.

Absences Calendar – No colors, no Absence Type in display

Now we need to get the display information we need from our Absence Type list. We’ll create a workflow using SharePoint Designer to do this.

Looking Up Fields with Workflow

In SharePoint Designer, open your site by clicking File -> Open Site… from the menu. Once your site is open, you can create a workflow and attach it to your calendar.

The goal here is to have our workflow update our color columns and our Absence Title so that we can use them in our calculated Display column. Since these columns are not "calculated" by SharePoint, they are not automatically updated whenever the Event is updated.

That means our workflow will need to check to see if the Absence Type has changed. If it has, the workflow will need to recalculate our colors for us.

• Right click on your site, then click New -> SharePoint Content…

 

New -> SharePoint Content…

• Give your workflow a name and choose your calendar to attach it. Uncheck manual start and check the other two options: to start (a) when a new item is created and (b) whenever an item is changed.

 

Define your new workflow

• Click Next
• Give your step a name, then click Conditions -> Compare Attached Listfield

 

Conditions -> Compare Attached List  field

• Click on field

 

Setting the conditions on which the Actions will run

• From the dropdown, choose OldDateType

 

• Click equals and change it to not equals
• Click on value and then the fx button

 

Click the "function" (fx) button

• Leave Source as Current Item, choose Absence Type for Field and click OK

 

Compare OldDateType to the current Absence Type

When the item is first created, OldDateType will not be set, so this condition will work both for Create and Change

• Click Actions, then Set Field in Current Item

 

Actions -> Set Field in Current Item

• Click on field and choose Color

 

Set the Color column

• Click on value, then click fx

 

Click the "function" (fx) button

• Set Source to Absence Type (or whatever you called your DateType list)
• Set Field under Lookup Details to Color
• Set the Field under Find the List Item to Absence Type (or whatever you change the Title field to in your DateType list)

 

Define Workflow Lookup

• Click the fx button

 

Click the "function" (fx) button

• Leave Source on Current Item and choose Absence Type for Field

 

Lookup value

The way to read this screen from an SQL understanding is to see the top part (Lookup Details) as the SELECT statement, and the bottom part (Find the List Item) as the WHERE clause.

So we would read this:

Understanding the Workflow Lookup dialog box

…as the SQL statement:

SELECT Color FROM Absence Type
WHERE Absence Type.Absence Type = Payroll Calendar.Absence Type

In other words, look at the Absence Type on my current Event. Now, go look that Absence Type (basically our Title column) in my Absence Type table and tell me what is in the Color column.

• Click OK

 

When you do, you will see a warning that a unique value is not guaranteed.

Non-unique Warning

This is because, in theory, you could have two Absence Types with the same name in your Absence Type list. In practice, though, you won’t because it just wouldn’t make sense to your end user to have two items in the dropdown list that were exactly the same. Which would they choose?

So, it’s safe to go ahead an click Yes

• Repeat for BGColor and Absence Title

 

Setting fields

If it seems strange that we are doing the same thing for Absence Title, remember that we cannot use a Lookup column in a calculation formula. By setting the Absence Title equal to the Absence Type, we’re effectively copying the Lookup value to a Single line of text column. We can then use the latter for our formula.

• Repeat this step one more time for the OldDateType

 

Recording the current Absence Type so the workflow will know if it changes

This is where we are recording the current Absence Type. If this Event is changed in the future, the workflow condition we set in Steps 3-9 will check this field to see if the Absence Type has been changed, so that the colors and Absence Title need to be updated.

• Click Finish

 

Now your calendar should have all the pieces in place to keep your color-coded calendar functioning properly.

Note: Since the colors and date type text are being updated by a workflow, the changes to your calendar will not be instantaneous. But wait a couple of seconds and refresh your browser and you should see your changes.

 

Guest Author: Jim Bob Howard
Jim Bob Howard is a web designer / web master in the healthcare industry. He has been working with SharePoint only since March 2009 and enjoys sharing what he has learned. You can email him at [email protected].