How To: Create Something Similar to Access Subforms

Options

Good afternoon.

I work at an enterprise level (10,000+ records per table/sheet), and am responsible for creating a Service Ticket system within Smartsheet. Previously, I accomplished this using Access' powerful Form/Subform datasheet systems with custom forms and conditional queries, but I am now required to use Smartsheet and its equivalent.

Context:
Essentially, we have the following use case hierarchy:
Work Orders
(which contain) → Assets being worked on

This is a simple one-to-many relationship. What I need is a display solution that, when a user clicks on a work order, they can (while still viewing the overarching details of the Work Order) see the details of every Asset "attached" to that order.

Use Case Example:
1. Technician creates a work order, with fields such as Name, Description, Type, Sub-Type.
2. Technician "opens" that order to add/assign assets to it.
3. User uses a scan gun to scan the asset tags of anywhere between one and several hundred assets.
4. As the technician scans, Smartsheet needs to assign the work order ID to that Asset so that they are associated.
5. When the technician finishes, they need to be able to click on the order in a list and open it to view the assets attached to it. Each asset is tracked separately and has its own repair status.

Important Caveats:
There's a few important things to note that I cannot be flexible on due to how our repair shop functions at the basic level.

  • We cannot force the user to use fancy filtering or sorting systems to do this. They need to click on one object and be given the info they need. Not all of my users are computer savvy, and teaching them how to use filters, groups, and sorts introduces too much room for errors while building and tracking orders.
  • An asset may be attached to multiple orders, in multiple stages of completion (a classic one-to-many database relationship). The user should be able to look up old orders and see what assets were attached to them. Adding or removing assets from current orders cannot be allowed to affect old orders.
  • Because of the sheer multitude of orders we work on (several thousand a year), I cannot build independent Sheets for every order, as this will take extravagant amounts of storage and processing time to retrieve as the database swells in number of records. It will also make managing automation a fresh hell.
  • Processing times must be reasonable. A user can wait a few seconds for a sheet to populate. Waiting multiple minutes for a 10,000-record table to filter, sort, and retrieve the desired records is not reasonable given the system I built in Access can accomplish this in less than 3s, and that is what our technicians are already used to. Worsening efficiency is not cost-effective.

If this isn't possible, I need a direct answer of "This is not possible." Due to the sensitivity of our work and DLP policies, we cannot use API calls or integrations, and are not permitted to use third-party tools or plugins. At least, if I'm told "This is not possible" I can provide that to our purchasing department so that I can justify returning to Access.

Tags:

Answers

  • dojones
    dojones ✭✭✭✭
    Options

    Based on how you describe it, I suggest using a work order maintenance system such as MaintainX or FIIX.

  • NateJ
    NateJ
    edited 06/10/24
    Options

    Unfortunately, decisions at that level regarding which software we use are beyond my scope, so I'm forced to work with what I've been given. Judging by the absolute deafening silence on this post, I can only imagine that it's not possible.

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    Options

    It doesn't sound too bad. I think what you'd want to do is parent the assets to the newly created work order. You would have Name, Description, Type, Sub-Type columns, then the asset tag column, which is scanned. You could also do calculations in helper columns for completed/not completed and create completion %s for each work order.

    You would have a lot more front-end options for this with the API to reduce user intervention, but it's possible with a little bit of training for your team on how to add rows to a parent after an item is scanned.

    Sincerely,

    Jacob Stey