6 Replies Latest reply on Feb 18, 2013 11:36 AM by dsivaa

    More than one path in relationship graph

    dsivaa

      Title

      More than one path in relationship graph

      Post

           Hello Experts,
           I am new to filemaker. I am making an inventory database.
           I have a problem in making relationship with three tables. Filemaker saying more than one relationship path is not allowed.

      PO
           PO_ID
           PO_Date
           Vendor_ID
           Vendor

      PO_Lines
           PO_ID
           Item_ID
           Item_Name
           Qty
           Price
           Amount

      Vendor_Items
           Vendor_ID
           Item_ID
           Price

           When i try to use another occurance of the table Vendor_Item,
           In the PO layout, i get either Item_Id filtered or Vendor_id filtered.
           If i use Vendor_Item in the portal, it filters only Item_ID.
           If i use Vendor_Item2 in the porta,l it filters only Vendor_ID

           I need to filter both Vendor_ID (from PO table) and Item_ID (from PO_Lines table) in Vendor Items portal..
           Any help would be appreciated.

      circular.JPG

        • 1. Re: More than one path in relationship graph
          philmodjunk

               To match two related tables by two pairs of fields, select two pairs of fields as the Match fields. You can drag a second time from one occurrence to another or you can double click the relationship line to open a dialog for doing this.

               But It's not clear to me that this is really what you need here.

               Is each PO limited to a single Vendor?

               I'd guess that if this is the case, you need a conditional value list of just the items supplied by that vendor and this does not require a relationship that matches by anything other than:
               PO::Vendor_ID = Vendor_Items::Vendor_ID

               Here are some links on conditional value lists that may be helpful:

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: More than one path in relationship graph
            dsivaa

                 PhilModJunk, Thanks for your reply.

                 In my case, there are 3 levels of graph.

                 Level 1 (Anchor) is PO table.
                 Level 2 is PO_Lines table. (PO Line portal)
                 Level 3 is Vendor_Items table. (Vendor_Item portal)

                 Level 1 and Level 2 are connected with PO_ID.
                 Level 2 and Level 3 is connected with Item_ID.
                 Level 1 and Level 3 should be connected with Vendor_ID. This is not possible at current relationship graph. 

                 Please look at the layout image attached.
                 Layout table is PO. PO_Lines are related records of the key field PO_ID.
                 Vendor_Items portal should show only records using the key fields Vendor_ID of PO table and Items_ID from PO_Lines table. But i can use the key either Vendor_ID or Item_ID. I need both keys should be connected with Vendor_Item table
                 I want to know, is there any solution or work around to solve this situation?

                 Is it possible to attach the database to this post or send the db to you for your reference?

                  

            • 3. Re: More than one path in relationship graph
              philmodjunk
                   

                        Level 1 and Level 3 should be connected with Vendor_ID. This is not possible at current relationship graph.

                   It is if you add new occurrence of the Vendor table.

                   Click the vendor Tutorial: What are Table Occurrences? box.

                   Click the duplicate button (two green plus signs)

                   Now link PO to this new occurrence of the Vendor table.

                   Any references to Vendor from the context of PO will need to refer to this new occurrenc of Vendor.

              • 4. Re: More than one path in relationship graph
                dsivaa

                     Phil,
                     As per your guide, i have created another occurance of Vendor_Item table. But i do not get expected result.
                     Please look at the the image attched.

                     I want single occurance of Vendor_Item table should refer both PO (Vendor_ID) and PO_Lines (Item_ID) tables.
                     Vendor_Items portal should not display Items from Vendor_ID other than 1 and Items_IDs other than 1 and 2.
                     Vendor_Items portal should display only items for a particular Vendor_ID (PO table) and Item_IDs (PO_Lines table) of that particular PO.
                     Is there a way to make a relational graph for Vendor_Item table to both PO and PO_Lines tables?

                     Thanks,
                     Siva

                • 5. Re: More than one path in relationship graph
                  philmodjunk

                       Matching by both Vendoer ID and Item ID will not produce the results you want as you have mutiple Item IDs selected for your PO and you need all of them.

                       If you try to match a record by vendor ID and Item ID, you match to a single record, not the list that you want.

                       The simplest solution is to take your portal to Vendor Items2 and give it this portal filter:

                       Not IsEmpty ( FilterValues ( List ( PO_LInes::Item_ID ) ; Vendor Items2::Item_ID ) )

                       You could also define a calculation field, cItemList, in PO with this expression:

                       List ( PO_LInes::Item_ID )

                       Then your relationship can be:

                       PO::Vendor_ID = Vendor Items::Vendor_ID AND
                       PO::cItemList = Vendor Items::Item_ID

                       But how do you intend to use this portal? Seems like it is showing very similar data to what you already have showing in your portal to PO LInes.

                  • 6. Re: More than one path in relationship graph
                    dsivaa

                         Phil,

                         Your both ideas are working like a charm.
                         You are a super expert. Thank you so much.