4 Replies Latest reply on Dec 20, 2012 11:02 AM by productionQC

    Corresponding Count Calculation...

    productionQC

      Title

      Corresponding Count Calculation...

      Post

           Perhaps someone can help me with this problem.  I have tried several ways to accomplish this with no success.

           I have 2 tables, the main or parent is Tests and the other is Products.  I have a portal within the Products layout that which I want to list all of the entered product names, followed by a count of how many times each product has been tested.  This is the part I can't seem to make work.  I do not want to use a Summary report as this is not how we want the information displayed.  Rather we would like to have a calculation field or summary field to put in the portal that can count the number of test that contain a particular product name.  Thus we get a portal result that looks like this:

           Product A              25Tests
           Product B                5Tests
           Product C              18Test

           ETC...

           We have tried everything and can't seem to make it work.  Right now we have all of the products listed in the portal using a self relationship with products.  We just can't seem to figure out the "Count" function or any other method to give us the test counts.  

           Any suggestions?

            

           Thanks

            

        • 1. Re: Corresponding Count Calculation...
          philmodjunk

               So you want a portal to products while on the products layout? And this portal lists all products or just some products?

               assuming all products:

               ProductsLayout -----X----ProductsPortal--------<Tests

               ProductsLayout::anyfield X ProductsPortal::anyfield
               ProductsPortal::ProductID = Tests::ProductID

               You can then put a summary field from Tests that counts productIDs in your portal row, or you can define a calculation field in ProductsPortal as:

               Count ( Tests::productID ), but if so, make sure to select ProductsPortal, not ProductsLayout as the "context" table occurrence for your calculation.

               If you are using FileMaker 12, you can also use a single calculation field in place of this portal: FMP 12 Tip: Summary Recaps (Portal Subtotals)

          • 2. Re: Corresponding Count Calculation...
            productionQC

                 Thanks Phil-  I thought it was that easy.  I had to re-arrange some of my setup but works great.  I went the route of using the summary field.  My biggest problem now is that I can not make a formula work in the portal.  What I am trying to do is list all of the products, the number of times they have been tested, and how many have been manufactured since the last test.  We have a guideline to test ever 150 products.  What I am trying to do is use a formula as simple as ProductVolume/TestCount to let me know if we are meeting the 150 objective, the frequency of our test.  I put a field in the portal containing the formula mentioned above.  The "TestCount" field is the summary field which lists the test count beside the list of products in the protal.  .  In the formula it gives a count of the total tests on all products.  Say I have a total of 125 test records in the database and my portal displays like this (the frequency is the problem part):  

                 NAME:         TESTS:     PRODUCED:          FREQUENCY:
                 Product A      100               500                                5           (500/100) (ProductVolume/TestCount)  Current results are 4  (500/125)
                 Product B        25               800                              32           (800/25)   (ProductVolume/TestCount)Current results are 6.4  (800/125)                        

                 and so on....

                  But when I use the same count field in a calculation field it simply divides the the ProductVolume by the TOTAL number of tests, the count field is always 125, instead of the product count. So obviously I always end up with 500/125 or 800/125 rather than 500/100 and 800/25.  Is there a way around this?  I simply want the count from the summary field that is working perfect to feed the same number to the calculation field, all in the portal.

            • 3. Re: Corresponding Count Calculation...
              philmodjunk

                   What context did you specify for the calculation? It sounds like you selected the occurrence specified for the layout instead of the occurrence specified for the portal. That's why I posted this information in my previous post:

                   

                        you can define a calculation field in ProductsPortal as: Count ( Tests::productID ), but if so, make sure to select ProductsPortal, not ProductsLayout as the "context" table occurrence for your calculation.

                   You specify the Context table occurrence for a calculation field by selecting it from the drop down found at the top of the specify calculation dialog.

              • 4. Re: Corresponding Count Calculation...
                productionQC

                     Hi Phil-  Thanks again for all your help.  I in fact did see that in your past reply and in fact had the context set correctly to productportal.  As It turned out the field that I had select to put in the portal was from products, not productportal.  I was blowing my brains out trying to figure out why I was getting the same answer for every line item in the portal (obviously it was calculating based on the current record and filling in all with that answer).  I have made the change and everything works great!

                     Have a Merry Christmas and I owe you a good bottle of Scotch.  Thank you on behalf of myself and everyone in the forum for taking the time to guide us through our learning curves.

                     Cheers!