Field Service Management in Smartsheet

PeggyLang

Has anyone built a Field Service Management solution in Smartsheet?

I need to maintain a database of;

• store locations
• technicians, their backup tech and their backup of the backup tech
• dynamic schedule of availability of said technicians

I envision some form of a 'lookup' worksheet where appropriate and relevant data from database is pulled in based on some questions;

i.e., is service required during business hours or after hours, where geographically is service required; if first tech is not available, who is second tech to call, etc.

A third component of this solution is to allow for ease of data entry on the tech's manager's side. Tech Manager needs to populate database with availability of each of his techs.

I'm just starting to let this idea percolate in my brain and have some ideas, but wondering if anyone out there has built anything similar that may be able to offer advice, tips and tricks, direction, etc.

Julie Fortney

Hi @PeggyLang

So many options!

I have a tech scheduling solution that pulls assignments made in multiple sheets (Vacation/PTO, On-Call Schedule, Project Assignments, etc) into one sheet. I then display that sheet using Calendar App on a dashboard with links and how-to docs to help managers enter new technicians and assignments. Here's a screenshot of the calendar. I had to add "TODAY" placeholders to display technicians who do not have assignments, so managers can see that they are available.

Perhaps instead of grouping by manager as I have, you could make the primary grouping by location.

Let me know if you have questions about this idea.

PeggyLang

@Julie Fortney

Thank you for responding!

Would it be possible for you to expand on your solution more in depth?

What 'pulls' the assignments? report? Lookup worksheet?

What do your other multiple sheets look like? Do you have a 'master' sheet with all the tech's data and a series of Index/Match formulas? I'm struggling on this solution and I think I really just need to break it down into bite size pieces instead of looking to solve for the 'whole'.

Julie Fortney

It's a bit bonkers, tbh. 😅

High-level: I need to pull assignments from multiple sheets into one sheet, AND have placeholder rows for each tech so that they will show up in the calendar even if they don't have any assignments for the time period someone is viewing in the calendar.

Right now I am sending copies of rows from the source sheets to my master tech schedule sheet. I am currently in the process of reworking this in Bridge so that rather than sending every column from the source sheets, I can just send what I need. I also need to dynamically update the information in case the assignment changes on the source sheet. (Dates change, it gets reassigned etc.) So currently that means even more columns with index/match formulas to pull the current data from the source sheet. I have over 200 columns and it's a nightmare.

With Bridge I am just sending the Unique ID from the source sheet to my master sheet, and I'm using Data Mesh in place of Index/match to pull current data. So much cleaner!

As for creating placeholder rows for each tech, we have a master tech list, so when someone is added I have automation set up to create two placeholder rows for them on the master sheet. One has a label of "TODAY," and the other is "ONE WEEK FROM TODAY." The start/end dates for placeholder rows are formulas: (today or today+7). The managers look at the schedule by week and like to place 1-2 weeks at a time. Since everyone has a placeholder for this week and next week (today and one week from today), everyone will show up.

I hope that helps - let me know if you have questions.

PeggyLang

@Julie Fortney

Julie, thank you for the info! I would LOVE to actually see this live. Is it possible to set up a meeting? Please email at peggy.lang@cbre.com