3 Replies Latest reply on Aug 17, 2011 1:02 PM by philmodjunk

    Show current job for an employee



      Show current job for an employee


      I have an Employee Table, Job Table, and Attendance Table all related by Employee_ID. The job table tracks the employees jobs throuhgout his/her career which can be many jobs. It also tracks the Start and End Dates for each job. The Jobs Table is in a portal on the Employee form, this allows the addition of future jobs.

      Out of all the jobs for an employee I need to show the current job Title in the employee form and in the attendance form, but not in a portal.

      I only see the first record out of the many that are tracked on either form by using the Title field. I don't know why this happens or how to stop it from happening.

      What method would be best suited to do this?




        • 1. Re: Show current job for an employee

          You have a one to many relationship from employees to Jobs. If you place a field from the related Jobs table on your employees layout, you will see the first such related record. In you case, this is currently the oldest such related record--the first job.

          Two options: 1) Go to the relationship in Manage | Database and double click the line linking these two tables. You can specify that Jobs be sorted in descending order by start date. Now the "first related record" will be the most recently dated job record instead of the oldest. This change in sort order could, however, affect the function of other parts of your database such as a portal to these records or a script that refers to the jobs table that does need to reference the oldest job record. You may need to create a new occurrence of jobs and link it to Employees, then pull the Job title from this occurrence to avoid that complication.

          2) I know you said you didn't want a portal, but a portal can do the job and the portal itself can be invisible so that the Job title looks like a simple field added to the layout. In this case, add the portal to your layout, specify a single portal row and then use the sort option in this portal setup... to sort the job records by start date in descending order. The borders of the portal can be made invisible if you select a pen width of 0, a transparent pen pattern or a pen color that matches the layout's background color. This may be a better way to avoid changes to your relationship that might have unintended consequences elsewhere.

          • 2. Re: Show current job for an employee


            Thanks for the two solutions, I used the second solution, less complications.


            I'm stumped on how to make permanent review reports. Each review uses three fields, Sum (Absent), Sum (Call-Ins), and Sum(Tardy), that show a quantity accorrding to the time when a review is printed. If the review is reprinted some time later, the three fields change accodingly because they are dynamic fields and change throughout the year. How would I freeze these three fields on the review report by the printing date of the review? Do I need to make another table to store the Review Reports as printed? 

            • 3. Re: Show current job for an employee

              I would think that you would want a new review record for each employee review. That way past reviews are preserved in case you need to go back and look at them later.

              In such case, the dates used to pull up your related data should be local date fields in the review table. That way you can have a 2011 review for employee #5 (John Smith) and also a 2012 review for employee #5. Such date fields can be used both in relationships and in portal filter expressions.

              A script can loop through your current table of employees to generate a set of such review records.