Any Ideas or feedback?

Hello

Looking for ideas on automation solution and hoping someone here can help.

Scenario:

My utilizes Smartsheet as Workforce Management System. We support a contact center of around 1k agents. The various sheets\reports\dynamic vie\ automations that have built cover the full gambit of a WFM system including a roster, schedules, attendance, exception management and Time Off Requests. I build solutions to most of our manual processes. However, there is 1 piece I have fully gotten yet.

Currently each day for any agent that is not accounted for by a certain time our WFM Specialists have to manually update the agent status to a value to indicate their unreconciled status. The times this update takes place can vary based on the time zone the agent is in, the normal shift start time and if the agent had a scheduled time off to arrive late. I have created a formula that can calculate what time the agent unaccounted for time kicks in. I have a report that groups the records by this time and can account for the time zone impact. A key requirement for the WFM Teams is having the agents updated within a short window. So, if an agent unaccounted for time was 12:45 PM CT the team would need\want the agent updated by a window of 12:45-1:00 PM or if the agent time was 12:30 PM CT, update need\want would be 13:30-12:45.

I have now been asked to determine a possible way to fully automate the status updates based on the time. I initially considered a sheet automation but the schedule limitations result in this not being the best option. Within our company I only know of 2 tools I might be able to use 1. Bridge or 2 Power Automate. I have created few Bridge workflows and am getting mor e comfortable with it but I am not sure I how I would accomplish the need. I have thus far not done any Power Automate interactions with Smartsheet but would be willing to explore this if it were an option.

Any ideas would be appreciated.

Thanks.

Find more posts tagged with

Formulas

Automations

Bridge

Accepted answers

Scott J.

I ended up finding a to solution.

First, I created a helper sheet. Currently it is only 2 lines. 1 line for the ET Time Zone and 1 for CT Time Zone. I have a column that is updated every 15 minutes with the current time in ET\CT using a Bridge Workflow. 2 additional columns outline the business hours and can be user updated as our project call centers expand hours of operation.

On my agent roster I pull the 3 time columns from the helper sheet. I then added a column with a formula that contains some if statements. The first results in the column the workday is 1 or 7 (Sunday\Saturday) since we are closed on those days. It then using if(and( determines if the agent is unaccounted for (No attendance status recorded), If the helper time is greater then the opening of business, less then or equal to the close of business and greater or equal tot he accounted for time. If yes the field with show triggered, otherwise ti remains blank.

I then build a triggered bridge Workflow that is looking for any value changes in the triggered column and I send the rows through some simple logic and I update the files on the row related to the attendance status.

This overall results in pretty near real time updates. Since our agents shifts overall start on the 15 minute, the unaccounted for time ends up being on the 15 minute mark as well. With the initial Bridge workflow running every 15 minutes, the trigger value is updated right at the unaccounted for time and thereby the 2nd bridge update workflow is triggered.

This may not be the most optimal way to accomplish the need but it was what I could come up with.

All comments

Naeem Ejaz

Bridge doesn’t support dynamic timing per row, but you can run a global scheduled workflow every 5-10 minutes.

OR you can use Dynamic views and make that field editable for the agents to update status.

Also explore smartsheet WorkApps they often prove useful in your type of way of work.

Andrée Starå

https://community.smartsheet.com/discussion/141303/any-ideas-or-feedback

Hi,

I hope you're well and safe!

I recommend either having a form and then an alert/automation that triggers from that, or the same with any change to a sheet/report, or in a Dynamic View.

But, I'm unsure I fully understand the process. Is there always a manual step, or could it be automated somehow?

Please share some screenshots and/or additional information. (Delete/replace any confidential/sensitive information before sharing.) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

Scott J.

@Andrée Starå The team used to complete the process using a form but this was time consuming. 1000-1100 agents, across 10 centers. The WFM Team is not centralized to any one center. The centers themselves cannot take on the submissions due to workloads. All the daily Workforce Management task are done by 3 members overall. The number of agents that are in an unaccounted for status varies by day. Example on a recent day we ahd 50 agents unaccounted for. So the WFM member working the task would have to submit 50 forms. This was how we did it previously. I created a report based on a agent roster that just shows agents with no attendance status, grouped by time zone and a calculated time value. The WFM team member sets the status for the agents and combination of data shuttle\data mesh creates the require tracking entries on another sheet.

This process is not becoming more complicated and require more completion times based on change in our call centers. Agent shifts start times are moving to a staggered approach. Add in if an agent had planned time off in the morning this offsets the accounted for threshold time point. 4 hours from start of scheduled shift.

My goal is find a way that I could automate this process. A thought would be run an automation 12:30 CT. Find any agent unaccounted for (blank attendance status) where the calculated value is between 12:15 - 12:30 CT. Update status.

Scott J.

@Naeem Ejaz I have been trying to figure out how to accomplish in Bridge. I believe it would be multiple workflows to accomplish it running on staggered scheduled starts. My knowledge level for Bridge is at a beginner level as we just recently got access to this addon, so I am just not sure how to set this up.

Dynamic view is not an option in my opinion. I am already using a report that filters and groups in records that a WFM member can update. Using a dynamic view where each record has to be clicked on, updated, saved individually compared to the report where the user chooses the value from a drop down and drags down across grouped records.

We already using WorkApps to access our sheets, reports, forms, etc. I was recently told by Smartsheet Support and Pro Services that WorkApps are no longer being developed, so new features such as Table View will not become available in them. There was indications that WorkApps will phase out at some point as the new Collections Option increases. Either way, this does not get to a point of automated these manual status updates.

Scott J.

I ended up finding a to solution.

This may not be the most optimal way to accomplish the need but it was what I could come up with.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.