Venue clash checking based on multiple criteria

[Bullion]

I've currently got this quarter working!

This is a venue booking system.

Three tables are:  

• t_ROOMS
• t_EVENTS
• t_ARRANGEMENTS

The idea is that you can have several events under the umbrella of one arrangement. (Think something like a conference that might have a Keynote speech in one space and several smaller meetings in other spaces. Or something that might go over days or weeks)

Relationships are:

• t_EVENTS has a foreign key from t_ARRANGEMENTS::Arrangements_ID
• t_EVENTS has a foreign key from t_ROOMS::Room_ID

I need to clash check against Date, Time, Room.

I have it semi-working for Date only so far: I have a second instance of EVENTS in the Relationship Graph, called EVENTS_DUP. Which has a self join relationship with EVENTS of EVENTS::Events_ID = EVENTS_DUP::Events_ID

In my EVENTS Layout, I have a portal to EVENTS_DUP. This has a filter of t_EVENTS_DUP::Events_ID ≠ EVENTS::Events_ID
So it only shows me other events that don't have the same ID as the event I'm looking at but that are on the same date.

However what I'm missing is the ability to see the Arrangement_Name field in that portal, so I don't know if these other events are part of the same Arrangement or a different one firstly.

Obviously then I need to add in time overlap and room overlaps as well, but I thought I'd get this working first before tackling more complexity and not knowing what is the failure point.

Let me know if you think the relationships are an ok basis for solving this, or if you think there's a better framework.

Then if you know of a calculation that can display the related Arrangement_Name in the EVENT_DUP portal?

[Bullion]

For the sake of anyone searching the same issue, I found a thread here just now that seems to solve my issues of checking multiple criteria for clashes.

Here: 

The whole thread is good, but that example file is the working model.

I still don't know how to get the Arrangement_Name field to show in the portal though.

 

[comment]

5 hours ago, Bullion said:

how to get the Arrangement_Name field to show in the portal

One way is to add an unstored calculation field to t_EVENTS = 

t_ARRANGEMENTS::Arrangement_Name

and place it in the portal to EVENTS_DUP. Note that the calculation must be evaluated from the context of t_EVENTS, but the field instance in the portal must come from EVENTS_DUP.

Alternatively, place another occurrence of t_ARRANGEMENTS "behind" EVENTS_DUP and take the field from there.

 

7 hours ago, Bullion said:

then I need to add in time overlap and room overlaps as well,

To test for time overlaps on the same room, define your relationship as:

EVENTS::RoomID = EVENTS_DUP::RoomID
AND
EVENTS::Date = EVENTS_DUP::Date
AND
EVENTS::StartTime < EVENTS_DUP::EndTime
AND
EVENTS::EndTime > EVENTS_DUP::StartTime
AND
EVENTS::EventID ≠ EVENTS_DUP::EventID

This is assuming that events do not cross midnight.

 

[Bullion]

This is awesome thanks! I had actually done exactly what you suggested with the stored calc field already, but I think I was getting the “evaluated from” and instance the wrong way around - or both from Events_dup. I have to admit that I still don’t fully get self-joins!

 

thanks for your help!

[comment]

2 hours ago, Bullion said:

I still don’t fully get self-joins!

It's not really about self-joins per se. A calculation always uses data from the first related record. If you look at the relationships chain:

t_ARRANGEMENTS --< t_EVENTS >- - < EVENTS_DUP

and start from EVENTS_DUP, you can see that a record in EVENTS_DUP can see only the t_ARRANGEMENTS records of the events it conflicts with. To get the  Arrangement_Name of its own parent arrangement, you must start from t_EVENTS.