Add Row - Capture Data from Multiple Rows

Sure, I don’t have them set up exactly how I would want them because I was just trying to get it working first.

On the Monthly Challenge table, I originally had one column named “Complete” to designate when the workout had been completed, but then I tried adding them individually denoted by “C_‘workout name’” because I wasn’t sure how to capture the individual values for the new row in the records table for each workout if they weren’t stored in their own column.

Okay, yes I see.

mmm, what you have there is a little cumbersome, and there will be some challenges. For example:

  • In your records sheet, you have a single column for Notes. But in the Challenge sheet, you might have several rows with Notes attached. So to condense all those into a single row you’ll most likely need a joined list.
  • You also have a bit of redundancy there. For example you have a Weight Deadlifts column, but as far as I can see that only applies to a single row in your Challenge sheet?

To make this more manageable and easier to summarise, you probably should consider restructuring things a bit. I could make some suggestions, but I’d need to think about it for a while.

But getting back to your original question, to summarise/condense several rows into a single value, you need to be looking at Joined Lists or Rollup columns.

2 Likes

Yeah, the two tables don’t match up well currently because I played around with them after I originally created them and changed columns in one table without updating them in the other. I’ll set it back up for what would be ideal and repost screenshots tomorrow.

Thanks, I’ll look into Joined Lists and Rollup columns. I’m pretty new to Glide and I’m not very familiar with those.

Thanks for your help!

No worries.
I’m kinda busy right now, but I’ll try and have a think about it later, and if I come up with any ideas will let you know.

1 Like

I went ahead and set up my “Monthly Challenge Records” table how I would ideally like to have it with some example data.

Now I need to:

  • figure out the most efficient way to set up the “Monthly Challenge” table
    • My initial thought would be to have single user-specific columns for values such as “weight”, “notes” or “complete”, that would be shared between several or all workout types. It would make the table much cleaner with fewer columns and would be easier to capture the data.
    • The thing that seems trickier about that is that I then have to parse out which workout the weight or other value corresponds to in order to record it in the Records table.
  • figure out how to add all of that data to the Records table in a single row using Joined Lists or a Rollup as you mentioned
    • planning on reading over the Glide documentation on these tomorrow

In case it helps to understand it better, I’m basically trying to convert this paper document into a tab within my “Workout Gym” app. Each month, members from the gym complete the same workout and try to improve on their previous one. I’d like to be able to store each month and have users be able to see what their previous weights, times, etc. was and for their trainer to be able to have access to all of their workouts so they can see their progress.

Okay, so after thinking about it (whilst I was driving home :slightly_smiling_face:), I would actually
approach this quite differently. Here is what I would do:

  • Firstly, I’d get rid of all those User Specific Columns in the Challenges sheet, and just leave one - a boolean.
  • This would be used for two things:
    • To indicate that each user had completed that challenge (it would be User Specific)
    • To allow filtering the challenges in a list view (“only show me the challenges I haven’t completed”)
  • Secondly, I would restructure the Records table so that it will contain one row per user, per challenge, per month (this would make things more expensive in terms of row count, but unless you have hundreds of users that shouldn’t be too much of an issue - and you can always archive older data if it does become an issue)
  • The Records table would have the following columns:
    • Date (when the challenge was completed)
    • Challenge ID (a reference back to the Challenges Row ID column)
    • User ID (a reference to the Users Row ID column)
    • User Notes (obvious, but specific to that challenge)
    • Result (result or outcome of the challenge)
    • Metric (see below)

Looking at your last screenshot, I can see that with most challenges you’re also capturing a “metric”. In some cases this is a duration, in others it’s a weight, or something else. I would combine all of these into a single column and call it “Metric”. The fact that you’re mixing apples and oranges doesn’t really matter, as you will be able to filter them later to make sense of it all (more on that in a bit).

So… with the above in place, and assuming that a user would be looking at a details view of a challenge when they complete it, I would:

  • Present whatever input fields you need to collect the associated data for that challenge, eg:
    • Notes
    • Result
    • Metric
    • (NB. You’ll need User Specific columns for each of the above to use as “temporary storage”)
  • Present a button on that screen. The button would do the following:
    • Set the user specific column in the Challenges table to true, indicating that the user had completed the challenge
    • Add a row to the Records table. All the information you need should be available at that point.

Doing it this way will make it much easier to work with in terms of generating summaries and reports.
Here’s one example of what you could do. Let’s say you’d like to know the Average Deadlift Weight for all users. You could do that like so:

  • Create a template column in the Records table that contains the ID of the Weight Deadlifts challenge
  • Create a multi-relation that joins that template to the Challenge ID in the same table
  • Now create a rollup column that takes the Weight Deadlifts → Average via that relation

That’s just one example. There is lots of other funky stuff you could do as well.

Hope that helps :slightly_smiling_face:

3 Likes

Thanks for taking time to think through that and write out your suggestions!

Initially, I’m concerned with recording a new row in the Record table for every workout for every user. That would be 14 rows per user, per month. The gym has about 100 members. I’m hoping to store at least 1 year’s worth of data for these challenges so users can track their progress over a year’s time. That would create 16,800 rows after a year just for that part of the app.

However, I’ll play around with your suggested setup to see how it works.

Is there any technical way to get the values into the single row like I displayed in my example Records table?

You’re building with a spreadsheet mindset in a database world. Working in the Glide table, that looks like a spreadsheet, doesn’t help. I am going through this learning curve as well. You may have to do a little bit of reading about relational databases.

