May 29, 2018 12:22 PM
I am creating a recording database. For each recording (track) I want to be able to select from a list of contributors (vocals, guitar, producer, engineer, etc,etc) to assign to each track.
Example:
For “ExampleSong1” the producer is called “Team1”
“Team1” is made up of Individuals John Smith and Bob Jones
Bob Jones is also vocalist
John Smith is also guitarist
the song should be credited
Producer: Team1
Vocals: Bob Jones
Guitar: John Smith
I have two tables: Individuals, Groups
Individuals
contains First, Middle, Last Name.
Column 1 is the formula: {First Name}&" "&{Last Name}
e.g.
John Lennon
Paul McCartney
Groups
links to Individuals table (allows linking to multiple records)
Column 1 is the Group Name
e.g.
The Beatles
I want to be able to select from Individuals and Groups in a single list in another table:
e.g.
Paul McCartney
John Lennon
The Beatles
I have created a table called Contributors, but I can’t worked out how to combine records from Individuals & Groups
Is this possible?
May 29, 2018 04:35 PM
It isn’t possible to reference to different tables (Individuals & Groups) from a single “Linked Records” field.
I’m not totally clear on the structure of your database, so I’ll do my best to word what I see as potential solution sin a way that I think will fit into what you are doing there…
1 solution is:
In your Contributors
table - which it looks to me like you are linking to a Track
record - you could create another field that checks which of the two has been selected for a particular role, and fills with the one that has been selected. It means 3 fields in place of one in the Contributors
table, but ultimately you can do a Lookup on only the 1 “Producer” field from the Tracks
table, so the Tracks
table will still only show one field for “Producer”:
Another solution is:
Collapse your Groups
table into the Individuals
table (and perhaps rename the amalgamation to Entities
to make it more clear). Create self-referencing links within the table to define groups, and add a “Group Name” field. the “Name” can display “Group Name” if it exists, otherwise “First Name” and “Last Name”; this resolves the problem of multiple “Producer” fields in the “Contributors” table, but makes “Groups” less dynamic as an entity:
Neither solution is a golden bullet, but one might get you where you want to go… :man_shrugging:
May 30, 2018 02:25 AM
Hi Jeremy,
Option 2 solves the problem perfectly. Thank you for taking time to answer my question. Really appreciate it!
May 30, 2018 03:14 AM
You have this very similar thread: Conditional Grouping?