Linking clients/accounts between Project tracker and Sales CRM

What you are wanting is possible.

You might want to check out this link

Linking Between Tables - Airtable Guide

With Airtable, you can create relationships between individual records.  Building on our Martian example, we can connect our Mars supplies with the vendors that manufactured them.  This will display the name of the vendor in-line and make it easy to...

Andrew_Johnson1 wrote:

What you are wanting is possible.

You might want to check out this link

Linking Between Tables - Airtable Guide

With Airtable, you can create relationships between individual records.  Building on our Martian example, we can connect our Mars supplies with the vendors that manufactured them.  This will display the name of the vendor in-line and make it easy to...

Hi Andrew,

Thanks for this link. Unfortunately this doesn’t work out because it refers to linking within the same database (linking different tables in the same app). I’m looking for a possibility of linking tables between 2 apps (CRM and Project Tracker).

Best regards, Sabine

Sabine –

You’ll have to combine the two bases into one to be able to take advantage of the synergies between them. I know that sounds daunting, but it’s not: Using the off-the-shelf Sales CRM and Project Tracker bases from Airtable templates, I was able to merge the two bases in under 10 minutes.

Unfortunately, describing the process has taken me, so far, more than 10 times as long as it did to carry out, and the law of diminishing returns has kicked in with a vengeance… I’m going to collapse for the night and try again in the morning, refreshed.

Let me expand briefly on what @Andrew_Johnson1 said: Yes, it is possible — but only if you combine the Sales CRM and Project Tracker apps into a single base. Ths is because Airtable currently does not support the linking of tables belonging to different bases.

There is also no handy utility or mechanism, as far as I know, to facilitate the merging or blending of bases. Fortunately, there appears to be little overlap or conflict between the two bases — with the exception of the account/client tables you wish to merge.

As a test, I just combined the off-the-shelf implementations of the Sales CRM and Project Tracker bases, using the dummy data supplied with the templates. It took about 10 minutes to merge the two bases and rebuild the links, with no loss of data. (However, see the comment on collaborator fields in Item 4, below.)

This process assumes you haven’t customized either base extensively. However, if you have, you should be comfortable enough with Airtable to understand what modifications are needed to the following instructions.

Here’s the process I recommend:

0. 
   

   First, always merge the smaller base into the larger. (Not that earthshaking a revelation, I know.) In this instance it means merging Project Tracker into the Sales CRM.

   
   


1. 
   

   As you noted, the obvious common point is the [Clients] (Project Tracker) / [Accounts] (Sales CRM) table. These are the tables you need to combine. Accordingly, begin by dealing with the other Project Tracker tables first.

   
   
   
   
   1. 
      

      In Project Tracker, open the [Design Projects] table.

      
      
   
   
   2. 
      

      Create a new Grid view with no fields hidden and no filters applied.

      
      
   
   
   3. 
      

      Click the ‘three dot’ extension to the view menu and select ‘Download CSV’. The contents of the table are saved to disk in CSV format.

      
      
   
   
   4. 
      

      Open the Sales CRM base. (Keeping both bases opened in different tabs or monitors will help speed the process.) Click the plus sign (’+’) and select ‘Import a spreadsheet.’ Browse to the CSV file saved in the previous step and import it.

      
      
   
   
   5. 
      

      The table imports as ‘Imported Table’ with each field configured as a single-line text field. Proceed through the table, right-clicking on each field and changing its configuration to match that of the original table. For now, leave linked record fields unchanged.

      
      
      
      
      1. The {Project Photos} attachment field will not convert correctly: When the configuration changes, it clears the cell values. Go to the original table in the Project Tracker base. Click the {Project Photos} cell in Row 1; scroll to the bottom of the table and while holding down the Shift key click the cell in the bottom-most row. A message will appear in the lower left of the screen stating ‘## cells selected.’ Press Ctrl-C; a new message pops up, reading ‘## cells copied.’
      
      
      2. Return to the Sales CRM base. Click the {Project Photos} cell in Row 1 of, um, [Imported Table] and press `Ctrl-V’. The images from the original field will appear in the new table.
      
      
      3. For such single- and multi-select fields as {Category}, during the conversion process Airtable will create a select option for each value encountered. If the original table had options defined but not used, you will need to add these options manually. Color-coded select options may need to be changed to match the original.
      
      
      4. With the sample bases provided by Airtable, collaborator fields ({Project Lead}, {Project Team}) cannot be converted, as the bases were created using different dummy data for the collaborator teams. Presumably, this will not be an issue for your own bases.
      
      
      5. In this instance, neither [Design Projects] nor [Tasks] contain any formula fields. Had that not been the case, they, too, would have imported as single-line text fields containing the formula’s calculated value. In such a case, you would need to copy-and-paste the formula configuration from the original field definition to the new one.
      
      
      6. Finally, rename [Imported Table] to [Design Projects].
      
      
      
      
   
   
   6. 
      

      Repeat the process for the [Tasks] table.

      
      
   
   
   7. 
      

      Once both tables have been imported, you can recreate the links between the two. From either table, locate what was originally a linked record field pointing at the other table. Right-click the field name, select ‘Customize field type,’ and select ‘Link to another record.’ When prompted to ‘find a table to link records from,’ select the other table from the menu. Airtable converts the text entries into links to records in the other table.

      
      
   
   
   8. 
      

      Go to the other table. Here you should find two fields of the same name, one a text field containing the name of records from the other table and the other a table of actual links to the records. Right-click on the text version and select ‘Delete table.’

      
      
   
   
   
   

At this point, you should have a Sales CRM base with two additional tables. The final step is to merge the [Clients]/[Accounts] tables and link the resulting table to [Design Projects]. What this entails depends upon your two bases.

When working with the sample bases provided by Airtable, the process is simple: As the two bases have no clients in common, one merely appends one list of names to the other, copy-and-pastes the contents of the remaining fields, and recreates the links. Presumably, though, your bases do have common clients, as that is what kicked off this exercise in the first place. As a result, merging the two tables will require a little more work.

2. 
   

   In the Sales CRM [Accounts] table, create two new fields: an attachment field called {Attachments} and a long text field called {About}.

   
   


3. 
   

   For each record in the Project Tracker [Clients] table that does not have a match in Sales CRM [Accounts], create and configure a new record.

   
   


4. 
   

   For each [Clients] record where the {Attachments} field is not blank, copy the value (select the {Clients::Attachments} cell and press Ctrl-C) and paste it into the corresponding Sales CRM [Accounts] record (select the appropriate {Accounts::Attachments} field and press Ctrl-V).

   
   


5. 
   

   Repeat the process for every non-blank {Clients::About} field.

   
   


6. 
   

   Finally, open the [Design Projects] table in the Sales CRM base. Right-click on the {Client} field and change it to a linked records field. When prompted, direct it to use the [Accounts] table.

   
   

And that’s it! Believe me, it is far more taxing to describe than it is to do. At this point you can either continue as you had before, operating two essentially separate bases, but with a shared front end, or you can begin to build processes and formulas taking advantage of synergies between the two operations.

I hope this helps; please don’t hesitate to ask should anything remain unclear.

Hey @W_Vann_Hall thanks for this thought out response! From what I am reading through your response it looks like the best approach is to consolidate all the various business users’ Bases tables into one base?

I have been googling around- is there a way to customize each user’s View into the Base so users are not stumbling over each other’s tables/tabs?