Our org is relatively new to Airtable and we are starting to design towards interface and analytics output. We are using Airtable as a mini- partner relationship management database and have a series of synced tables already set up with this rough structure:
Partner Table: list of partner names and metadata associated with the accounts
Case Table: list of cases our company runs, with associated metadata (e.g. region, industry, client name)
Practice Tables: teams indicate where a Partner impacts a case (yes, unfortunately this is set up so each practice has it's own table for other logic reasons; ideally this would have all been one master table)
Case X Partner rollup table: fed by all the Practice Tables, acts as a master for all the practice table activities. Data that rollsup is read only, but I can add columns manually and link other bases
The question we face now is that we want to report out all the interesting details about partners in our partner interface, starting withhow many cases did each partner impact (by year, by region etc.) The challenge is that we have some information about partners in the partner table, and some in CxP but they are not linked together to make a connection for us in reporting out.
Is there an elegant way to unify the two data sets, Partner and Case x Partner, together and report pertinent information from both sets? I have already tried hacking this using the faux-vlookup (using automation) but find it doesn't work well if a new partner name gets entered, it takes time for the new firm name to even be an option as it flows through all of our table sync logic.
Should we be using 2-way sync between the partner table and the CxP table? Or between practice tables and Partner tables (though more than one practice can use a single partner)? Should we simply sync Partner data set into the Case x Partner rollup table and run all of our interfaces from there instead? If so, how do I ensure the system links Acme Inc with Acme Inc in a read-only environment?
Any recommendations would be GREATLY appreciated!!!