Hello,
I'm currently attempting streamline work status changes with Smartsheets as they are integrated with my JIRA instance via cloud connector. The way we currently run our projects within JIRA (that are synced to Smartsheets) is by creating "Epics" that have "Stories" mapped to them(as children). Through using the Smartsheet sync I was able to have all the relevant project info synced from JIRA to smartsheets, and additionally through the following formula I have automated the status change of the Epics to reflect the status of the stories:
=IF(COUNTIFS(CHILDREN(), "To Do") = COUNT(CHILDREN()), "To Do", IF(COUNTIFS(CHILDREN(), "Done") = COUNT(CHILDREN()), "Done", "In Progress"))
Essentially the formula which is based on the row's that have the Epic (where the stories are nested under as children) tells the Epic status to be "To Do" If all stories are "To Do", "Done" if all stories are "Done", and "In Progress" if any of the child stories are "In Progress". This formula has worked great, the issue I'm having now however is with the Smartsheet cloud connector with regard to the following:
- I've tried plugging that formula into every row that is an Epic under the "status" column which worked great for a short period.
- As the cloud connector is synced to push/pull bi-directional for "Status" within Jira (Because both applications should always reflect the same status) the formula is overwritten whenever the Epic status changes--this is because on the JIRA side the status is a normal text field while in SmartSheets is a formula
- I've since created a column "Epic Status"(See attached screenshot) and moved the formula under it instead of "Status" so it won't be overwritten (this new column does not sync)
- The work around I now have in place is a workflow that alerts me based on the "epic status" when an epic becomes "In Progress" or "Done." From there I review the status change and the fields, and open the JIRA Epic to manually change the status, which isn't ideal.
There has to be a way to fully automate this right? Does anyone have advice on how my approach can be adjusted here?
Any help is appreciated.
Thanks,