Newbie Q on Matching fk for NFL Database

Discussion created by kingusc on Aug 2, 2018
Latest reply on Aug 2, 2018 by philmodjunk

Hi everyone - intimidated first-time poster here.  I'd sure appreciate any help the community can provide, however.


I've used a FM database that I've built to track NFL gambling data for the past few years. But now, in ramping up this year, I'm adding a player module to the database, so that I can keep updated injury and depth chart data within my database rather than on paper logs.


The good news is that the data is pre-provided. The bad news is that it does not, of course, match my FM terminology, nor is the data even provided in a way that would seek to view the data from the same perspective I am (the data comes from a prebuilt SQL solution in the form of CSV files that I'm importing).


My Primary ID for teams is simple - numeric by teams' first name (Arizona is 1, Atlanta 2, Baltimore 3, etc). And I also have in my Teams table a team abbreviation (ARI, ATL, BAL...).  As luck would have it, the player table I'm importing also contains a field called current team with the exact same abbreviations.  I did think to just change the PK to the abbreviation, but then I can't track franchise changes (Oakland to Las Vegas, STL to LAR).


Can I somehow, via script or even just a mere calculation in the FK field, tie the Players' current team to the Teams' Abbreviation and assign the corresponding PK from the Teams table to the Players' FK field?  Does this even make sense?  Thanks so much.