Transpose columns?

In Pages I have a column for each client and their status with respect to a given document, if any.

Looks sort of like this:

I now need to essentially transpose that data so that it is also in columns by status or can be displayed as if it is. I essentially want to do two things.

  1. I want to be able to display that data in a list where the list or lists are grouped by status, I.e.:

Review
Doc 1 - Client A
Doc 2 - Client B, Client C

Approved
Doc 3 - Client A
Doc 2 - Client D

  1. In a list of all documents, I want to include the name of the client and their status, sort of like this.

Doc 1 - Client A: Review | Client C: Draft | Client D: Returned
Doc 2 - Client B: Review | Client C: Review | Client D: Approved

Is there a way to do both by essentially transposing the data in the data editor or using a template? Or some filter trick in the collection I haven’t thought of?

I’ve seen @Robert_Petitto’s Miracle Method, but that seems like more than I need here…

Having each Client as its own column raises a red flag for me. I think instead, you’d want the statuses as column headers and a comma-separated list of clients for each document & status.

2 Likes

What are some of the red flags? I’ve already begun implementing it with each client having its own column, but not too late I suppose to change course.

But also, if I do as you suggest, how might I achieve the display in my second example?

As @Robert_Petitto said, it is a bad idea to make separate columns for each client, add 3 status columns, and place clients’ IDs there… so instead of Client A, Client B… create Review, Draft, and Approved columns.
This way, you can have dynamic Docs and Clients.
Then create an inline list that will get relations to users’ IDs… so you can generate Clients’ statuses for each document, In the detail view.
But if your Client list is fixed, then it is ok to do your method.

It’s a red flag because user data and identifiers don’t belong in column headers. Glide can’t use data within column headers to make relations or create filtering and conditions.

Instead, I would do as I suggested above and then do the following:

2 Likes

Thank you both. And particularly @Robert_Petitto for the very clear video. Wow!

I suppose I should have made one thing clearer. ā€œClientsā€ in this instance are just an organization…(e.g. ā€œThe Coalition on Good Ideas.ā€ )they aren’t users in the user table. I can see the alarm if I was going to put user names or other data that relates back to the user table as a heading. Also, not sure if it matters, but setting the status (review, draft, etc). is something that is done by an admin user.

In any event, I think I’m convinced that making the status the column header is probably better. But I have some followup questions based on what you’ve presented. First, a quick explanation:

I have the list of different ā€œstatusesā€ in the first column of a table. And on any given document, click a button to see a screen that has the column of each client, and a choice component that reads from the table listing the status and writes it in the column/row for that client/doc.

Question 1: Since I’ve already started implementing it the wrong way :joy: I wonder if you have any quick suggestions about how on the back end or in an excel sheet I might do a one-time transposition of what is already there.

Question 2: Bob given what I explained above, is there a simpler way to do what you show in the second half of the video? If not, can you share your code in the javascript column with me to get me started? One other thing on this: I don’t need it to have piping or any of that. If there is a way to display the same information with existing Glide Components…fields maybe…? that would work