AnsweredAssumed Answered

Data Summarized on Tab Control

Question asked by ruhlanma on Oct 31, 2014
Latest reply on Nov 2, 2014 by TKnTexas


Data Summarized on Tab Control


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:


                                                     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:


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


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?