3 Replies Latest reply on Apr 1, 2013 9:32 AM by jgritt01

    Parent calculation field to find current job position

    jgritt01

      I have a parent table of People and a child of Job Positions the person has held over the years. I want a calculation in my parent table that will return the person's current job position -- i.e., the one without an end date from the child table. I have tried everything I can think of and it's not working. Tables are linked with a People UI. I tried Case function saying that if the end date was blank, return job position, and if the person has held one position it works, but if multiples, is blank. Example is for jobs, but need it for other parent/tables like telephone #'s and addresses - basically want the calculation to return field contents in a record based on the value of another field in the record. Any insight would be greatly appreciated. Thanks

      Jamie Rittenhouse

      University of Louisville

        • 1. Re: Parent calculation field to find current job position
          AlanStirling

          Hi Jamie

           

          Add a calculation to your Job Positions table, called 'People UI Current' with the following formula;

           

          People UI Current = Case( IsEmpty(End Date) ; People UI)

           

          Set it to the same field type as People UI.

           

          Set this field to calculate when no data exists (Use the checkbox at the bottom left of the window)

           

          Then make a new relationship between People and Job Positions, using People UI in the People table and People UI Current in the Job Positions.

           

          This relationship will only link to current Job Positions.

           

          Best wishes - Alan Stirling, London UK.

          • 2. Re: Parent calculation field to find current job position
            ch0c0halic

            In the relationship From People To Job Positions add a Sort based on the "Job Positions::End date" field, Ascending. This will cause the empty End Date record(s) to be the first record(s) of the sorted related records. Relationships always 'use' the first related record, including the sorting of the relationship.

             

            You will have the problem of not having any empty End Date records. However, the "Case()" checking for empty isn't needed as the first record is the earliest date. If you need the latest date you need to get the last related record. There are ways to do this if its needed.

            • 3. Re: Parent calculation field to find current job position
              jgritt01

              Alan, I can't thank you enough for this.  It doesn't just solve this one, but several other issues I've had as well.