Automatically Linking Columns to a MAster Table to Track Monthly Data (Record Label Streams)

[Phillip_Bodine]

Hey there,

I need some help with my airtable. I know it's possible but I'm relatively new and eager to get into database work. I'm now working on a database for my record label and want to create reports for my artists regarding their streams that they can get via a monthly e-mail that I will later automate. I'll break down the workflow to better explain it. And here is the Airtable for you to look over: https://airtable.com/shrQtfIpRU8JlMScQ

1) At the end of every month. I got to the Music Distributor's dashboard (MDD), which only I have access to, and go look at the stream count for the selected period (every month). The table that is presented is each individual track NOTE: I cannot filter the table in my (MDD) meaning the order in which the tracks are presented is only based on stream count. So the order is ever-shifting.

2) With a plugin, I scrape the HTML of the MDD's table and convert it into csv and import it into airtable as a new table, "Month YYYY." Meaning each month has it's own table. MDD Table in CSV TRACK, UNITS 'Yellowma-Oh, Hello',279 'Benni Matern-Graf Bravo', 215

NOW: Here is what I need help with.

3) I want that when I import the monthly stream CSV, the table can automatically look up the master table (ISRC), which connects the monthly stream to the artist, album, and unique ISRC code. Because if I manually do this, I run the risk of making mistakes as well as scaling issues.

4) Every month, send out a customized report of all the streams for each artist showing which tracks had how much but only for that artist.

5) I want to be able to create a dashboard where I can flip through the different releases, tracks and stack different monthly time periods.

HERE IS where I need help: 
A) What would I do so that when I copy and paste the month's streams that the new streams automatically "look up" "call" and "retrieve" the artists name and track.  
B) How should I set up the table so that I can then build so I can set up a record review that is interactive for example I can click the artist name, tracks made by the artist, and the time frame and immediately see the stream count. 

Let me know, 

Cheers

Phil

[TheTimeSavingCo]

Hm, if I were you I would try to consolidate the stream data into a single table instead of having one table per month.  You should be able to import into existing tables I believe

> 3) I want that when I import the monthly stream CSV, the table can automatically look up the master table (ISRC), which connects the monthly stream to the artist, album, and unique ISRC code. Because if I manually do this, I run the risk of making mistakes as well as scaling issues.

If the CSV has the ISRC code, that'd be best.  You'd could then switch the primary field of the ISRC table to be the ISRC code, link it to the stream tables, and then just import it into a linked field to the ISRC table.  Doing this will automatically link everything up

If the CSV does not have the ISRC code and only has the track and artist name, then you could try converting the primary field in the ISRC table to match that format exactly.  Importing should again link everything up as needed

> 4) Every month, send out a customized report of all the streams for each artist showing which tracks had how much but only for that artist.

In the Artists table, create some rollup / lookup fields to get the data you want to send out, then conditionally filter said field by the date range you want, e.g. Date is within the Last Month.  Then make an automation that'll just run on the last day of the month or something

> 5) I want to be able to create a dashboard where I can flip through the different releases, tracks and stack different monthly time periods.

Check out Interfaces for this

I think my answers above also cover A and B in your post, apologies if that's not the case