Give suggested values conditioned on two fields

AppSheet AppSheet Q&A
,
1

Hi, I have 2 tables, 1 is Room, 1 is Event. I want to give a list of available room whenever a new Event row is added with a specified date such that total duration of room used in that date does not exceed 13.

I manage to find events with similar dates but cannot sum up duration of events happening in the same room because I haven’t figured out a way to sum duration of rows having the same room ID. Thus, I cannot have suggested values.

I would appreciate your help!

2

Perhaps something like this:

FILTER(
room ,
COUNT(FILTER(
event,
AND(
[room_id] = [_THISROW-1].[id] ,
[date] = [_THISROW].[date]
)
) ) < 14
)