A Showcase of what FileMaker Can Do (AKA, Any Cyclists in this bunch?)

[exizldelfuego]

Hi all, I don't want to take up much time or space site-plugging (and I apologize if I'm overstepping my bounds here), but hopefully this will be of interest to people out there.

 

To all FileMaker-loving cyclists, I'd like to invite you to our recently-launched redesign of BicyclePaper.com, covering all things cycling in the Pacific Northwest with an event calendar stretching into the Rocky Mountain region.

 

To all FileMaker-loving non-cyclists, I'd like to invite you to our recently-launched redesign of BicyclePaper.com to see the results of what a tremendous amount of time and energy in FileMaker and LassoScript can get you.

 

To power the new site and help us on the production of the printed paper, we've completely redeveloped our FileMaker 5-era series of database into one, unified database containing approximately 30 different tables managing everything from contacts to orders to news to events to regions to issues to articles to photography. It's easily the most intense database of which I've been part, and it's incredibly rewarding to see the new site up, taking full advantage of the new system.

 

We're really excited about the recent release of FileMaker 9, opening up the possibility of migrating the data storage to MySQL (while still retaining our internal FileMaker interface) which would speed up the site a fair bit, but that'll have to wait. This round of development has been exhausting and we're still licking our wounds.

[exizldelfuego]

And of course, I spaced out wrapping my link with the actual link Opps!: BicyclePaper.com

[Sunshein]

Great newsletter layout. I would like to hear more on how you published the pages onto your website. Is it interactive with you database?

[exizldelfuego]

Great newsletter layout. I would like to hear more on how you published the pages onto your website. Is it interactive with you database?

 

Thanks for the interest; the site is, with the exception of a few pages of obviously static content, completely dynamic and database-driven. Entries become visible to the website either by natural inclusion (such as a news item getting a posted date) or by explicit inclusion (like an article getting its approved flag raised).

 

The website, which is powered by an Apache host running a Lasso server, maps incoming URL requests looking like, for instance, /news/2008/02/28/...3 to the site's index.lasso page with parameters telling it to display all news listings for 2008-02-28 on paginated page 3. Note: the above example is sure to find nothing; 2008-02-28 would have had to be an incredibly busy news day for there to be enough entries to reach to a third paginated page, but you get the idea.

 

The site is laid out with primary, secondary and tertiary columns and depending on the focus of the page requested, get filled with different components. The tertiary column is pretty static as it's holding this month's advertising. But if you take, for instance, the articles listing page being loaded in the primary, the site will then load the news listings in the secondary. But if you're looking at an article detail in the primary, the site will load up the article components in the secondary so you can more easily skip from article to article.

 

The site has been coded to be as modular as possible, which allows us to be so flexible when it comes to loading up various areas of the site.

[zradosevich71]

First off, I want to comment about what a fantastic website you have. Second, I'd like to ask how you created your interactive calendar. I'm trying to do the same thing with a database I'm working on and have hit a brick wall. Was it in the Filemaker scripting, or was it in Lasso? Or something else? I'd appreciate any ideas. Thanks!

[exizldelfuego]

First off, I want to comment about what a fantastic website you have. Second, I'd like to ask how you created your interactive calendar. I'm trying to do the same thing with a database I'm working on and have hit a brick wall. Was it in the Filemaker scripting, or was it in Lasso? Or something else? I'd appreciate any ideas. Thanks!

 

Hey, thanks for the comment! As for the interactive calendar, it's all done in Lasso. The important thing here is to separate the content (events, which reside in FIleMaker) from the display mechanism (the calendar, as presented through Lasso). [side note: that the page is called "/calendar" rather than "/events" is, I believe, a misnomer; but that was an internal debate I lost.]

 

In terms of data for the calendar, all I really cared about was figuring out which days had events (a calendar of this size is much too small to show any event details). Along the way, I discovered it'd be trivial to figure out HOW MANY events were on a given day. To accomplish this, I created a Map as it's called in Lasso; other languages might call it a hash or a dictionary; it's basically an array of pairing. The keys of the map are going to be the numerical days that have events. The values of the map are going to be the count of events on that particular day. The process of finding this information is really simple:

 

1. I do a search on the Events table for events in my given month.

2. I loop through the records doing my_map[day]++.

 

The actual Lasso syntax is uglier than that, but that's because Lasso syntax sucks. Hard. The results is a map of days with a count of events on each day.

 

The second step is figuring out how to build the calendar. The basic algorithm I came up with is:

 

1. Get the month of today.

2. Get the last day of today's month.

3. Start an HTML table.

4. Start an HTML table row.

5. Starting from Sunday, fill in empty HTML data cells until you reach the first day of the month's day of the week.

6. Looping though the days of the month until you reach the last day of the month:

6a. If the loop-day of the week is a Sunday, close off the previous HTML row and create a new one.

6b. Put down a table cell for the loop-day.

6c. If my_map[loop-day] has a count > 0, put down an anchor tag () with an href to that day's url and a title containing the count of the day.

6d. put down the day's number and close off the (anchor and) table cell tags.

7. Close off the last table row.

8. Close off the table.

 

This should build your table. You can have fun with the table's header rows and caption to make your HTML table look like a nicely-formatted calendar (see here). Format the anchor tags within the table rather than the data cells with CSS to easily denote which cells have events.

 

Just about any programming language will have some concept of time; and in that concept of time there will probably be some mechanism for determining things like day of the week, etc. One caveat is that dates are generally date-times, and when you initialize a date without a time, the time gets set to zero o'clock. This is a problem when a time-change occurs. Initialize your dates at noon and it'll never be a problem.

 

The next step from there is getting the table to be interactive without a page-reload. I had a leg up here because, as I mentioned a couple of posts up, I'd broken each individual component out into its own file, allowing the overall page to be really flexible and modular; modular being the key here. The above instructions are based around the assumption that I'm building the calendar around "today." But if I just open that up a little bit and say "I'll build the calendar around some day, and if I don't get a day, I'll build it from today," you've set yourself up nicely for some client-side AJAX work, which is probably outside the scope of this post.

 

PHEW! So Cool!

[exizldelfuego]

ps: I should note that i actually have a little bug in my code which I haven't yet stopped to track down; once you get outside of the current year, the calendar extends to swollow the following month's first day. I figure I have approximately eight months to figure that one out before people start to notice. Oh Really!

[zradosevich71]

Wow! Thank you so much for your help and advice. That reply took a lot of time and effort, and it is very much appreciated. I can't wait to get started!

By the way... I will be forwarding on your site to all my cycling friends...smiley_cool

[exizldelfuego]

Glad you appreciate it, and thanks for forwarding on the site! It's not often I get to elaborate on my trials and tribulations; for some reason, my family and friends just don't seem get this stuff smiley-undecided. If you've any more questions, post them and I'll get to them as I'm able!