5 Replies Latest reply on Oct 3, 2011 2:54 AM by DannyVu

    Counting values in different records based on a field

    DannyVu

      Title

      Counting values in different records based on a field

      Post

      Hi all,

      Sorry if this question has been raised before but I've just started using FM Pro and am kind of stuck on how to achieve the following.

      Say I've got a Table called "Products" which has a field called "Barcode" (primary key).  A second table is called "Purchases" which has the "Barcode" field from the "Products" table as a foreign key and a "Quantity" number field.

      How would I go about creating a calculation field in the "Products" table to count / add up all the quantities for each record in the "Purchases" table based on the "Barcode"?

      I'm using Filemaker Pro 11 with Mac OS X 10.6.8

      Thanks in advance,

      vak82

        • 1. Re: Counting values in different records based on a field
          mgores

          You probably need a summary field with the calculation set to be the sum of Purchases::Quantity, then set up your report to perform the find on Purchases::barcode or set it up to group by barcode to see the subsummaries for each item

          • 2. Re: Counting values in different records based on a field
            DannyVu

            Hi Mark,

            Many thanks for your prompt response.  I'll give it a try and let you know how I go. 

            Cheers,

            • 3. Re: Counting values in different records based on a field
              DannyVu

              Hi Mark,

              I've been able to gather the quantities using the group by barcode in a report using subsummaries.  Many thanks for that suggestion.

              In this case, is it even possible to have a dynamic calculation field in the "Products" table to do a sum of "Purchases::Quantity" based on the "Barcode" or is it something that will need some scripting?

              Any advise or assistance would be much appreciated.

              Thanks,

              Danny

              • 4. Re: Counting values in different records based on a field
                mgores

                You should be able to define a field, say cQty, as a summary field.  Then define it as a total of the Purchases::Quantity with the "restart summary for each sorted group" option checked.  If you have your report set up to group by bar code you should get the quantity for each Barcode in that report in the cQty field.

                • 5. Re: Counting values in different records based on a field
                  DannyVu

                  Hi Mark, and many thanks for your constant help.

                  I've been able to create a cQty field in the "Purchases" table to "=Total of Quantity (running with restart), when sorted by Barcode".  I can see in the table view of the "Purchases" table that it continues to add the quantities grouping by the "Purchases::Barcode".

                  The issue i'm struggling with is how do I go about having a field in the "Product" table which will reference to the "Purchases::cQty" where "Product::Barcode" = "Purchase::Barcode".  Would I use a calculation field with "if" statements?

                  The whole idea which i'm trying to achieve is that when i'm looking at any given record in the "Product" table, I want to be able to see the total stock available.  This total stock field will look up the "Purchases" table grouping by the "Product::Barcode" value (Primary Key) that I'm currently viewing and calculate the total quantity. 

                  Maybe i'm going about it the wrong way but any help would be appreciated.