Calculate a Field Based on Approval Status


  • I have a list that coordinates purchasing approval and tracks our budget.  Someone submited a new item to the list with Estimated Cost, Vendor, Description, etc.  When a new item is submitted, it triggers a workflow that sends it to me as the purchasing approver.  I approve the task and it noties everyone and puts the Approval colum as Approved.  This is all out of the box functioning.  I don't have designer on our network yet (working on it).

    What I want to do now is create a column that checks the approval status and if it's approved puts the estimated cost in the column, if it's not approved, it puts 0.  This way I can sum that column to track the expenditures of only approved purchases and ignore the ones I disapproved.  This will allow me to have an accurate picture of how much money we have without having to totally delete a purchase request out of the list.

    I've tried using the calulcated field for a new column but it doesn't like having to look at approval status.  I've tried it as both text and integer.  IE "=IF([Approval Status]='Approved',[Estimated Cost],0)" as well as "=IF([Approval Status]=16,[Estimated Cost],0)" as 16 is the number in datasheet view for approval.  Neither of them works as it doesn't like the approval status column use in general.

    Friday, November 12, 2010 2:32 PM


  • Hi,


    The approval status is a lookup field and the lookup field does not work in the calculated field.


    I would suggest using a workflow.  In SharePoint Designer you can create a workflow that triggers when a new item is created or changed, add a condition to check the Approval Status, and the use “set field in current item” to update the Estimated cost.


    Hope it helps.

    Xue-Mei Chang
    Monday, November 15, 2010 6:21 AM