10 Replies Latest reply on Apr 25, 2012 11:18 AM by philmodjunk

    Easiest way to count how many...

    JCPython

      Title

      Easiest way to count how many...

      Post

      Im looking for a way to get a count of how many ames and female records i have, (its a dropdown list), also each records holds a field that is auto enter the current year on creation, id like to show the number of records created per year. the number of records that have a value of true out of a true or false radio field. what would be the easiest way to achieve such results, i just want to make a small list of an overview on the data to display to the users.

        • 1. Re: Easiest way to count how many...
          philmodjunk

          Im looking for a way to get a count of how many ames and female records i have

          How's that again? "ames"???

          It sounds like you want a report listing counts in a number of different ways. A summary report with sub summary layout parts, summary fields and the correct sort order might be able to produce what you want here.

          Example:

          You can use a Date Created Field to auto-enter the creation date. a cYearCreated field can be defined as Year ( DateCreatedField ) and then you can sort your records by cYearCreated and use a sub summary layout part with a summary field to display the number of records created in each year. The individual records need not be listed in such a report--just the various sub totals.

          • 2. Re: Easiest way to count how many...
            JCPython

            my bad, males is what i meant not ames lol.

             

            i know how to ad the sub sumarry somewhat, but im not sure if that will work for what im trying to do, i have my main layout and all i want to do is add several text lines such as:

             

            # of males:

            # of Females:

            # of Different Morphs

            # of different species

            # of feedings due today (based on a field i have that calculates to get the animals next feeding date)

            # of Customers (all contacts have a checkbox that have [customer,vet,vendor] as the options as a way to catagorize)

            # of vets (all contacts have a checkbox that have [customer,vet,vendor] as the options as a way to catagorize)

            # of Vendors (all contacts have a checkbox that have [customer,vet,vendor] as the options as a way to catagorize)

            ect...

             

            and i want to just place these bits of text on the main layout for my Reptiles table as just a overview (Quick View) of all the data.

            • 3. Re: Easiest way to count how many...
              JCPython

              and i already have the cYearCreated field, im just not sure how i can get all the totals of these fields

              • 4. Re: Easiest way to count how many...
                philmodjunk

                Not all of these can be counted the same way and much depends on the structure of your database--not something I can recall from day to day.

                Males and Females could be counted via a relationship that matches cMale and cFemale to a gender field.

                Number of unique Morphs--depends on whether or not you have a table where you have a single record for every Morph or not. And also on whether you want a simple grand total or all those meeting other criteria such as the year.

                Feedings due to day can be set up like gender counting, but using an unstored Calculation field with get ( CurrentDate ) as the field to match to your next feeding date field.

                # of customers total in a customers table? Customers that currently have an animal listed in your database? Over specific time interval such as this year? or ????

                The same applies to number of vets and vendors.

                • 5. Re: Easiest way to count how many...
                  JCPython

                  Males and Females could be counted via a relationship that matches cMale and cFemale to a gender field.

                  do you mean i need to create a seperate table occurence in the relationship graph? with a calculation for male and female fields like: "Male" and "Female" as the specified data? would thi be similar to the cStringBasic field that you pointed out to me in that reptile morph example databse i sent you?

                  Number of unique Morphs--depends on whether or not you have a table where you have a single record for every Morph or not. And also on whether you want a simple grand total or all those meeting other criteria such as the year.

                  Each reptile has a morph but not every morph in the morph table may not be related to a reptile. each reptile can only have one morph, i want to only list the morphs that are currently related to any reptile.

                   

                  Feedings due to day can be set up like gender counting, but using an unstored Calculation field with get ( CurrentDate ) as the field to match to your next feeding date field.

                  I think i can figure this out, i already got a layout that displays all the records with a nextfeeddate if match the current date, i just need to ad a field that gets the foundset count? and then use <<getfoundsetcount>> on the reptiles layout. please correct me if im wrong...

                  # of customers total in a customers table? Customers that currently have an animal listed in your database? Over specific time interval such as this year? or ????

                  yes the customers table is basically the contacts starter solution i just intergrated it and added some extra fields, i just want to display the number all all customers, regardless if they bought anything or not, same as for vendors and vets, just the total number of how many there is.

                   

                  • 6. Re: Easiest way to count how many...
                    philmodjunk

                    do you mean i need to create a seperate table occurence in the relationship graph?

                    Yes, that's the idea. It's not the only way to do it, but I think you'll need that approach given the additional totals that you want at the same time.

                    Each reptile has a morph but not every morph in the morph table may not be related to a reptile.

                    I read that as every reptile record in your table. Let me know if it's not.

                    Define a calculation field, cReptileFlag, as:

                    Reptiles::MorphID

                    and Clear the "do not evaluate if all fields are empty" check box.

                    Then if you have these relationship:

                    Layout'sTableOccurrence::anyField X Morphs::anyField
                    Morphs::MorphID = Reptiles::MorphID

                    count ( Morphs::cReptileFlag )

                    or a summary field defined as the "count of" this same field in the Morphs table will give you the count of all Morph records that link to at least one record in the Reptile table.

                    I think i can figure this out, i already got a layout that displays all the records with

                    Not quite. There's no found set to count. but the count function or it's equivalent "count of" summary field like I have already described, can count your related records here as well.

                    yes the customers table is basically the contacts starter solution

                    Ok, but how did you set it up? Do you have separate tables for customers, vendors and vets or do you have one table with a field that identifies the contact as 1 or a combination of these? You can get your counts with either approach.

                    • 7. Re: Easiest way to count how many...
                      JCPython

                      Yes, that's the idea. It's not the only way to do it, but I think you'll need that approach given the additional totals that you want at the same time.

                      So i would put the cmale and cfemale fields in the reptile table, create a occurence of that table, but how would i relate those tables?

                       

                      I read that as every reptile record in your table. Let me know if it's not.

                      Define a calculation field, cReptileFlag, as:

                      Reptiles::MorphID

                      and Clear the "do not evaluate if all fields are empty" check box.

                      Then if you have these relationship:

                      Layout'sTableOccurrence::anyField X Morphs::anyField
                      Morphs::MorphID = Reptiles::MorphID

                      count ( Morphs::cReptileFlag )

                      or a summary field defined as the "count of" this same field in the Morphs table will give you the count of all Morph records that link to at least one record in the Reptile table.

                      ok that has worked very well, and i will use this as well but what i really need is to display one the number of diferent morphs in the database, meaning if two reptiles have the morph "Albino" related to them then only one is counted. if this makes sence.

                      Not quite. There's no found set to count. but the count function or it's equivalent "count of" summary field like I have already described, can count your related records here as well.

                      Oh ok...i created a field called cFeedingsDueToday -- Calculation, result is a number and cleared the do not evaluate box. for the calculation i put Count ( reptile_next_feed_date ). result is returned as "1"

                      Ok, but how did you set it up? Do you have separate tables for customers, vendors and vets or do you have one table with a field that identifies the contact as 1 or a combination of these? You can get your counts with either approach.

                      Yes the customers, vendors and vets are all in one table with a checkbox to select the type of contact.

                      • 8. Re: Easiest way to count how many...
                        philmodjunk

                        but how would i relate those tables?

                        Reptiles::cMale = MaleReptiles::Gender

                        Reptiles::cFemale = FemaleReptiles::Gender

                        Then count ( maleReptiles::ReptileID )

                        counts all male reptiles in the table.

                        Come to thinkg of it, you can also use this relationship:

                        Reptiles::anyfield X AllReptiles::anyfiled

                        Then define a summary field as the count of some field in the table--such as your serial number field that is never empty. Place two filtered one row portals to AllReptiles, but use different portal expressions such as AllReptiles::Gender = "Male" to limit the portal records to a specific gender.

                        This approach requires FileMaker 11 or newer, but simplifies your relationshp graph by quite a bit.

                        But what i really need is to display one the number of diferent morphs in the database

                        Don't you have only one record in Morphs for each type of Morph? Thus only one Morph record for Albino? This method should only count the records in Morphs and this should then not count a morph more than once.

                        Oh ok...i created a field called cFeedingsDueToday -- Calculation

                        with what relationship?

                        You need a calculation, cToday defined as Get ( CurrentDate ) and which is unstored. Then you can set up a relationshp:

                        Reptiles::cToday = FeedTodayReptiles::NextFeedDate

                        and Count ( FeedTodayRepties::NextFeedDate )

                        will count the reptiles to be fed today. Note the use of a table occurrence name inside the count. Count ( fieldname ) will return either a 1 or a zero depending on whether there is or is not dat in the field. It only references the field of the current record.

                        Yes the customers, vendors and vets are all in one table with a checkbox to select the type of contact.

                        I've just spelled out two different approaches for counting mail and female reptiles. Both methods would also work for counting customer, vendor and vet records in the contacts table (and will work even if a contact has more than one check box selected.)

                        • 9. Re: Easiest way to count how many...
                          JCPython

                           

                          Ok i understand how to count the males and females now, it works great, i applied the same setup to count the contacts. I added three fields to the contact table:

                          cVets - Calculation defined as "Vets"

                          cCustomer - Calculation defined as "Customer"

                          cVendor - Calculation defined as "Vendor"

                          countTotalVets - Calculation defined as Count ( Contacts~Vets::ContactID )

                          countTotalCustomers - Calculation defined as Count ( Contacts~Customers::ContactID )

                          countTotalVendors - Calculation defined as Count ( Contacts~Vendor::ContactID )

                           

                          Relationship were setup the same as i would for the counting of males and females, but no results came from the counting of contacts. I will continue to play around with it.

                           

                          As for the morphs...i have 85 reptile records, five of those records are listed as the "piebald" morph, two of them are "albino" and twelve are "pastel" and the remaining 66 records are listed as the morph "Normal". with a count im looking for the result should be "4" and not "85". My fields are:

                          Reptiles::cReptileFlag - Calculation Defined as Reptiles::MorphID (Number)

                          Reptiles::countReptileMorphsAssigned - Summary Count of cReptileFlag

                          i created a table occurence of Reptiles table ("Reptiles Morph Count") and set up the following relationship

                          Morphs::MorphID = Reptiles Morph Count::MorphID

                          Morphs::Mutation x Reptiles Morph Count::Reptile_Status (when you said relate any field to any field i assumed this was ok)

                           

                          You need a calculation, cToday defined as Get ( CurrentDate ) and which is unstored. Then you can set up a relationshp:

                          Reptiles::cToday = FeedTodayReptiles::NextFeedDate

                          and Count ( FeedTodayRepties::NextFeedDate )

                          will count the reptiles to be fed today. Note the use of a table occurrence name inside the count. Count ( fieldname ) will return either a 1 or a zero depending on whether there is or is not dat in the field. It only references the field of the current record.

                          i done the above but no luck, is FeedTodayReptiles a occurence of the Feedings table or Reptiles Table, i tried both ways but no luck.

                          Also if i want to display all these counts from different tables and display them on the Reptiles layout, they all need to be related to get count result of contacts, morphs, and other tables, what is the best field to relate all these tables, ReptileID = ContactID, ReptileID = InvoiceID ect...

                           

                          Thanks so much for taking the time to help me out, as soon as i get some cash saved up im taking the filemaker trainning courses so i wont be such a pain.

                           

                          • 10. Re: Easiest way to count how many...
                            philmodjunk

                            no results came from the counting of contacts.

                            Check the data type of your check box field. It should be text. You have one field with three checkboxes correct?

                            Make sure that you have text as the return type in the calculations.

                            Make sure that the values returned exactly match the values in your check box. (Note if some one has both Vets and Customer checked in this field, they should be counted as both a vet and a customer when you have a single text field with three check box values as the match field in Contacts.

                            As for the morphs...i have 85 reptile records,

                            Which is why we want to count related records in the morphs table, not the reptiles table. This is a many to one relationship from reptiles to morphs so you should get just the count of how many morphs have a related reptile record--4.

                            Let's turn these relationshps around.

                            First, we have based this layout on reptiles, haven't we? (If not use the occurrence for your layout in place of Reptiles here.)

                            So we need a relationship that matches to all reptiles records:

                            Reptiles::anyField X AllReptiles::anyfield
                            AllReptiles::MorphID = ReptileMorphs::MorphID.

                            ReptileMorphs will be an occurrence of Morphs.

                            use Count ( ReptileMorphs::MorphID ) and you'll get a count of the morphs that are related to at least one Reptiles record.

                            That's simpler and I tested it just now to be sure that it works.

                            is FeedTodayReptiles a occurence of the Feedings table or Reptiles Table?

                            errr, did I missing something? I didn't know you had a feedings table here.Wink I had assumed a date field in the reptiles table. You'll need to explain the relationship between the feedings table and your Reptiles table. I'd need to see that for sure, but if the feeding date is a field inthe feedings table and a record in the feedings table tells you the date of the next feeding, then feedTodayReptiles would be an occurrence of your feedings table.