4 Replies Latest reply on Jul 23, 2012 11:52 AM by braxton

    Summarize data from a portal

    braxton

      Title

      Summarize data from a portal

      Post

      I have two tables in a database:

      Products

      • ProductName

      Stores

      • StoreName - from list
      • Cost

       

      For each prodcut file I have a portal of the stores and their price for the product.  I would like to be able to take a found set that has prices from a couple stores and see the total cost to purchase all the products at each store.

      In the graphic example I would have the following totals at the top of the layout:

      Winco:  $10.00

      Safeway:  $11.00

      I might even want to do this with a few stores.  I only show the top two best prices in the portal that is sorted by price.

      I have tried creating all kind of summary field but can't figure this out.  Any help would be appreciated.  Thanks.

      products.jpg

        • 1. Re: Summarize data from a portal
          philmodjunk

          What version of FileMaker are you using?

          If you are using FileMaker 12, you have the option of using the new Execute SQL function which can be used to produce such totals.

          If you are using FileMaker 11, you have the option of using filtered portals to get the totals that you want.

          If you are using a version older than 11, you can set up "filtered" relationships that produce the desired totals.

          Can you confirm the following tables and relationships? Is this what you have?

          Products-----<Stores

          Products::StoreID = Stores::StoreID

          It looks like you really need three tables here, not two:

          Products----<Prices>----Stores

          Products::ProductID = Prices::ProductID
          Stores::StoreID = Prices::StoreID

          This allows you to link one product to many stores and link many products to one store--a many to many relationship.

          • 2. Re: Summarize data from a portal
            braxton

            I'm using FM Pro Advanced 9.  I'm thinking of getting the latest soon.

            This is the relationship I have set up.  I figured the stores were the children so I put a foreign key.  I'm going to get rid of the PriceSale info.  Just going to use the store name and price of the product.

             

            • 3. Re: Summarize data from a portal
              philmodjunk

              This may be sufficient, but if so, I'd name that second table "Prices" rather than "store" since you have many records with the same store name. I think you'll get better flexibility if you add that third table like I described. (For one thing, you can identify each store with a unqiue serial number if you have a table of Store data with only one record for each store.)

              Using names--not the best option as store names can change on you, and a bunch of calculation fields, you can do this in FileMaker 9.

              You could, for example, define constSafeway as returning the text "Safeway" and constWinco as "Winco".

              Then:

              Product::constSafeway = SafewayStore::ID_Store

              Product::constWinco = WincoStore::ID_Store

              can be relationships that work for this.

              SafewayStore and WincoStore would be new "occurrences" of the Store table. In Manage | Database | relationships, make a new table occurrence of Store by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as "SafewayStore".

              We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

              Repeat the process to get an occurrence named WincoStore.

              If you define a summary field named sPriceTotal in Store to compute the total of the price field, you can put SafewayStore::sPriceTotal on your Product Layout to get a total of all prices for SafeWay and WincoStore::sPriceTotal would display the total of all Winco prices.

              Not the most flexible approach and rather complex, but it works FileMaker versions 10 and older.

              • 4. Re: Summarize data from a portal
                braxton

                Thank you Phil.  This sounds good.