4 Replies Latest reply on Jun 25, 2012 9:42 AM by JillX

    Filter problem between tables



      Filter problem between tables


      I have two tables, one is E and another is Shipping. Both two tables have field "OrderNumber" and "Container". And they are the only tables with both OrderNumber and Container.

      Now OrderNumber is the relationship between those two.

      If I want to also filter container indivisually through relationship between those two, how can I do that?

      Thank you!

        • 1. Re: Filter problem between tables

          Am I remembering correctly that this is a one to many relationship from Shipping to "E"? (Using letters for table occurrence names seems a bit odd BTW.)

          Note that others reading your post will not know this.

          Shipping--------<E   (---< means one to many)

          Shipping::OrderNumber = E::OrderNumber

          If you have fileMaker 11 or later and want to show a list of all records in E for a given shipping record, but specifying a specific value for "container",

          you can set up a filtered portal where your portal filter expression filters for either a specific container or a container specified by the user.

          The filter expression might look like this:

          E::Container = "Standard"

          or it might be this:

          E::Container = Shipping::Container

          Where the user selects a value in Shipping::Container and you might choose to make this field a field with global storage.

          You can also create new occurrence of table E and build this logic directly into a new, separate relationship:

          Shipping::OrderNumber = EbyContainer::OrderNumber AND
          Shipping::Container = EbyContainer::Container

          • 2. Re: Filter problem between tables

            You are right Phil, Shipping table has Po relationship with T and E. I tried to build a new table based on table E, while I can not find it in the database relationship graph.

            • 3. Re: Filter problem between tables

              Shipping table has Po relationship with T and E


              You don't need a new table, you need a new table occurrence--these are two different things. Table Occurrences are boxes found on Manage | Database | Relationships.

              Select the box for Table E on the relationships tab, then click the duplicate button (two green plus signs). This creates a new reference to your Table E, but does not create a new table. You can double click the new occurrence box to open a dialog where you can change its name.

              • 4. Re: Filter problem between tables

                Sorry for the confusion, po is the same as order number.

                Thank you so much ! It worked!