?
Solved

Help with a calculated sharepoint field?

Posted on 2011-09-19
3
Medium Priority
?
579 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
3 Comments
 
LVL 14

Accepted Solution

by:
abhitrig earned 1400 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 600 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Pimping Sharepoint 2007 without Server-Side Code Part 1 One of my biggest frustrations with Sharepoint 2007 in the corporate world is that while good-intentioned managers lock down the more interesting capabilities of Sharepoint programming in…
We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

839 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