8 Replies Latest reply on Feb 4, 2013 4:04 PM by FileMakerNovice

    Count of record

    FileMakerNovice

      Title

      Count of record

      Post

           Hello,

           I am working with another person's database and wanted to confirm I was using a count correctly.  I have the following tables:
           Employees- Contains employee name, id, email, etc.
           Calls- Contains date of call, employee id, call id
           CallSummary- Contains call id, CustomerSkill id, ListeningSkill id, TechSkill id
           Grade- Contains id, value and notes.

           With the following relationships:
           Employee::EmployeeID=Calls::EmployeeID
           Employee::StartDate<=Calls::DateGraded
           Employee::EndDate>=Calls::DateGraded
           Calls::CallID=CallSummary::CallID
           CallSummary::CustomerSkill id=Grade::id
           CallSummary::Listening id=Grade::id
           CallSummary::TechSkill id=Grade::id

           So... when an employee gets a call, the call is populated in Calls.  When a manager grades a call, it attaches a call summary to the Call.  A manager enters a 0-10 grade for the three options: CustomerSkill, Listening and TechSkill.  These can be left blank and do not affect grade.

           This is someone elses table and I'm having difficulty getting a count of CustomerSkill, Listening and TechSkill for use with calculations in the Employee table.  This person's database is in another filemaker file that I have built the relationships into mine.  I have gotten a "Call Count" already by creating a summary field "Count of CallID" in Calls.  I can then reference this in Employees in a calculation.  I have other tables that I'm calculating grades in and I want to get a grade for CallQuality.  Something to the affect of (CustomerSkillSumOfGrades/CustomerSkillCount)+(ListentingSkillSumOfGrades/CustomerSkillCount)+(TechSkillSumOfGrades/TechSkillCount)/3.  

           Not positive why the person developed it this way but there is no opportunity to change his database.  I will need to work with the data the way it it.

           Thanks in advance.

        • 1. Re: Count of record
          philmodjunk

               I think that you have described two relationships here:

               Employee::EmployeeID=Calls::EmployeeID AND
               Employee::StartDate<=Calls::DateGraded AND
               Employee::EndDate>=Calls::DateGraded

               Calls::CallID=CallSummary::CallID AND
               CallSummary::CustomerSkill id=Grade::id AND
               CallSummary::Listening id=Grade::id AND
               CallSummary::TechSkill id=Grade::id

               If I am correct in that interpretation, the second relationship seems very unlikely to be correct.

               Matching 3 different fields in CallSummary to the same ID field in Grade means that a record in CallSummary can only match to a record in Grade if the same id number is entered in all three CallSummary fields of the same record--which makes no sense from my perspective.

               I think that you need either three corresponding id fields in Grade or three different relationships to three different occurrences of Grade. The latter is the more likely one from what I see here.

               

                    Not positive why the person developed it this way but there is no opportunity to change his database.  I will need to work with the data the way it it.

               Then I think you have a very serious road block to getting a correct result as there looks to be a major error in the data model.

          • 2. Re: Count of record
            FileMakerNovice

                 Thanks for the response.  You are correct that there are multiple relationships.  Apologies for not being clear on that.  

                 The way I understand it works, in his layout, once a manager marks a grade, it populates the same UUID in CallSummary::X and Grade::ID.  For instance, if a manager marks a 5 in Customer Skill, it populates the value in Grade::Value.  It populates the same UUID in CallSummary::CustomerSkill_ID and Grade::ID.  So... now that I think about it, here is the appropriate relationships:

                  

                 Employee to Calls:
                 Employee::EmployeeID=Calls::EmployeeID AND
                 Employee::StartDate<=Calls::DateGraded AND
                 Employee::EndDate>=Calls::DateGraded

                 Calls to CallSummary
                 Calls::CallID=CallSummary::CallID 

                 CallSummary to Values (Results in 3 separate relationships in relationship view of Database)
                 CallSummary::CustomerSkill id=Grade::id
                 CallSummary::Listening id=Grade::id
                 CallSummary::TechSkill id=Grade::id

                 What I am wanting to do is get a sum and count of CustomerSkill, Listening and TechSkill.

                  

            • 3. Re: Count of record
              philmodjunk

                   I suggest that in future posts, you use the actual names from the relationship graph. The different Table occurrence names will help avoid confusion.

                   It looks to me that you have these relationships:

                                                            CustomerGrade
                                                                     v
                                                                     |
                   Employee------<Calls-----<CallSummary-----<ListeningGrade
                                                                     |
                                                                     ^
                                                           TechGrade

                   CallSummary::CustomerSkill id=CustomerGrade::id
                   CallSummary::Listening id=ListeningGrade::id
                   CallSummary::TechSkill id=TechGrade::id

                   What version of FileMaker are you using? FileMaker 12 can produce the totals you want more simply than can be done from older versions unless we try to set this up with a weighted average in a summary report layout based on the Grade table. (You'd perform a find for the employee(s) and a date range, then sort your records by Employee and grade type.)

              • 4. Re: Count of record
                FileMakerNovice

                     Awesome.  You got the correct relationship.  I have FM12.

                • 5. Re: Count of record
                  philmodjunk

                       What criteria, besides the employee ID do you need to specify to get the correct counts for each type of Grade record?

                       The start and end dates specified in the employee table? All records linked to that employee? Or ???

                       I was going to suggest ExecuteSQL--a new function in FileMaker 12, but on further review, if the start and end dates in employee are what we need to use here, then a calculation field in Employee defined as:

                       ( Average ( CustomerGrade::Grade ) + Average ( ListeningGrade::Grade ) + Average ( TechGrade::Grade ) ) / 3

                       should produced the desired "average of the averages" you have specified.

                        

                  • 6. Re: Count of record
                    FileMakerNovice

                         You actually helped me create the relationships above last week.  Basically, I have a layout that the user would enter employee name, start and end dates.  Once that is done, I would display the count of scores for each and then the overall average.  So... I enter John Doe, 1/1/12 1/4/12.  The result would be the grade for CustomerSkill, ListeningSkill, TechSkill (typically is the same number) and the average.  Something like 1-1-1-10.  I think you hit it above but which table would this be located in?  I have been doing a simple test by just calculating the count.  I've tried adding count(CustomerGrade::Grade) in Employees, Calls, CallSummaries and Grade to no avail.  Either gives me a 0 or blank value.

                    • 7. Re: Count of record
                      philmodjunk
                           

                                then a calculation field in Employee defined as:

                           

                                ( Average ( CustomerGrade::Grade ) + Average ( ListeningGrade::Grade ) + Average ( TechGrade::Grade ) ) / 3

                           Since your test doesn't produce the expected results, better check your relationships and the data in your various fields. Something isn't matching value like it should. Since you are tracing a chain of table occurrences to get to an Occurrence of Grade, the fault could lie with incorrect relationship details or data in any one of the intervening tables or relationships.

                           Once quick test I use to see if a relationship is working as expected is to add a portal to the related table. If it's empty or shows the wrong records, that becomes a major clue towards figuring things out.

                      • 8. Re: Count of record
                        FileMakerNovice

                             Yeah... that's what I've been up to lately.  I have a portal that shows related records from "Calls" with the layout using the Employee table.  It displays the CallID, and each of the Grade values correctly.  I was really hoping it was something I was doing to with the calculation that was wrong.  I feel stupid now.  After reading your last post, I wasn't doing the count of the correct field when I put the calculation in the Employee table.  You sir are smart and great at making sense of my questions.