2 Replies Latest reply on Aug 11, 2011 4:12 AM by tomo17

    Lookup using data from three tables



      Lookup using data from three tables


      I am trying to get a layout to autofill a field based on a place that is achieved.  I have three tables

      Table 1 Event Information              Fields include - Event, Location, Age Group, DIVISION

      Table 2 Event Results entry            Fields include - Name, Place, Time, House, PlaceCode, Points

      Table 3 Points standards                Fields include - Division, Place, PointsCode, POINTS

      A layout is based on the Event Information with an event entry portal.  Divisions are A,B,C or relay;PlaceCode and PointsCode are calculations from Division and Place and the Points are predetermined in the Points standard table. The tables are linked by PlaceCode and PointsCode


      At the moment when i enter the place in the event entry portal i do not get the correct corresponding points from the Points standard table. The PlaceCode is correct and matches what is in the Point standard table but does not bring across the points.


      Hope this makes sense.

        • 1. Re: Lookup using data from three tables

          I don't see how that could work, if I understand the set up correclty. Check my analysis of your set up and see if I've correctly guessed your setup:

          Event Info::Event = Event Results::Name

          Event Results::Place =Points Standards::Place

          or maybe:

          Event Info::Division = Points Standards::Division

          You have a portal to Event Results on your Event Info layout.

          It looks like you need to match information from Event Results to Points standards by both division and place, but Division does not exist in the Event Results table.

          If you define a calculation field in Event Results as: cDivision that returns DIVISION from Event Info, you can then set up this relationship to match by both points and division:

          Event Results::cDivision = Points Standards::Division AND
          Event Results::Place = Points Standards::Place

          • 2. Re: Lookup using data from three tables

            Perfect works a treat!