7 Replies Latest reply on Jun 6, 2014 10:18 AM by philmodjunk

    Tracking Time Off



      Tracking Time Off


           I am creating a database to keep track of employee absences.  I used the preloaded vacation option in the preloaded templates with some modification. If I enter each day of vacation or sick leave seperately, It displays all of them with no issues. Since I have over 400 employees on site, individual data entry is not really an option. 

           Currently time off is maintained in SAP and exported to Excel to view.  I would like to be able to use the Excel sheets to upload the data.

           I am able to import the data but I am only able to see one entry on the Time off view tab. How can I get each line in excel to display on the time off tab?  i am using the employee ID as the matching value and this seems to work fine.  Please help. 

        • 1. Re: Tracking Time Off

                      I used the preloaded vacation option in the preloaded templates

               Which template is that and if this is a starter solution, from what version of FileMaker?

               What version of FileMaker are you using?

               Then we'd need to see and compare the structure of your target table and the excel spreadsheet before we can suggest how to get the results that you want.

          • 2. Re: Tracking Time Off

                 It is theStarter Solutions,  personnel details template in File Maker Pro 12

                 If you look at this template, you can see the two tabs in the form and table view.

                 I can make one line of data from the excel sheet appear, but I need for each line of data in the spreadsheet to appear on the time off tab. 

            • 3. Re: Tracking Time Off

                   It appears that you are working with the personal records starter solution and are referring to the Personnel Details layout where there is a portal to a related table called "Time Off". I am assuming that the screen shot that you uploaded is a shot of your excel file.

                   There are two potential issues to resolve:

                   Making sure that all the data in your excel file is successfully importing and into the correct fields of this table.

                   Linking that imported data to the correct records in the Personnel Records table--which requires that the PERSONNEL ID MATCH FIELD in Personnel Records correctly match to the PERSONNEL ID MATCH FIELD in Time Off.

                   First issue that I see is that you have more columns in your spread sheet than there are fields in your time off table. You don't need the employee name, bit have you added the needed fields to time off so that you can import all information?

                   And is the PERSONNEL ID MATCH FIELD in Personnel Records still defined as an auto-entered serial number?

                   Are you mapping the employee ID column shown to the PERSONNEL ID MATCH FIELD during import? Does this value actually match values in the PERSONNEL ID MATCH FIELD of Personnel Records?

                   If you have not already done so, you may want to add a new layout based on Time Off, import your records and use table view with this layout to inspect your imported data to see if you are getting all the records you expect with the data that you expect in each column of that table view.

              • 4. Re: Tracking Time Off

                     I do not know how to go back and tell the system to use the employee ID as the unique identifier. I have been trying to figure that  out. 

                     I am only attempting to import relevant fields. 

                     I am thinking that there may be an issue with not telling the database to use the employee as the unique identifier may be the problem. Does the program have the capability to use each row on the spread sheet as a sperate line item in the time off portal?

                • 5. Re: Tracking Time Off

                       When you import data from an Excel spreadsheet file, each row of data in the spreadsheet is imported as a separate record. That should be exactly what you need.

                       But that imported data will not match to records in the Personnel Records table and thus won't show in the portal on the "Details" layout that you mentioned earlier.

                       There are two basic approaches to fix this and a lot of technical details in exactly how you make this work in either case. Either you modify the Personnel Records::PERSONNEL ID MATCH FIELD so that it is no longer an auto-entered serial number field and then you update this field with the Employee ID number shown in your spread sheet and you import this Employee ID into the PERSONNEL ID MATCH FIELD of the Time Off table


                       You add a new field in Personnel Records and in Time Off  for the Employee ID number and define a new relationship to a new occurrence of the Time Off table that matches by these new Employee ID number fields. You'd then import the data into Time Off and use a script and this added relationship to copy over the PERSONNEL ID MATCH FIELD value from the Personnel Records table into these newly imported records.

                       The second option is much more complex to set up, but it keeps your system based on an internally generated serial number as your Primary Key. That's a better design option than relying on an imported Employee ID number as that leaves your system vulnerable to issues created should the employee ID number ever change on you or be the wrong ID number for a given employee.

                  • 6. Re: Tracking Time Off

                         I think I understand for the most part. (I have been working in this program for about 5 days.)

                         How would I create the script that you mentioned?  Do I need to do anything but create the relationship between the match fields? Will the rest simply populate?

                         If I want to be able to seperate each calendar year, shoudl I make them seperate tables?



                    • 7. Re: Tracking Time Off

                           Do not use separate tables for each calendar year. That will tremendously complicate your database and would require making a lot of design changes to your database for each newly added table. You can use finds, relationships, sorting, portal filters, etc to limit your view of this data to a specific year without having to use a new table for each year.

                           With regards to your first question, I outlined two different solutions. Which one do you want to attempt? Have you made the design changes yet that I specified for the option you want to try?