Using a calculated field in a relationship

[nick_jp]

Hi All

 

I'm working on a real estate investment sales DB

 

I have a file with three main tables; Deals, Investors and Opportunities. An opportunity is a join of deals and investors. Whenever an investor is interested in a deal we create an opportunity. 

 

Because it’s real estate, when one investor closes on a deal all the other opportunities are no longer valid. We would like to keep them in whatever state they finished up because it's a good historical record for investor activity. 

 

On the “Home” screen I want to have a portal showing all open opportunities - where the deal is not expired or sold. 

 

I have a calculated field in the opportunities table that refers to the deal_status field in deals. The “display” table which home is based on has one record in it and there are a couple of number fields that hold the key value for an expired or sold deal.

 

I set up the relationship between the display table and dispay_opps where display.deal_sold does not equal deal_opps.deal_status and display.deal_expired does not equal deal_opps.deal_status but nothing is showing.

 

I guess this is due to opportunities.deal_status being a calculated field (FMP makes it unstirred) but I’m pretty new at this and do not know the correct method to get the portal to load.

 

Any help would be really appreciated. Thanks!

 

Nick

[comment]

If I understand your description correctly (big IF), you want to have a chain of relationships:

Home -< Deals -< Opportunities

where the Home -< Deals relationship is based on matching deal_status. Then a portal to Opportunities, placed on a layout of Home, would show only opportunities belonging to "open" deals.

[nick_jp]

You are right. Let me have a go at that.

Perfect. Thanks very much.