Relational Database Schema Design
Airtable’s guide to many-to-many relationships

2 Likes

Wow that’s a lot to think about whilst driving. Me = ‘why is there so much traffic on the roads today’ :wink:

2 Likes

Singapore traffic isn’t that bad these days :grin:

2 Likes

Yeah, I’m trying to figure that out again, but it’s making my brain hurt haha. I majored in Computer Information Systems, but I only had one SQL class and that was 10 years ago so I don’t remember a whole lot. Thanks for the links, I’ll check those out.

I’m going to re-setup my table how I originally had it so that there aren’t redundant columns. I’ll post a screenshot once I do that.

The issue I was facing is that it seemed as if the Glide Action to Add Row wasn’t able to add anything except the first row of the table. Maybe I’m missing something like needing to setup some Relations or something.

Okay, here’s how my table is currently set up and it currently has values filled in to give an example of how it would look once a user completed the challenge.

Here’s what an example screen looks like to show how the data is collected:

And here is a screenshot of the Google Sheet to show how I would ideally have the data recorded after the challenge is completed.

As I mentioned previously, when I currently try to record the data to the Google Sheet, it only pulls the values from the top row of the table.

@Darren_Murphy, I wanted to give a more detailed reply to your post after I’ve had more time to think through it. My above posts may help clear up some details about what I’m trying to do as well.

If I do this, I’m confused how I would capture unique user data. Later in your post I saw you mention user specific columns for “temporary storage”, but I don’t understand where those would exist if they’re not in the Challenges sheet. I currently have them there and am using them as temporary storage that would be cleared out after it’s recorded to the Records sheet.

I have this set up.

I currently have a filter set up like this with two inline lists to show “Remaining Workouts” and “Completed Workouts”.

As I mentioned above in more detail, I’m concerned with how many rows this would create over time and I’d like to keep data for historical/comparison purposes.

I don’t have a reference back to the challenge ID in my updated Records table. I’m not sure if I would need it they way I’m trying to do it, but I could add it in case. Instead of User ID referencing the Row ID, I have one referencing email, but I think that will accomplish the same thing? I have the rest in place.

I have added this. The only thing I wasn’t sure about what was kind of Column Type to make it. I have it set for Number, but I’m not sure it’ll be entirely obvious to the user that it needs to be a number. I’ll have to think through how to make it clear about what to enter for “Metric” and what to enter for “Notes”.

Your assumption is correct, it’s a details view and I included an example of that in a screenshot above.

I have these set up as you can also see in the above screenshot.

I have this set up as well. My issue as I’ve mentioned is that with my current setup, I can’t figure out how to get the “add a row” action to add the info I need. With your suggested setup, this would be easier, but as I mentioned I don’t know if it’s feasible due to the number of rows that would be generated.

I would like to have summaries and reports, mostly for an individual user to compare their own progress to themselves. I’m not sure how much comparison between other users would be needed. I’d like to have reports for the trainers to easily see (I haven’t worked on setting these up yet, but I plan for them to be in a tab that’s only visible to trainers). I’d also like users to be able to see their own progress. I’ve started on this, as you can see in the above screenshot that includes a graph of the weight used each month for squats. I’d also like to create an overall view that shows all of the workouts at the same time.

It’s definitely helpful, thanks! I’m just not sure what to do since I think your suggestion of recording individual rows for each workout type is going to end up with too many rows in the long run. But I’m also not sure if the way I’m currently trying to do it is technically feasible.

I’ve been reading through other posts to try to learn more and I found one from @Robert_Petitto that may help in my situation.

Robert showed a way to add multiple rows at a time and within that, he talked about using a single value column to pull up data so it would all be on the top row. I’m going to try that to see if it will allow me to get all of the data for each workout type on the top row.

*Update: I think this works! This will at least allow me to get all of the info onto a single row in the Records table. I will now need to figure out how to parse that data back out from that single row to get it into reports.

Are you sure about that?
A Single Value (as the name suggests) populates an entire column with the same value in every row. I don’t think that will help in your case.

I’m thinking that you’ll probably need to use some variation of the trebuchet method.

2 Likes

I’m not positive, but in my testing so far, I think it does. As far as I can tell, it doesn’t matter that the entire column has the same value, because when the “Add Row” action is performed, it only pulls the values from the top row, so all of the other rows are ignored. Does that make sense or am I missing something?

For example, the “Push-Ups” workout is on the second row, so I create a Single Value column that is configured: “From start”, “2”, “Notes”. This pulls the value “Toes=all”. When I go into the action for “Add row”, I can now select this new Single Value column and it pulls the value in the top row and spits that out into the Record sheet.

I’ll look into this sometime soon to see what it’s about.

ah yes, I see.
yes, that’ll work fine as long as there is only one value in the entire column that you’ll actually need.
However, what about your notes? Presumably you’ll be expecting two or more rows containing data? For that, you’ll probably need to look at a joined list. Keeping the notes in context may also be a challenge.

1 Like

For notes, yes, I would expect that multiple rows would contain data. The same would go for metrics. However, my plan is to make a single value column for each value I need.

i.e. Push-ups notes and sit-ups notes would both need to be recorded on the Record table. So I would have a single value column for each that pulls the note for the corresponding workout.

It makes the Challenge table kind of messy and redundant looking, but I’m not sure if that really matters? It’s basically pulling all of the unique data points from the 13 rows (workout types) up to the top row.

I think I understand you to mean that it would be difficult to keep them in context assuming they would be in a joined list, but since I’m not doing that, it’s simply a direct map to the specific column.

1 Like