Filter portal records based on date range
I am trying to get my head around a portal that would filter records based on date ranges using 2 different tables in its calculations. Here is my current situation:
I have 3 tables: Consultants, Consultants_Tasks and Projects.
When I create a new project, I assign it consultants and tasks with start dates and end dates using the Consultants_Tasks table to manage the many to many relationships.
The Consultants_Tasks table is linked to Projects with the Project_ID foreign key and to Consultants with the Consultant_ID foreign key.
I would like to add a portal to the Projects layout that would allow me to specify a date range (Project_Start_Date and Project_End_Date) and would display all the records from the Consultants table but filter out the consultants who would have a task assigned anywhere between these two dates in the Consultants_Tasks table. I just have no idea how to proceed.
I am using FileMaker Pro Advanced 12 on Windows 7.
Any help will be appreciated!