1 Reply Latest reply on Jul 30, 2013 1:06 PM by philmodjunk

    Count Portal Records with a field value of X

    tono

      Title

      Count Portal Records with a field value of X

      Post

           Hi!

           I have a problem with portals and COUNTING how many records inside that portal have a specific value in a field (from the related table).

           It works like this:

           I have a Orders Table. Orders has a one-to-many relationship with the Products Table (i.e. a Order must have many different products). The Products table has a field called 'status'. This field is updated with the current status for that specific product (i.e. 'started', 'inspection', 'finished', etc). Also, this status might tell us when the production of this specific product failed by setting the 'status' field to "F - inspection" or "F - assembly" etc. 

            

           I have a Layout of the "Orders" table. In this layout I have a Portal with all of the related Products. the portal has the following columns and might look like this:

           ProductType        ProductSerialNumber      Status

           A                                    15983                 Assembly

           A                                    15990                 Assembly

           CA                                  23922                F - Inspection

           B                                    16013                  Finished

            

           What can I do to set a field named something like "finishedCount" which counts how many of the related Products have a "Finished" Status, and btw, in THE SAME LAYOUT as the portal?

           what I did, in a rather unorthodox way, is to make a summary field in the Products table. Then make one Table Ocurrence  for every thing I wanted to count and create a relationship to a newly created field in the Orders table called "finishedCountHelper" or something like that. 

           To illustrate: I made a TO of the Products Table named 'FinishedCountRel', in this I created a relationship between the 'status' and "Orders::finishedCountRel". Also, I again created the Product->Order relationship.

           With a script, I set all the "Orders::finishedCountRel" in all the records of Orders to "Finished". 

           That way, my TO FinishedCountRel will show only the Products with the status "Finished" and, if you insert the summary field in my Orders Layout, I can see how many products have been finished. All is good, right? wrong. 

           The problem comes when I want to see that for the 10+ kinds of status. Plus their failed counterparts. Also, I want to count something similar to this, but with will take me another 20 TO at least. I know that this is in no way the most elegant solution, but is the only way I have managed to do this. 

           Summaries do help but count ALL the records. And I have not found a way to use the Count function with a filter. And GetSummary function needs a TO with a relationship to work, so its almost the same as my solution. Also, I don't quite understand how a subsummary and a break will work

           Even though, there's got to be a better way..  does anyone here knows how to do this? Is it even posible?

            

        • 1. Re: Count Portal Records with a field value of X
          philmodjunk

               Do you need only to display these counts or will you need to use those values in scripts or calculatons?

               For display only purposes, you can place that summary field in a one row portal where a portal filter expression omits all related products records except for those of the desired category.

               Another option that can pull such data together for you is ExecuteSQL (FileMaker 12 or newer only).

               Also a table of your different categories (One record per category) may be used to compute each of the different groups of records). This allows you to have single portal with a different count in each row of the portal.Each record in thi intermediary table matches to a new occurrence of Products by both OrderID and by Status.