How to create a primary field that is a conditional lookup from another table

[Anya_Davis]

I am building a CRM database. There is a table in my database called Deals that contains a multiple select field called Deal Stage. One of the options in that field is called Closed Won. Those are deals for which we have a signed term sheet and are going to provide a loan.

I want to create a new table called Loans where I list details for each of the loans we have made to that subset of Deals for which we Closed Won. For the primary field in the Loan table, I would like to create a formula that looks up the name of the deals in the Deals table and only finds those ones that have the status Closed Won in the Deal Stage field.

I would be so extremely grateful for help me with this.

Thank you!!!

[ScottWorld]

Welcome to the community, @Anya_Davis!

You would need to script that process using JavaScript, and then trigger that JavaScript via one of these methods:

1. A button field
2. The scripting block.
3. The new automations feature, which is in beta.

You could also use an automation tool like Integromat or Zapier, which doesn’t require any JavaScript knowledge.

However, it seems like the much better way to setup your database would be to NOT duplicate your efforts across multiple tables. That’s not the ideal database design, and it makes things extremely complicated for you down the line — for example, if you add/delete a field in one table, then you have to mirror adding/deleting that field in the other table.

It seems like the best way for you to set this up would be to just keep all your deals in one table, and then create a single-select field (or a checkbox field or any other type of field that you would like) where you can distinguish between whether the deal was won or lost. It actually sounds like you already have that field — you’ve called it the “Deal Stage” field.

Then, you can create separate views that are filtered to show you only the deals that you want to see. So, for example, you can create a view that is filtered to show you only the deals that have been won.

[Anya_Davis]

Hi Scott,

Thank you for this thoughtful reply!

I also noticed in one of the Template bases a solution that created a new table that included a link to the equivalent of the Deals table, and I guess you just manually select those deals that are applicable.

See this template: CRE transaction management Template - Free to Use | Airtable . They have a Properties table, that is the equivalent of my Deals table, and a Transactions table, which is the equivalent to my Loans table. The properties listed in the Transactions table are a subset of the the total properties. It doesn’t look like they use a formula to choose which properties are linked, so I’m guessing it’s manual.

What do you think of this approach?

Thanks!

[ScottWorld · Accepted solution]

That template is broken up into different tables because there are multiple transactions for each property. That’s what’s called a “one-to-many relationship”, which is why they need 2 tables. One property has multiple transactions associated with it.

In your case, do you have multiple loans associated with one deal? If so, then you need different tables too, because that’s a one-to-many relationship. One deal has multiple loans associated with it. And yes, links would be manually created between your records in different tables. There is no automatic linking of records in Airtable.

However, if you just have one loan per deal, then those aren’t really different tables. You COULD put them in different tables if you really wanted to, and you’re more than welcome to do that, but that’s really unnecessary work on your end. But if you just have one loan per deal, that’s really just additional fields to fill out for the deals that have actually been won. This scenario would be a one-to-one relationship, which means that you would ideally keep everything in one table. But then again, if you really want to split things up into 2 tables, you could.

Also, on a side note, if you have a budget for this project and you’d like to hire an expert Airtable consultant to help you design this system for you, feel free to send me a private message.

[Anya_Davis]

Hi Scott,

This is extremely helpful! I can see now how keeping all the data in one table with different views could solve my problem.

Thanks so much.
Anya

[ScottWorld]

You’re welcome! Glad I could help! :slightly_smiling_face: If you don’t mind, could you please mark my comment above as the solution to your question? This will help other people who have a similar question in the future. :slightly_smiling_face:

[Anya_Davis]

How do I private message you?

[ScottWorld]

Click on my profile icon (or my name), and then click the blue “Message” button.