At the club, we keep most of our records in a Filemaker Pro 6 database: stuff like attendance and cash register totals for each night.
(And when I say "most" I mean "except that a bunch of our data lives in QuickBooks instead, for some reason that none of my employees has ever been able to explain to me in a way that I understand.")
Anyway, something about this Filemaker situation is so hellaciously complicated that it's like pulling teeth to get any kind of sensible reporting out of it. I don't know if this is a property of Filemaker itself, or the database schema we are using, or just that nobody here knows how to use the damned thing.
It strikes me that all we need here is a spreadsheet: one axis is "date", and the other axis is a bunch of keywords and values associated with that date (e.g., "register-1", "box-office".) Then some simple computed fields (e.g., "total=A+B+C"), and a bunch of different views onto that grid (e.g., show a report of all dates where "event-name" is "Foo", with some columns totaled or averaged or whatnot.)
Surely the sensible thing to do here is for me to extract this data into some kind of tab-delimited text file; import that into a simple spreadsheet; and throw Filemaker away, right?
So my questions are:
- Is this, in fact, a sane line of thought?
- How do I extract this shit?
- What's a simple, free-or-very-cheap spreadsheet for OSX that will suck less than Filemaker?
I don't even know if I'm asking the right questions here, because I don't actually use this software; my employees do. But when I ask them for different kinds of reporting, it takes way too much work for them to deliver it, and when I ask questions like "if this is so hard, why are we using Filemaker instead of something else?", I just get blank stares.
A wise man once said, "Some people, when faced with a problem, think, 'I know! I'll use a spreadsheet!' Now they have two problems."
If your database schema is really that complicated, then you'll find it's more effort up front to fix it, but less effort in the long-term to use it. Since the brains are presumably concentrated in the up-front area, where you can find/bribe/pay a programmer to do the work (or do it yourself, In Your Copious Free Time), and since you don't really want to require your staff to be omniscient and 110% error free in all they do, it seems to be a better idea to use the right tool for the job.
Oh, and sorry for turning your most famous quote against you. I'm a bad, bad, bad bat, and I will come to a nasty end.
If you were trying to actually say something, what that was has completely escaped me.
Heh. What I meant was: it's a bad idea to send a spreadsheet to do a database's job. You're thinking "rewriting this FileMaker abomination is too hard, so I'll implement it with a spreadsheet instead". This isn't removing the difficulty, merely taking it away from you and handing it to the poor bastards who'll have to use your lashed-together, error-prone and insecure spreadsheet solution. It's the equivalent of saying "menus and windows are too hard, I'll write my program as a command-line thing and hope it's still as usable as it would have been".
He's trying to say that spreadsheets allow for sloppy working, so while it may look like a viable solution now, it will devolve into a monstrosity of inconsistent data interspersed with spaghetti macros. That's particularly bound to happen if you expect that your employees will be writing macros to add features and reports.
Personally I'd build this as a web app on top of one of the newfangled frameworks like Django or Catalyst or Rails or something. If your needs are simple enough that they can be covered by a spreadsheet, then someone conversant in the chosen language and framework will be able to build an equivalent web app in some 2-4 days. (This is not conjecture; I've done it and seen it done. You don't get anything fancy in that little time, but it does the job and the code is short and clean.) A capable programmer with no prior knowledge of the framework but a grasp of the language basics might take a bit over a week.
Failing that, you have the option of outsourcing to DabbleDB, as others have mentioned. Depends on whether you want to trust your business data to another business. Watch their screencast to get an idea whether it's something you can work with.
What about Open Office Base? I haven't personally done an extraction from Filemaker, but I've heard it's a great open source alternative to Access & Filemaker.
More informative articles than the shitty comment I just left.
Lazyweb: where your Lazyweb friends are also lazy.
It's horrific. Honestly, don't even bother looking.
Aside from being crashy and buggy in all sorts of horrible ways, and _really_ slow, it doesn't even have features like "Dashboard" (is that the Access word? I don't know), so instead of springing up a nice GUI, you have to pick the form out of the midst of a list of tables and queries and reports and shit just to get to the front page of your database thingy.
Seriously, I've tried using it, and I would find it hard to believe that this Filemaker thing could be any worse.
1. I'd say that the spreadsheet idea or a MySQL database sound like reasonable lines of thought. Since I don't really know how to create such "views" in a spreadsheet, my preference would be MySQL, but then you effectively have to pull a UI out of your ass.
2. ???
3. PROFIT!
Wait, no. I mean:
3. NeoOffice for OS X is a free and tolerable spreadsheet. It is basically a rewrite of OpenOffice, but in Java. Considering the alternative is to use OpenOffice in Apple's X11 emulator, the Java route acts more like a native OS X app. They both kind of suck, but I have found NeoOffice to suck less.
NeoOffice is not a rewrite -- it's the same C++ backend, with the X11 frontend rewritten to use Java libraries to provide an Aqua-like experience. It's the obvious solution!
Although I use NeoOffice for simple spreadsheets, personally, I wouldn't trust a business to it. I've seen it grind to a halt after being open for long periods, presumably due to memory leaks.
NeoOffice is scriptable, and there are rumors of it being able to use an ODBC/JDBC source like Filemaker, but I see no easy setup guide and a lot of people complaining it doesn't work.
One useless data point about NeoOffice (which might be useful, depending on the hardware at the club)... it was insanely, painfully slow on my iBook (1GHz G4, 768MB of RAM; not exactly high-end).
If you value your financial data at all, putting it in a database that will silently truncate data (MySQL) is not the way to go.
A) Yes, and people want to sell spreadsheets and databases, so you're fucked anyway.
B) I have only lies and conjecture, which you dislike, so I dunno.
C) OpenOffice/NeoOffice, or if they can stomach it, throw it onto the Webified spreadsheet programs. Then you don't have to worry about lock-in, just JavaScripts bugs and server downtime.
I've been trying to solve similar problems for a while and have no better solutions than existing tools. Love to hear what you finally decide to use.
your organization's accounting scheme scares me.
quick books took 5 minutes to learn and it the better the packages feature better reports. enter register1, box office, etc... as items in your inventory and that was you could plot these in quickbooks.
i worry what happens if a government agency stopped by and wanted to look at the books.
Actually, I agree. Over the years I've seen over a dozen different accounting apps from Quicken and Quickbooks to Peachtree to ACCPAC and other atrocites. For anything smaller than a huge business, really, Quickbooks is pretty damn good. I'm all for open source, but QB is just dead simple and good. I hate to admit that too, because I hate Intuit's blood sucking tendencies.
i worry what happens if a government agency stopped by and wanted to look at the books.
They'd probably be so surprised to see one of the few honest nightclubs in this town that they'd completely forget to wonder at the redundant features built in to the DNA accounting system. FMP sucks, but QB doesn't track everything that needs to be tracked.
It may not be very-cheap enough but dabbledb does some nice things with not very complicated data like you've described: http://dabbledb.com/ It is all web 2.0 though.
This is the closest thing to what you want that doesn't involve writing it yourself, I think.
Seconded. I would hesitate to trust everything to a startup like DabbleDB, but as long as you do regular exports and backups, it might be exactly the thing.
I forgot it's name, but I'm pretty sure that office software on caroline's mac has a spreadsheet app.
The important thing when planning a spreadsheet or databse is to know up front what you want from it. What information goes in, and what you want to know from that information. Without knowing exactly what you want to know before setting it all up, it will be a huge PITA to change that later.
Spreadsheets are also disaster-prone if you don't/can't lock the formulas, as cut-n-paste is relative. If B2 references B1, copying B2 and pasting to J8 will reference J7.
Typically the way you would do things the way you described, is to build one row of inputs and formulas, then copy and paste across all the columns. I've never seen where you can set up a "template" for a row, and just have it automatically apply across that row.
I've done a fair bit of work with spreadsheets, but generally not a lot for accounting. I made one to automate 80% of the BS involved in making a monthly schedule for floor. It did all kinds of crazy field crap to build a calendar grid based on month/year, counted shifts, set start-times based on doors, etc. They can be powerful, but you have to plan them really well, and they are not always the best tool for the job.
cut-n-paste is relative. If B2 references B1, copying B2 and pasting to J8 will reference J7.
Can be. Referencing $B1, B$1, or $B$1, OTOH--in Excel, at least--has very different results.
IIRC Filemaker export is possible via the "File > Export" option. Last I checked there was an Excel option, the classic CSV/TSV text files and some XML stuff. Unfortunately, for spreadsheets you've got Excel and things that suck a lot more than Excel on OS X (an impressive feat) at an exciting variety of price levels.
Have you thought about something like DabbleDB? From the sound of it, it does basically what you want to do, but it has the disadvantage of storing your data someplace else, which might bother you (or it might not).
The answer to your question about 'what is so complicated' is that by far the most likely answer is that no one there knows how to use the damn thing.
While Filemaker certainly has its flaws (including historically some appalling flaws as a developer platform that they are gradually fixing), its not that hard to use once you've learnt how - and has several virtues, including being a very solid and reliable and easy to use solution once its set up (for straight data entry and pre-prepared reports and other such mundanities).
You proposed solution sounds like it has a large danger of throwing out a rather thriving baby in pursuit of changing the bathwater - you might get your reports, and in the process other stuff that is now very straightforward and easy and reliable will become less so. In particular, replacing a data entry app with a spreadsheet is a good way to add encourage adding errors to data entry.
My suggestion is just hire an experienced Filemaker developer. And they will add the capabilities to do what you want in under half a day, most likely (I'd happily do it, but I do live in Australia, which is inconvenient). And that will be a whole lot less painful . If you ask them, they'll also add spitting out that data in some form you can input into a spreadsheet for those occasions when you feel like doing a bit of extra analysis.
Or, for that matter, someone who does know how to really work with Filemaker could probably show you what you need to know to do it yourself in an hour or two.
Two words I would never use in relation to FileMaker: "solid", and "reliable".
Also, my history with experienced FileMaker devs is that they are both expensive and useless. :(
Two words I would never use in relation to FileMaker: "solid", and "reliable".
Admittedly I haven't used it much in the last few years, so I don't know v7 and v8 have got flakier, but my experience in the FMP 6 and 7 timeframe was that a lot of people have apps that have kept on solidly doing their fairly simple thing for years without many problems, and the DNA experience of having a simple app that has kept a business running for years essentially untouched isn't that uncommon.
my history with experienced FileMaker devs is that they are both expensive and useless.
Like any of that sort of thing, a lot of people drift into development for it because they don't have the background to be real developers (and couldn't write in a real language to save their life), but seem to be able to get things done. Such people are generally useless when they hit problems outside their limited experience, yes. But I've known a few smart ones too (mostly people who did it as a sideline because why use a real language if all you need to do is churn out dozens of reports and data entry screens?).
So, finding a good one is hard - but then again, what we are asking for here isn't rocket science, its just a bunch of reports.
According to this PDF on FileMaker versions, FileMaker 6.0 was pre-multi-table files. That is to say, it is designed to be a spreadsheet with a GUI and a handy filtering system.
So, learning FileMaker 6.0 would probably not just get him what he needs. Neither would improving the schema, as FileMaker 6.0 can't really do schemas. You can link multiple files, but that's not what it's for, if you see what I mean.
My answers are:
As inane and expensive as this may sound, I would either upgrade FileMaker or move the accounting to an accounting system.
You can link multiple files, but that's not what it's for, if you see what I mean.
Not really. Linking multiple files exactly the way you would link tables in an RDBMS is exactly what its for, if you see what I mean.
My advice was based on the idea that whatever its doing now, its doing perfectly well, and is keeping track of the actual cash quite OK but it could do with a bunch more reports. I could be mistaken in this impression, but if I'm not then replacing it with something that does at least as good a job of keeping track of the actual cash but also does the needed reports was the goal - and the shortest path to that goal seemed to me to just add the damn reports.
Linking multiple files exactly the way you would link tables in an RDBMS is exactly what its for, if you see what I mean.
In theory, yes. In practice, this often does not work so well with FM6. It's not a real solution for linked tables. I've been working with this stupid program for years.
A relational database with a properly-designed schema will give you the ultimate flexibility when it comes to massaging the data for any report that's theoretically possible.
I suggest PostgreSQL, MySQL, Firebird, or any other SQL-accessible relational database program combined with a friendly front-end. I think it might even be possible to hook it up to a spreadsheet program using ODBC.
The downside to this suggestion is that it'll take some skill to set up.
The fact that you're asking for different views of your data (show a report of all dates where "event-name" is "Foo", with some columns totaled or averaged or whatnot) just screams that you really need a relational database behind it instead of merely the fixed spatial organization that a spreadsheet offers.
Outside of that, if you can handle the data entry errors, a spreadsheet would do just fine. You can still use a spreadsheet here; however, you're going to be spending a good chunk of time and energy manually building the other views, which may or may not make the whole purpose of moving to this kind of thing rather moot.
Not that I entirely disagree, but realize that Excel has had "pivot tables" for most of a decade by now. It's similar to the old Lotus Improv stuff -- basically enables trivial OLAP work based off a simple list in a different sheet.
Would probably do lots of what jwz wants, but (1) it's microsoft, and (2) you still have spreadsheet problems.
But they are a bit more powerful than just a simple x/y anymore, and it doesn't take grody coding, either.
- This is a sane line of thought.
- Export as tab delimited as already mentioned.
- docs.google.com has all you need. The online spreadsheet has all of the Excel-related functions that you'd need for bookkeeping.
This is a sane thought only as a way to look at one set of data. I was an access fiend; I'm only just learning to use FileMaker. If you can find someone to teach you how to make the report once, though, it should be something you can set up and then just look at whenever you want, and it will auto-update.
Sounds like you've probably got too much info for one spreadsheet, though. Not to mention... one spreadsheet means that everyone can see/possibly f-up all of your data, and maybe that's a little too intimate for all levels of employees?
Crap; if it were access, I'd SO offer my help :)
<3 good luck!
if you hate the filemaker, then I would suggest a mySQL programmer to come and fix you up. I haven't heard anything either way about the dabbleDB, but maybe that's also a good idea.
Preface: I did tech support for FM so I both love and hate it.
1) seems to make sense, You have a big blob of amorphous data from something someone put together and hasn't trained and maintained. What you have is one way to fix it.
2) exporting depends on the schema. chances are it is made as a flat file and you can export all the fields as CSV/TSV or not. If it has related files then you have to export all the files and make sure that you know what keys you need to reassemble the data
3) 4D claims to be competitive but I think you are just looking to get away from a DB solution. Apple Works if you can find it will do the job. Open Office on X11, etc ,etc
Filemaker's reporting is a bit obtuse and stupid. FM lets unskilled people design the schema and interface enough to get by on it, then share to other people in the office until it became a mission critical app and IT's problem.
If you want I could take a look at it and tell you why it's hard for your people to get answers out of it, or at least help get the data out.
The example reports that you are asking for are pretty simple and Filemaker should be able to support it. It might be easier for you to spend an hour or two with a O'Reilly FileMaker Pro book and figure out how to do it (look for pivot/cross-tab functionality) then spending the time to configuring the spreadsheet extraction.
If you do want to go the spreadsheet extraction route, you can use either OpenOffice or NeoOffice and configure a JDBC data source [go to create New Database/Existing Database] and supply it the FileMaker JDBC driver. It will then be able to extract the table data from FileMaker, but you still will have to do some spreadsheet formulas to get the final reports.
I agree with tague re: trying FileMaker first. FileMaker has many problems, but getting reports out of it isn't one of them. I imagine it's both set up wrong or inefficiently, and that they don't quite know how to use it well.
You certainly could figure it out, but it sounds simple enough that your time is probably better spent dropping the dough for a FileMaker guy. Just don't let him change the scope of the project on you--"Oh, you know, what you really need is a FM server and 12 iMac clients and a Web connector..."
As simple as this sounds, it should be 5 hours or less. The other benefit to a decent FM guy will be that the form can be made to look and work a lot better than what you'd get with a spreadsheet. You can make a lot of pretty good quick&dirty apps with FM.
Has this problem been solved already? I imagine this is something that venues in general would be keeping track of / reporting on, and there is probably good/standard software for it already. Other venue owners might be a better source of advice here than random LJ people. (Or, if everyone else is just winging it too, sounds like a good project to take on in your copious free time.)
I've been working in this field for the last 7 years or so, so I can take a stab at question 1 at least.
Well great. There goes my idea for a night called, "Foo."
If you are using this database for accounting purposes, then your best option would be to go out and buy some ledger books and write this shit down. In pen. You're more than welcome to use a computer to help you with the sums, but the canonical source should be dead trees. Unless, of course, you have some magical way of never getting audited. Using computer software that you find "hellaciously complicated" throws up some red flags for me. One, you're screwed if you get audited. Two, how do you know that nobody's stealing great gobs of cash from you?
So, if you want this data as your accounting books, putting it in a spreadsheet is insane. Perhaps reporting will be somewhat easier, but your data will be even more susceptible to accidental and purposeful errors than in a database. Also, you might be surprised at how bad spreadsheets are at dealing with large amounts data.
On the other hand, perhaps the Filemaker stuff is just a data visualization environment. (Perhaps that's why your also use QuickBooks. If that's the case, you should be happy.) In that case, sure, try out a spreadsheet. If it's wrong, you might mismanage your business a bit, but at least you won't mismanage your business and pay giant fees to the IRS. Other posters have described the solution space fairly well, I have nothing to add except that I agree with baconmonkey.
And here I thought accountants printed off those reams of paper because they were building nests..
Haha, read paragraph 3. Yeah, yeah, TLDR, piss off.
I think you'll find that those spreadsheets aren't The Books. They are a data visualization (/manipulation) environment. As per paragraph 3.
Two, how do you know that nobody's stealing great gobs of cash from you?
He'd see that at the end of the month, his profits would be waaaaay down.
Er, wait...
Extraction of the data should be a snap. Once you identify the labels of the fields that contain the data you want,go to File > Export Records ..., name the exported text file, and then start selecting which fields you want the export to include.
I've made a few databases in FileMaker 6 (Mac OS 9 and X) and I can't say I've had endusers complain about stability problems like I'm seeing here...
why not use a financial package?
That's what QuickBooks is.
um, yeah. I know. My timestamping seems to be messed up...
When you say that you have records in filemaker I'm not sure you are being specific enough. What kind of records? Income? Expenses? Auditable data? Internal information like inventory?
Once the type of data is defined what are the exceptions in the data (if any)? Like - this is a running tally of our attendence - but it doesn't include people we comp because that data is kept over here so we can write off the comps as business expenses on our taxes. Or this is our income from attendence but it doesn't include the fact that we had a discount that night (like maybe a food drive $1 off if you bring a can sort of thing). All of which complicates the problem. Which has a tendency to dictate the set of solutions available to you.
Basicaly what I'm saying is that you've defined the problem in very broad and vague terms. Since the problem definition is very broad the proposed solution space is going to be very broad as well. The more accurately and concretely you can define the problem you are trying to solve the more narrow your solution space will end up being.
For example, if your problem boundaries include 'easy to used by untrained monkeys', 'doesn't contain auditable data', and 'we never look at this shit again' your solutions will be very different than if its defined by 'easy to used by untrained monkey', 'the tax man will want to see this', and 'our business falls over and burns up without this data'.
"Me too" on the "don't change to a spreadsheet" themes above. I use an old version of FileMaker for databasey things, and it limps along just fine. You just need to get its reporting feature to do what you want.
Last time I looked (about four years ago), there are a bunch of not-too-expensive Filemaker consultants in the Bay Area. I suspect that a couple of hours of wand-waving will make you feel better about your system and not set you back too far.
Any mention of Filemaker gives me horrible flashbacks to a project where the publisher had their bug tracking software implemented in Filemaker. On the other hand, it seems to be possible to construct bad software from any set of tools, so perhaps it was a problem with the implementation.
Why aren't you just using Quickbooks Pro for everything?
This is a business, right? That's what Quickbooks Pro is for.
If Quickbooks isn't capable of generating the kind of reports you want (not likely) you can always export the data and screw with it in Excel if it makes you happy.
This is one of those questions that, when I ask it, gets me answers ranging from "uh, I don't know" to "it doesn't do that", depending on day-of-week and barometric pressure.
Someone is blowing smoke up your skirt. Get QB Pro, and pick someone to spend a day doing nothing but it's tutorials. If they don't understand it at the end of that day, find a person with a pulse. QB is powerful, easy to use, flexible, and for a business your size, if it's not doing what they want, then they're doing it wrong. If I were in SF I'd come by and show you myself. I hate Intuit because they're assholes, but sadly QB is worth it.
Yeah, something isn't right if QuickBooks Pro isn't working for you. My roommate uses QuickBooks Pro to run a fairly large restaurant/bar, and it will generate all kinds of reports. And he's not really all that computer literate except for e-mail and YouTube. So if he's getting it to work, you should definitely be able to.
I think the people using it just don't know how it works and how to make it work for your bar.
Trying to run a business without real accounting software is like trying to work on a large software project without having a bug reporting database. The fact that your employees can't give you the reports you want should be setting off a dozen alarms in your head. Maybe your employees like using FileMaker. Maybe they are using it because, you know, they've always just done it that way.
Ultimately, though, I would recommend putting the toys away and getting some real accounting software. It shouldn't be too hard to setup and once you do, you'll be able to track where all of your inflows and outflows are. Trying to roll your own reporting scheme seems like a waste of time. Business accounting software is a solved problem.
Disclaimer: I was very close to becoming an accountant before I was saved by software development instead.
I would also recommend QuickBooks Pro. It'll do everything you need to do, you won't have to try and come up with stuff out of the box, and it'll be someone elses problem to maintain.
Now, I don't know what issues other people have with Intuit, but I find it extremely comforting that the company is run by lawyers and accountants. I carpool with someone who used to do QA there, and they do NOT fuck around with features. There is no feature creep at that company. They ship their software on the same day every year.
There is no feature creep at that company.
Having been a Quicken user for the past decade, I agree. And Intuit has had problems with that, because they've already implemented every possible useful feature, meaning that the traditional "new and improved!" revenue model does not work.
"Is this, in fact, a sane line of thought?"
Based on what you want stored in terms of data, yes. But, the reporting you want might be a bitch.
"How do I extract this shit?"
There should be an option for this in the top-level menus. Can't remember exactly where and what it's called, but Filemaker allows you to export in a variety of formats and, wonders, you can actually specify the field order you want rather than having everything show up in the arbitrary order it appears in the database file.
"What's a simple, free-or-very-cheap spreadsheet for OSX that will suck less than Filemaker?"
Any of the available spreadsheets should be able to handle your data just fine. The gotcha, as I wrote above, is the reporting. I've done "similar" things with OpenOffice, but it's not at all enjoyable.
That said, for the data you want to track and the reports that you want to generate, FileMaker is actually a pretty decent choice. I've used it since version 1.x and, for flat-file databases hosted on a single machine, you really can't find a better solution on the Mac.
Have you tried handing your employees a book on FileMaker? You can buy the FileMaker Pro 6 Bible for less than $10 on Amazon. It's gotta be worth that investment just for the sake of not having to create, and then support, the kind of spreadsheet you're talking about.
I maintained our 6-7 (slightly crosslinked to each other) Filemaker DBs at my old job for 4 years or so... (FMP 4, then 5 at the time)
Filemaker's not bad. I suspect the problem is whoever's creating your DBs and forms doesn't know what they're doing. I'd offer to fix it all for you in exchange for bus fare there from Minneapolis, but I don't see you taking me up on that.
So, if you get someone who knows Filemaker well (damn near anyone can do pretty much anything in Filemaker, it's easy as hell, but not many people can do it so that it's not a pain in the ass to do complicated stuff), make them fix it. If not, Filemaker can export to CSV. Then get a real business accounting program (QuickBooks Pro or something) and use that.
I'd stay the hell away from "real database" talk. There are existing solutions for your needs that are vastly better than designing a new one.
The answer that seems to wreak from the way you've put the question and then commented on it:
On one side, you've got yr office staff with their existing skills, the trust you have in them, and not wanting to break things that function.
On the other side, you've got you who wants different pictures on the raw business, who knows some IT, etc.
Suggestion: note that the shrink-wrapped solutions for your kind of business suck, at the logical level. Over time, that's something to cautiously tinker with. But, also note that your staff's push-back ("blank looks" etc.) suggest they are protecting something. It could be, like some paranoid comments suggested, that they are protecting you from knowing about theft or vulnerability to audits but -- I doubt that: you seem strong in terms of creating a better class of culture than that, mostly. Rather, I think they are probably protecting an incumbant IT infrastructure that is somewhere close to the neighborhood of cheapest-fastest-best in terms of keeping the place operational. SO: instead of asking the office staff for reports, right off the bat, ask for something simpler -- get them to dump the raw data in a parsable file, pick up a lisp or something, and write your own damn report generators. Then, when your thinking is clear enough on those -- then think about taking those back upstream to your staff and making them part of the process.
-t
Congratulations, you win a punch in the junk for Most Clueless and Useless Comment of the Day. And that's quite an achievement here.
OUCH!
No, really... please bitch slap me harder. Why is that so useless? You have this "running code" (the existing office) and you want some new functionality that it's not coughing up. But that new functionality seems kind of trivial if you just write some scripts over the raw data, where it seems like the "raw data" is something the office could trivially cough up. If you use your hacking skills to get a clearer notion of what you want the office to automate you win by getting the reports you want quickly, and the operational IT changes more slowly / organicly.
Geeze.
The idea of using cigar boxes and tab-separated plain text files sounds like a good hypothesis but, realistically, it might mean subsidizing your employees taking a semester of "Introduction to Unix" somewhere -- just make sure you know what you're aiming for, first.
-t
Oh christ.
Employees do not and should not need to know Unix to get an accounting job done. That will be met with so much resentment it's not funny.
Maybe jwz's right, you just won a second punch in the junk.
If you only have one Event/Day and you count totals at end of that day and consider all collections for a given day to be associated to that Event, then a spreadsheet would seem to work as that data is pretty much normalized
However I would stick with the DB if you would also like to report on event specific data (i.e. a spreadsheet would not be best to report on "TOTALS FOR ALL 'MUSIC' RELATED EVENTS"), Where "MUSIC" is the Event_Type for a specific Event in a master EVENT Table].
You can do the above in a spreasheet, however EVENT_TYPE would have to be repeatedly entered for each event (even if it is a repeat event). This can be automated with Spreadsheet macros, however as everyone pointed out, that becomes a mess.
I Rx exporting your data out, parsing and normalizing in your favorite script (p.e.r.l. would be perfect) and then reload in a proper DB schema that would perhaps have these tables, EVENTS, EVENT_COLLECTIONS.
You can Export from Filemaker into CSV.
Good Luck.
We run our (Retina, and some of my consulting work) financials on SQL*Ledger. It's awseome, and you'll probably enjoy the fact that most of it's reporting system is in perl (with LaTex used to produce fancier reports to PDF and PS.)
It handles everything from the main books, to inventory, and does standard dual-entry accounting. My only complaint with it is that it doesn't handle rental inventory which is 60-70% of my business.
The backend is postgres, which is a simple install (yum install postgres), or you can use Oracle (now free for Linux) or even Mysql, although Postgres is transactional and better suited for accounting.
Maybe that would work for you? The reporting is ok, but better reports could be written for it.
I recommend that you hire a Consultant.