Link Record with Conditions

[matt3]

Hello AT Family

Table: Properties (is our base table with all of our info)

• Table: Mortgage
  • has all mortgage info pertaining to each property in Properties Table
  • The main statuses in this table is "Yes, No, Refinancing"
  • Note: some properties in this table have had two mortgages, which means to two lenders at one point, which means two status, "Yes and No"
• Table: Insurance
  • I need ONLY the lenders from the "Yes" status in the Mortgage Table to be looked up in this table

The problem I'm having is it's pulling the both lenders with "Yes and No" status.

How do I make it only pull the Yes lenders?

When I filter to show ONLY Yes, it doesn't show anything because the property may have a Yes and No status.

[ScottWorld]

You'll need to change it from "has any of" to "is exactly".

[matt3]

When I do that, the records that have are tied to one property address with a yes and no status disappear.

[matt3]

  See screenshot attached.

There are a couple of properties that have an old mortgage, and that status is now NO. So I don't care to see the lender for the NO status. Those few properties now have an active mortgage, and I just want to see the Lender from the YES statuses.

[matt3]

I fixed it. 

I had to create a formula in the Mortgage Table

• IF({Status} = "Yes", {Lender}, "")
• IF({Status} = "Yes", {Loan #}, "")

Then I looked up these columns in Properties Table.

Finally, I pulled this info into the Insurance Table from the Properties Table from the Mortgage Table column with the formula I created.