How to create a date range that is connected to a linked column?

[kathleenxx]

I have a table of Contacts where one column is the Companies where these contacts have worked. The Companies column is a lookup column to a table of companies. Contacts might have more than one company that they've worked for. I want to be able to attach a year range in some way to the company information, so that I can see that John Smith has worked at Company X between 2021-2022 and Company Y between 2022-2024. What are some ways I can think about doing this?

[Shawn_Peters]

You can parse the YEAR from both the MIN and MAX dates of a contract group belonging to the desired company.  I would likely use a script to do this,  triggered manually or automatically based on need. 

Depending on the overall use case,  there's a few options to render the info into a report or store it within the contract record as an array of start dates and most recent dates. (separate fields, or one,  depending on your level of need. Example: "2016|companName|2022, 2018|companyName2|2024".

Le would be glad to help,  if needed. 

shawnjp@gmail.com