When your data has inconsistent numbers of fields?

[Joshua_Vern]

I am having difficulty finding the correct terminology to explain my issue, so I will give an example. I have created a demo table to help illustrate: https://airtable.com/shr9kkOX5v8ic8Rez

Let’s say that I work in intellectual property and I need a table which will show what percentage of a work is owned by whom.

First, I have my table of songs. This consists of: Song Title, Artist, Composer(s), and Publisher(s). Everything fills out nicely, until I get to the composer(s)/publisher(s) fields. Since different songs have varying numbers of composers, I linked it to the composers table, and checked “Allow linking to multiple records”. From here, though, I also must assign what percentage of the song is owned by whom.

Michael Jackson’s song “Pretty Young Thing” was written by 3 people. I would like to be able to show the following data in the Composer field:

Michael Jackson, 75%
Keith Ernesto Harris, 5%
William Adams, 20%

How can I attach the respective percentages to the Composer, while keeping the composer names linked to the Composer table? Currently I have two fields, the first linking to the composer table, and then the second being a simple Long Text field with the above info. This works fine, excepting that the composer info must be entered twice for every song. I would prefer to avoid this.

I have considered having a separate field for each individual composer, then placing a percentage column next to each, but then comes the issue of one song having one composer, while another has four or five. This solution would leave a lot of empty fields, which I definitely don’t want.

I have been trying to find some way to write a formula around this, but I can’t seem to find an in-depth enough explanation of the formula function to help me (The formula field reference is helpful, but I’m still not sure of what approach to take with this data.)

[Katherine_Duh]

I suspect that one answer to your question is to treat the intersection of composers and percentages as line items. (If you read our guide to many-to-many relationships, it can give you a better idea of how to use line items in Airtable.)

My suggestion would be to create another table for “Composer percentages,” so that each composer’s percentage of a given song is itself a record. So, setting up something like this (and using a formula in the primary field to automate record name creation):

Then, in the song table, you have linked records representing each of the composer-percentages for each song.

You can also put in a lookup field to pull just the vanilla information about the composers’ names, without any percentage information:

A potential drawback of this setup is that it takes two clicks through linked record fields to get to the composer data, rather than one (you need to click on the linked record line item, then on the composer link in the composer percentage line item). The upside of this is that you would have somewhere to put information on each composer’s contribution to a particular song.

Let me know if you need more explanation, or if you have any other questions.

[Joshua_Vern]

Thank you! This does a pretty good job of laying out the data how I’d like. The majority of people will only be looking at a small selection of the data pool, and I will be one of the few using the junction table, so this could be a relatively practical solution.