Creative Use of Calculated Site Columns
This might not have direct application to your situation, but hopefully if will give you some ideas to try…
The Requirement
I recently had to provide a filtered drop down for a content type in a list containing two content types.
The content types were – 'Actions' (parent: Task) and 'Deliverables' (parent: Item).
The filtered drop down needed to be on 'Action', to link it to a ‘Deliverable’ by a lookup column (named 'Deliverable').
This all worked, apart from the fact that you can not only associate a 'Deliverable' to an 'Action', but also another 'Action' to an 'Action', or even the same 'Action'… you can see the rather nasty potential circular reference issues there!
The above screen shot shows two ‘Actions’ in the first page of the ‘Deliverable’ drop down – Business Process Tool Integration and Calendar view for leave, that we don’t want there.
Now, of course, we could add two separate lists for each content type, and a content query web part to combine them.
But in this case the user wanted the full list abilities, particularly views, across both content types, and no more items on the default menus, or messing about with web parts, audiences, security etc.
The Solution
I had to scratch my head for a few minutes, but this is the quick and dirty fix I came up with.
Add a new 'calculated' site column called 'DeliverableTitle', with the formula defined as: =IF([Content Type]="Deliverable",[Title],"")
As this references the same inherited 'Title' field, we'll only see it in a list when the content type is 'Deliverable'. You can make it hidden on any content types if you want, or just never show it in any views.
Now, as I mentioned, these two content types are already in a list called 'Deliverables / Actions'.
If you don't have that already:
1. Add your two content types, 'Action' and 'Deliverable', without any lookups or calculated fields:
2. Put the 'DeliverableTitle' calculated site column on the 'Deliverable' content type.
3. Put them both on a list that allows management of content types.
We can now create (modify in my case) the lookup site column called 'Deliverable', to get information from the 'Deliverables / Actions' column 'DeliverableTitle'.
If it wasn't already on your equivalent of 'Action', put the 'Deliverable' lookup site column on the 'Action' content type now.
And hey presto, now when adding a new ‘Action', we only ever see ‘Deliverables’ in the lookup.
As can be seen above, the errant Business Process Tool Integration and Calendar view for leave 'Actions' are no longer visible in the ‘Deliverables’ combo box.
Potential Gotchas
Of course the usual lookup column issues still exist, such as no control over ordering.
Aside: Wouldn't it be nice to be able to point lookups at views, or some other list 'filter' – are you listening Microsoft?!
But there is one issue you may encounter – because of when calculated columns get evaluated, you could see this ‘RENDER FAILED’ issue with collapsed view groupings:
To fix this, you can either set the Views ‘Group By – By default – show groupings:’ to be ‘Expanded’…
…or apply this Microsoft Hotfix: http://support.microsoft.com/kb/952698/en-us
You try to render the view of a custom list. When you do this, you may receive the following error message:
Render failed
This issue occurs when the following conditions are true:
The list is grouped by one of the columns.
You try to display the sum of all the values of a single column.
The list contains more than the limit for the number of columns that have a certain data type.
For example, in Windows SharePoint Services 3.0, the limit for the number of columns that have the "float" data type is 12.
Therefore, this problem occurs when the following conditions are true:
The list is grouped by one of the columns.
You try to display the sum of all the values of a single column.
The custom list contains 13 columns that have the "float" data type.
Thanks for reading, and I hope this helps those with other curly lookup field issues come up with creative solutions.
The end of another MOSSuMS post
mikes