1 Reply Latest reply on Jun 3, 2015 2:16 PM by philmodjunk

    Product Category Totals on Business Day Recap



      Product Category Totals on Business Day Recap


      Hey all,

      I have a FMP13 solution that is used to import various daily reports and then combine them for daily/weekly/monthly recap reports. The part that I'm having trouble with is in adding a section to show totals for product categories on a daily recap. There are 4 tables at play in this:

      Business Days (Combines numbers for each day)

      Product Sales Totals by Day (a join between Business Days and the Products Table) (i.e. on 5/1/15 Product A sold 50 widgets and produced $500)

      Products (Each product that is offered)

      Product Categories (Groups various Products together)

      I'm developing a daily recap report that is based on the Business Days table that will include a portal for the top 10-15 product categories with various stats. The problem I'm running into is that since the Business Days table is related to the Product Sales Totals Table by a BusinessDayID, I can easily list daily product sales, but I can't find a way to then calculate daily sales totals for each product category (with the way I have it setup, if a product is sold on a given day, I'm going to get the lifetime sales total for the product category).


      I appreciate any help and will provide additional info if this seems confusing!


      Thank you in advance!