Using Forms and Formula to Simplify User Experience

jvalz

I want to share a cute solution I've built as a lightweight Engineering Change Management solution in Smartsheets. I've found some useful techniques which could help others! Hopefully, seeing a solution is a useful inspiration for ya'll.

Brief:
We're a manufacturing company. We have a custom web app to manage creation of parts. I worked with our developers to add a button in the app to submit Change Requests into Smartsheets. We're using the Change Management Database Smartsheet as a prototype for a future custom development PLM solution (2-3 years out).

Philosophy:
I think about my smartsheets as both a database and an application. I like to collect my information in typed, controlled columns and then present that information simply to the users. It helps a lot with adoption to focus on UI/UX!

Here's some tactics I've learned —

Technique 1: URL Parameters in Forms
Populating forms via URL parameter is awesome! You can ensure Smartsheets data is in sync with other systems that way.

Technique 2: Consolidate form fields into a single, carriage-return delimited list
Since the Change Request form has a lot of fields, it's hard to look at in the Smartsheet database.

I love thinking about the 7 forms of waste, digitally. Movement is one of those wastes — scrolling is a big offender, and horizontal scrolling is the worst!

You can eliminate horizontal scrolling by consolidating multiple fields into a single field. Check out below. For my "Analysis Details" in the image below, I really took advantage of this. Check out the formula at the end of the post.

Technique 3: Use DataShuttle and DataMesh to sync in useful data
Since the Change Management process requires an Impact Analysis step, I wanted to bring in data from our ERP, custom PLM, and other systems. DataShuttle and DataMesh to the rescue!

Technique 4: Construct URLs with parameters
This is a nice one, too. Since our ERP is web-based, I wanted to give links to useful pages. I made some helper columns that produce urls with the [Part No.]@row as the input. Then, I used Technique 3 to combine 4 links into a single list of links.

I designed the overall data with my team; we're a Microsoft Shop and use PowerBI.

The full flow is interesting:

1) Set up PowerBI data flows to ingest data from various web app API.
2) Use those data flows and set up a Semantic Model in PowerBI to get the measures and values you want. Schedule your refreshes.
3) Use Microsoft Report Builder to bring the measures into grid format. Schedule to drop into Onedrive. Now for Smartsheets stuff :p
4) Set up your Data Shuttle to grab the OneDrive file and bring it into Smartsheet. Try to schedule this so it's in sync with your analytics scheduled jobs.
5) Set up DataMesh to pull from the synced source smartsheet into your target database!

In the image above, you can see all that useful ERP information per part. It's really helpful. Even better, you can start feeding that data into other Smartsheets with more DataMeshes!

Here's that formula I mentioned earlier. What a beastie!

PS: Roll Tide 🐘