Solved

Help with a calculated sharepoint field?

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SharePoint 2013 6 68
Add Context Menu Item 4 89
Open Attachment from within List Field in SharePoint Online 1 82
DataTable column sorting incorrectly 2 22
For SharePoint sites, particularly public-facing ones, there are times when adding JavaScript, Meta Tags, CSS Styles or other content to the page <head> section is more practical than modifying master pages.  For instance, you could add the jQuery l…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
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 …

726 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