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.
Hi Chayce,
The most recent team is identified in the current report (in the screenshot) - I need to add the 2nd most recent team (or 1st inactive team).
Does this make sense?
Thanks,
Allissa
It would still work that way. I imagine your 'most recent' team is their current, where as the 2nd most recent (1st inactive) is right before it. You can achieve it by just filtering the most recent record where Status = Inactive so you ignore the most recent active assignment. If you need the most recent (2) teams, so you'd have your current team, the prior team, the prior team you can still more or less leverage the same technique by summarizing the RID of the most recent inactive team, bringing that back down as a lookup, the summarizing the RID of the most recent inactive team where it's not equal to the actual most recent and on and on.
Chayce,
THANK YOU! I'm not sure I would have put 2 and 2 together with a reverse lookup.....but it worked like a charm!
Best,
Allissa
