5 Replies Latest reply on May 20, 2010 1:26 PM by niponki

    Counting Occurance of an Item in a Drop Down Menu



      Counting Occurance of an Item in a Drop Down Menu




      First time posting here, but I have received a ton of help already by searching and reading through what all you FM whiz's have had to say about some tricky problems.  So, thank you very much to everyone for the help so far!  :smileyhappy:


      Version: File Maker Pro 6 (I know I know..  we are waiting for an upgrade...  still waiting...  still waiting... haha)

      Platform: Windows XP

      Me: Fairly new user, basic understanding of how to get around and create things, but no master by any means. 



      I am creating a database that allows users to input various information about an item, all related to its serial number.  One of the layouts in my database has an area for a machinist to enter comments about his work on the item.  There are over 3000 items in the database, so each item has its own record.  Basically, he preforms a search for the serial number he wants, that record is pulled, and he enters in his comments.  For trackability purposes, the comments are in the form of a drop down menu.   



      I want to to be able to have a separate layout that is more or less a summary layout which will collect certain data from each of the layouts and display how many instances of "good, bad, etc" have been selected from the drop down menus.



      I think I should be using some sort of global field, but why doesn't a "running count - summary field" work?  I thought of using a calucation field that would be in the form of:


      IF (fieldName=machinistComments)

      COUNT (machinistComments)


      But I am not sure how to get it to read all the occurrances in all the records, not just the one shown on the current layout.  Also, I am pretty shakey on the syntax for it, but I think that is the least of my worries. 


      Any thoughts?


      Thank you

        • 1. Re: Counting Occurance of an Item in a Drop Down Menu

          Sounds like you need to add some relationships and talbes to your database.

          How many tables do you have?

          • 2. Re: Counting Occurance of an Item in a Drop Down Menu

            Hi, thanks for the reply. 


            I have all the data in the database related to the respective serial numbers.  I have a separate file that is linked to a parent file via a portal.  The user enters the data in the parent file with repsect to the appropriate serial number.  Is this what you mean by having some relationships set up, or did you have something else in mind?


            When you ask how many tables do I have, what exactly do you mean?  I mean, I have a table per layout, but I am sure that is not what you mean.  Sorry for the newbness :)



            • 3. Re: Counting Occurance of an Item in a Drop Down Menu

              Here's some basic info that might help you understand:



              A Table is used to store Unique Entities:  Contacts, Items, Invoices, etc.  Each Record in the table would have it's own unique Id, commonly known as a Primary Key.


              In your case, it sounds like you'd need to create the following tables:  Items, Machinists, Comments_List. 

              Each Item  and Each Machinist would have a unique value/primary key.  The Comments_List table would allow you to create some Many to Many relationships.


              Many Machinists might make comments on Many Items. 


              so your relationship would look something like this; (Tables are in Bold, with the related fields below each table)


              Items             ====>   Comments_List       <=====     Machinists

              pk_Item_id                   fk_Item_id                                 pk_Machinist_id





              By establishing these tables and relationships and through the use of portals, you'll then be able to see all the comments for an Item while in the Items layout,  and a list of all the Machinists who've made comments for an item.  In the Machinists Layout, you could see all the Items a machinist has made comments on.


              You would also be able to perform Counts of related records in the Comments_List table based on the comment selected.


              And all this would be under one "roof", one database, instead of multiple databases and files.

              • 4. Re: Counting Occurance of an Item in a Drop Down Menu

                That's definitely an improved database structure. However, if you want a "quick and dirty" count like you describe, you might try something like this:


                Define a calculation field such as.


                If ( commentfield = "Bad" ; 1 ; 0 )


                Define a summary field as "total of" this new calculation field and you'll have a count  of all the items where the comment field = "Bad". There are variations possible where you can do the same thing for cases where commentfield contains the word bad and you can also set up additional pairs of fields to count other entries.

                • 5. Re: Counting Occurance of an Item in a Drop Down Menu

                  rdowler:  Thank you so much for such an indepth answer.  What I think I might do, since I have something that is not organized at all like you have suggested, is to go ahead with the quick and dirty method that Phil had mentioned for now, and then, once I have a better understanding of what is going on, try to make a new database from the ground up using your method.  I don't think it is very easy to try to retrofit the old one no?


                  Phil:  Thank you for your advice on this post, I will definately give this a try and come back to let you know how it worked. 


                  Thanks again,