5 Replies Latest reply on Aug 8, 2011 9:55 AM by philmodjunk

    Preventing a field from being modified after entry if related data is changed in another table

    GregNichols

      Title

      Preventing a field from being modified after entry if related data is changed in another table

      Post

      Hi I'm new to database development and learning...I need assistance understanding how to prevent a field from being modified in a related table.  So here is the senario, I have an employee entering a time card via a Filemaker Layout.  We have multiple lists that I have created separate tables for such as 'Employee List', 'Job Lists', etc.  Now we want to be able to update these lists as we lose and gain employees, and jobs.  I have another table called 'Time Card' that will have a layout for time entry.  I want the manager to be able to type in an employee number or begin typing a name and have a list of employees to select from and entered into 'Time Card'.  I believe this makes it a one to many relation ship between the Time Card table and the other table lists.  So I dont want a name to be changed in the time card table if say, an employee name changes.  Is this a problem and how can I avoid this?  Is this a problem that exists only in many to many relationships and if so how is that problem avoided...Thanks for any help...

        • 1. Re: Preventing a field from being modified after entry if related data is changed in another table
          philmodjunk

          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?

          Or?

          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.)

          • 2. Re: Preventing a field from being modified after entry if related data is changed in another table
            GregNichols

            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?

            • 3. Re: Preventing a field from being modified after entry if related data is changed in another table
              philmodjunk

              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

              Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

              And here's a demo file showing several script supported enhanced value selection techniques:  http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

              • 4. Re: Preventing a field from being modified after entry if related data is changed in another table
                GregNichols

                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!!!

                • 5. Re: Preventing a field from being modified after entry if related data is changed in another table
                  philmodjunk

                  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.