Splitting a one to Many

[Garrett_HUssey]

Hello,

I have a table that has tickets and clients, often we have more than one client interested in that same ticket. So we associate all the clients to that ticket (making a many to one relationship). However, the problem is when we got to clients and the tickets that they are interested in we get a bad view. if we group by client we cat groups with multiple clients as the header, this is really bad when we have rollups and look-ups. Is there a to have Airtable automatically split them in certain views?

[Justin_Barrett]

Garrett_HUssey:

Is there a to have Airtable automatically split them in certain views?

Unfortunately not. If you’ve got multiple clients tagged on a single ticket record, grouping by the client link field will do what you saw, making some groups for multiple clients, and others for single clients. Why? Well, if a single ticket is requested by two clients, then Airtable would need to show that same ticket—i.e. the same record—in two different groups. For clarity/accuracy/other-reasons-I-might-not-be-considering, each record only appears once in any view, which is why Airtable makes groups for tickets that are requested by multiple clients. That’s where that one record belongs.

To get what you want will require a third table. Instead of directly linking ticket records to client records, you’ll do the linking on the third table. Here’s a rough example. First, my dummy ticket table:

And my dummy client table:

I named my third table {Requests}, and built it like this. The first field is a formula that gives you an at-a-glance view of the complete request.

When grouped by the {Requested by} field, it creates this:

As you can see, Clients A, B, and C all requested Ticket 1. Because each request is a unique record in this {Requests} table, each client has its own group, and that single requested ticket is shown in all three groups.

[typo]

For reporting purposes, it would be helpful to be able to break apart these one-to-many groups . I understand that AirTable doesn’t want to duplicate records, but maybe AirTable could add a block feature which would let you break apart the one-to-many relationship and then export that report as a CSV? Bosses and supervisors often want these kinds of reports. I’d love to have a quick way to generate one (without needing to completely restructure my data by adding/linking additional, mostly unnecessary tables-- each of which I would need to create every time someone asked me for a different break-down report)