Solved

Help with a calculated sharepoint field?

Posted on 2011-09-19
3
564 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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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