I have multiple tables that are slightly different but which all have a DateTime column and a Description column and I want to be able to have a single widget that displays data from those tables so that I can create a sort of timeline of activities based on that DateTime column. Is it possible to do this? Any tips to get me started?
what’s your level of knowledge with Grist? Not sure you don´t know how to do simple things or if you are trying to make something complicated…
I’m new to Grist. This is child’s play in a typical relational database, which is what I am used to. I’ve set up a few widgets so far and am working with the API a little and that’s about all the experience I have. I’m sorry if this is a simple thing to accomplish, so just a pointer is all I need, since I can’t find the info I need to move forward.
I don’t think there is an integrated widget to do that; it should be possible with a custom widget, especially since there is an sql endpoint. You may try, for example, to use this query (which evidently doesn’t mean anything) on this sample:
select `Value`, `Labels`, null from `values`
union
select `Name`, null, `Categories` from `products`
Ah ok! I didn’t know that was possible. I will look into that!
Before doing that, I would look at the tables and see if it might make sense to combine them into one table.
I’d need to know more about your use case to see if that’s a good idea, but much is possible with a single table. For example, this document is built off one table: Software Deals Tracker - Grist Each page has a very different workflow/visualization, but it’s the same underlying table.
I looked into and I don’t think that will let me do what I want, since I didn’t see an easy way to then use that union query with the built-in charts.
The use case is that I am creating a tool for life logging and want to track a number of types of activities. Some of these activities have different metadata than others so the idea was to have different tables and them join them. I think instead I will have one master activity table that has references to activity entries in the other tables. That way I can produce a time stream of activities but pull the activity details from the referenced table with another query, or use a different widgets for them.
Could you please prepare a sample on docs.getgrist.com ? It’s the easiest way to see exactly what you intend (and allows collaborating on the same sample if you make it world-writable).
https://docs.getgrist.com/3Wz9zPkbVgv4/Activities
Does that work? This is a really basic example, but you’ll see that there are three types of activities, but the final solution could have dozens. Each activity type could have its own metadata that’s specific to the activity. For example, a driving segment will contain the GPS coordinates, but other activities might not. A book reading activity might contain a reference to the book itself.
So, the goal is to create a view of the entire day’s activity.
I’ve considered just having a single table with a choice list for the activity type, but that list could have potentially dozens of entries. That would also really complicate the columns since I’ll have more columns in that table than necessary since the metadata for each activity type is not the same.
Another option is to point to a reference instead of putting the details of the activity in the master activity table, but then I would need a column that points to a specific table for each activity, and that row could point to a corresponding row in the activity table for that activity which would contain the additional metadata needed.
A union, on the other hand, would let me query all of the activity tables at one time as long as the columns I want for the daily activity feed are in each table.
I tried looking at the document link above but I don’t have access, maybe turn on public access for viewers?
The newly announced Jupyterlite notebook custom widget sounds like it might be helpful for this. You can write Python code to pull in data from multiple tables using await grist.fetch_table(table_id) and then do whatever you want with them, e.g. combine them into one pandas DataFrame and then feed that into the plotting library of your choice.
I will take a look at that! In the end I decided to go with merging the tables into one and then using a reference for specific activity types that need additional metadata. Still, this new widget could be helpful. Thanks!
1 Like