I am writing a time tracking app using Google Sheets as the database that is tracking the particular activities in which people engage, along with detailed information about the nature of the activity and its properties. Among other information, the main input form collects the identity of the Actor, the Activity in which the Actor is engaging,i.e., Eating, Driving, WatchingTV, etc, and information about the place in which the Activity takes place, i.e., Home, Work, School, Indoor/Outdoor, etc.
So example parts of the form ask users to input values relating to: Actor Activity Place
When Activity is selected, all the allowable activity types are presented as in the examples above.
Now ideally what I’d like to happen is that when a given Activity is selected, all the activity-specific details appear on the same form under the Activity variable and before the Place variable. For example, if you select Eating, a FoodType menu would appear where users could select what type of food they are eating; if selecting driving, then the ToAddress, FromAddress, and Distance variables would appear for input, etc.
Now I know I could simply put all the Activity-specific detail variables on the same sheet as the Activity variable, and then use a Show-If expression when the corresponding activity is selected.
The problem with this approach is that the number of columns will eventually become very large and unwieldy, I’d imagine. I eventually will have up to 50 or so different activities, each of which might have 2 to 5 different properties- thus requiring hundreds of columns.
What I would like to do instead - and which I thought would be easy - is to have different data tables (Spreadsheet tabs) for each Activity and their related properties, and then when you select an Activity, the corresponding data table and their variables would display underneath the Activity on the input form.
But I can’t figure out how to make that work. How could this be accomplished?
Alternatively is there another strategy I am not thinking about that could essentially accomplish the same thing? Or should I just go with the unwieldy 100s of columns in the same data table approach?
Any advice would be greatly appreciated.
LinkedIn
Twitter
