8 Replies Latest reply on Feb 28, 2012 3:42 AM by matt.rowe

    Can I create a relationship based on a calculated field?

    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!