1 Reply Latest reply on Feb 22, 2010 8:03 AM by ninja

    HELP! group by product and find the total quantity over numerous locations.

    LaurenYoung

      Title

      HELP! group by product and find the total quantity over numerous locations.

      Post

      Hi,  If anytone could help me with this i'd be very grateful...

       

       

       

      I'm using FileMaker Pro 10 on a Mac running Mac OS X (Version 10.6.2) - I'm a complete rookie (This is my first attempt at creating a database ever).

       

      I am creating a database for my company - handling customer/supplier data as well as basic functions such as our product catalog, Loan unit catalog and stock.

      The problem i'm having refers specifically to the 'Stock' table - I have 2 layouts; detail and list. My problem regards the list layout. Each record has three variable fields; Product name, Location and quantity - We may have stock of a specific product spread over 3 different locations.  I want Filemaker to display a grand total of all units in stock, but I would also like Filemaker to show me the total number of units of each product.  To do this i require a calculation that groups each record according to its product name and then totals their combined quantity values.

      If anyone know how I could do this I would be really thankful for the help - everything i try doesn't seem to work and i don't have enough knowledge to really come up with my own calculations.

       

      Thank you! 

      Lauren Young

       

      For Example:

       

      (When sorted by Product)

       

      PRODUCT A  : 9 UNITS IN STOCK

      Product Name: Product A / Location: Storage Space A / Quantity: 3

      Product Name: Product A / Location: Storage Space B / Quantity: 1

      Product Name: Product A / Location: Storage Space C / Quantity: 5

       

      PRODUCT B  : 11 UNITS IN STOCK 

      Product Name: Product B / Location: Storage Space A / Quantity: 4

      Product Name: Product B / Location: Storage Space C / Quantity: 7

       

      PRODUCT C : 10 UNITS IN STOCK 

      Product Name: Product C / Location: Storage Space B / Quantity: 4 

      Product Name: Product C / Location: Storage Space C / Quantity: 6

       

      TOTAL UNITS IN STOCK -  30

      ......

       

      (When sorted by Location)

       

      STORAGE SPACE A  : 7 UNITS IN STOCK

      Product Name: Product A / Location: Storage Space A / Quantity: 3

      Product Name: Product B / Location: Storage Space A / Quantity: 4

       

      STORAGE SPACE B  : 1 UNIT IN STOCK 

      Product Name: Product A / Location: Storage Space B / Quantity: 1

      Product Name: Product C / Location: Storage Space B / Quantity: 4 

       

      STORAGE SPACE C  : 12 UNITS IN STOCK

      Product Name: Product A / Location: Storage Space C / Quantity: 5

      Product Name: Product C / Location: Storage Space C / Quantity: 6

      Product Name: Product B / Location: Storage Space C / Quantity: 7

       

      TOTAL UNITS IN STOCK -  30