Enhancing Forms with Additional Information

NateJ

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.

NateJ

So it's been a month with no final answer, so I'll provide the solution I ended up going with, and explain how it turned out.

I ended up building a massively complex URL query system that autofills fake fields that happen to be labeled the same as the actual fields being populated. That way, if they're damaged or destroyed by an incompetent user, no true damage is done. Then, when the record is created, automation populates data based on several lookup tables, and voila. Ugly and bloated, but it gets the job done.

Example:

1. User opens the Assets Sheet.
2. User opens the prebuilt filter "pre-filled" with a generic ID number. They click into the field and scan the asset they want to red-tag, then click Apply.
3. Hopefully nothing goes weird and the one record is shown. The user then mouses over to the URL, clicks it, and opens the form.
4. The form imports all of the legitimate fields as URL queries, filling in a bunch of duplicate "fake" fields that allow me to autofill data while stopping the user from corrupting legitimate data. Unfortunately, that makes my Assets Sheet even more bloated, with 5-10 columns really adding up across thousands of records.
5. The user fills out the form and submits it.
6. A series of VLookup automations trigger to make sure correct information is stored based on proper database normalization rules.

However:

Here's why this is a pain, a problem, and a drawback of using Smartsheet:

• My users used to be able to just click "Red-Tag", then scan an asset to red-tag, and type in a reason. Simple, easy, very fast. Now they have to open a report with all our assets listed (many thousands of them), wait for it to load very slowly, use a filter system, make sure the filter works, wait for the filter to apply, click a scary-looking URL that opens a form, fill out the form, then wait 15 minutes for all the automation and data shuttle processes to run.
• My users miss having proper UI elements with easy-to-find buttons. Being forced to work in Excel 1.5 is remarkably frustrating for many of them, particularly those who were used to having a tailor-made solution. While this certainly is a "workaround," the notable grumbling and efficiency loss is certainly felt.
• This was a pretty simple problem. In Access, less than 5 minutes of basic VBA coding and drag-and-drop buttons and elements. AzureSQL, same. Excel less so, but, Excel really wasn't built for this and technically I could have implemented DirectX controls to make it behave the same. The engineer in me is pretty bummed at being forced to come up with a complex solution to a simple problem instead of a simple solution.

Brent C. Wilson

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

@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. :)

NateJ

So it's been a month with no final answer, so I'll provide the solution I ended up going with, and explain how it turned out.

I ended up building a massively complex URL query system that autofills fake fields that happen to be labeled the same as the actual fields being populated. That way, if they're damaged or destroyed by an incompetent user, no true damage is done. Then, when the record is created, automation populates data based on several lookup tables, and voila. Ugly and bloated, but it gets the job done.

Example:

1. User opens the Assets Sheet.
2. User opens the prebuilt filter "pre-filled" with a generic ID number. They click into the field and scan the asset they want to red-tag, then click Apply.
3. Hopefully nothing goes weird and the one record is shown. The user then mouses over to the URL, clicks it, and opens the form.
4. The form imports all of the legitimate fields as URL queries, filling in a bunch of duplicate "fake" fields that allow me to autofill data while stopping the user from corrupting legitimate data. Unfortunately, that makes my Assets Sheet even more bloated, with 5-10 columns really adding up across thousands of records.
5. The user fills out the form and submits it.
6. A series of VLookup automations trigger to make sure correct information is stored based on proper database normalization rules.

However:

Here's why this is a pain, a problem, and a drawback of using Smartsheet:

• My users used to be able to just click "Red-Tag", then scan an asset to red-tag, and type in a reason. Simple, easy, very fast. Now they have to open a report with all our assets listed (many thousands of them), wait for it to load very slowly, use a filter system, make sure the filter works, wait for the filter to apply, click a scary-looking URL that opens a form, fill out the form, then wait 15 minutes for all the automation and data shuttle processes to run.
• My users miss having proper UI elements with easy-to-find buttons. Being forced to work in Excel 1.5 is remarkably frustrating for many of them, particularly those who were used to having a tailor-made solution. While this certainly is a "workaround," the notable grumbling and efficiency loss is certainly felt.
• This was a pretty simple problem. In Access, less than 5 minutes of basic VBA coding and drag-and-drop buttons and elements. AzureSQL, same. Excel less so, but, Excel really wasn't built for this and technically I could have implemented DirectX controls to make it behave the same. The engineer in me is pretty bummed at being forced to come up with a complex solution to a simple problem instead of a simple solution.