In your time card table, what does one record represent?
One employee's work day?
One employee's work week?
Time card data for multiple employees?
If you have a time card for Mary Jones and she gets married and changes her name to Mary Smith, wouldn't you want that name change inthe Employee table to change the name in the TimeCard table automatically? (I read your post as wanting to prevent that and don't understand why that would be.)
Thanks for the response so quickly! In my time card, one record represents a work day for an employee. Now we have many employees, so there will be possibly a hundred entries per day. I thought it would be best to place all time entries into one table called "time_card" and that table is what I would use to view, export employees time sheet for the week. I am not opposed to having the name changed throughout the tables, however being new to this, I didn't know what standard practice is...should records in the past that we have exported to our Timberline Payroll system be kept unchanged? Granted they no longer serve a purpose other than a history. The goal here is to use filemaker Go on the iPad and iPhone to allow our managers in the field to remotely enter time for employees into the 'time_card' table and then use that table to generate reports, create a layout for pulling up names and viewing current weekly and monthly etc time cards and then to be able to update the various lists that need to be entered into the time_card table. For instance we have a job_list, company_list, employee_list and a few others. We want to use a drop down option on the time_entry layout for the managers to select from these list so they don't have to re-type everything and to eliminte error. So is the best thing to do is to have a table for each so that they can be easily updated through a layout view by administrative staff?
Whether you keep existing name data unchanged or not is really a legal/business decision rather than a database design decision. FileMaker can be set up to either copy the name into the timecard field (If you don't want past data to change) or to just link directly to the name in the employees table (If you want changes in the employee table to update timecard names automatically.)
Either way, I'd use one table for timecards with a link to an employees table that is based on an EmployeeID serial number field defined in the Employee Table. But I'd also use some combination of a conditional value list and/or scripted enhanced value selection techniques to make the long list of employees easier to work with.
Just to give one example, a manager can select their name from a manager's list and then a drop down list of employees would use a conditional value list to list only employees that are managed by that manager--thus breaking one long list into several smaller lists.
There are also several ways to use scripts and either a portal or a drop down list of names so that the user can enter a few characters and the list reduces down to just the employees whose names start with those characters.
To create a new time card for an employee, you'd create a new record in TimeCards and then use the drop down list or portal tool to select an employee for that time card.
Links on conditional value lists:
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
And here's a demo file showing several script supported enhanced value selection techniques: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html
Ok, so I'm working on this project that I have decided is above my head. Never the less, I am bound to press forward...my question next is on relationships and lists. So lets say that I have a 'time_card' table with a field called 'eid' for employee id, and I have another table called 'emp_list" for employees. Currently the fields in my emp_list table are (eid, name_first, name_ middle, name_last, company, per_diem). I want to use the emp_list table to provide a list to a Time Entry layout so a manager can type ahead and select an employee from a list. I also will use the employee table to automatically populate a field in the 'time_card' table with the (per_diem) rate from the emp_list table. I know there is are related fields 'eid' and 'per_diem' between the tables emp_list and time_card so my question is do I need to decreate a relationship connector between these tables if I am using the emp_list as a List for my Time Entry layout? Also, we want to be able to update the emp_list table with new employees and update current ones through a separate layout for office staff. Are there any problems using this table in this way? Thanks in advance for any help!!!
You've bundled a lot of different tasks into that single paragraph. You'll need to break this down into smaller tasks and tackler one at a time. Everything you describe is possible and in more than one way. Let's make sure you have a basic working relationship where you can select an employee by name and see the correct per deum value be copied into the time card record. From there we can move on to a more sophisticated, script enabled approach that supports auto-complete (type ahead) entry for the employee's name.
First you need this relationship between Employee_List and Time_Card:
Employee_List::eid = Time_Card::eid
Note that the per deum fields are not included in this relationship.
Define a new calculation field, cFull_Name in Employee_List as:
Substitute ( List ( Name_last ; name_middle ; name_first ) ; ¶ ; ", " )
Now, on a Time_Card layout, add the Time_card::eid field and format it as either a drop down list or a pop-up menu with a value list defined to list Employee_List::eid as field 1 and Employee_List::cFull_Name as field 2.
When you select a name from this list, the matching eid is entered into the Time_Card::eid field. You can place the cFull_Name field from Employee_List next to this field to display the Employee's name if you wish.
Now find the field definition for Time_Card::per_deum in Manage | Database | Fields and double click it to bring up field options. On the auto-enter tab, select the looked up value option and select the Employee_List::per_Deum field as the field from which to look up a value. Now, when you create a new record in Time_Card on this layout, you can select an employee from this value list and see the matching per deum value appear in the time card's per deum field.
This is just step one, but if you can get it to work, you have the first "building blocks" that you need in place and we can build in more sophisticated stuff to support easier selection of employee names from here.