New to coda, be kind. Hoping someone can point me the right direction.
I am building a database of Aus Music Chart history. I have an Excel sheet that has columns like Song, Artist, Year, Position, etc. As a test, I’ve copied and pasted some 3000 rows/records into an coda table. Let’s call it ‘Master’.
What I want to do now is create another table called ‘Genre’ that will automatically update the ‘Master’ table (as described above) based on a given Artist’s name. So, for instance, if I enter “The Beatles” and assign them the Genre “Pop” in the Genre table, it will automatically assign the value ‘Pop’ into the Master table wherever Artist = ‘The Beatles’.
Does that make sense? I’ve played around with relationship/linked Lookup fields, but the best result I’ve had is having to re-input the Artist name in a separate field – i.e. it’s not automatic. As I said at the start, there are already 3000-odd records as a test – my master Excel sheet is well over 25,000, so entering each again manually is out of the question.
I’m thinking it might be done via a formula but am not getting it right. I’m thinking if might sound something like (in the Master table):
If the field ‘Artist’ in the Master table matches the field ‘Artist’ in the Genre table (i.e. they’re both The Beatles), then get the value of the field Genre from the Genre table (Pop) and insert it in the field Genre in the Master table for the matched Artist.
Can you share the doc you are working on now and I could jump in to help a little.
You can try importing the two tables as CSV, then create a formula with some Regex to find the relations and then copy paste a few hundred relationship at once from the formulaic column to the editable one.
The Genres table is only a couple of rows, just as a concept. Any help very much appreciated, I’ve been mucking around with it all day and not making headway but am sure there’s an answer there somewhere.
I had a look at your doc and can say the following.
You need a paid account as the amount of data is to big for the free tier.
What you want is perfectly achievable with minimum amount of manual work.
You will have to clean up your Excel data set first so that when you import it in Coda as a CSV then Coda can do the heavy lift and auto detect the values.
I would have to see how is your Excel data set structured so that the importing is easier and with minimum of manual work.
I would say that the best in your case is if you want we can go over Zoom or whatever and can guide you live to prepare your Excel and making the doc ready for the importing… and no you do not have to pay for anything.
You need a column describing the genre in your artist table - or, the other way around (but less practical), you need to add the artist(s) in an extra column in your genre table. I opted for adding a column in your artist table.
In the song table I changed the formula in the last column to show the genre(s).
