Hi Amit! Reading through your description of what you’d like to accomplish, it seems like the best solution for you is to use different views to look at your companies table—one view with all of the companies that hides unnecessary fields, and one view that uses filters to show only the companies with direct competitors investing and shows all fields. In database software, rather than using separate sheets/tables to look at subsections of your data, you usually use views, which show just a specified slice of the entire dataset. (This is how Salesforce works, for example.) This eliminates redundancies in the data that might come from making many different tables.
Rather than creating a separate table to show only the companies with a “direct competitor”-tagged investor, we’d recommend creating a new view within your existing companies table. Here’s the steps you’ll need to take:
- Use a Lookup field to pull the investor categories into the “Companies” table.
- In the Companies table, now, we’ll create a view called “Funded by direct competitors” that filters to show only the Companies which have a “Looked-up” investor category containing the phrase “Direct Competitor”.
- We can then add fields to the companies table, and hide them from the other views in that table so they only show up when looking at “Funded by direct competitors”.
In general, using a single table with multiple views and hidden columns to achieve this sort of workflow, as opposed to multiple tables with duplicated data between them, is cleaner and more “correct” in database-land (as opposed to spreadsheets)—and is something that you’d also do in other database products like Salesforce. Hope this helps!