2 Replies Latest reply on May 18, 2016 8:43 AM by addison

    Table Relationships and Auto-Enter

    addison

      I'm building a time card database (each record is a different time card). I have an Account field in the Time Cards table that auto-enters the record creator's account at the time of creation. This field cannot be overridden, and individual users can only view records that have their account in the Account field.

       

      I have another table called Employees, where I want to store Account, First Name, Last Name, and ID Number. In this table, each record represents a different employee.

       

      I want my Time Cards table to auto-enter the employee's first and last names, and ID number upon record creation. Basically, when an employee creates a record, the record should auto-enter their Account (which I've already figured out) and ALSO auto enter their name and ID in the appropriate fields.

       

      I think I will probably have to create relationships between all these fields, but I don't know how to make the relationship dependent on one table or the other, so that it will pull the correct data from one table and display it in the other.

        • 1. Re: Table Relationships and Auto-Enter
          erolst

          adurolaser wrote:

          I want my Time Cards table to auto-enter the employee's first and last names, and ID number upon record creation. Basically, when an employee creates a record, the record should auto-enter their Account (which I've already figured out) and ALSO auto enter their name and ID in the appropriate fields.

          You've got the ID; why also store other data from the employee record when you can reference it as a related field on the layout? (I can understand that you may want to store a datum that may change over time, e.g. an account name; but this is usually not true for a name …)

           

          That being said: if you have a relationship like TimeTable::account = Employee_forTimeCard::account, you can define auto-enter calculations that copy the desired values from that Employee TO, e.g. Employee_forTimeCard:id for the foreign employee ID.

           

          Of course, the idea of a unique identifier like a primary key is that you use it to reference/copy data via a relationship, not the other way around …

           

          Another method could be to put the employee ID into a global $$var on log-in, then reference that in an auto-enter calc.

          • 2. Re: Table Relationships and Auto-Enter
            addison

            I'm fairly new to FileMaker and I read that it would be more secure to store employee information in a separate table (one that they don't have access to) in a situation like this, hence the two different tables. Employees can't be able to view other employees' time cards or be able to edit any of their employee information, and I wanted an easy way to be able to edit account info, etc.

             

            The employee name and ID has to appear on the time card records for payroll purposes, but I don't know how to get that information to auto enter when an employee creates a new record.

             

            By referencing it as a related field on the layout, do you mean relating the fields of the two tables? Otherwise I'm not really sure what you mean. And in this case, is the Account field the primary key?

             

            Thanks!