10 Replies Latest reply on Jul 25, 2014 6:04 AM by philmodjunk

    Summary of records

    David40

      Title

      Summary of records

      Post

           I am using FMP-9.  I am running Maverick on my iMAC.

           I have several tables that show a count of records.  I also have a summary page that list various information and I have tried to add the record counts from these tables.  Two of the record counts only show either a "1" or a "0".  When I look at the field name, it shows "::recordcountname".  The record counts that show accurate values are missing the "::".

           What am I missing?  The record counts work fine on the tables, but when I inserted them on my summary page, I ran into the above problem.

            

           Thank you in advance,

            

           Dave

        • 1. Re: Summary of records
          philmodjunk

               There are two ways to get an aggregate value such as a count. (Aggregate values are values based on the data in the same field of multiple records.)

               You can use a calculation field with the Sum function (if you have a relationship that works).

               Or you can use a summary field (which may still be controlled by a relationship in many cases.)

               What exact method are you using to count records?

               And do you want a count of all records in the table or just a subset of all the records in the table?

          • 2. Re: Summary of records
            David40

                 Thank you for your reply.

                 I am not having a problem with the summary field.  On all my tables, I have a "count of records" shown at the top of the page.  They work perfectly.

                 My problem is when I try to show these "counts" on a summary page.  As noted above, one total shows up correctly, while the other two show either a "1" or a "0".  The two fields that show a "1" or "0" have the field name preceded with a double colon "::".   The field that shows the correct value does not.

                 Could this be a relationship problem?

                 Thank you again!

                  

                 Dave

            • 3. Re: Summary of records
              philmodjunk

                   Sorry but that does not answer my questions fully.

                   

                        On all my tables [layouts], I have a "count of records" shown at the top of the page.

                   How exactly did you set up that "count of records"? Is that a symbol inserted from the insert menu, some kind of calculation field that you defined or something else?

                   As I stated in my previous post, computing an aggregate value, such as the count of how many records are in a found set, a related set or that are in the table as a whole, is normally computed from an aggregate function call such as count or by referring to a summary field defined in the table where you want to count records.

                   Other methods, such as a special layout symbol, get ( FoundCount ) or get (TotalrecordCount) might be used in certain circumstances though only the last one, Get ( TotalRecordCount ) could be used from the context of a layout based on a related table.

                   And you still have not indicated your answer to this:

                   

                        Do you want a count of all records in the table or just a subset of all the records in the table?

                    

              • 4. Re: Summary of records
                David40

                     I apologize for not answering your question.

                     I simply want to count all the records I have on a table and have that quantity appear on a separate summary page.  

                     The field I am using is a "summary" field that is a "count of the records".   It displays the correct number of records on the table, but not on the summary page.  I have attached a copy of my summary page with the missing information.

                • 5. Re: Summary of records
                  philmodjunk

                       And on what table is your summary page based?

                       The relationship between the summary page's table and the table where you have the records you want to count will control what total is returned by a summary field defined in that related table.

                       So if we call the table for the above layout "Summary", you might set up a relationship like this to get a count of all records in a different table:

                       Summary::anyfield X TableToCount::anyField

                       You open manage | Database | Relationships and drag from any field defined in Summary to any field in TableToCount. Then you double click the relationship line and change the = operator to the Cartesian join operator, X in the dialog that opens up. This special operator matches any record in Summary to all records in TableToCount so a summary field defined in TableToCount but placed on your Summary layout will show a value based on all records in the TableToCount table.

                       Had we used a different relationship, the value returned by Summary might be different as the total would then be based only on those records in TableToCount that match to the current record in Summary.

                       Note: This method requires that you have at least one record in the found set of your Summary layout.

                  • 6. Re: Summary of records
                    David40

                         I appreciate your suggestions and assistance, but I think I may be sending you in the wrong direction.  My terminology may be the problem.

                         The page I uploaded is merely a "Welcome Page", "Menu Page", or simply a page that allows you to go to whatever table you want.  It is not a summary page.  I added the three "count fields" to simply give me a quick over view of the number of records I have in each table.  These same fields are on their respective table and show the correct number of records.  I have checked and re-checked the relationship table and they are correct.

                         When I changed the operator from "=" to "X", the number changed from "0" to "83" (it would be interesting to see what are those records).  

                         What is odd to me is that the two count fields that read "0" have a double colon (::) in front of them, while the count field that reads correctly, does not.

                         I have tried "counting" other fields within the table, but the results are still the same.

                         If I remove these three fields from the "Welcome Page", it is no great loss.  In the past, I have been able to resolve problems with my FMP-9 manuals or the great help of this forum.  I know this is an easy fix to a non-critical problem and I apologize for taking up so much of your time.

                    • 7. Re: Summary of records
                      philmodjunk
                           

                                The page I uploaded is merely a "Welcome Page", "Menu Page", or simply a page that allows you to go to whatever table you want.  It is not a summary page.

                           There is really no such thing as a "summary page". That's just a label I used to refer to the layout shown in your screen capture. Any layout you add to your database MUST be based on a table occurrence (a box in Manage | database | relationships). You cannot create a layout without such a setting selected in "Show Records From". You can check this in Layout Setup... The relationship between your layout's table and other tables in your database will control how summary fields and aggregate function calculations evaluate.

                           

                                When I changed the operator from "=" to "X", the number changed from "0" to "83"

                           which suggests that you have 83 records in the related table. You at least have 83 records where this field is not empty. And this would be the expected result if you want to count all records in that table is it not?

                           

                                two count fields that read "0" have a double colon (::) in front of them, while the count field that reads correctly, does not.

                           If there is a :: in front of the field's name, it is not selected from the layout's table occurrence. It refers to a field from a different table occurrence. If you click that field while in layout mode, you can see the name of that table occurrence in the "Display dat from" box on the Inspector's data tab. My best guess based on limited information here is that the field without the :: is correctly counting records in the found set of your layout's table. The two that do not are evaluating from the context of a relationship that currently matches to zero records in the related tables.

                      • 8. Re: Summary of records
                        philmodjunk

                             To learn more about Table Occurrences, what they are and how to use them, see: Tutorial: What are Table Occurrences?

                        • 9. Re: Summary of records
                          David40

                               Thank you for your assistance.  

                               It appears from your last note that what I am trying to accomplish is not possible in FMP-9.

                               In closing, I will leave with another example.  I have three phone books representing three cities in one state.  I would like to see the number of names on each phone book in one place without having to go to the individual books.

                               If it is possible, I would be interested in seeing how it is done.

                          • 10. Re: Summary of records
                            philmodjunk

                                 I am quite certain that what you want to do IS possible in FileMaker 9.

                                 But what you now describe does not match your original post. You are no longer counting all the records in the table, you are counting all of the records in a subset of the records in that table based on matching criteria--the number of phone numbers in each state. This would be much easier to do in a later version of FileMaker where Filtered portals and ExecuteSQL become available tools to use, but this was something that can be done in FileMaker 3, let alone FileMaker 9 though it takes more work to do in some circumstances.

                                 Summary-----<Cities------<PhoneNumbers

                                 Summary::anyField X Cities::anyField
                                 Cities::__pkCityID = PhoneNumbers::_fkCityID

                                 With these tables and relationships in place and one record for each city in the Cities table, You can define a calculation field in Cities as: Count ( PhoneNumbers::_fkCityID ) to get the count of PhoneNumbers in each city. A portal to Cities can be placed on your Summary layout that includes a City name field and this calculation field and you will then get a list of cities and the number of phone numbers in each.

                                 Alternatively, you can use a summary field defined in PhoneNumbers and set up a summary report on a layout based on PhoneNumbers where you use a Sub Summary layout part and this summary field to show the number of Phone Numbers in each city. (And this can be done without listing all the individual phone numbers.

                                 And a third option would be a list view based on Cities that shows this same data by using the same calculation field or the summary field from PhoneNumbers.