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.
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.