Allow Status Fields to be based on a Formula.
D
Doug Lott
Jon Darbyshire - we've done something similar where we set the status off of a formula based on % complete and due date. I think we solved this use case by using automations and formulas. In our use case, we did not want the user to set the status. Rather the status defined but other inputs.
Jon Darbyshire
Thank you for posting, Rosson Long! I have a few more questions for you:
- Can you provide examples of the types of formulas you would like to use for status fields?
- What specific criteria would you like to use to determine the status of an item?
- How would you like the system to handle situations where the formula criteria are not met?
Rosson Long
Jon Darbyshire Sure! I'm just going to throw together an example of things I've done in the past in other systems.
Simple:
If([Start Date]>Now(),"Planned",
If([End Date]<Now(),"Inactive",
If([Start Date]<=Now(),"Active"
,"...")))
Complex:
If([Date Approved] Is Not Null, "Delegation",
If([Delegated Date] is Not Null, "Schedule Sprint Meeting",
If([Meeting Date] is Not Null, "Prepare for Meeting",
If([Meeting Date] is < Now(), "Follow Up Complete",
"Awaiting Approval"
))))
The Status field is really neat! But often times it cannot be leveraged in these situations.
- I would use if statements as shown above.
- With If Statements if their criteria is not met then the value would likely just be blank if they have left the else blank.
R
Ruben Uzan
Jon Darbyshire Joining the conversation. I have the same need, here's my user's case.
I run a company that gives training, and I m being audited every 18 months by a gov equivalent of an ISO certificator.
They request that I show proofs of quqlity management. They have 7 criterias, each criteria has indicators attached to them (32 criterias), and to prove that i m compliant with these criterias\indicators, I have to show them about 70 documents or tables or surveys, or dashboards they call proofs.
In order to be uptodate for their visit I created 3 tables, criterias, indicators, proofs and their links.
To review every year that I m up to date with their requirements I have set a status for each proof that says Not created, not applicable, Non compliant, Needs review, Ready 4 audit, compliant.
Same statuses for the Indicators and Criterias linked.
I want to do that : when all proofs of an indicator are Compliant, or Not Applicable set the status of the indicator attached to Compliant.
When all Indicators linked to a criteria are Compliant or Not Appliable set the status of the criteria to Compliant.
I have created a formula I called "calculated status" for it that looks like below
IF(
COUNT([Related Proofs]) = COUNTIF([Related Proofs].[Status] = "Not Applicable", [Related Proofs]),
"Not Applicable",
IF(
COUNTIF([Related Proofs].[Status] = "Needs Review", [Related Proofs]) > 0,
"Needs Review",
Etc
Instead of now create a ton of automation for each use case of these IFs, I would rather have attached my formula to a status, ie make my formula RESULT a status.
That will save me a lot of work, mental space and avoid creating tens of fields just to trigger a change of status or date (but I guess it s aplicable to any field, like have a calculation like "Quantity (number field) x Price of a service = TOTAL PRICE (formula that I would want to be a CURRENCY).
Hope I was clear enough, my brain is fried now.
Thanks for considering the demand.