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

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.

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.

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

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:

How do I private message you?