4 Replies Latest reply on Jun 25, 2012 2:53 AM by DougieReid

    Showing related records in a portal

    DougieReid

      Title

      Showing related records in a portal

      Post

      Hi, I am new to FMPro but not to database design and development. I have a simple database with 3 tables, Holiday, Client, Booking.

      The Booking table is a junction table representing a many-to-many relationship with attributes between the Client table and Holiday table.

      On the Holiday layout I wish to show booking information, which requires data from both the Client table and Booking table, to provide a full picture of Client:name, Client:email, Booking:invoicePaid etc.

      It seems simple to define that a portal show related records from Booking for example, but less clear how to show joined data from both Client and Booking that relates to the Holiday in the portal.

      I am sure this is possible but would like some direction on the FMPro method for achieving this. Many Thanks,

      Dougie

        • 1. Re: Showing related records in a portal
          GuyStevens

          Is it possible for there to be multiple records in the Bookings table so that you would have to select one record in the portal to be able to see it's details?

          Because the technique of selecting a record from a portal and showing it's detaisl can be seen in this demo file:

          http://dl.dropbox.com/u/18099008/Demo_Files/Books_EditionsV3.fp7

          This however doesn't happen to be a join table, but the idea remains the same.

          Pay attention especially to the relationship dialogue. And notice the second TO of the "Edition Selected"

          If you can't figure it out, let me know I'll look into your situation in more detail.

          • 2. Re: Showing related records in a portal
            DougieReid

            The layout should show the Holiday details, the portal the related Bookings on that holiday so if I was writing this in SQL it would look something like:

            select Client.firstName, Client.secondName, Client.email, Booking.invoicePaid, Booking.clientTravelPlan

            from Client, Booking, Holiday

            where Holiday.holidayId = [parameter]

            and Holiday.holidayId = Booking.holiday

            and Booking.client = Client.clientId;

            I can get the portal to show related Booking or Client data, but I cannot see a way to join the 3 tables, and select the data I wish to display in the portal....

            • 3. Re: Showing related records in a portal
              Sorbsbuster

              You would have this relationship series:

              Holiday Table --[by HolidayID] -> Booking Table -- [by ClientID]-> Client Table

              That will let you start from the Holiday Table and have a portal based on that Booking Table relationship (Table Occurance).  From that Holiday record you will see a list of all the Bookings for that HolidayID.  Picking up the Client Table information from that chained relationship (Table Occurance) will pick up all the Client details for that booking.

              • 4. Re: Showing related records in a portal
                DougieReid

                Hi, thanks for your reply. I had the relationship defined, I was just struggling to find the right way to show the records in the portal, which I have just figured out and it is pretty darn easy which is cool.