On one of my layouts I want to provide a list of upcoming due dates for an employee's active projects. There are three pertinent tables which are essentially as follows:
ManagerID (links to Employees::ID)
ProjectID (links to Projects::ID)
Initially I attempted to use a portal with filtered relationships on a layout based on Employees. I created an additional field in Employees called ActiveStatus that was a calculation (="Active"). Then I added that as a condition to the relationship with Projects (conditions: Employees::ActiveStatus=Projects::Status and Employees::ID=Projects::ManagerID). I then added two fields to Projects: Uncompleted, a calculation field (="x"); and TwoWeeks, an unstored calculation ( = Get ( CurrentDate ) + 14 ), and I created one additional field in Due Dates: CompletedDateX, a calculation field ( = CompletedDate & "x" ). Then, my relationship between Projects and Due Dates was as follows: DueDates::ProjectID=Projects::ID; DueDates::CompletedDateX=Projects::Uncompleted; and DueDates::DueDate<Projects::TwoWeeks. Using this I was able to get the data that I wanted in the portals. Yay!! ... Except that it was extremely slow in loading the portal, especially for remote users. The portal was set to sort by DueDates::DueDate and filtered based on the DueDates::Name, but even when I removed both of those, it was still quite slow. There are nearly 130,000 records in DueDates, but I thought the filtered relationships would make it more manageable (after all the filtering takes place, the largest result set is around 150 due dates for one employee's projects, but this took over 30 seconds to load and I'm working locally). I'm not sure why it is so slow.
So, instead I ended up using a List view on DueDates, and did a scripted find, searching for DueDates::DueDate < Get ( CurrentDate ) + 14; DueDates::CompletedDate=""; Projects::Status=="Active" and Employees::Name==EmployeeName. This was much faster. More what I had expected. A few seconds at the most.
But I'm still confused why the portal was so slow? Is it because the portal is working down: Employees -> Projects -> DueDates, whereas the List View is directly looking at DueDates? Or was there something about how I was setting up the filtered relationships that made it slow? (using the unstored calculation for TwoWeeks, for example)?
Obviously there are significant advantages to using a portal in terms of layout control, but I wasn't aware there was such a significant performance hit.
Oh yes, we are currently using FileMaker 11, but will likely be upgrading to FileMaker 14 in the next week or so. I'm not sure that I can expect a significant difference from that, but perhaps it's relevant.
Thanks for your help,