2 Replies Latest reply on Dec 16, 2009 9:40 AM by comment_1

    Portals in tabs displaying data based on two criteria

    JonnyDY

      Title

      Portals in tabs displaying data based on two criteria

      Post

      I have a two tables, contracts and purchase order log.  I would like to display some of the data in the purchase order log on a layout for contracts, such that if a purchase order has a contract number assigned to it, it appears in a portal on the contracts layout.  However, the purchase order also includes a catergory field, say hardware, paint, glass, appliance, other.  I would like each of these catergorys to be displayed in a different tab within the contracts layout.  It also is required that from the contracts page you can add (via. the portal) to the purchase order log, and vice versa.  My fields on the contracts table include contract no. and my fields on the order log include purchase order no, contract no. and catergory. 

       

      I have tried linking them, but my portals show either all the records or no records at all.

        

      I am new to file maker and trying to convince the company I work for to invest in a database system rather than their very messy series of spreadsheets they currently use.  I am using the latest 30 day trial (of which I have 22days left) on a windows vista pc. 

       

      Many Thanks,

       

      Jonny

        • 1. Re: Portals in tabs displaying data based on two criteria
          philmodjunk
            

          The technique you describe is called a "Filtered Portal". If you click the above "Advanced" search link you can find a number of threads that discuss this method.

           

          Here's a step by step method for setting this up for one tab in your tab control. You can adapt this method for multiple tabs or you can use a method I'll include at the end of this message that spells out an approach that uses a combo box or pop-up menu that will give you what you want without any tabs (it's easier to implement and is more flexible.)

           

          Let's say you want to show all Purchase Order Log records for a given contract that are for "hardware".

           

          Define a new Field in contracts as a calculation, HrdwrKey, and enter "hardware" with the quotes as its expression. Change the return type to "text".

           

          Switch over to your Relationship Graph and make a relation ship that matches 2 pairs of fields instead of one:

          Contracts::Contract no. = POLog::Contract no. AND

          Contracts::HrdwrKey = POLog::category

           

          To set this up drag from Contract no. to Contract no. and then drag from HrdwrKey to category or double click the line linking these two Table Occurrences and edit the relationship in the dialog.

           

          Now a portal to POLog on a Contracts layout will show only those "Hardware records" with the matching contract number.

           

          The catch here is that you'll need to create additional POLog table occurrences for each such tab in your tab control with a new calculation field specifying a different category.

           

          Here's an easier way.:

          Change HrdwrKey to a text field called "CategoryKey".

          On your layout, place this field above/next to your portal and use Field/Control | Setup... to format it as either a drop down list or a pop-up menu and give it a value list of all your categories.

          Now you can select a category and you'll see any matching log records for the given contract appear in the portal. You can even shift - click items in a pop-up menu to display items from more than one category in the same portal and you only need one relationship to set it up.

           

          Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

          Table vs. Table Occurrence (Tutorial)

          • 2. Re: Portals in tabs displaying data based on two criteria
            comment_1
              

            It's not efficient to use a tab object for this: just define a global gCategory field in Contracts, and another relationship to (a new occurrence of) PurchaseOrders based on:

             

            Contracts::ContractID = PurchaseOrders::ContractID

            AND

            Contracts::gCategory = PurchaseOrders::Category

             

            A portal based on this relationship will show the purchase orders in the category selected in the global field. If you like, you can make a button for each category (to set the global) - so it will function like a tab object.

             

             

            Re your other question: you cannot create purchase orders items from a layout of Contracts, unless you select a specific purchase order first