6 Replies Latest reply on Jan 21, 2013 10:00 AM by FileMakerNovice

    Database relations

    FileMakerNovice

      Title

      Database relations

      Post

           Hello,

           I have a database that I'm having difficulty doing what I want to do.

           I have 4 files that I imported from Excel.  These are imported into individual tables.  They are all reports for employees:

           CustomerSurveys-
           Contains EmployeeID, Date, TotalSurveyReceived, PositiveSurveys, NegativeSurveys
           Basically, throughout the week, the employee will get surveys.  The total amount that day is "TotalSurveysReceived".  Positive and NegativeSurveys are a count of if the surveys were negatively or positively graded.  IE Employee A - 1/2/2013 - 3 - 2- 1.

           Calls-
           Contains EmployeeID, Date, TotalCalls (taken).
           This provides a total amount of calls taken that day.  IE Employee A - 1/3/2013 - 5

           EmployeeTime-
           Contains EmployeeID, Date, TotalStaffedHours, TimeOnCalls, TimeOnBreak, TimeInMeeting
           This provides a line for each employee per day that they are staffed and how they allocated their time throughout the day.  IE Employee A - 1/3/2013 - 8 - 6.5 - .5 - 1

           CallQuality-
           Contains EmployeeID, Date, CallGrade
           This is a quality control report that provides a 1-10 grade for each call listened to.  There could only be one to two grades per week.  Conversely, they could recieve 2 grades in one day.  The dates for the grade may be on days that the employee was not at work.  IE Employee A - 1/1/2013 - 10

           I then created a Employee table that has EmployeeID, FirstName, LastName, Email, etc.

           I would like to search for an Employee and date range and the result to show: Average Surveys score, Average Calls Taken, TotalOnCalls per Calls Taken, Average Call Quality.  I need help with how to relate the tables and if another table would be necessary.  Also, how I would create a layout that would search.  The fomulas, summaries and calculations, I should be able to figure out on my own, I think.

           Thanks in advance.

        • 1. Re: Database relations
          philmodjunk

               I am assuming that you have Filemaker 11 or 12.

               You should have all of these tables linked directly to your Employee table by EmployeeID

               You can define summary fields in each of the tables as needed to compute your averages and totals. (TotalCallsTaken would be a summary field defined in Calls, for example.)

               Then you can define a pair of date fields in Employee to specify the date range. Put one row portals to each of the other tables, one portal for each such table on the Employee layout. Put the summary fields you have just added in the appropriate rows of these portals. Give each portal a portal filter expression that filters the related records by your date range.

               Example filter expression for the portal to CallQuality:

               Employee::StartDate < CallQuality::Date AND Employee::EndDate > CallQuality::Date

               Include the StartDate and EndDate fields in each of these relationships to foce the filtered portals to automatically update each time you edit either of these fields.

               The relationship between Employee and CallQuality would be:

               Employee::EmployeeID = CallQuality::EmployeeID AND
               Employee::StartDAte X CallQuality::AnyField AND
               Employee::EndDate X CallQuality::AnyField

               AnyField can literally be any field in CallQuality and the relationship will still work.

               If you specify global storage for StartDate and EndDate, you can set up one date range and then view the statistics for any number of different employees all over the same date range without having to select new dates.

          • 2. Re: Database relations
            FileMakerNovice

                 Thanks for the reply.  Unfortunately, I now am importing the Employee table from an external data source.  I can no longer edit this table.  The external data source has a couple hundred employee records.  There are about 200.  About half are "Active", there is a boolean field noting this.  

                 I feel like I'll need to do what you mention but have a new table, lets call it "AllActiveEmployees".  Is there a way to copy all employeeIDs into this table?  Maybe a script that did this copy everytime the database or layout is opened?  

                 Thanks again for any help.  Have a great day.

            • 3. Re: Database relations
              philmodjunk

                   If you are really importing the data, you  have already "copied the data" into a table. But I suspect that you have not actually imported the data but are using an ESS type link to and ODBC data source. Is that correct?

                   It's certainly possible to import the data from an external source, but whether or not it is a good idea to do so is a question to be answered with care. Once you start down that path, you have to be very careful to keep that copy of the data properly in synch with the data from which it was imported to avoid issues due to discrepancies between the two tables when data in one of the two tables is modified and the other doesnot get the needed update to keep them in synch.

              • 4. Re: Database relations
                FileMakerNovice

                     Apologies for not being clear.  The Employee records are in another filemaker database.  This is added by going to file>>manages>>external data sources.  I then add the file path of the Employee database.  

                     All other tables are imported.  This is done by file>>import>>file.  They were previously excel sheets.

                     The external data source is all employees in the company, not just sales people.  The database does have a "role" field though.  I was hoping there was a way, any time the database is open to run a script that went to the external data file and updated my Employees table's employee IDs.  

                     The external database has all fields that I mentioned for the employees table previously but also has a "Role" field.  This can be populated with which department the employee is in.  My database should only contain sales people.  Therefore, I would only want the Employee IDs from that database in mine.

                     This a wise move?  How else should I try to do this?  Your tips and advice helped greatly and I got it to work but the data is in this external database now though.  Thanks again for any help.

                • 5. Re: Database relations
                  philmodjunk
                       

                            The Employee records are in another filemaker database.  This is added by going to file>>manages>>external data sources.  I then add the file path of the Employee database.

                       Then if you need to make changes to the design of this table, what is preventing you from opening that other file and making the changes that you need? Is the only change required that you add the two date field? This should be very easy to do from that other file and adding a pair of fields to that table won't affect the function of that other file.

                       I was hoping there was a way, any time the database is open to run a script that went to the external data file and updated my Employees table's employee IDs.

                       There is, but this is an option that complicates the design and use of your database. If you have no other alternative, it can be made to work, but life will be simpler if you do not do so.

                  • 6. Re: Database relations
                    FileMakerNovice

                         Haha.  I like the way you think.  Simpler is always better.  The other database is not administered by me but I'm going to talk to that person about adding the global start and end date fields.  Again, you "showed me the light".  Have a great one.