4 Replies Latest reply on Mar 21, 2010 1:19 PM by SwissMac

    Can a portal contain data from two tables?

    SwissMac

      Title

      Can a portal contain data from two tables?

      Post

      I have a simple four table FM10 database. Three tables are data tables, and one a relational table to allow a one to many to one link. These tables are linked by ID number. The tables are:

       

      dContract with Primary Key contractID

                       |

      one to many relationship

                       |

      rContractCompany with Foreign Key contractID and Foreign Key productID

                       |

      many to one relationship

                       |

      dProduct with Primary Key productID

                       |

      one to many relationship

                       |

      dPurchase with Primary Key purchaseID and Foreign Key productID

       

       

      I want to show all the information on one Layout. The data from the first table is displayed on a normal view. The data from dProduct is shown on a Tab, and the Tab contains a portal in order to show the information from dPurchase. The problem is, I can only display the ID number of the products in the portal that shows the purchase info, and not the name of the item which is what I really want to know.

       

      How can I get the portal to show the Product Name as well as the productID? Or should I use an alternative means of showing this info?

       

      TIA

        • 1. Re: Can a portal contain data from two tables?
          comment_1

          This is for me, so I can understand what you are talking about:

           

          Contracts -< ContractProducts >- Products -< Purchases

           

           

          Now, in order to show ProductName in a portal to Purchases on a layout of Contracts, you can either:

           

          a) add a calculation field to Purchases = Products::ProductName;

           

          or:

           

          b) append another occurrence of Products to Purchases:

           

          Contracts -< ContractProducts >- Products -< Purchases >- Products 2

           

          and place the field Products 2::ProductName in the portal

          • 2. Re: Can a portal contain data from two tables?
            SwissMac

            Thanks for your help.

             

            I tried the Calculated field method and while I could see entries I could not add anything new to the field, just change the name of existing entries. This meant I could not use the layout with the portal as a working tool to manage the database data. I read in the Filemaker Pro Bible that this is normal behaviour for a Calculated Field.

             

            I then tried to do your second suggestion, but I really don't understand what I have to do here... can you be a little more explicit please? I have for instance used the same link field for both Products -< Purchases and for Purchases >- Products2 but I'm a bit overwhelmed as to what do do next.

             

            Can you help a little more, please?

             

            TIA

             

             

            • 3. Re: Can a portal contain data from two tables?
              comment_1

              I am not sure it's a good idea to change the name of product in a portal showing records from Purchases: such change will affect not only the current row, but the product record itself - and consequently all purchase records related to the product.

              • 4. Re: Can a portal contain data from two tables?
                SwissMac

                Maybe I'm using the wrong process then - I'm a newbie to Filemaker and finding it pretty convoluted. I'm used to MySQL!

                 

                What I really want is a way to enter and review the data for a client in a transparent way. That means I need info about the client, eg name and address, then about the Product, then about the Transactions for that product for that client - all showing in one layout. Do I need a different table structure or a different method of displaying it?

                 

                TIA