Multiple "Group by"

In an effort to reduce Glide Bugs occuring, I was wondering if there is a way to Group Rows of Data via Multiple Criteria? Similar to a Google Query using GROUP BY X,Y,Z ?

I currently do something similar to below where I query my data and group it via multiple criterias so it’s ready to be displayed in Glide in the format I want to.

image

My goal is to be able to display a list of Stores for Tony (similar to the bottom of my above screenshot). There would be 2 rows in my inline list, one Wal-Mart and one Target. The raw data contains 2 rows for each store, so doing a simple relation to the raw data would still give me multiple results per store.

What exact “bugs” were you facing?

You can sort of do a “unique” setup in Glide.

  • Create a rowID column for the first table.
  • Create a relation from the Store column to itself, make it a multiple match.
  • Create a single value column on top of the relation above, returning the rowID from the first match.
  • Create a rollup column on top of the relation to count the number of orders.
  • Show the inline list of the first table, filter by rowID is the same as the “first match” rowID in step 3. Display the count from step 4.
2 Likes

When users submit data via a Form or action (Add New Row), I often get data from other users being sent instead of the submitting user. For some reason the UI displays the proper data but when they click submit, it sends the wrong data.

I assume this is some bad luck from data being refreshed from Google Sheets and Glide and the fact that I manipulate my data via Google Sheet Queries to format it nicely in Glide afterwards. So rows of data in the tab pulled via a Query can move up or down on depending on the incoming raw data. I guess Glide does not like that.


Thanks a lot for the suggestion. Seems a bit complicated for a simple problem but will def try it! :slight_smile:

That does sound like something wrong on how you’re setting that flow up. Are you using a native form or a custom form? Can you share with us how are you setting that up?

That should not be a Google Sheets problem. The query, if you structure it right, should also work, albeit taking a bit of time for the calculation to happen. I would still move all that logic to Glide for the best performance, though.

Here is my flow from Google Sheets to Glide :

this is just an example, in reality the amount of data is 10x that

Google Sheets tab 1 = Every items that needs to be purchased, each of them is assigned to a Driver. All rows here data that is copy pasted from our CMS and supervisors manually assign each row to a Driver via a dropdown. The rows here can moved up and down (ie they could insert a new row at row 3 to add a new line or they could decide to move row 2 to the last row.)
Google Sheets tab 2 = Aggregation of Store & Drivers

Glide Part 1 = Google Sheets tab 2 = A list of Store, filtered based on each users name.

Glide Part 2 = Form Screen with an in-line list based on Google Sheets Tab 1.

Glide Part 3 = Edit Screen where they input the quantity for each item.

When done, they take a picture of their receipt and submit the form (ie Glide Part 2). The submit will create a new row on an other sheet based on the data on the screen.


So the issue comes from when they click submit in the form of Glide Part 2. For some reason, it sends the wrong data. I suspect it is linked a combination of those two factors :

  • Data in Google Sheets Tab 1 being moved around by my supervisors.
  • Users staying in the Form Tab for long periods of time (sometimes they need to purchase 15 items at the same store).

Example would be :

John submits receipt for Store XYZ. Items are Hammer and Nails.
Data sent to Google Sheets is Bobby for Store ABC. Items are Drywall and Lumber.
The only data point that is correct is the receipt. But the data attached is wrong.

I will say this…I do use a query for one of the tables in my app, but it is strictly for display purposes and to build relations. For me, it’s taking 3 column values from another table and creating unique combinations. I could use @ThinhDinh’s solution for finding unique rows, and I might someday, but I came up with the query solution long before we had much of the functionality that we currently do in glide today. In my case though, it is very unlikely for the order of the queried data to ever change. Data in my queried table does not move around.

I think you’ve already identified the problem you are running into. What you are doing is providing a moving target for glide to track where a row is located. Glide may be looking at Row 3 for example, but all of a sudden the data in Row 3 changes to something else because the data in the table was moved around, so then the wrong data is being used. Data shouldn’t really move around like that in a database. It’s probably fine if it’s done during off hours when nobody is using the app, but if the app is actively being used and data is jumping around like that, then that can cause problems.

Ideally, the supervisors wouldn’t have their hands in the data like that. You could easily create an app or page as a front end for then to assign drivers. You can also control sorting within the app/page without messing with data itself. I think that would solve a lot of your problems right there.

1 Like

Cool, at least it kinda confirms what I thought is the problem. I would love to build them that UI but they kinda need to mass copy and paste new items multiple times a day. My probable solution is to try something like ThinhDinh proposed.

Thanks everyone.

1 Like

I think the copy and past part is probably fine. Just don’t let them move stuff around. A simple UI could be whipped together pretty quickly with some basic sorting and the ability to assign drivers. It might be overall easier for the supervisors. All they have to do is copy data into the sheet, then open the app and take care of the assignments.