Enhancing Forms with Additional Information

Options

Afternoon, all.

I've got what feels like a simple question given my programming background, but after nearly 6 hours of smashing my head against Smartsheet's documentation and forums, I feel like I might be asking for Unobtainium.

Context:
I work in a maintenance shop, repairing entertainment equipment. We have multiple sheets composed of our many assets, parts inventory, and a service ticket tracking system.


The Need / Use Case:
I need users to be able to single-click create Service Tickets. The users I have creating these tickets are not extraordinarily gifted with computers, so I need the system to hold their hand as much as possible.

When a user creates a service ticket, there's a few critical things that must happen.

  1. The Asset ID being attached to the Service Ticket must auto-populate or be displayed, and become uneditable. The number of times (on our current and previous workspace solution) that users fat-fingered a single number or letter and destroyed every ounce of data tracking was too great to count. I need that field to be uneditable, but it also must be visible.
  2. An Asset ID is just a bunch of "numbersnumbersnumbers" to some of our users. They need prompting to understand for what "Asset" they've opened a Service Ticket. Is it a moving light? A loudspeaker? This information (in the form of Asset Name and Asset Type Description fields) is stored in our sheets, and I need to be able to pull it up, show it to them, and allow them to confirm that the Asset they're taking out of service is the correct one… before they click submit. This information must also be uneditable. Automation elsewhere hinges on the fixture name "Smarty Max" not becoming "Snarty ax" because of misclicks/mistypes.
  3. I need this solution to not take 40 hours of workaround to develop, or require an encyclopedia to teach the other tech support folks to support. I'm extensively experienced in Azure SQL and Access, both of which can very quickly and powerfully create what I'm trying to create in Smartsheet- but Smartsheet is what I'm required to use. I need the solution to be reasonable. If my user has to take more than 5-6 clicks to do such a simple task, they will stop doing the task, and the effort we put into the workaround will be for naught.

Example Case:

Fixture ABC123 needs to be removed ("red-tagged") from service for a broken fan. ABC123 is a Lights LLC Moving Spotlight.

User Joe Shmoe will take this fixture out of service. Joe clicks to access the New Service Ticket form.

Joe needs to either be able to scan-in via scan gun and asset tag (SKU: ABC123), or look up the fixture (Lights LLC Moving Spotlight) for which he wants to make a ticket. That information must be displayed to him at all times during the ticket creation process ("Creating Ticket for Lights LLC Moving Spotlight, Asset# ABC123"). Joe must not be allowed to fat-finger, mis-click, or otherwise muddle with other data, to avoid catastrophe.

Joe enters the required data, and voila, a completed service ticket.

Beyond this point, I have the rest figured out.

Important Notes:

  • Our company is not permitted to play with the API or third-party apps due to Data-Loss Prevention rules. All solutions must be natively contained within Smartsheet Enterprise.
  • The workaround must be labor-efficient. 40-hour deep dives into macros calling macros calling macros is unacceptable.
  • The form must be accessible, reasonably adaptable, and completable in a "reasonable" number of clicks. Again, asking an inexperienced computer user to open 10 different pages and not break everything in the process is a big ask.

I have followup questions, but I'll hold on to them while awaiting a solution to this.

Thanks to any and all of you who choose to help. Much obliged.

Answers

  • Brent C. Wilson
    Options

    There are two things that I would recommend here.

    1. Use a URL query string to prefill default values on a form
      https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

      This can allow you to embed this information into a barcode and when scanned will pull up the form pre-populated with the information you want
    2. you can create a helper column with a URL and use that helper column to create the URL for you. that would allow the users to click. Depending on what you want you could do a few things
      1. If you created that helper column in the file you can then make the file read only to those users so they can only see and click on the URL.
      2. You could also create a report for each machine and again make it read only and then they can see the machine and item. Also you can pass a picture in a field so it is even visible to them the worklight on the machine vs the andon on the machine.
      3. You could also create all the urls for the parts and then create a dashboard for each machine as well.

    I used a different system for a Visual Production system and having the URL's seemed to get us there.

  • NateJ
    Options

    @Brent C. Wilson Thanks for the reply.

    I've already looked into URL Queries for prefilling information. The barcodes we have are created by a separate team responsible for purchasing and acquisitions, so any changes to the barcodes would require their collaboration. Right now, barcodes are restricted to Code 128 encoding, with no more than 16 characters permitted, to keep barcodes on equipment small and easy to hide. That means no long URLs via Code 128. QRs could solve this, possibly, but would still require internal consultation.

    It's also worth noting that prefilled information from URL Queries is still editable, and since I can't force focus onto designated fields, or lock fields from editing, users can (and at some point will) tab into those fields, and end up editing them. And since the fields I'm importing uniquely identify the Asset and Service Ticket, that would result in broken automation. Ideally, I'd be able to display prefilled information as raw text, just for information purposes, so the user could be aware but unable to do damage. Considering this is possible when generating automation messages, I feel as though inserting fields into text descriptions or headers is a fair ask for core functionality.

    I have created helper columns for URL Query creation, though. That was actually a good bit of fun, and it works great. It's just a partial solution to a wider issue.

    Interestingly, after a lot of experimentation, it looks like dynamic views get us closer than any other Smartsheet tool to doing what we need the ticketing system to do- but they don't seem to be template-able, and they also tend to be more resource-intensive when opening, as compared to forms.

    Thanks for your response, I appreciate you taking the time to help. :)