Solved

Help with a calculated sharepoint field?

Posted on 2011-09-19
3
569 Views
Last Modified: 2012-05-12
Hi,
We have MOSS 2007 installed. I'd like to create a calculated field that does some magic logic for me :) As background, I have the following fields:

Status (APPROVED/PENDING/REJECTED)
Created (Date)
ApprovedDate (Date)

I'd like to add 1 calculated field...   where if status = PENDING, it does "today - created" to show # of days pending.
If status = APPROVED or REJECTED, it should do "ApprovedDate - created" to show # of days that elapsed before a decision was made.

Is that possible to do in 1 field?
0
Comment
Question by:runelynx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 14

Accepted Solution

by:
abhitrig earned 350 total points
ID: 36563350
for the formula, yes. The problem however is that the "today" value won't get refreshed the next day.
http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/

So you will get a correct value at the time of item creation it won't refresh the next day. Option, use a workflow to manage/update the values.

BTW, your formula will be something like this =if([Status]="PENDING",Today-Created,ApprovedDate - Created). You will need to create a dummy column called "today" to get the formula working.
0
 
LVL 16

Expert Comment

by:quihong
ID: 36563887
abhitrig, pointed out the flaw regarding the calculated field not updating.

One possible solution would be to write a powershell script to "touch" the each item in the last daily.
0
 
LVL 4

Assisted Solution

by:leopolde
leopolde earned 150 total points
ID: 36564126
In order to update the items daily (and ensure your calculated field is updated), you might want to try to create two small workflows in SharePoint Designer that basically do the following for every item:

1st Workflow (Run on new and modiefied items)
* Sleep for 24 hours
* If the item status is "PENDING", then update a dummy field in the item called something like "Loop" so it contains a "Yes"

2nd Workflow (Run on modified items)
* If the field "Loop" is "Yes" then change it to "No".

The reason you need two workflows for this is that a workflow cannot start itself.  Remember to create the "Loop" field, with a default value of "No"
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question