Hopefully I've posted this in the right place and someone can point me in the right direction here... 

I have staff and staffAllocations tables related by staffID. For each staff member I'm trying to visualise their events from the allocations table, displaying them in a row so that the user can easily see what events are allocated to them over the next 10 days. I'm using a repeating field (10 repetitions ) in the staff table with the following calculation:

Let (
[ current_date = Extend ( gview_startDate ) + Get ( CalculationRepetitionNumber ) ] ;
If ( current_date > Extend ( staffAllocations::dateStart ) and current_date < Extend ( staffAllocations::dateEnd ) +2 ; Extend ( staffAllocations::_eventID) )
)

...which works apart from the fact that it only shows one (the first) related record. It does pull the eventID into the appropriate repetition(s) depending on date range for the event but I can't work out why that calc wouldn't evaluated on all repetitions and therefore show all appropriate events... I'm trying to avoid using lots of portals and relationships to generate my summarised view of events so hope I'm missing something simple (or even something relatively complex..)

Any help or advice with this would be greatly appreciated!

In a calculation, a reference to a related filed is evaluated using data from the first related record. If you want the calculation to take into account all related records, you must use one of the aggregate functions, such as List(). However, Filemaker has no conditional aggregate functions (such as Excel's SUMIF(), for example).

I'd suggest you use either a filtered portal or a dedicated relationship to get only records within the desired range. If you decide to go with a filtered relationship, you can then use a repeating field to display the data instead of a portal - though I cannot see what advantage this would bring. On the contrary, it only adds undue complexity.

Edited May 15, 201510 yr by comment

Thanks for the clarification and advice comment – I figured that might be the case and I'd be best just creating the required relationships.