Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Implementation of web forms Idea

L_123 ✭✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

I have been working on a somewhat complicated 5S audit for a while and I thought I would share one of my favorite ideas we have had.

We needed to create an audit sheet that was filterable by plant, then area, then by zone, and could have any amount of submissions per each zone, and included pictures and notes. We didn't want the auditors to have to type in the plant, area, and zone each time they input information, and we didn't want them to be in the Smartsheet itself.

So I created a powerpoint directory with hyperlinks allowing them to navigate to the pages with the area they are auditing. I hyperlinked the webpage with the URL filled with queries to auto update the web form and hid the plant, area, and zone in the web form. I saved the powerpoint as a show so it is uneditable on the userside, and saved the list of URLs in word as a template. Saving the URLs in word means I can use the replace function to replace the URL on all of the links while keeping the query meaning it is much easier to copy the form for other purposes.

The end result is the user opens the show, clicks links to navigate to their area, selects their area, and it opens the correct web form. They can fill it out as many times as they need to. When the smartsheet itself is actually opened it contains all applicable information to the audit carried out, with the user never having to input any of it other than the audit itself.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Luke,

    One word, Amazing!

    Can you share something of what you did because i would very much like to se more.

    I just got a ton of ideas.

    Have a fantastic weekend!




    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Richard Rymill SBP
    Richard Rymill SBP ✭✭✭✭✭✭

    Nice one Luke

    We love it when what is created makes life easier for those contributing to the workflow and all the clever stuff can go on it the background while the User Interface is "Kept Simple". 

    If you are able to share any more of this i'm sure there will be a number of interested people who could benefit? 

    Anyone else want to share a really good solution? 



  • L_123
    L_123 ✭✭✭✭✭✭
    edited 06/30/17

    Not a problem. The reports and sights are kinda self explanatory, just grab the info you want put them in a report and post it to the sight how you like it so i'll skip those unless someone wants more on that.

    First i designed the queries that I would want for the audit input page. It actually ended up being 124 of them which I listed in word. a couple examples (i built this in word for reasons you'll see later) :




    After doing this I saved it, then saved a copy of it. I copied the url for the webform from smartsheet and used the replace function to replace the ? with the url and added the ? back at the end of the url.

    Replace ?

    With www.smartsheeturl.com/1342134?

    Now I have 2 databases, one with functioning URL's, and another in case I need the list of queries again.

    I built a powerpoint that leads users to specific sheets using powerpoint hyperlinks (I also added links to excel pages that have listed duties assignments, and maps showing which area has which code) and then created boxes for each zone. I then hyperlinked the boxes to the web forms. (this took a long time, I had 124 of them). Finally I saved the powerpoint, then saved it again as a powerpoint show so it would be uneditable userside, but I still had a way to update it.

    The way our audit works is our audit managers take the audit inputs and give each zone a score for each question in our audit. for example they give 1.1  a grade from 1-5 for plant dm in the production line. then 1.2 1.3 1.4 2.1 2.2 and so on. That grade is created by a user (One of the managers) so I needed one more interface to input from. I saved the previously created powerpoint as a new file so I could keep the original, deleted one level of the powerpoints so I was left with Plants hyperlinked to zones, and did the same thing with the url's to the next sheet with all of the calculations for the data on it.

    So user side there are 2 powerpoints that link to 2 different web forms at the end. But all our math is automatically calculated, a lot of user input is put in without the user even seeing it, much less having to type it, and reports and sights are automatically updated based on the status of the audit. The audit calculation sheet is expandable all the way up to 6S for each certification that we get, so there is no need to update as we go farther, just unhide the sheets, then rehide the ones you don't want. This also allows for progression math to show how far we are from reaching our end goal.

    Man that ended up being an essay haha. Anyway I share because I want to see the community grow from just help to adding input and providing new answers and solutions to problems that we didn't even know we had. If anyone wants to see my formulas or anything for the math sheet I would be happy to provide them, a couple are rather complex. I believe I shared them in a previous post, but I can post them again here.






  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 07/04/17

    Very impressive and thought provoking. And now I know what a 5S (or 6S) audit is too.

    Worth a bookmark for rereading.


This discussion has been closed.