Skip to main content

Link Record with Conditions

  • August 17, 2023
  • 4 replies
  • 27 views
matt3
Forum|alt.badge.img+3
  • matt3
  • Participating Frequently
  • 5 replies

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.

    4 replies

    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • 9808 replies
    • August 17, 2023

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

    matt3
    Forum|alt.badge.img+3
    • matt3
    • Author
    • Participating Frequently
    • 5 replies
    • August 17, 2023

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


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

    matt3
    Forum|alt.badge.img+3
    • matt3
    • Author
    • Participating Frequently
    • 5 replies
    • August 17, 2023

      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
    Forum|alt.badge.img+3
    • matt3
    • Author
    • Participating Frequently
    • 5 replies
    • August 17, 2023

    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.


    Terms & ConditionsAccessibility statement