Building a Chart

[boykin]

I am building a "Ticket Reservation" database and would like to make a seating chart. Something that has the physical layout showing each in the auditorium, and a status (maybe red for sold, green for available) of each seat. The database contains a one record per seat, each with the seat number (e.g. A 107) and status (Sold, Available). I can easily add fields as necessary.

Does anyone have a clue how to do this?

[Fitch]

This isn't exactly elegant, but...

- create one table with a container field for each seat.

- create a layout and arrange all the fields on it as in your seating chart

- set each field as a button, and assign it a script "Set Seat" with a script parameter corresponding to the actual seat number

Hint: assign the button/script to the first field, then duplicate this field -- that way you save time, as you won't have to assign the script afterwards. But you'll still have to assign the parameter to each one.

The Set Seat script would set a global field to the parameter, then use a relationship to another table that has a record for each seat to set the status and the graphic for that seat. I think using a table like this with just a few fields and one record for each seat will be easier to manage than adding hundreds more fields to the main table. But it will require generating all the seat records in advance for each event that is being booked.

Hope that makes some sort of sense -- it's just off the top of my head. FWIW there was a really cool demo at DevCon this year that essentially showed just this solution, but it was implemented in Flash, with FileMaker as the back-end. Very cool! I believe it was from www.isolutions-inc.com .

[boykin]

This means having another table with one record and hundreds of fields. I don't think it needs to be a container; indeed, a calculation field that has the name and appropritate text format would do the same.

I think this would work, but there is no flexibility here; the database is hard coded to a particular seating arrangement and, if anything changes (add a seat somewhere), and you need to change the database proper (e.g. a layout) not just a record. I was definitely hoping for something more elegant!

What I'm hoping for is some way to e.g. create a whole bunch of rectangles on the screen, then, with X and Y Coordinates in each of the seat records, turn on/off an indicator that says the seat is available or not.

[Fenton]

Another variation of Tom's idea; well, actually pretty different. Only have 1 field. But consider each "vertical column of seats" as 1 portal relationship. It's the opposite of the way people generally think of seats, which is as horizonatal "rows," but portals prefer to be vertical.*

You need a relationship for each of the portals, so it's a fair amount of those. But it's only 1 "Seat" field, making it a heck of lot easier to do anything else with it. You could have fields for its "row", etc., so you could still present them that way to the user.

Or, to turn things sideways (which makes less sense to FileMaker, but more to "normal" people), you could create portal relationships for the "rows." Then, to display, you'd could enter Preview mode, copy the layout, paste into a rotated global container field; like you can do for charts.

Personally I'd rather do it the first way, but "fake" the display, using a "row" field and "seat number" fields as visible labels, to make it appear to be seats in rows, not columns. You'd only have to put data in these fields once; the seats are not going to change.

*You could do a relationship for each "row," then set each seat to show only 1 row of the portal, portal row 1, portal row 2, etc. across. That would give you your "rows" orientation; but you'd have to do this for each seat; a PITA. As I said, I'd rather do it the way FileMaker works best, vertical portals, and fake the interface. Nobody has to know.

[transpower]

Another way would be to use The Shadow's custom matrix functions, with a single field (a matrix) having all the rows and columns you need; and you could set each element to 1 if sold, 0 if not. You could label the rows and columns as you wish.

[boykin]

I played around with Fenton's method of using portals. I think I got it right, but he implied I would only need one field, for the column. I used such a field in the seats table, but on the layout with all the portals I needed a field for Column1, another for Column 2, etc. Am I missing something.

That is: I created a new table called "Chart" with fields "Col1", "Col2" (etc) , "Date" and "Time"; I also put a "Column" field in my seats table. Seats A101, B101, .. are Column 1, seats A102, B102... are column 2, etc. Then built a relationship between Charts::Col1 and Seats::Column (plus date and time). I then needed to create new instances of the seats table with relationships betwen Charts::Col2 and Seats::Column. Repeat for each column.

The good news is that if I get the formatting done right, this will work. The bad news is that it's a totally manual process requiring a new database if a seat gets added.

Well, given that no one has come up with a better strategy, maybe that's the way I need to go!