Filter rollup fields for a planning tool

Hi,

I am trying to filter a rollup field. Is that possible ?
I am trying to do a very simple planning tool but struggle.
Here is a loom that explains my issue in more details : Loom | Free Screen & Video Recording Software

Thanks !!

Charlie

1 Like

Create a template that joins the place and name together. Do that in any table that is needed for your relation. Then have your relation use those template columns. Then your rollup should only pull data for the matching place/name.

Thanks Jeff ! That works but it will quickly become unmanageable : the places I have are always changing. I have new ones and ones that I need to delete constantly. I could create an automation of course but that seems overkill. Plus let’s say I have 100 places and 100 names, that means I need to create a template with 10000 lines !
Is there really no other way ?

You only create a template column once and it dynamically populates based on the value in the place column and the value in the name column. I don’t understand how it would become unmanageable. It’s only two columns (one in each table) using values you already have in those table. How do you expect to create a relation based on both place and name if you don’t use a template column to join them together?

The template column can be as simple as ‘P N’ and you replace P and N with the Place column and the Name column respectively. I don’t understand what you mean by creating a template column with 10000 lines.

1 Like

I think there is definitely something I’m missing here. I really don’t understand the method you’re describing : I cannot add a name column to my place table as there can be many names to the same place. Still, being inspired by the concatenation of places and names that your method uses, I managed to do it by another method and here it is :

Do let me know if you find a better way ! (And please do explain the method you’re describing above, I would love to understand it :slight_smile: )

The template columns that Jeff was describing should be created in the Glide Data Editor, not in your Google Spreadsheet.

Thanks Darren ! The template column is simply used to join two existing columns. Using a template columns in Glide or a concatenate in Google sheets achieves the same thing.
The issue is elsewhere :wink:

To complicate things, I not only want the number of hours per employee and per place but I also want to be able to filter by month.
Basically I want to have the number of hours an employee works in a place for a given month…

oh, it can be used for much, much more than that.
But anyway…

How do you envisage this will work on the app user interface?
ie. Would you expect to first select an employee, and then select a month, and be presented with a list of locations and hours worked?
Or something else?
If you can describe your desired end result, we can help you get there.

In our case it is simply used for that, isn’t it ? Again, super interested in knowing more about this feature !
The desired end result is first to select a place, then select a month and be presented with a list of employees and hours worked.

Yes, but creating it in the Google Spreadsheet rules out the possibility of creating anything dynamic that will work in real time.

Okay. Please give me a short while and I’ll make a sample app to show how that can be done, that you can copy. (It’s quicker to do that than it is to describe all the steps).

Here you go. Make a copy of this, study it and let me know if you have questions.

Wow ! Thank you ! Now I understand why I couldn’t make sense of the method just with the indication “create a template”.
So the short answer is you create a “temp table” to store the values of your filters. That’s pretty neat ! The only thing is, because there is so many temp columns, the computed and filtered data is not stored anywhere (and I kinda wanted to be able to easily extract it).
I will look into it and see how I can mix all of these methods together to reach my objective.
Again thank you very much Darren and Jeff !

I must have completely misunderstood your goal. In your first video, you showed the Chantiers table which did in fact have a Place column and a Name column. It appeared that you were attempting to create a relation to the Planning table which also had a Place column and a Name column. That’s why I assumed that you could use a template column in each table to join the Place and Name and then have the relation use those template columns instead of just the place. This would have allowed the rollup column to only show a total for the place/name combinations that you had in both tables.

I now see that you have removed that Name column from the Chantiers table, so the problem is a little more clear to me.

The “Report” table that you see in the sample app isn’t strictly necessary, but I added it in this case as it keeps things a bit cleaner, and makes it easier to see how it works. Those user specific columns and screen could have been attached to any of the other tables.

This is possible, and this is where you’ll find template columns useful. Depending on what you want, all sorts of options are available - even creating a CSV file and emailing it yourself if that was what you wanted.

Now that you’ve had a look, there are a couple of other things about that sample that I’ll point out:

  • You’ll notice that there is no Google Spreadsheet attached. I did that deliberately in order to demonstrate that you don’t need any funky spreadsheet formulas or data manipulation to do any of this. It can all be done in Glide.
  • You’ll also notice that Person Names only appear in one table (Users), and Place names only appear in one table (Places). The Planning table doesn’t contain either, but rather references to each (Who/Where). This non-duplication of data is generally considered a good practice. It means if you need to change a name, it only needs to be changed in one place.
2 Likes