I have a question in regards to a board that I am building.
I have a formula column that says “if ‘estimated cost’ is less than ‘actual cost’, then it ‘needs further approval’ otherwise its ‘approved’”
IF({Actual Cost}>{Estimated Cost},“Needs further approval”,“Approved”)
What I would like to be able to do is be able to update another ‘status’ column based on this formula; ‘Approved’, ‘Declined’ which will then allow me to add an automation to it. Most of what I have read is reversed; if a status changes then update the formula column. I also found one automation that says ‘if this column is <=> a number then notify someone’ but nothing for a column that is <=> a column! The other one I found was ‘When a number is greater than a value, change status to something’. That would work if ‘value’ was ‘number’.
Hopefully this makes sense and there is something that can help me out! If not, its back to the drawing board to come up with a work-around.
I created a similar scenario using Integromat, basically you have get the column values and use a router to compare the different values and update the status.
I collaborate with @basdebruin providing these kind of ad-hoc solutions. Send me a PM if you would need some support to create this automation.
I have two suggestions. First, update your formula to say: IF({Actual Cost}=0,"",IF({Actual Cost}>{Estimated Cost},“Needs further approval”,“Approved”))
This will have a blank approval column if there is no actual cost.
Second, you could always use Google sheets and the API to update the status you want. Send me a PM if you would like to look at what that can look like as a solution.