4 Replies Latest reply on Nov 16, 2009 6:04 PM by Taylor28

    Isolating records that meet certain criteria



      Isolating records that meet certain criteria


      I'm trying to isolate records in a database that tracks employees working on a project from day to day. The employees may or may not be requested back to work the next work day (by default they are and "YES" is auto-entered into a text field, if not it is changed to "NO"). The next work day will usually be "tomorrow", but on a Friday, for example, it will most likely be the following Monday.


      After employees are entered into the database on any given work day, I'd like to be able to single out which employees were requested back for that date and who failed to make it back, in other words a list of "no shows".


      Here are the names of some of the relevant fields:


      - request_back (text field, yes or no)

      - request_back_date (date field with date requested back)

      - employee_name (text field)

      - work_day_date (date field, auto entered from record creation date)


      Would really appreciate any suggestions.


      Thanks so much!




      PS - Beginner, Using Filemaker Pro 10 Advanced with Windows Vista.


        • 1. Re: Isolating records that meet certain criteria

          The important information is how are the table(s) setup in the database.

          Is there an employee table with a unique ID number?

          Is there a workday table with a unique ID number?

          Each employee will have many workday entries.


           White Paper for FMP Novices


          • 2. Re: Isolating records that meet certain criteria

            Hi and thanks for your response.


            I'm very new to this so I probably didn't set it up very well to begin with.  I'm learning as I go along... I hope!


            The database has two tables.  The "EmployeeTable" contains information about potential employees who are already entered in the system, such as social security number, name, and other personal information.  We use the social security number as the unique ID for each employee.


            The second table, the "work details" table, contains all the fields relating to specific project-related work performed by an employee on any given day.  When an employee shows up for work, his/her information is called into the "WorkDetails" table by entering the social security number. Other information, such as the start time, finish time, lunch duration, project description, etc, is also entered.  So, each record in the "WorkDetails" table records the employee SSN and name along with specific information about work performed on that day, including time started, project description, total hours, etc.


            Hope this helps and thanks again for any suggestions.



            • 3. Re: Isolating records that meet certain criteria

              First to answer your specific question:


              Select your "Work Details" layout.

              Enter find mode:

              Enter "yes" (or select the value if the field is formatted with a value list) in request_back

              Enter desired date in request_back_date

              There's probably a field that is blank or which contains a specific value if the employee did not appear for work on that day. If it's blank, enter an equals sign in that field. If there's a specific value, enter that value.


              Perform the find and you should now have a list of all Work Details records for absent employees on the date you specified.

              The above find can be scripted or performed manually.


              Now a design comment. In this day of rampant identity theft, I strongly suggest that you not use SSN's as your employee ID number. Even if you keep this number perfectly secure, you might find yourself expending considerable $$ defending yourself should one of your employees suffer ID theft and accuse you of exposing their SSN to the ID thief. Replace this number with an auto-entered serial number. (You can use Replace Field Contents to update fields in both tables to make the change less labor intensive.)

              • 4. Re: Isolating records that meet certain criteria

                Thanks so much for your response.  I'm on the road for a few days so I haven't had a chance to check out your suggestion yet but it looks like it should do the trick.


                I also appreciate your advice regarding the SSN.  I've never liked the idea myself and will investigate this further.


                Thanks again,