Structuring app to track work on equipment

Hi, I’ve been reformatting a service app we use to reduce the amount of blank cells generated in a Google Sheet. Purpose of app is to generate a service ticket for a customer that has a piece of equipment to be serviced. We used to have over 300 columns per customer to track many pieces of equipment but it left us a ton of empty cells and longer sync times than necessary.

What I’m working on now is making “Equipment” it’s own table with each row tied to its owner in the Customer table by a randomly generated customer ID.

What I’d like to be able to do though, is when creating a new row in the Service Ticket table which references the Customer name in the Customers table, is have an Equipment field populate with a suggested list of equipment pieces matched by the Customer ID.

Ultimately we are using this to track service tickets per piece of equipment at a customer location rather than a service ticket at customer location only.

At the end of the day, I’m just not quite sure how to structure the suggested values formula in the service ticket row to only pull the appropriate pieces of equipment from the Equipment table based on the Customer name I pick in the service ticket.

I hope this makes some sense! Any ideas?

Thanks in advance,

Justin

Hi Justin,
One possibility is to use a Valid_If constraint that looks at a table listing for each customer what are the possible service ticket. However, note that new values that are not in the list cannot be added by the user.

More info and options are available on this at https://help.appsheet.com/en/articles/1211505-the-lookup-dropdown-data-pattern

1 Like