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!