Dynamically filter view by column

[Nelson_Doris]

So I have ~50 machines that have daily uptime values (hours) for 20+ years. I am trying to create an app that allows operators to input those daily values into a table, and also see a graph of values over time for any given machine. I currently have two tables, one with unique machine Id’s and pertinent info about them, and another with dates in the first column, machine names in the column headers, and values for uptime for a given machine on that date. I am thinking I need to break the values table into 50 tables (one for each machine) to make this work, but I am hoping there is a more elegant solution that will allow me to dynamically filter a view to visualize or input data for any specific machine within the combined values table. Is this possible?

Thank you!

[Austin]

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

not sure about the specifics in regards to graphing stuff but you definitely don’t need 50 tables.

[Nelson_Doris]

Thanks for your reply, Austin,

I think my issue there is mostly the format of my value table, and my fear of ballooning this data set. I think what I need is a row >> column name reference, but as far as I can tell, that referencing between tables can only reference rows between tables via a Ref ID. It should work if I format my table, from 50 columns and 8500 rows, into 4 columns (unique ID | Ref ID | Date | Value) and 8500*50 rows. That would take it from 425k cells to 1.7M cells, and 200 new cells would be added every day. I don’t know if that should scare me or not, but I was thinking I could cut out 425k cells by using the table name as the reference. Maybe better is to make 50 slices of the one table, but I don’t understand how slices work well enough to know if that would make things better or worse.
Anyway, I am pretty novice at this stuff, and I feel like my ‘solutions’ almost always have a better way that I can’t think of. Please set me straight if I am fundamentally not thinking about this data structure and linking in the right way.
I guess my question is now: Is it better to have a very large (I think 1.7M cells qualifies) table with nice clear linking, or or to have 50 tables/slices/views in whatever combo with more dynamic linking? Or is there another dynamic solution that removes the need for the 50 subdivisions?

[Austin]

it would still be 8500 rows. Make an app with both of your tables. Take the machine name column in your table with the date information and make that columns data type a REF to the machine table. Make sure the key for the machine table is this unique machine ID. When you open a detail view of a row from the machine table it should show you all rows from your larger date table that match that machine ID.

[Nelson_Doris]

I think I may not be clearly stating my value table format.

Date | Machine 1 | Machine 2 | … | Machine 50
1/2/21 | 15.6 hrs | 12.2 hrs | … | 20.3 hrs
1/3/21 | 15.9 hrs | 12.7 hrs | … | 20.1 hrs

Because the reference is the header (column name), not a column value itself, I don’t think there is a way to use it as a Ref ID. I would have to reformat it to:

Date | Name | Hours
1/2/21 | Machine 1 | 15.6 hrs
1/2/21 | Machine 2 | 12.2 hrs
…

and because there wouldn’t be a unique ID at that point (currently it is date), I think I would have to add a 4th column with a UID.

I’ve successfully used Ref ID’s before, and I think this is how it would need to be set up. Is this correct?

Thanks,
Nelson

[Austin]

OH. Uhm might be out of luck on not having to re-format that data. I do not see any way of turning that into information that appsheet would use for a graph.
Time for the expert. @Steve

(maybe not using Appsheet but I think Excel would have a way to visualize this data structure. Could still use an Appsheet app for entering the data while using an excel sheet to visualize it)

[Nelson_Doris]

Thanks again for the help!

Reformatting may definitely be the answer here , but I just want to make sure I am building out the best of my options here. I feel like with this many cells, I am running the risk of getting some serious bloat and/or performance problems.

I’ve made a slice that is just the values for one column (i.e.for one machine), and then used a statistics view of that slice and it works great. I was thinking I could do something similar by creating individual views that only had the date and one column selected and then linking to them dynamically from the machine ID table. But all this may just be more trouble than doing it the big table way…

[Steve]

Before going too far, make sure AppSheet’s graphing capabilities can provide the visuals you want. Historically, graphing has been very limited. They’ve been working on it, but I don’t think the new capabilities have been rolled out yet.