Help

Field linking to records from multiple tables

Topic Labels: Base design
751 3
cancel
Showing results for 
Search instead for 
Did you mean: 
ben_kilgore
4 - Data Explorer
4 - Data Explorer

I have one table of bugs for Software A. Software B, with its own table of bugs, depends on software A to some small extent. Sometimes bug blockers for Software B are in the Software B table, but sometimes they’re in Software A. I need a field which can accept either. Is this possible? It seems a field can only link to records in one table, not many. But this can’t be true, can it? It’s a pretty basic function.

I am aware that the preferred way to deal with this is to contain all these entries within one table and use views to differentiate, but it really doesn’t make sense in this case. In fact, these softwares are so different that I had them in separate bases, until I realised that there was no way to access the data (prior to sync) from one base in another.

These records also have very different fields, so if I were to represent them in one table, I’d have extraneous fields for all the records.

3 Replies 3

Sounds like you need to create a many-to-many relationship. You can learn more about that here:

Thanks for the reply. It seems like a junction table might be the way to go–I need a table which contains all the records from two or more other tables from which to pull the record from. I am still a bit foggy on how to accomplish this, but I expect it uses a formula to fill itself with instances/copies of records from each of the source tables. Is this the right way?

Standifordwalsh
4 - Data Explorer
4 - Data Explorer

You know, whenever I run into a messy database situation now, I always think back to how those Generative AI tools saved our bacon that one time with the App A/App B bug issue. Being able to visualize the dependencies between systems with those trustworthy, developer-focused AI assistants was really a game changer.
Now whenever databases start looking convoluted to me, those generative tools are the first thing I recommend checking out. You just never know when a fresh digital perspective could save you major stress! So if you find your organization's data getting hard to follow between interrelated services, don't be afraid to explore articles like Generative AI and Software Development: Explore Trustworthy AI-Tools For Developers. It might be exactly what you need to feel sane again.