Do you need help or want to help others with challenges your organization is facing with COVID-19?

Kyle Chipman

Thanks for the recommendations and resources @Dave Gordon and @Debbie Sawyer. I have to focus on my day job at the moment but I'll have some time tonight - I can take a stab at incorporating your steps into a structured approach for initiative assignment and tracking. This evening, I'll spin up the Smartsheet Community workspace, invite us all as admins and start laying down preliminary structure.

Of course, if folks would prefer to keep working directly with @Amy Donahue in this thread so she's not waiting on a larger structure to implement a PPE inventory tracking (looks like you've got the resources to help her immediately), don't let me hold you up :)

Dave Gordon

No worries @Amy Donahue , just so you are aware (as not obvious) there are two drop-down;s in the PPE Management dashboard that I have highlighted beliw

which show extended information. Switch to "Grid" on the first dropdown to see detailed info and you can alo scroll across to see all fields.

It's important to note this is simply a mod of the Asset Management template that Smartsheet provides - an so no need to reinvent the wheel :)

Dave Gordon

Hi @Amy Donahue

I've had a quick look at your Inventory sheet and can see you have group things by row (horizontally), which means you then having to manually move them into the location to be computed.

It typically makes more sense to have the type/sub-type/size/ defined as a separate dropdown fields, and then use a another smartsheet/report to compute/extract the desired information.

Dave

Amy Donahue

@Dave Gordon drop downs would have been smart! I can try to convert some of these from parent/children. Thinking thru how to manage the ordering process too. May be I'm doing too much on one sheet? Orders/donations keep coming and we need to track what is still outstanding...

Really appreciate your review - perhaps we could chat too? Email me if you have time? adonahue@advocates.org. THANKS!

Dave Gordon

Hi Amy,

I just shared the workspace with you, and made your an Admin user.

If anyone else would like me to share so they can make their own copy please just shout with your respective email address.

Keep safe!

Dave

 

Kyle Chipman

@Dave Gordon

Just freeing up from calls / the day - I'd like to grab access to the workspace as well. I was about to set up the more general "Smartsheet Community" on my account, but we could just build off what you're already developing. Excited to see what's built and contribute!

kchipman@chipman-design.com

Kyle Chipman

Evening. I started putting together a structure for a larger initiative tracking solution, based on Dave's example / recommendations and my own intuitions, but figured I'd like a bit more time to work that piece out and instead moved on to complimenting @Dave Gordon and @Debbie Sawyer's support of @Amy Donahue's request.

The examples they've provided are clearly based on real-world experience, and are quite robust (I'm going to be grabbing the Form Entries Above / Managed Assets Below for my own builds in the future - very clean). I'd also be curious to understand from Dave and Debbie how the barcode column comes in to play, if their doctors scan the equipment and log status through a mobile device.

The only thing I could really offer here is an approach that builds on the In Stock / En Route / Withdrawn functionality that Amy already put together, while incorporating dropdown adjustment Dave flagged, specifically for PPE that cannot be cleaned and must be disposed of. This would circumvent the Status tracker present in Dave's resources.

ADVOCATES - ORDER SHEET https://app.smartsheet.com/b/publish?EQBCT=3d68d9d8d8284b08871bf5d92990a286

ADVOCATES - WITHDRAWL SHEET https://app.smartsheet.com/b/publish?EQBCT=e262a3adf436425fb0819796b55e63ca

ADVOCATES - SUPPLY SHEET https://app.smartsheet.com/b/publish?EQBCT=2c431cfe866d4b10a711468c24a7b47f

This approach allows for a running log of orders, withdrawls and overall supply for disposable items. The benefit of this approach is using =SUMIFS(...) calculations per @row Item Types to incorporate Item Type dropdowns as opposed to the strict hierarchy of =SUM(CHILDREN()). I've highlighted the formulas in yellow. I made these =SUMIFS calcualtions in a sheet key as opposed to sheet summary to allow for cell linking, rather than dashboard reporting.

Generally speaking, these sheets would become more valuable if they incorporated timestamps per month. Right now, it's one big running log, which feels like a liability over time. On the Order Sheet, I used a secondary ISBLANK calculation to separate In Stock orders against En Route orders. If anyone knows a better =SUMIFS way to solve for this so a second column isn't invoked, feel free to holler. Final note - Withdrawl could definitely be incorporated into the Order sheet; I just split it for the sake of this example.

The big trick with the Withdrawl component would be building webforms that have hidden, pre-baked Item Type fields. Creating individual webforms would allow you to create QR codes specific to the URL, which could be made into stickers and placed next to the physical items, wherever they may live. When someone grabs an N95 mask or a gown, they could scan the QR code and log their withdrawl with their cellphone, which would feed into the running log. For reference, N95 and KN95 webform links are below:

N95 Mask - Withdrawl Form https://app.smartsheet.com/b/form/e4768b4c4edb4b38b8a56fb896cf3bcc

N95 Mask - Withdrawl Form.png

KN95 Mask - Withdrawl Form https://app.smartsheet.com/b/form/a9ec6aae9e16404e978c58891affc295

KN95 Mask - Withdrawl Form.png

