Hi,
I’m building an app for my company that currently has a tab called Groups, which has a few columns “Date” " Group Members" and “Staff”. The latter two get there data from choice components that grabs names from our “Staff” and “Consumers” sheet
The problem is that I would like there to be a tab called “Attendance” where one could select a month. Then the sheet would populate rows based on
-
If the consumer is “current” ( I would add a boolean column to the Consumers table for this) then a add a row.
-
If they are current, then check for each day within the month selected that they were assigned into a group. If they were, then for that day, set the value to 1, if they were not, then set it to 0.
-
Finally, after this was done for each day in the month, total the number of 1’s in a total days attended column.
I’ve researched for a few days on how I would set this up but I keep getting confused on where to even start, or if it was even possible based on how I set up my data tables. Our company used to pull this report by manually updating a bare bones excel sheet
-and I thought this could be done dynamically within our app instead. Would love any direction on this!
I see what you’re trying to do, but i don’t think this approach will work.
I’ve built several attendance apps, and I can say that it isn’t an intuitive process given Glide’s limitations when it comes to clearing multiple rows of data at once.
You’ll also want to make sure you’re adding attendance records as efficiently as possible. I’ve seen some apps that record each day for each member on its own row but that will quickly exhaust your row quota.
The most efficient way I’ve found to take daily attendance (as long as you’re only using two attendance codes like true/false or present/absent) is to actually use a multiple choice component.
I plan on making a daily attendance template as it’s a request I see often in this forum. Let me know if you need further guidance!
1 Like
@Robert_Petitto makes some good points as far as row usage. You could eat up rows real quick if you are creating multiple attendance rows per day.
Ignoring that for now, I will also add that the layout for your dynamic table may not be ideal depending on what you plan to use it for. I would almost have to say that a row per consumer per day would be ideal, but it all depends on your use case.
But, let’s try to get what you want. This is a rough guide off the top of my head, so you may need to make adjustments as necessary:
- First you need to add a single value column to you Groups table, that will retrieve the chosen month from whichever table holds the month you selected. Preferably that value would ultimately be a number in MM format or maybe YYYYMM format.
- You will also need a math column in the Groups table that will return the month from the date column
MONTH(Date) or year and month YEAR(Date)*100+MONTH(Date)
- Create an IF column that returns the Group Members list if the single value and math columns match.
- Create a Split Text column to convert your IF column comma delimited Group Member names (preferably these would be ID’s) into an array.
- Also create a math column that returns only the day number.
DAY(Date). This will be used below.
- Next I would create an Attendance table with enough rows to hold the maximum number of consumers you will ever have in a month. It won’t grow automatically, so you’ll have to plan ahead.
- Next I recommend following the second use case in this tutorial to get automatic row numbers. How do I create an auto-incrementing row number with Glide Tables?. As an alternative, you could skip this and instead create a number column and manually number each row starting with zero.
- Next create a Query column the retrieves rows from the Consumer table where the Current checkbox is checked.
- Next add a Single Value column that retrieves the name (or preferably ID) of the consumer from the query relation, but use the row number to get the nth from the start name/ID from the query relation. Now you should have a dynamic table of current active names.
- Next you will need to create a relation that links the Name/ID to the split text array created in the Groups table.
- Create a Lookup column that returns the DAY math column as an array of day numbers
- Now for your 31 day columns, you will need to create them as IF columns. Each IF column will check if each respective day number is included in the lookup array. If it is, then return 1, else return 0.
- Finally, you will need a math column that adds the numbers from each day IF column. It will be quite large because you will need 31 replacement values in you math formula.
I think that should give you a table that resembles your screenshot.
1 Like
Thank you both for the suggestions. After a lot of tinkering, I ended up going with a slightly different solution. I recreated the old excel sheet in google sheets, where there it dynamically updates based on what I originally outlined. Then that sheet is displayed as a data grid tab in glide. The only hiccup I have now is figuring out in glide how to change the date in cell A1 which is my month year picker in google sheets that updates the entire table.
