I think you can do this with a little creativity in grouping items in a collection (assuming you need the grouping text to say just like what you lined out in your post).
Create a query column in your reviews table, and filter by reviewer company is this row > reviewer company, and reviewed company is this row > reviewed company. A rollup over that query would give you the total count.
Then construct your pluralized column (1 => 1 review, others => X reviews).
Finally a template so that we get X review/reviews for … from …
Add a collection and allow filtering by the reviewed company, and group by the template above.
Sorry, I forgot to say, I want to return this in a text field so I can put it in the Description part of a List. Not sure I can do grouping in the case?
“Create a query column in your reviews table, and filter by reviewer company is this row > reviewer company, and reviewed company is this row > reviewed company. A rollup over that query would give you the total count.”
I’m not sure I clearly understand what you’re describing here. Can you elaborate?
“Finally a template so that we get X review/reviews for … from …”
I’m also unclear here.
Hardest thing I think for me is getting a row with a) 34 reviews b) beside Company XYZ, then the next row (/n), with a) 6 reviews b) beside Company ABC …instead of having a) all the reviews lumped together (34,6) separately from b) the company names (Company XYZ,Company XYZ). Does that make sense?
I think from the company table, you can get the information you need. Is the reviews table storing the review from and review for on the person-level or the company level?
Say Thinh, from company A, reviews hirereach, from company B, or do you just store a review from company A directly to company B?
A relation establishes a link between a single row and one or many other rows by matching common values. So in the above case, you need something unique that identifies a Company in your Reviews table (usually a RowID), and then you can use that to create a relation between the two tables.
Important: A relation doesn’t contain any actual data. It’s just a link. That’s where the lookup comes in. Taking the above as an example, imagine that you have a CompanyID in your reviews table, and you need to know the Company Name. What you would do is create a single relation in your Reviews table that matches the Company ID with the same value in your Companies table, and then use a Lookup column to fetch the Company Name via the relation.
Create a Query or Multiple Relation column that matches the Company value in the Reviews table, and then use a Rollup column to get a count via the query/relation.
I think that may have done it, except for one thing.
I think I’ve got the “X reviews from Company A” part working, but when I try to break each result into its own line by adding “\n” between results, the line breaks are not working.
I’m using a Joined List field to get all the results into a cell in Glide, but in the Collection Description field that I want to display results in, it’s showing “X reviews from Company A\nY reviews from Company B\nZ reviews from Company C\n” instead of what I want:
“X reviews from Company A
Y reviews from Company B
Z reviews from Company C”
“\n” works in AIrtable but how do I fix this in Glide?
Last(ish) question on this…Now that I have an awesome list with line breaks, how do I add a first/top line to my list that displays some text that I wrote in a different cell?
Ugh, taking a step back, I’m actually still struggling with my core problem here (I thought I had it solved but I don’t).
Here’s where I’m stuck:
I have a Glide Lookup column named {ReviewFrom} that’s populated with the name of the reviewing company each time they reviewed for that Record/RowID:
Company ABC, Company XYZ, Company 123, Company ABC
Company ABC is listed twice because it has submitted 2 reviews for that Record/RowID.
I have a Rollup column that counts the total reviews from all companies (4), but that’s not what I want. I want the count per company in a Joined List column (or at least I think that’s the type of column I need to use).
For example (assume I’m using a Template column to connect the company name and Rollup column value), I WANT to list this in the Joined List column:
“Company ABC 2 reviews”
“Company XYZ 1 reviews”
“Company 123 1 reviews”
I do NOT want:
“Company ABC 4 reviews”
“Company XYZ 4 reviews”
“Company 123 4 reviews”
How do I get a column to do this? (Also, what type of column?)
You probably need a combination of a query column and a rollup column, but can we take one step further back, and can you show me what your Reviews table looks like?
