Can I create a relationship based on a calculated field?

Discussion created by matt.rowe on Feb 27, 2012
Latest reply on Feb 28, 2012 by matt.rowe

This should be simple, but for some reason I just can't seem to get this right, please help!


I have two tables Property & LettingUnit, related thus Property--<LettingUnit.


LettingUnit has a calculated field called Status which is either "Let" or "Vacant" depending on a calculation (Not Stored because it relies on something from another table).


All I want to do is place a field on a Layout based on the Property table, that adds up the LettingUnit::RentPayable field IF the LettingUnit::Status field calculates to "Let".


I have tried creating a self join on the LettingUnit table which joins to itself using it key field and a constant (set by calculation to "Let" but this doesn't seem to yield a result.


Any help would be much appreciated, this is driving me insane!