4 Replies Latest reply on Nov 2, 2014 2:19 PM by TKnTexas

    Data Summarized on Tab Control

    ruhlanma

      Title

      Data Summarized on Tab Control

      Post

      Once again I'm stumped.  I have a database with a table of customers and related tables containing purchases, rebates and various other elements.  This inquiry focuses on the customer purchases. 

      I'm attempting to display (on a yearly basis) the sum of sales by product for a given customer.  So:

       

      Customer XYZ:

                                 YEAR:

                                                           PRODUCTS                         UNITS                   DOLLARS

                                                           PRODUCT A                         225                       $10,985.75

                                                           PRODUCT B                         3                           $1000.00

       

       

      Then, when the user taps a product line on the summary, another portal? will show the detail that makes up that number.  So below that would be a section displaying:

       

                        INV#   DATE             PRODUCT      RETAIL LOCATION         UNITS           DOLLARS

                         101     09/17/2014    PRODUCTA    STORE 125                   100               5000.00

                         102     10/01/2014    PRODUCTA    STORE 125                   125               5985.75

       

      I want to do this within a tab control.  The customer info is being displayed on the left half of the screen.  On the right half of the screen I have a tab control that displays the various data related to that customer.  One of the tabs is to show the purchases as I've shown above. 

       

      The data structures currently in place related to the above are simply the customer table and the purchase detail table.   These are related by the customer ID.  The purchase detail table contains all of the relevant detail for each purchase.  (Inv#, date, store, product, units, dollars)

       

      My data is all contained in the standard filemaker 12 database.

       

      Normally I would just create a simple query that would run in perhaps a script, something like:

      varyear=2014

      varcustid = the ID of the current customer being displayed

       

          SELECT product,SUM(units),SUM(dollars)

          FROM purchases,customer

          WHERE purchases.cust_id = customer.cust_id

              AND YEAR(purchases.inv_date) = varyear

              AND customer.cust_id = varcustid

          INTO CURSOR c_prodsummary

       

      And when they clicked a row in the product summary I would refresh the detail with a query:

      varproduct = c_prodsummary.product

      SELECT *

      FROM purchases

      WHERE purchases.cust_id = varcustid

      AND YEAR(purchases.inv_date) = varyear

      AND purchases.product = varproduct

      INTO CURSOR c_productdetail

       

      But I don't see any means of running these sorts of simple queries.  I'm trying to avoid having to build tables to house all of the above. 

       

      Any suggestions anyone?