I have Associates(Obj1) who are connected to Communities(Obj2). I have Tasks(Obj3) which are connected to Communities and Contractors(Obj4).Multiple Associates can be connected to each Community. A Contractor can be assigned to multiple Tasks and each Task includes a status field.
What I need to accomplish is to generate Evaluation Records(Obj5). For each Associate, there would be 1 Evaluation record per unique Contractor that shares a common Community.
It would be straight forward to nest FOR loops and get the data, however, this would require a significant number of API calls since I would have roughly 400 Contractors, 20 Communities, and 50 Associates. On a per Community basis, there would generally be about 8 Associates and 80-100 Tasks.
I've created some tables from different perspectives and viewed the data objects for those views(console.log). Some issues are this - If I start with the Contractor Obj, I can pull in fields from the Task Obj but I can't filter records based on the status within. The same holds true from the Community side. The most promising approach seems to be to use the Task Obj and filter on the Status field. The downside is there is a much larger number of records (could be a few thousand) that would then need to be gone through to remove duplicates (I only care about the Contractor-Community relationship in this case, not the task).
I would certainty appreciate any guidance on overall approach, how to apply filters to get unique (based on multiple fields) records from an Ajax call, or how to sort out duplicates once retrieved.