AnsweredAssumed Answered

Count Portal Records with a field value of X

Question asked by tono on Jul 30, 2013
Latest reply on Jul 30, 2013 by philmodjunk


Count Portal Records with a field value of X



     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?