1 2 Previous Next 25 Replies Latest reply on Mar 23, 2016 8:12 AM by erolst

    Counting Related Records

    sccardais

      I want to create a report that includes a count of related records.

       

      The layout is based on a table "Price Tiers". The layout will pull fields from two other tables based on the relationships shown below.

       

      Price Tier Relationships.png

       

      This is a mockup of the report I want to create. I'm having problems getting a correct value for the 3rd column - Count Users in Tier. A calc field in ORGS that counts records in Users is not giving me the correct count. The value in the 2nd column (Count Orgs In Tier) is correct. It shows the total count of all Orgs in each Price tier based on the relationship between Price Tiers and Orgs.

       

      Price Tiers.png

      For each Price Tier, I also want to show the total count of Users from the User table. Any help would be appreciated.

        • 1. Re: Counting Related Records
          CamelCase_data

          Add an unstored calculation field with the formula Get ( FoundCount ) on the Users table and use that field for the 3rd column.

          • 2. Re: Counting Related Records
            sccardais

            CamelCase:

             

            Thanks for your suggestion but the calc you suggested is returning a count of every record in Users with the same value for all Price Tiers.

             

            The 2nd column in my report is showing only the number of Orgs in each Price tier if the Status in Orgs matches g_Status in Price Tiers AND if the Product in Orgs matches g_Product in Price Tiers. Assuming the result for Tier 1 = 200 Orgs. I want to know how many Users this represents in USERS.

            • 3. Re: Counting Related Records
              CamelCase_data

              Not sure you've set this up right in that case. Did you set the calculation field in Users to be unstored, and are the relationships really as you described? Maybe you can do a screen shot of the relationship graph and the field definition...

              • 4. Re: Counting Related Records
                sccardais

                You are right. I forgot to set the field to Unstored.

                 

                Thanks very much.

                • 5. Re: Counting Related Records
                  keywords

                  CamelCase201507 wrote:

                  Add an unstored calculation field with the formula Get ( FoundCount ) on the Users table and use that field for the 3rd column.

                   

                  I beg to differ. I agree it should be an unstored calc (it will be in any case because it will reference a related table), but the Calc needs to be in the Orgs table and should be Count ( Users::OrgID ). This will deliver the number of User records that are related to any given Org record. The count you need is nothing to do with found sets, hence FoundCount is not what you need.

                  • 6. Re: Counting Related Records
                    CamelCase_data

                    keywords If you have tested, you will see that both methods return the same result, and both are certainly valid approaches.

                    The advantage is see in the one I propose is that you can use the same field from a number of different contexts to display the number of records found over a relationship, be it from Price Tiers, Orgs, Users itself, or any other table occurrence one may have in the database.

                    I normally add such a "FoundCount" field on every table in every databases I create. You can just copy-paste the same field definition, without needing to adjust for which field the records are related on.

                    If you e.g. need to be able to do finds on the count, the method you propose is indeed the way to go.

                    • 7. Re: Counting Related Records
                      sccardais

                      CamelCase:

                       

                      Thanks very much.

                       

                      I appreciate your explanation of the pros and cons of one technique over the other. I’m going to experiment with your idea by adding Get(FoundCount) to every one of my tables but honestly, I don’t understand why it works and that’s a little troubling!

                       

                      If I’m trying to count the number of records that match a relationship between A and B, I’d expect the field that does the counting to be in A. Obviously I’m missing something. Can you explain or point me to something that might explain this more?

                       

                      Thanks again.

                      • 8. Re: Counting Related Records

                        Could you include a simple matrix (spreadsheet data format, for example) of actual data for each of the tables and related tables?  With that matrix please indicate what the sums should be vs. what they are.

                         

                        Getting sums in related tables is simple and straightforward so it's confusing why you're having troubles.

                         

                        Your screenshot is a good start, but isn't sufficient to really explain what's going on. As Camelcase above suggested, you need to present more documentation about your situation.

                         

                        - m

                        • 9. Re: Counting Related Records
                          CamelCase_data

                          First of all, as you say, it does work, and reliably so - which sort of is the major reason.


                          As for WHY it works, the are people around who can explain that more elegantly... I would say that you need to see the Found Count as something relative, which depends on the context in which it's being evaluated (as so much in FileMaker, and in the world in general for that matter). A field is also always evaluated in a given context (which is why you need this to be an unstored calculation field).

                          Say that you have a layout based on the Price Tiers table occurrence, with a relationship to order Orgs.

                          If you are looking at all just the currently Price Tiers records, then the found count will be the 5 or however many Price Tiers records you have.

                          If you look at it from the perspective of a single Price Tier record, you see that you also have found records on other tables - Orgs in this example. So when you're showing Orgs::FoundCount on this layout, the number or records found is the number of records found from the standpoint of the given Price Tier record. If you add a portal for Orgs on the same layout, this will be even more visible - you'll get as many lines as the FoundCount.

                          • 10. Re: Counting Related Records

                            Here's a video that might help the OP:

                             

                            https://www.youtube.com/watch?v=CyezggeYNLY

                             

                            - m

                            • 11. Re: Counting Related Records
                              sccardais

                              CamelCase:  In the past, I’ve added multiple fields in my Parent table to count the number of records in a related child table - even if the related table was a self-join. This approach just didn’t seem right because it required so many single purpose calc fields that only worked from a single context.

                               

                              Your approach, Get(FoundCount) may be a solution. I’m looking forward to testing.

                               

                              Once again, thanks for your explanations.

                               

                              Morkus: I frequently struggle with getting a correct count of related records. I know it should be easy but I seem to struggle with it for some reason. As I said above, I frequently resort to creating many single purpose calc fields that clutter up my field definitions and I “know” there must be a better way. I’m sure it has something to do with a fundamental misunderstanding of how “context” works. Conversations like this with support from this forum is a fantastic resource and I greatly appreciate your help.

                              • 12. Re: Counting Related Records
                                CamelCase_data

                                sccardais wrote:

                                 

                                CamelCase:  In the past, I’ve added multiple fields in my Parent table to count the number of records in a related child table - even if the related table was a self-join. This approach just didn’t seem right because it required so many single purpose calc fields that only worked from a single context.

                                Been there, done that! Until someone (thanks vgilet@lasource.fr) showed me the Get ( FoundCount ) trick back in 2007 - I haven't looked back since!

                                • 13. Re: Counting Related Records
                                  beverly

                                  FoundCount is based on a find of records (scripted or manual) in a table. It is not based on the counting of related records. Perhaps you need this function:

                                   

                                  https://www.filemaker.com/help/14/fmp/en/html/func_ref1.32.3.html#1027699

                                   

                                  In the "A" (parent) you can place the field (calculated, unstored) "count_related" (or named however you desire):

                                   

                                  = Count( relationship_to_B::myField_fk )

                                   

                                  The Count() function works on non-blank fields, so using a foreign key (or a primary key) to get the number of related records works well.

                                   

                                  beverly

                                  • 14. Re: Counting Related Records

                                    Check out the video I posted. It may be helpful.

                                     

                                    Usually, what I do in situations like this is to create a quick "sample" play solution. Create some data and play around with various options like unstored calcs and such until I get it and see what's going on.

                                     

                                    We all struggle with FMP from time to time. There is SO MUCH stuff in there, it can be mind-boggling.

                                     

                                    You're asking all the right questions and you have good curiosity. Both signs of an excellent developer! 

                                     

                                    Glad to help.

                                     

                                    - m

                                    1 2 Previous Next