7 Replies Latest reply on Apr 6, 2017 8:18 PM by taylorsharpe

    How can I count related data with a special parameter?

      Hi,

      I have a quite simple database with companies, contacts and materials per contact. Now I want to show the number of a special types of materials in a list of companies for each company. Among materials we have "photo" or "document". Each material recordset has a field "type".

       

      I tried something like a sum together with an if-clause but it always counts all related material recordset if at least one of it had the right type or nothing.

       

      Any ideas?

      Luna

        • 1. Re: How can I count related data with a special parameter?
          taylorsharpe

          The sum will count based on the join relationship and if you don't want it to count all related material, the join may need additional relationships added. 

           

          Personally I use SQL for a lot of this type of thing. 

           

          Let ( [

               F1 = CompanyID ;

               F2 = "SELECT

                              CompanyName,

                              type,

                              COUNT ( * )

                         FROM

                              MaterialsTable

                         WHERE

                              CompanyID = ?

                         GROUP BY

                              CompanyName,

                              type" ;

               F3 = ExecuteSQL ( F2 ; Char ( 9 ) ; ¶ ; F1 )

          ] ;

               F3

          )

           

           

          Of course this just gives you an array of company, type and the count of what is in each type and I'm not sure that is what you wanted.  But it is a starting point for discussion.  Maybe you wanted to know how many types for the company and if so, you would have an F4 that is ValueCount ( F3 ). 

          • 2. Re: How can I count related data with a special parameter?

            Thanks. In Case of many companies this seems to add up... Is there a way

            to do it with a TO-relationship and/or a formular-field without SQL but

            something like

             

            count ( if (... ))

             

             

            Am 06.04.17 um 16:08 schrieb Taylor Sharpe:

            >

                How can I count related data with a special parameter?

             

            Antwort von Taylor Sharpe

            <https://community.filemaker.com/people/taylorsharpe?et=watches.email.thread>

            in /Discussions/ - Komplette Diskussion anzeigen

            <https://community.filemaker.com/message/654327?et=watches.email.thread#654327>

            >

            • 3. Re: How can I count related data with a special parameter?
              philmodjunk

              When you reference a field from a related table such as:

               

              Count ( If ( RelatedTO::Field = "A" .....

               

              The calculation only references the "first" related record from RelatedTO. It doesn't reference data from any other records. That's why your original attempt failed.

               

              WIthout SQL, you have two options:

               

              Use a relationship that matches only to the records you want to count. Such as:

               

              Companies::__pkCompanyID = MaterialsByType::_fkCompanyID AND

              Companies::gSelectedMaterialType = MaterialsByType::MaterialType

               

              gSelectedMaterialType can be a global field and by selecting a type in that field, your relationship will only match to records for the current company that also are of the type selected in the global field. Count ( MaterialsByType::_fkCompanyID ) will then return the expected result.

               

              Option 2:

              Set up a one row portal to Materials where the relationship is the usual match by company ID. But now add a portal filter expression such as:  Materials::Type = "Photo". Put a "count of" summary field defined in the Materials table in this one row portal and you'll get a count of the number of materials of type "photo" for that company. You can also use a field in this filter instead of quoted text to use the same portal to select for different types.

               

              Note that the filtered portal approach can produce layouts that are slow to update if you have a lot of records in your table.

              • 4. Re: How can I count related data with a special parameter?

                Thanks! There are different option - great ideas!

                 

                 

                Am 06.04.17 um 17:03 schrieb philmodjunk:

                >

                    How can I count related data with a special parameter?

                 

                Antwort von philmodjunk

                <https://community.filemaker.com/people/philmodjunk?et=watches.email.thread>

                in /Discussions/ - Komplette Diskussion anzeigen

                <https://community.filemaker.com/message/654386?et=watches.email.thread#654386>

                >

                • 5. Re: How can I count related data with a special parameter?
                  philmodjunk

                  Please note that while I answered your question, my own personal preference is to use ExecuteSQL and thus keep my relationship graph simpler. I do recognize that not all FileMaker Developers are comfortable with ExecuteSQL however.

                   

                  And I also would not write the query as presented in Reply #1 here as it encloses field and table occurrence names in quotes. If you use that method and later rename a table occurrence or a field, this query starts returning a ? to show that there is now a syntax error. I use a different method of composing the query that uses custom functions to avoid "quoting" any names. My method even uses a series of custom functions to write most of the query calculation for me to save time and avoid typos.

                   

                  You can find the custom functions and information on their use in the latest release of:

                   

                  Adventures in FileMaking #2-enhanced value selection

                   

                  See the "flexibile SQL" tab in the "Button Bars as Value Lists" example.

                  1 of 1 people found this helpful
                  • 6. Re: How can I count related data with a special parameter?

                    eSQL is no prob for me - just wanted to find the "fastest" way. As it

                    ssems that to many TO-Relations slow down the database (at least while

                    starting it) I appreciate eSQL if this is really the fastest way.

                     

                    I also will have a look in your suggestions!

                     

                    Am 06.04.17 um 17:38 schrieb philmodjunk:

                    >

                        How can I count related data with a special parameter?

                     

                    Antwort von philmodjunk

                    <https://community.filemaker.com/people/philmodjunk?et=watches.email.thread>

                    in /Discussions/ - Komplette Diskussion anzeigen

                    <https://community.filemaker.com/message/654394?et=watches.email.thread#654394>

                    >

                    • 7. Re: How can I count related data with a special parameter?
                      taylorsharpe

                      I know you commented that you wanted the "Fastest" way.  I'm not sure if you meant in development or performance. But one word of caution is that ExecuteSQL in FileMaker still uses the same Draco engine and usually gives about the same performance using it the traditional FM way.  However, strategically using ExecuteSQL to avoid jumping between layouts and similar short cuts can make it feel like it is performing faster.  Also, ExecuteSQL lends itself well to Perform Script on Server which can often speed things up a lot.  I just wanted to make sure you realized that just because you use SQL does not make it faster and there are even some times when it can make things slower (e.g., multiple joins, using LIKE, etc.).