Add a field from second record (of the same person) to a report?

Question

Hi All, 
I'm not sure where to start with this one.  It seems relatively simple, but my search terms have yielded me nothing thus far (As you can probably tell from my ambiguous titling of this message!)  .....I know, it hardly makes sense to me (the title) and I know what I'm trying to ask!

Staff/Employee Application:
I have a staff application that has multiple tables. 
One table is the employees themselves; another is their assignments.
Since we have employees that often move from one team to another, assignments are added as active, then the previous one is "inactivated" (via checkbox). 
In the report that I have built (based on dates, etc) - I am pulling in the ACTIVE assignment.  
Is there a way to add a column to this report (a field, I'm assuming) that shows the first INACTIVE assignment's record for TEAM NAME?  
Specifics of this report:
This report is specifically for anniversary dates/bonus eligibility - for specific people. 
The people that are eligible are those that have made lateral transfers.  However, since we have several teams that have more than one "team"/more than one supervisor also (Travel Team 1, Travel Team 2, Travel Team 3, et al) - the people that move from Travel Team 1 to Travel Team 3 are not eligible. 
They would be eligible if they moved from Travel Team 1 to Accounts Payable though.  
So on this report that I've built, I'm showing the employees current assignment, team name, supervisor name, start date, and I'd also like to show their PREVIOUS Team Name - to make it easier to distinguish who is NOT ELIGIBLE - in this instance. 
I don't know if I need some sort of formula or what, but I feel like this should be possible.  I just have a serious case of the Mondays, and have no earthly idea how to get from here to there. 
Any help is appreciated!

Allissa
------------------------------Allissa Weber------------------------------

ChayceDuncan · Answer

Do you just need to identify the most recent Team? The simplest way would be using the relationship to find the 'most recent assignment' Record ID# that isn't current and create a reverse relationship to that assignment based on that value to bring in the old team. What that looks like in practice is:

In the current summary between Staff and Assignments - make a summary field of the Max Record ID# where the status is Inactive. This will get you the most recent assignment RID.

Create a new relationship where Assignment has many Staff (the reverse of your current) and when it asks you to define the related field, pick the Max RID from above. This way your relationship connects automatically to the most recent assignment.

With the relationship, add the Team Name as a lookup field and rename it to 'Prior Team' or whatever you want.

You could also do the above without the reverse relationship if you prefer not to. You could combine a handful of summary lookups in your current main relationship to find the Max RID, bring it back down and then do a combine-text summary field where RID = the Max RId, or you can go the formula query route. Lots of options to achieve the same goal.

------------------------------
Chayce Duncan
------------------------------

Sign up

Login to the community