Search for latest occurrence

[Randy Bricker]

I have created a related database for my Strat-o-matic baseball league. I want to track a player's movement over his entire career as he moves from team to team by way of the  player being originally drafted into the league, traded, cut, drafted again, etc.

I have created one table that is the master Player Tracker, which has portals to the following tables:  Rookie Draft, Trades, April Supplemental Draft, Mid-season Supplemental Draft, Players Cut. All of these tables have a "date" field that records the occurrence, and a "team" field that records the team. There are multiple times a player changes teams.

I'm trying to write a formula for the Master Player table that will search these related databases and tell me what team the player is currently on (the occurrence that happened most recently) and then enter the current team in the Master Player table "team" field.

 

Thanks.

[AHunter3]

I think you have a structure problem.  At a minimum, it's not how I'd set it up.  I would have a table in which every record is the record of a player's assignment to a team.  Doesn't matter if it's a draft or a trade or a cut that's involved, each of these records has a start date and an end date and a Team ID and a Player ID.  You could put a Notes field and enter notes about exactly what happened ("drafted in supplemental draft by Cogder McGrew of the West Bubblegum Chewies"), but the primary bones of your tracking system should be those four fields.  If Joe Fireball plays for the Chewies from 2007 to 2009 and then later gets traded back to them for the 2017 season, Joe has two records with the Team ID of the Chewies.  If he's still playing for them now, the end date for the current Chewies record is blank.

Now for any player you go to the related records by Player ID, sort by date, and there's your player's career.

[Randy Bricker]

Thanks for the reply. I will take a look at your ideas. One of the goals of the database is also to present the "events" of drafts, trades, cuts, etc., in a way that can be accessed. So for instance, if I want to find all of the players drafted in 2017 I can just do a simple find in the Rookie draft table. Perhaps that's a different goal from the main one, but part of my point in creating the database is to not have to enter the same information more than once in multiple databases. I want to access all of that info from one source.

[Randy Bricker]

OK. I did as you suggested. I now have two tables, one for "players" that has a portal to the other table called "transactions". I have a field in players called "Team". I want to write a calc that finds the last "transaction" for the player and places the new team name in the "Team" field. All of the transactions have a date associated with them. While I am able to find the latest date (using the Max function) I have not figured out how to place the correct team in the "Team" field. Thank you ahead of time for your help.

[AHunter3]

Mildly messy, that one.  Here's how I'd go at it:  we make a field that contains BOTH the date AND the Team name in such a way that when the combined field is assessed with the Max function, the value with the maximum date is correctly returned; then we envelope the outcome of that in a text parsing function that extracts just the team name.

It has to be a calc field with a text result, and that means we need to render the date portion in such a way that they sort in date order when treated as text.  The way you do that is to convert it to YYYYMMDD format with leading zeros as appropriate.  5/22/20 for example becomes 20200522.  

Hence:   Year (TransactionDate) & Right ("0" & Month(TransactionDate); 2) & Right ("0" & Day(TransactionDate); 2) & "|" & TeamName

-- calculation field, result type "text"

 

You define that field in the Transactions table.  Let's call it YearTeam.

 

Then in Players you define a field, let's call it LatestTeam:

 

Let (MaxYrTm = Max (Transactions::YearTeam);  Middle (MaxYrTm; 10; Length (MaxYrTm) )  )

 

MaxYrTm is going to be something like 20190317|Saxons for a player whose most recent date of all their Transactions records is the one on 3/17/19 for the Saxons.  The Middle function starts at the 10th character — that's eight for the 8-digit date string and one for the pipe separator and starts at the next character, the "S" in Saxons — and grabs everything from there on out.

 

Result:  Saxons

 

 

[Randy Bricker]

Brilliant! I would never have thought of that. Thanks so much. Worked like a charm, as they say.

Best,

 

Randy