When you say "Both of these tables are pulling from two different data sources" do you mean that they are syncing from other Airtable bases or they are coming into Airtable from outside sources via Zapier/Make/other integration?
When you say "Both of these tables are pulling from two different data sources" do you mean that they are syncing from other Airtable bases or they are coming into Airtable from outside sources via Zapier/Make/other integration?
@pressGO_design Thank you for the reply. Currently, I am manually exporting this data from an excel spreadsheet. Automating it will come later. Just trying to get the basic functionality down first.
Best. News. Ever. Since you’re manually pasting stuff in, you can play around with linking fields without messing up existing syncs and integrations.
OK. Step 1 is make a copy of your base. In the copied base, you’re going to create a table called Lead Sources and you’re going to put all your Lead Sources in the Name column of that table. You’re probably going to need to dedupe them, but since you’re already using Excel, you can probably do that there. Once you’ve done that, you link the Lead Source field in your Matters table with your Lead Source table. That will allow you to get the rollup you need for the #Matter(s) field.
Then, in your Bills table, you’re going to link the Matter(s) field to the Matters table. Important note: if you have duplicate Matter Names in your Bills table that do not refer to the same Matter, do not do this! Instead, create a data structure that uses a formula to create Client-Matter IDs (or do something else to ensure that your Matters are unique).
Once you’ve linked your Bills and Matters table via the Matter(s) field, you can use a rollup in the Matters table to calculate the Total for each matter and then use a rollup in your Lead Source table to get the total dollar amount for all the Matters per Lead Source.
Best. News. Ever. Since you’re manually pasting stuff in, you can play around with linking fields without messing up existing syncs and integrations.
OK. Step 1 is make a copy of your base. In the copied base, you’re going to create a table called Lead Sources and you’re going to put all your Lead Sources in the Name column of that table. You’re probably going to need to dedupe them, but since you’re already using Excel, you can probably do that there. Once you’ve done that, you link the Lead Source field in your Matters table with your Lead Source table. That will allow you to get the rollup you need for the #Matter(s) field.
Then, in your Bills table, you’re going to link the Matter(s) field to the Matters table. Important note: if you have duplicate Matter Names in your Bills table that do not refer to the same Matter, do not do this! Instead, create a data structure that uses a formula to create Client-Matter IDs (or do something else to ensure that your Matters are unique).
Once you’ve linked your Bills and Matters table via the Matter(s) field, you can use a rollup in the Matters table to calculate the Total for each matter and then use a rollup in your Lead Source table to get the total dollar amount for all the Matters per Lead Source.
Thank you very much! This worked perfectly. Your explanation was very easy to understand and accomplished exactly what I needed it to do.