Per the Supply Sheet - what I didn't quite have time to figure out but I'd love to dig in to (if you're interested in this whole approach, Amy) is the calculation and projection of Capacity per Item Type based around Time. This would require an additional Patient log, but some projection of time, patients and demand could calculate Capacity, so that based around [x] number of days you could understand when you'd need to resupply. You could also incorporate average lead time and average unit cost calculation based on your Order sheet.

Hope you find some helpful ideas in all this! I'll post my thoughts around a Smartsheet Community initiative tracker this weekend.

Dave Gordon

Great idea's @Kyle Chipman !

@Amy Donahue here's a demo of how the scanning could be utilised https://www.smartsheet.com/content-center/product-news/product-releases/increase-speed-accuracy-mobile-barcode-scanning

If you cant see the barcode when you are running the Smartsheet mobile app on your phone then select the more up arrow highlighted below

and then select the barcode icon highlighted below

it will fire up the camera and you can scan anything with a barcode that will then automatically update the cell

Dave

Debbie Sawyer

@Kyle Chipman

Just a quick note - @Dave Gordon and I are 2 separate companies. The help I am offering is through Smarter Business Processes and we have developed our own solution for PPE management, Face Fit Testing etc not using the template.

We are offering NHS clients access to this piece of development with a couple of hours consultancy to help people modify it to suit their needs.

The work @Dave Gordon is doing here is amazing and I thank him for helping the community too in this situation we find ourselves in, but I cannot take any praise for any of the work on this thread :) It is all Daves ;)

Kind regards

Debbie

Kyle Chipman

Thanks for flagging Debbie, and agreed, Dave's work is great :)

In my desire to pitch in and help last night I typed before my mind had a chance to catch up. @Amy Donahue I think you'd benefit from folks like Dave with more direct experience in inventory management when it comes to structuring your underlying sheets. Smartsheet's own article on Inventory Control was a helpful starting point for me to better understand the methods and techniques, particularly Reorder Point.

https://www.smartsheet.com/inventory-control-methods-procedures-programs

Once someone has worked out the base structure I'd be happy to jump in and provide some feedback, specifically around automation opportunities and conditional formatting. Also, for SUMIFS, you can just use "" to represent an empty date cell (Actual Delivery Date) rather than creating a separate ISBLANK formula. The correct formatting hit me after posting the comment.

I've dropped my reference sheets to the Advocates workspace Dave created, in a folder titled "SUMIFS Example Sheets". I'm happy to pursue any direct requests you might have, I just don't want to lead you down the wrong structural path due to inexperience in this particular field.

Dave Gordon

Hi @Amy Donahue

I presume the reason you have not responded is because you are overburdened with other work, but I just wanted to make sure we were trying to do everything we can to help you.

So this is more of a community effort, my suggestion on they way forward is:

1) you take up @Richard Rymill SBP / @Debbie Sawyer offer of two hours consultancy, and use this time to incorporate your Type/ Sub Type / Size to the Sheets and Forms / Dashboard

2) You allow @Kyle Chipman to independently work on a wizzbang Check In / Out procedure

3) If other members can offer assistance, they can help with;

a) testing the system and

b) migrating your data

The project plan can be viewed at https://app.smartsheet.com/b/publish?EQBCT=aded9950b9fa45548dc0f88883735d80

I have created three columns 1st/2nd/3rd Work Offer Person. For all those willing to help could add their email address (or just names and send me your email address to support@davidgordon.com.au) I'll share the workspace with you. This includes 1) and 2) as @Richard Rymill SBP / @Debbie Sawyer and @Kyle Chipman 's positions may have changed in the last few days, so it would be nice to have backup personnel!

Please be sure to understand, and I think I can speak for all of us, that none of us want to add and further pressure onto you. If you are working on other things to resolve this problem, please don't feel obliged to have to continue on with this work. We are all simply trying to help, as will completely understand if you choose not to progress, and absolutely nothing has been lost.

Kind regards

Dave

Richard Rymill SBP

To the wider Smartsheet user community involved in Healthcare, UK and USA, we welcome the opportunity to support you with Face Fit Testing Registers, PPE usage and Requirements > Reports and Dashboards which will require some customisation to match your exact needs, with Two hours of free remote consultation to assist, from Smarter Business Processes.com.

@Dave Gordon and SBP are happy to collaborate together to help users as we both have considerable Health Service Experience. Keep safe and reach out if you wish to discuss urgent needs? Richardr@smarterbusinessprocesses.com

Amy Donahue

Hi all -

From what i have been able to glean thus far, the sheets you are creating are much cleaner than my clunky startup and are exactly what we need. The checkout form as well as the On Order, In Stock, Checked out (distributed) are all very clean. I can say with certainty that barcode scanning is beyond our capacity right now. We don't have the technology (we are a nonprofit; mostly social workers are figuring this out!) AND resources are going out as fast as they come in. A "good enough" solution will fit our needs for the short term!

I have been inundated with both multiple requests to order more while simultaneously receiving reports from our current suppliers of the high drama (arrests, possibly worse) that is occurring overseas in responding to US orders. Add to this the day-to-day responsibilities of "normal business" and the challenge of parenting a 6 and 9 yo, the oldest of whom is on the spectrum, I am stretched quite thin!

All that said, I would like to convert to your workflow. I would appreciate a time dedicated to talking through the conversion? how best to coordinate that given the multiple folks in the workspace now? @Dave Gordon @Kyle Chipman @Richard Rymill SBP @Debbie Sawyer

Your generosity continues to amaze me and I am really grateful for the expertise on this!

Dave Gordon

No worries @Amy Donahue , and when we have a solution developed we would be more that happy to work together to migrate your work.

Just to advise, the bar code scanning can be processed with any standard iPhone/Android phone, and you don't need any additional technology.

I'm conscious of how busy you are, but if you have someone else who can take some photos of a couple of the PPE barcodes and provide information on what equipment this relates to. We can then incorporate scanning into a demo , and also provide you with a "real world" example of how it works using something you can easily relate to.

Regards

Dave

Mark Fantasia

Hi @Amy Donahue and @Dave Gordon , I am an experienced Smartsheet admin/user. My current position was eliminated at the beginning of March. I have a good amount of free time to help out. I can definitely take on the testing function and whatever else is needed!

Mark