10 Replies Latest reply on Aug 9, 2010 12:48 PM by philmodjunk

    Flag 'newest' related record?

    Wimmachine

      Title

      Flag 'newest' related record?

      Post

      Hello all.  This seems like it would be simple but I'm having a case of the Monday's and can't figure it out..  I have a Contracts table related to my Employee table, which acts as you may expect:  each employee can have multiple contracts.  The contracts table contains start and end dates, and I need a way to flag the employee's 'active contract' i.e. the newest contract record based on the most recent start date.  Any ideas?

      Thanks

        • 1. Re: Flag 'newest' related record?
          raybaudi

          "I need a way to flag ... the newest contract record based on the most recent start date"

          "Most recent" isn't clear but, supposing 3 days:

          Contracts::StartDate > Get ( CurrentDate ) - 3

          This calculation field ( Contracts::Flag ) must be UNSTORED

          • 2. Re: Flag 'newest' related record?
            philmodjunk

            Simply establishing a relationship between employess and contracts that is sorted by effective date in descending order so that the effective start date is first, would appear to do the trick here. You might need to add an extra clause to the relationship to filter out contract records that are put into your contracts table before their start dates are effective.

            Then, on your employee layout, you'd just place the desired fields from contracts directly on the layout and you'll see the current contract fields for them. If you used a portal, you'd see the current contract record at the top of the portal and any past contracts listed below.

            • 3. Re: Flag 'newest' related record?
              Wimmachine

              Sorry, let me elaborate.  Employee X has 4 contract records with the following start dates:

              01/20/2010

              04/15/2008

              01/10/2007

              09/25/2005

              The record with the most recent start date (01/20/2010) is the 'active contract' 

              • 4. Re: Flag 'newest' related record?
                Wimmachine

                Thanks Phil.  I have a portal set up as you described, and currently we are using it to flag the 'active contract' record using a Yes/No checkbox set, setting a text field.  This process works ok but I am trying to figure out a way to automate it via a script. 

                • 5. Re: Flag 'newest' related record?
                  Wimmachine

                  Ok I've had my coffee and now I think I've got it sorted out.  Gotta love mondays.  Basically what I'm going to do is:

                  -  New contract record is created for employee. Once the start date is entered a script is run.

                  -  Emloyee ID is set as a variable.  Contracts table layout is opened, filtered by employee ID, records are sorted descending on start date, all 'ActiveContract' fields for this employee ID are set to null, go to first record (newest based on sorting), set 'ActiveContract' field to Yes.

                  Seems like this should work ok.  Sorry for using a thread as an outlet for my thought process :)

                  • 6. Re: Flag 'newest' related record?
                    philmodjunk

                    Why do you need to "flag" it?

                    I am suggesting that you don't need to do any scripting at all if you just need to know which contract is "current" for a given employee, but perhaps there is a need I'm not seeing in your earlier posts...

                    • 7. Re: Flag 'newest' related record?
                      Wimmachine

                      Yes sorry for not painting the whole picture Phil.  I need the flag in order to filter out active contract records in other scripts, and/or for a second table occurrence of this table that only shows the active contracts.

                      The whole picture is:  Employees get scores associated to them (stored in a related Score table) at an irregular interval.  Employee X can have score records going back several years.  I need to begin tracking these scores on a per-contract basis, at 'quarterly' intervals.  These quarters are determined by the employee's start date.  e.g. Employee X starts on 1/1/2010, their first quarter score would be an average of all scores in the date range 1/1/2010...3/31/2010.

                      Flagging the active contract record is the first step to getting the correct start date for each employee's contract, which I can then use to filter the scores table in order to get the average score for these variable date ranges.

                      I'm sure I can explain this a lot better so if you need me to clarify I can certainly try.

                      • 8. Re: Flag 'newest' related record?
                        philmodjunk

                        "I need the flag in order to filter out active contract records in other scripts, and/or for a second table occurrence of this table that only shows the active contracts."

                        Hmm, well that's exactly what the relationship I describe does. In a script, a reference to contracts from employees that uses the sorted relationship I describe will refer to exactly that, the most recent contract for the employee.

                        If you need to move from a specific contract record to a related record marked with a date that falls in that date range, you might set up this relationship:

                        Contracts::EmployeeID = RelatedTable::EmployeeID AND
                        Contracts::startDate < RelatedTable::Date AND
                        Contracts::EndDate > RelatedTable::Date

                        That filters the relationship by both employeeID and date range.

                        • 9. Re: Flag 'newest' related record?
                          Wimmachine

                          Would this work for a variable date range?  The date range is essentially StartDate...(StartDate + 90)

                          I tried creating the sorted relationship you described but I don't understand what you mean by "add an extra clause to the relationship to filter out contract records that are put into your contracts table before their start dates are effective."

                          • 10. Re: Flag 'newest' related record?
                            philmodjunk

                            The relationship will work for any date range where you can put start and end dates in separate date fields of the same record. The EndDate could certainly be a date calculation such as StartDate + 90 if all contracts will be 90 calendar days in length.

                            I'm not sure from your post whether you might enter a contract with a start date that is lager than today or not. If that's not a possibility, you can ignore that part.

                            If it's possible that future contract records might be entered prior to when they become effective, you'd do this:

                            In employees, define an unstored calculation field, cToday, that uses Get ( CurrentDate ) and select Date as the calculation field's return type.

                            Now you can use it to filter out contract records that aren't yet effective:

                            Employees::EmployeeID = Contracts::EmployeeID AND
                            Employees::cToday > Contracts::StartDate

                            "Employees::cToday > Contracts::StartDate" would be the "extra clause" that I referred to.

                            In either case, you specify  that the relationship in the contracts side be sorted by StartDate in descending order.