I'm working with SharePoint 2013 and recently take over and learn how to do stuff in it, and had to migrate some old project databases to sharepoint. I currently have our old databases uploaded in an access app, a project list, and a drawing list. Both lists being exported from the access file.
The project list has around 11000 items in it and has a unique column of project number being Year-XXX and the drawing list (around 7000 items, i get some kind of 5000 limit message) has a unique column of drawing number NXXXX and a column for Project number corresponding to the project list. I'm looking for a way to link the two together like how the lookup column works, but automatically. I've tried making a new column in the drawing list set to lookup from the project list and autofill from the existing project number list and that didn't work.
Is there a way to do this that isn't too complicated? Would it be easier to make a completely new list?
My ultimate goal on this is to make a search where my team can search through our old projects and be able to quickly see the associated project form associated with the drawing.