Availability Report - not sure how

[MartinaL]

I want to create a report that displays the dates that certain Acts are available to be booked.

Now here lies my problem. I have (which relate to this) 5 tables acts, members, events, avails, agents

So Acts is self explanatory, members are the individual people who make up the acts because some are in more than one Act (1 act can have many members, and each member can be in many acts), Events are the event bookings for Acts for certain dates and avails are when an act has made itself unavailable on certain dates and agents are the people who look after the acts.

So I want to run a find by agent and date range to draw up a report to show, grouped by act, what dates they are available to play.

Now this will have to check if a member of one act is not available on a certain date because they are in another act then all of the acts they are in are not available on that date.

Right now I am struggling to work out how to do this!

[MartinaL]

i'm not sure if this would wokr, but maybe some kind of portal in a calendar view?

[Søren Dyhr]

I would say that the real danger here is to get stuck in a known metaphor, simply because it's familiar ... databases as such is in my humble opinion something else than arranging things in a way making it possible to eyeball empty slots and recognise certain patterns.

Your case here is quite similar to this contemporary thread:

http://www.fmforums.com/forum/showtopic.php?tid/201085/post/317037/fromactivity/myposts/#317037

...I'm fully aware that the linking it provides are hard to digest ... but are unfortunately silly to ignore if you really wish to get to the bottom of such problems, more than just getting the work done.

--sd

[comment]

This is a challenging puzzle - not sure it can be solved entirely within the constraints of a forum Q&A. But there are a few things that need to be clarified first:

1. You describe a many-to-many relationship between Members and Acts - but there's no mention of a join table (Roles?).

2. The purpose of the Avails table is not clear. It seems to me that "when an act has made itself unavailable on certain dates", this is for all practical purposes the same thing as an event and therefore should be recorded in the Events table.

It's also not clear if an act being unavailable means that ALL members of the act are unavailable too (e.g. is the act unavailable because it's booked elsewhere, or because the lead singer is in detox therapy?).

[MartinaL]

Thanks for the replies, I have attached an ER diagram of this part of the database so you can see more clearly the relationships.

There is a table between the Acts and their members as you will see, it goes Acts - Membership - Person

Also, if a person is not available for whatever reason then all of the acts that they are in are not available.

Avails.pdf

[MartinaL]

If for example Act A is booked for a gig then all of the other bands that Act A's members are in will unavailable for that day.

Also, if one of the member of Act A is either playing with another band on a certain day OR have an entry in the Avails DB that they are on holidays for example on that day then Act A is not available.

Your right, this could probably have been done in the events DB but unfortunately the DB was already built by the time I was brought on board to try and help out with the DB!

Edited February 8, 2009 by Guest

[comment]

if a person is not available for whatever reason then all of the acts that they are in are not available.

...

if one of the member of Act A ... have an entry in the Avails DB

But that's not what you have: you have either a record in Events for the entire act, or a record in Avails, again for the entire act. There is no place to enter the unavailability of a single member.

Anyway, to move forward with what you do have (and assuming you have rolled Events and Avails into a single table) the first issue here is how to identify the acts affected by bookings of other acts. This is relatively easy, and I will put it aside for the moment.

The second issue is how to produce a report of things that are not. You can easily produce a report of acts that are booked in a given range, broken by day - because each such booking is a record in Events. OTOH, the available acts are acts that do NOT have bookings in this period. It's possible to find them, but they cannot be sub-summarized by day, because there is no table that has a record for each such act/day combination.

Therefore the report must be produced by either writing it into a global field, or by creating records in a temp table. Whichever method you choose, the process needs to follow a looping pattern:

For each day in the given range:

• Find the acts that have events booked for the day (or are unavailable due to members' conflict);

• Show omitted acts only (these are the acts available on this day);

• Create the part of the report that deals with this day.

Are you with me so far?

[MartinaL]

Actually Avails are on the join between Acts and People (i.e the memberships), i actually got this a little wrong, so a "ticket" is created in this table for each of the following BK - a booking is made for an act so every person in the ACT gets a ticket with the status BK for that day, and if a person from this act is also in another act then all of the people in the other affected acts also get a ticket with a status PB (previously booked ticket) for that day, and then there is the third type of ticket which is NA and this is when a person makes themselves unavailable on a particular day and they get a ticket with a status of NA.

I have attached and updated ER Diagram and an example of the Ticket screen

So I would need a field in the temp table for each day of the month?

ER.pdf

Ticket.pdf

[comment]

I am not sure I understand this ticketing business - it seems like that it's mostly replicating redundant information. I think there should be a table of act events (including both bookings and unavailabilities) and a table of individual member unavailabilities.

So I would need a field in the temp table for each day of the month?

No, it would need to be a RECORD for each day of the reported period - or rather a record for each day of the reported period on which an act is available.

Maybe you should try a simpler example first: suppose there is only one client, like a singer. There are some bookings for the month of March, say 2, 10, 15 and 25. So all you have is a single table of Events with 4 records.

Now you need a report of all dates in March that are still available. To produce such report, you need a report table with 27 (31 - 4) records (or a report field with 27 lines). This is possible using one of the following methods:

1. Have a script go over each day of March: if there's a booking on that day, skip to the next day; else create a record for that day in the report table (or write a a line in the report field);

2. Keep a table of all possible dates from now until the end of days; for the report, find all records in March that do NOT have a related record in Events;

3. Keep a table of enough records to produce a report (say 31 records to produce a report of up to a month), and use calculation fields to "assign" the records to the reported period.

Methods #2 and #3 are essentially a calendar, and are described in more detail here:

http://fmforums.com/forum/showtopic.php?tid/176396