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 
)
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 
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