Connecting multiple fields between two bases

In general, you would typically not want to spread your information across multiple bases in Airtable (unless there is a strong reason to do so), because that would require you to use sync tables, which will present you with several unique challenges & limitations.

Spreading out your data across multiple bases would also make it incredibly difficult (if not impossible) to create advanced automations for your system, or to create client portals where your customers can view all of their information & all of their trips.

Additionally, in your case, most of this can't be done on the free plan. For example, if you want to use sync tables (which I don't recommend for your use case), you would need to upgrade to the Teams plan.

Your #1 best base design is to unify everything together in one base. Your best bet would be to move all of your trips into a single "Trips" table in your base, and all of your rosters into a single "Rosters" table in your base.

Then, you can easily used "linked record fields" to link your clients to trips and rosters. I discuss linked record fields in depth in my free Airtable training course, which you can take for free by signing up for a free 30-day trial with LinkedIn Learning. 

You can even use Airtable's interfaces to easily view all the information for any particular trip or any particular client.

And all of this can be done with the free version of Airtable, but once you reach the record limit, you will need to upgrade to a paid version of Airtable.

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld

You can share data between bases via shared links.  

You can share data between bases via shared links.  

Yes, that would be using sync tables. (Sync tables are not available on the free plan, but they are available on higher plans.) Sync tables would solve the problem of bringing in clients from the clients base into each trips base.

Note that on the Team Plan, there is no editing in the destination table — although that can be worked around by giving the user a button to jump back to the original record in the original base

So sync tables could solve the problem of bringing in clients from the clients base into each trips base, but I typically try to avoid sync tables in situations like this where there could potentially be an unlimited number of trips bases, because it limits future growth, such as displaying trips in the clients table.

For more than 1 sync source per base (such as displaying trips in the clients table), it would require jumping up to the business plan, which comes with a new set of limitations on the two-way sync.

The biggest limitation would be the hard limit on number of sync sources per base. So, for example, while the syncs might be perfectly fine going from the customer table to each unique trips base, it wouldn't work the other way around if the business grew to have more than 50 trips. For example, if you wanted to see more than 50 trips within the clients base, that wouldn't be possible.

But assuming that the business stays beneath 50 trips, any external automations or external integrations or external portals would also need to be reconfigured for each new trip base. (Although if the business stays beneath 50 trips, Airtable's interfaces could be used instead of external portals.)

And any changes to an existing internal Airtable automation would need to be manually updated across all "active" trip bases (i.e. a trip base that is still being actively used).

In general, I feel that sync tables are not a good design choice for this use case, because it limits future growth and flexibility.

re: Is this possible with the free version of Airtable?

I'm afraid not, you'd need to use external automation tools like Zapier etc to help you pull the data over

---
re: And if not, is it even possible with one of the paid versions?

Yeap!  On the Teams plan you can use synced views: https://support.airtable.com/docs/getting-started-with-airtable-sync

And your setup would look something like the following.  You'd have a "Clients" base like you do now:

Link to base

And your various trip bases would have a synced table like so:

And you'd link the records together between your "Roster" and "All Clients" tables to pull over the data with lookups:

Link to base

You'd have to deal with the various limitations around syncing though:

===
May I know why you chose to use one base per trip?  Given the use case it seems like it might be easier to manage if all the trips existed in one base along with the client data

The post above mentioned Zapier, but I would not recommend Zapier.

Make is INFINITELY more powerful & customizable than Zapier, yet it is SIGNIFICANTLY CHEAPER than Zapier.

I wrote an entire post here comparing Make vs. Zapier.

In the Airtable consultants community, essentially all of the consultants have switched from Zapier to Make over the last 5 years because Zapier has been technically surpassed & financially underpriced by Make.

p.s. If you need an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld