6 Replies Latest reply on Jul 23, 2010 8:49 AM by BenGribaudo

    Multi-Table Join Incorrectly Populating Portal

    BenGribaudo

      Title

      Multi-Table Join Incorrectly Populating Portal

      Post

      Hello,

      I have been struggling to populate a portal with a list of Invoice Items that are related to a License and to the current Client. This protal is in a layout that is showing records from the Client table.

      For one client, the portal shows licensed items for all clients. For all other clients, the portal shows no licensed clients.

      What am I doing wrong?

      Thank you,
      Ben

      ----------------------

      Abbreviated Table Layout

      Client table:
      ClientID
      Name

      Invoice table:
      InvoiceID
      fClientID (related to Client::ClientID)
      Date

      Invoice Item table:
      fInvoiceID (related to Invoice::InvoiceID)
      Qty
      fItemID (related to Item::ItemID)

      Item table:
      ItemID 
      Name
      fLicenseID (related to License::LicenseID)

      License table:
      LicenseID
      Name

      I've built some extra relationships trying to get this to work:

      • Licensed Item (Licensed Item::fLicense = License::LicenseID)
      • Licensed Invoice Item (LicensedItem::ItemID = Licensed Invoice  Item::fItemID)
      • Licensed Invoice (Licensed Invoice Item::fInvoiceID = Licensed Invoice::InvoiceID)

      The portal is pulling from LicensedItem.

      Software: FileMaker Pro Advanced 10

      Update (2010-07-22): I've attached an image of the database's relationships. I've also added a relationship between Client and Licensed Invoice, as was suggested in this forum thread.

      Table-Relationship.jpg

        • 1. Re: Multi-Table Join Incorrectly Populating Portal
          philmodjunk

          I don't see a relationship that links a client record directly to your Item table.

          You appear to have: (Data source table names in parenthesis)

          LicensedItem(ItemTable)::fLicenseID = License(License)::LicenseID

          LicensedItem(ItemTable)::ItemID = Licensed Invoice Item (Invoice Item)::fItemID

          as the relationships linking to your join table.

          You don't identify any relationship that links to the Client table and this is the table you indicate your layout is based upon. Perhaps you should edit your original post to include a screen shot of your relationships graph to clear up the confusion.

          • 2. Re: Multi-Table Join Incorrectly Populating Portal
            BenGribaudo

            Thanks for your response, @PhilModJunk.

            I added a relationship between Client and Licensed Invoice (see image attached to my original post). Now, the licenses portal shows no licenses for clients who have not purchased a licensed item (good) and shows all licenses purchased by all clients when the current client has purchased at least one licensed item (bad).

            The layout pulls from Client and the portal is based on Licensed Invoice Item.

            • 3. Re: Multi-Table Join Incorrectly Populating Portal
              philmodjunk

              I don't see any relationship between Client and Licensed Invoice in your relationship graph. You have a link that goes from Client to invoice to invoice item to item to License to Licensed Item to Licensed Invoiced Item.

              Since you based your layout on Client and your portal on Licensed Invoice Item, that's the path your relation evaluates to determine what records appear in the portal.

              Normally Portals from many to many relationships are based on this table structure:

              Table1----<Join>----Table2

              You put a portal to Join on a Table1 layout  to select records from Table2 that relate to table1 adding fields from Table2 to display information about these related records. You can also place a portal to Join on a Table2 layout and do the reverse.

              The large number of table occurrences in your graph leave me a bit in the dark as to what you are doing, but I think you should base your layout on Licensed Client (a Table Occurrence of Client I presume) and your portal should show records from Licensed Invoice with fields added as needed from Licensed Invoice Item. Either that or you need to restructure your relationships.

              Here's a demo file linking contracts to companies in this fashion you may want to examine.

              • 4. Re: Multi-Table Join Incorrectly Populating Portal
                BenGribaudo

                Thanks for this help.

                <PhilModJunk> "I  don't see any relationship between Client and Licensed Invoice in your  relationship graph. You have a link that goes from Client to invoice to  invoice item to item to License to Licensed Item to Licensed Invoiced  Item."

                Relationship added.

                Summary of how the relationships are supposed to work:

                Basically, an Item can be related to a License. An Item can also be related to one or more Invoice Items. Each Invoice Item belongs to one Invoice which is associated with one Client.

                Layout goal:

                I'm trying to build a layout showing details of all Clients (licensed or not) with a tab showing details of any licensed items the client may have purchased. Thus, I'd really like to base the layout off of Client so that it includes unlicensed clients.

                • 5. Re: Multi-Table Join Incorrectly Populating Portal
                  philmodjunk

                  Just because you name the table occurrence (TO) "Licensed Client" doesn't mean you can't see records of unlicensed clients in a layout based on that table occurrence. You'll be able to see all client records in a layout based on that table occurrence--again assuming that this TO has Clients as its data source table.

                  Using the screen shot I currently see in your original post. A Portal to Licensed Invoice with fields added from Licensed Invoice Item could be placed on a layout based on the "licensed Client" TO to show what you want. Unlicensed client records would presumably show an empty portal.

                  • 6. Re: Multi-Table Join Incorrectly Populating Portal
                    BenGribaudo

                    Thanks, Phil, for your help. I appreacite the quick turn-arounds of your responses.

                    The idea you mentioned in your last answer got me closer to the goal but still some non-licensed invoice item rows to appeared in the portal (IIRC). At any rate, a decision was reached to redo that portion of the layout as its workflow didn't match what was desired.