Trying to create an dynamic table

Ask for Help
3

@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