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.)