locked
List with Content Approval to show Approval Date as a calculated column RRS feed

  • Question

  • Hi Everyone,

    I am working on a list that has content approval turned on. One of the requirements of this list is to display the date an item was approved.

    I know I can use the modified date for this however as you can know, if someone edits the approved item, Approval Status reverts to "Pending" and this will in turn update [Modified].

    I'm looking for a formula that would show the Approval Date [Modified] when the Approval Status is equal to "Approved".

    I have thought I can get around this using views and filters which only return items that have been approved and use a calculated column called APPROVAL DATE displaying [Modified]. If the Approval Status = Pending or Rejected, then it won't show up in the view.

    I'm really looking for a formula to return [Modified] if Approval Staus = Approved. If the Approval Status=Rejected or Pending then there is no need to insert [Modified].

    It's important to note that SP Designer isn't an option for our environment due to IT restrictions... OOTB solutions only :(

    Thanks in advance.

    JB

    Friday, January 13, 2012 2:25 AM

Answers

  • Yes JB, this should be possible.

    But unfortunately, Approval Status is not allowed in the formulas for calculated column (Look Up fields are not allowed)

    Check this thread for a workaround

    http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/b5d2773a-144b-4d99-a7db-fd458968bf9d

     


    Ram Prasad Meenavalli | MCTS SharePoint 2010 | MCPD SharePoint 2010
    • Marked as answer by JohnnyBloggs Sunday, January 15, 2012 10:00 PM
    Friday, January 13, 2012 5:04 AM

All replies

  • Hello JB,

    Calculated columns doesn't save their data anywhere. I mean, like other column's do, the values of Calculated columns are not stored in the content DB, they are calculated on the fly based on the formula of the column.

    In your case, that value of the calculated column should be persisted in case the item's approval status is not Approved, which is not possible using a Calculated column.

    One thing you can get from OOTB options, is the Version History page. Open the ECB menu of the item and click in Versions. This opens a page which shows the modified log of each version. There you can find the date of the last approved version.


    Ram Prasad Meenavalli | MCTS SharePoint 2010 | MCPD SharePoint 2010
    Friday, January 13, 2012 2:43 AM
  • Hi Ram,

    Thanks for your prompt response.

    I understand calculated columns don't store data and how they work. I'm looking for a formula that is checks the condition of another column and returns the corresponding set response. Maybe an IF-ELSE formula could work?

    =IF([COLUMN1]>2, "Over","Under")

    As you said, they calculate on the fly (in other words, when modified). All I'm trying to do is ask my Approval Date Column (Calculated column) to check if Approval Status is equal to APPROVED and if it is, display the item Modified date. If someone changes this item to pending or rejected, the Approval Date column will return a value or 0 or NULL (preferably empty).

    Does this make sense or is this not possible at all?



    • Edited by JohnnyBloggs Friday, January 13, 2012 4:43 AM spelling
    Friday, January 13, 2012 4:33 AM
  • Yes JB, this should be possible.

    But unfortunately, Approval Status is not allowed in the formulas for calculated column (Look Up fields are not allowed)

    Check this thread for a workaround

    http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/b5d2773a-144b-4d99-a7db-fd458968bf9d

     


    Ram Prasad Meenavalli | MCTS SharePoint 2010 | MCPD SharePoint 2010
    • Marked as answer by JohnnyBloggs Sunday, January 15, 2012 10:00 PM
    Friday, January 13, 2012 5:04 AM
  • Thanks Ram, I will give this a go.

    Thanks for your advice here.

    Sunday, January 15, 2012 10:00 PM