I was also thinking that I could have a candidate pipeline report and a position pipeline report.
a candidate pipeline report would show each position that a candidate applied for and what stage they are at, and a position pipeline report would show a list of each candidate that applied for the position and what stage they are at. So basically this creates 2 more tables in between candidates and positions to solve the many to many relationship problem, creating 2 one to many relationships between it.
Would this work?
What you need is called a "join" table:
Candidate::__pkCandidateID = Candidate_Position::_fkCandidateID
Positions::__pkPositionID = Candidate_Position::_fkPositionID
See this link: Common Forum Relationship and Field Notations Explained if this notation is not familiar to you.
With these relationships, a portal to Candidate_Position on a Candidates layout will list all positions for which they applied. A portal to the same join table on the Positions layout will list all candidates that have applied for it.
You can base report layouts on this join table and include fields from Candidates and Positions as part of the report to list positions and who has applied from them.
Here's a many to many demo file you may find useful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
I would call this Candidate_Positions join table "Pipeline".
So let me confirm, what you're saying is that both Candidates and Positions would be the parents for the Pipeline join table.
So it would look like this:
Candidate | - || Pipeline || - | Positions
So there is a one to many relationship from Candidate to Pipeline,
and there is also a one to many relationship from Positions to Pipeline.
And you're also saying that on the Candidate form it would be able to, display every position a candidate has applied to given that there is a portal to Pipeline,
and on the Position form it would show every candidate who has applied to that position, given that there is a Portal to Pipeline.
Is that right?
Sorry for the newbie question.
Yes, what you have posted is correct. Please investigate the links and demo fields for more detail on what I have suggested.