7 Replies Latest reply on Nov 14, 2012 2:52 PM by flusheddata

    Help sum a count field

    bmccoy

      I have a database with three fields.

      Manager, Employee Name, Time Not =Entered (date field)

       

      Each manager will have many employees who do not enter their time. I know how to count the employees by Manager and I did that by created this field: Count (Employee Name)

       

      But, I now can’t figure out how to only count each employee 1 time as oppose to counting each instance of the employee for a particular manager.

       

      Example: Manager Smith will have 5 different employees, but the total record count will be 25 for Manager Smith for his group of 5 employees. I only want my field to show 5 as that is the actual total employees Manager Smith has. I can't figure out how summary this. Each time I try, I get 25.

       

      Help a novice programmer.

        • 1. Re: Help sum a count field
          flusheddata

          Hi,

          You need two tables EMPLOYEE and MANAGER.

          In the MANAGER table you need a field Manager_ID (sequential-autogenerated number), a field name, etc.

          In the EMPLOYEE table you need a field Employee_ID (sequential-autogenerated number), a field Manager_ID, afield name. etc.

          Then make a relationship from MANAGER::Manager_ID to EMPLOYEE::Manager_ID.

          You may crate a value list MANAGERS based on the field Manager_ID showing the name field too.

          Then, in the Employees layout assign this value list to the field Manager_ID so when you create a new record for a employee you assign him/her a manager from the value list.

          Then you may put a portal in the Manager Layout to show all his/her employees.

          I hope this helps,

          King regards,

          Miguel Angel

          • 3. Re: Help sum a count field
            bmccoy

            Thank you. 

            • 4. Re: Help sum a count field
              bmccoy

              Hi,

               

              Here are my fields. How do I make my fields work wiht your solution.  Thank you so much for your time.

               

              1. Manager Name
              2. Employee Name
              3. Time Not Entered (date field)
              • 5. Re: Help sum a count field
                flusheddata

                Hi,

                herewith you have a tip file.

                I hope it will help you.

                Best regards,

                • 6. Re: Help sum a count field
                  bmccoy

                  Hi,

                   

                  Thank you so much for your solution.  However, it is so over my simply filemaker skills.  So, I have attached my file.  All I need is to apply a calculation that will count the number of employees for a particular manager.  The employee can be listed in ly database more than one time.

                   

                  Manager:  Alok  Karnwal count should 1

                  Manager:  Anandaraman Thirumalai count should be 6

                   

                  The total records in my database are 39.

                   

                  It could be that I might have to create a related file to get what I need.

                   

                  I will remain hopeful that someone can help a simply programmer such as I.

                   

                  Blanche

                  • 7. Re: Help sum a count field
                    flusheddata

                    Hi,

                    You have Name (whose), Delivery manager, Project, Employee, Group leader and Project leader all in the same table.

                    A record is an instance of one and only one entity while a field is an entity's propperty.

                    Let's think of an entity PERSON. Every person (John, Anne, Will, etc.) is a record.

                    Every PERSON has attributes or properties ( name, phone number, height, weight, address, etc.).Theese are fields.

                    So you have put entities into fields when they should be records in a table. Everything is mixed up.

                     

                    So you would need a table for Projects, a table for Employees, a table for Managers, etc. Then make relationships between them.

                     

                    Sorry for my dreadful English, I hope you understand well.

                     

                    I would recommend you to have a look to the FileMaker Pro help. Chapter "About relationships". There's a basic approach.

                     

                    I apologize for not being able to help you any further

                     

                    Best regards,