I have ended up now setting up a “helper” or translator table for each situation like that. That 3rd helper table is linked to both Table 1 and Table 2 and that way I am automating the linking from Table 2 to Table 1 via the non-primary fields. It works okay, it’s less gimmicky than using search step in automation or Integromat but I can imagine this could get messy with multiple linked fields like this.
Not to mention I have to keep detailed notes of how I have done this because the time passes and if I have to modify it I will be looking at it like a kid eyes open as if looking at something for the first time 