A Year on the Titanic
01/19/2011 - In late October 2009, I was contacted by the owner of a tourist venue in Branson, Missouri, and contracted to develop a FileMaker solution for their new attraction. Little did I know that a three or four-month project would turn into an exciting voyage that never ends!
Cedar Bay Entertainment is based in Branson, Missouri; they created the Titanic Museum, the world's largest museum attraction, several years ago and have been enjoying booming business since its doors were opened.
On April 8, 2010, their new Titanic attraction was opened in Pigeon Forge, Tennessee, near Gatlinburg, which is at the entrance to the Great Smoky Mountains National Park. The museum is fascinating; designed to look like the ill-fated Titanic both inside and out, it features two floors of exhibits, artifacts from the ship, and interesting things to see and do for all ages (you can visit their site here).
The existing software for managing the Branson museum was written in FileMaker Pro, but needed an extensive overhaul to provide all the enhancements required by the management of the Titanic museum. Rather than try to work with the existing system, I advised that it would be more cost-effective for me to develop a new solution from the ground up; less than three days later, I was contracted and ready to begin the daunting task.
April 8, 2010 was opening day; we held our breaths and watched as employees began receiving the first guests to the museum. Needless to say, the first few weeks were not without a few "challenges," but in the coming months I worked feverishly to get the system to where it was running smoothly and providing the features and reporting that were required.
The Titanic database solution comprises several key areas: ticket sales, merchandise sales, merchandise purchasing, customer tracking, event planning, group scheduling, web sales, product receiving and inventory, and reports. In addition, there are other key parts to the system, including gift certificates, combo passes which allow guests to visit more than one venue with a single ticket, guest passes, and vouchers (provided by merchants--for example, hotels--to their guests, who can visit the Titanic as part of their hotel purchase).
Management also wanted a way to regulate ticket sales for each hour of the day, so that the museum didn't get overcrowded at any time; included with this was the need to regulate web ticket sales so that website visitors couldn't buy tickets for hours of the day when either an event was scheduled, a large group was already booked, or when sales already reached a preset limit.
Another request from management was that the system be flexible enough to accommodate changes in tax percentages, ticket pricing, adding new ticket pages, or adjusting museum capacity limits for each hour of the day. Particularly within the first few weeks, managers experimented with different hourly capacities until they reached levels that seemed to best fit the museum, so the database needed to be easy to adjust.
The accounting department required a lengthy list of accounting reports, as did the merchandise managers. More than 50 reports make up the system to date, with more being added almost weekly; using a wide variety of reports, managers can quickly monitor sales, attendance levels, traffic for each hour of the day, merchandise sales, inventory, and orders, and much more.
WHO USES THE SYSTEM
Guests to the Titanic enter through their admissions gateway, where there are three kiosks for processing visitors. In addition, the Titanic adds outside kiosks during spring break and the summer months, to speed guests through the purchase process and into the facility. Alongside the admissions area is the gift shop, where guests can buy a wide variety of souvenirs, snacks, and other items; there are three checkout stations in this area. In other areas of the facility, merchandise is checked in, stamped with barcodes; events and groups are scheduled; weddings, vow renewals and other special functions are scheduled; new ticket packages are planned and implemented. Seven hundred miles away in Branson, the accounting department and management team also use the system, creating reports, booking groups, and planning new events. In addition, a phone room with more than 20 employees (all using FileMaker) takes ticket orders 12 hours per day, fielding thousands of calls each day for the two venues.
WHY THE TITANIC CHOSE FILEMAKER
FileMaker Pro is a perfect fit for a company like Cedar Bay Entertainment. With dozens of users who constantly work in the database, requirements included: the ability to strictly limit access to parts of the system; flexibility to be able to add reports as needed to the system; expandability; scalability, so that the system could be put to use in future venues; web compatibility, so that the system could work with the museum’s website, selling tickets and limiting purchases to preset capacities; accurate usage logging and employee tracking. At present, each facility has its own machine running FileMaker Server, with approximately 20-40 people logged in at any one time using the system. In addition, the owner of the company has an Apple iPhone, on which she can view a subset of the financial reports at any time.
POINT OF SALE (POS)
Our first hurdle with this new system was to decide how to deal with tickets. In their previous system in Branson, tickets were printed for each order and given to the guest. Guests would then step to turnstiles, where a scanner on the top of the turnstile would read the ticket and unlock the turnstile for a single rotation. In practice, it was found that many of their elderly guests, as well as their very young guests, were unable to scan their tickets and needed an employee to assist. As a result, an employee had to stand beside the turnstile and scan each ticket for the guest so that they could proceed into the museum.
In the new system, I implemented a "virtual ticket punching" system. When a guest arrives at a kiosk, the POS worker quickly finds the order (I employed a "clairvoyance" script so that tickets are found as the worker types in either a name or an order number), then the order appears on the screen. Since the employees are using touchscreens, it was necessary to make buttons large and easy to read from a distance; the employee can click a button, which pulls all the order's tickets up; by tapping on each ticket, they "punch" the ticket--or they can tap a button that punches every ticket on that order at one time. The guests are then able to enter the museum.
I also built in the ability to scan a ticket, if printed tickets happened to have been issued. So far, we've not printed tickets more than a few times, so this feature isn't often used, but should the need arise, the barcode on each ticket can be read into the system, the order pulled up, and then the tickets can be punched by the POS worker.
Using FileMaker conditional formatting, the database displays ticket status at a glance; a green ticket hasn't been punched; a red ticket has been used; a blue ticket was refunded to the purchaser. Popup windows also use conditional formatting, to show ticket availability for each of the 13 hours of operation; if the hour is unavailable, the button for that hour is greyed out, and clicking it produces no result. A script calculates the current hour, so that when the popup window opens, the current hour is preselected for the employee.
By tying ticket sales to group sales, I was able to write a calculation for each hour of the day, so that employees--and the web ticketing system--are unable to sell tickets if a particular hour is already sold to capacity. Conditional formatting shows this to the POS workers, who then can suggest alternate hours to guests.
Another layout on the screen is available to the group sales team, who can view a grid showing a week at a glance, and blocked-out times during the week when groups are going through the museum. Since some groups can be as large as 200 people or more, it was important for management to be able to see at a glance when to schedule new groups. Using repeating fields and conditional formatting, I created a grid that is constantly updated to show groups at all times during the day, with color-coding to show times when the hour is completely filled up. In addition, a new group can't be scheduled in an hour that is already at capacity; by using script triggers, I have a script that prevents someone from scheduling a group during a blocked-out period.
A vital element to the POS screens was to make them fast, uncluttered, and simple for new employees. There are three layouts--one for admissions, one for the giftshop, and a third for phone room sales, where employees are using normal computers with a mouse rather than a touchscreen.
Another key part of the POS screens was the credit card processing layout, where employees can take any combination of cash, cards (two cards can be used on a single order), and gift cards. Using card swipers in their keyboards, POS workers can swipe cards and process the order through a payment gateway, using the 360 Works Plastic plugin, which interfaces with the payment gateway. The Titanic recently switched to a new gateway, and the excellent programmers at 360 Works were able to create a custom version of the plugin to work with the new gateway.
I used the ontimer script trigger to write the scanning scripts that are used in the system. Basically, the process works like this: the employee clicks a SCAN button, at which point the trigger runs, waiting for input from the card scanner; the scanner sends the card data to FileMaker, the ontimer trigger quits and runs a subscript; the subscript parses the card data, runs the card, returns the results, and updates the order record.
This same scan script set was used for manager authorization cards, which managers wear on a lariat; using the cards, managers can authorize refunds, overrides, or other functions, and every transaction is logged and tracked to the particular authorization card that was used. In the Admin area of the system, management can easily add new cards to the system, and track its use throughout the museum.
In the giftshop, employees can sell tickets as well, although it's not used at present. Scan wands are attached to each computer, and POS workers scan product to add them to an order; they can also manually find products using their SKUs if the barcode is unreadable for any reason. In the Receiving area, where thousands of products are received almost daily, the receiving manager can scan product, create and print barcode labels, conduct inventory counts, and check in new product. The system shows on-hand quantity at all times, calculating received minus sales to show an accurate count at all times. In addition, merchandise managers can adjust inventory by adding records for damaged, promos, or other reasons; all of this is available at a glance in the system, as well as in reports. Conditional formatting was used here as well, to show the status of orders; when an item falls under a certain set limit of quantity on-hand, emails are sent from FileMaker to merchandise managers, so that they are alerted to order more product.
USING THE SYSTEM FROM TWO LOCATIONS
The management offices for the Titanic are in Branson Missouri, which is 700 miles from the museum in east Tennessee. We quickly saw that reports were going to run far too slowly when database users tried to work from the offices in Missouri, accessing the database on its server in Tennessee. After working to speed up reports as much as possible, we finally settled on a solution.
The Titanic Tennessee system uses SyncDeK, which synchronizes two or more copies of the same database (or databases) running on different servers in different locations. It's not a simple system to implement, but once set up, SyncDeK copies records and record updates every 7 minutes between the two databases running on their respective machines. There are a few things to keep in mind when using SyncDeK: a) any updates to the system need to be done to each copy of the database, unless you plan to copy the database and replace it on the other location (node); b) any new tables that you add to the system need to be made known to SyncDeK so that it can include it in sync cycles (this isn't hard to do, but is a step that must be performed); c) the system has to be modified so that there is no possibility of creating a record in one database that has the same ID (or invoice number) as an order in the other database.
Thanks to the help of WorldSync owner and wizard Jason Erickson, I was able to implement SyncDeK and get it working well in this system. Users in Tennessee use one copy of the database, while the museum employees use the other; data flies back and forth between the two; web orders go into the Missouri copy, then get synced over to the museum so that by the time the guests arrive at the museum, their web order is awaiting their arrival.
In addition to phone sales and walkup sales, Titanic visitors are encouraged to buy tickets on their website, which also integrates to the system. I used a separate database and FX.php to write a web application that processes orders; to get the order from the web database to the main database, a simple script is triggered in the web app; the script moves the data from the web database into the main.
The web ticketing system also ties to the main system to ascertain ticket availability for the 13 hours of each day in the museum. If one of the hours of the day reaches its set capacity limit, this hour is not made available for purchasing online. Using this method, managers can easily block out hours of a day (or entire days) so that web purchasers can’t buy tickets for that day or time.
Buyers can also use promo codes to get special discounts on tickets; promo codes use starting and ending dates so that they can be used only for a selected period.
A merchandise shopping cart is scheduled to go live in the spring, and will allow web visitors to buy merchandise from either the Branson or Pigeon Forge locations.
THOUGHTS ABOUT THE PROGRAMMING
Throughout the Titanic database, which is a single-file solution containing approximately 70 tables, I used Anchor/Buoy to organize the relationship graph. I also try to comment scripts as much as possible, both to help me work on them later, and also to help any future programmer who might have to decipher the programming. Field names follow my own naming conventions, which weave in and out of others' conventions; the important thing here is that they are consistent throughout and follow a logical structure.
I used Ray Cologon's UltraLog custom function to write change logs for almost every table in the system. A future version of the system will include FMDataGuard in the system, which will allow more flexibility and logging capabilities.
The Titanic solution was started in FileMaker 10, which was yet to provide the excellent charting capabilities of FileMaker 11. In the Spring of 2011 I will be adding charts to the reporting section, and I expect rave reviews on their usefulness for management.
I've also created an iPhone interface for some of the key reports; upper management has access to these reports, which are optimized for use on the iPhone screen using FileMaker Go. The iPhone reports don't require any resizing or zooming; they follow common iPhone UI guidelines and provide at-a-glance reports on museum attendance and key accounting numbers. I would also like to do an iPad version of the FileMaker Go layouts as well, should management decide to buy iPads. Using script triggers, I've worked out a method for switching from portrait to landscape on an iPhone or iPad; the change looks great and works well on both devices. Read here for more on this.
Another key piece of work for the near future is an archival system, which will pull orders from the main database on a regular basis. In the event of a merchandise refund, the system will also be able to re-import an archived order, so that POS workers can seamlessly refund a product that was purchased months before. In addition, product inventory levels will be automatically adjusted as orders are archived.
On January 7, 2011, the Titanic Branson museum was switched over to the same system that was written for Titanic Pigeon Forge; the only difference is that this system doesn't use SyncDeK, since the museum and management office are both in Branson, Missouri. Future venues are already planned; a new venue called Water Magic will be opening in Pigeon Forge, Tennessee in 2013, and I expect to have the same solution employed in this facility as well. There may be other exciting additions in the future as well, and I look forward to being part of the continued success of this fine company.
I started out on this jaunt in late October 2010, and it's now been 14 months; at the rate the company is growing, and with approximately 100,000 visitors per month to the Tennessee museum, it looks like the ride will continue for some time to come. To give an idea of the rapid growth of the database: when I delivered it in April 2010 it was 28MB; eight months later it's 2.6GB, and we predict it'll reach 6GB by the end of the summer. In these first 8 months of operation, FileMaker has performed superbly, endearing itself to the owners and management of the Titanic museums.