| Comments: |
![[User Picture]](http://l-userpic.livejournal.com/56260827/10743742) | From: ![[info]](http://l-stat.livejournal.com/img/userinfo.gif) etfb
Tue, 29-May-2007 4:49 AM (UTC)
| (Link)
|
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.
![[User Picture]](http://l-userpic.livejournal.com/5887295/515656) | From: jwz
Tue, 29-May-2007 4:51 AM (UTC)
| (Link)
|
If you were trying to actually say something, what that was has completely escaped me.
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.
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.
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.
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.
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. :(
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.
![[User Picture]](http://l-userpic.livejournal.com/15168285/422568) | From: nrr
Tue, 29-May-2007 5:35 AM (UTC)
| (Link)
|
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.
- 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.
![[User Picture]](http://l-userpic.livejournal.com/61285277/586623) | From: tague
Tue, 29-May-2007 7:18 AM (UTC)
Try FileMaker first, Extraction Second | (Link)
|
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.
From: darkengobot
Thu, 31-May-2007 9:03 PM (UTC)
Re: Try FileMaker first, Extraction Second | (Link)
|
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.
- Yes, it's perfectly sane to want simple output in a CSV format. Just about everything reads or write it, which means that once you have it, the spreadsheet and graphing programs of the world are your fingerbowl. You probably want it in a flat format for accounting purposes if nothing else. So yes, that's a very common request.
- Once you have those CSV exports, there's really no reason to ditch the source program and retrain your staff, unless that'd be trivial or you're sure you have something better to replace it with. You're safe from vendor lockin at this point, and you can look at the data in whatever program you like this week. No, I don't know any for the Mac. Also, I'm not familiar enough with your business requirements to recommend a flat system over a relational one, for more than the reporting stage. Sometimes it's fine (in those situations where you could substitute an account book for the computer especially), but I've also seen some horrible messes made by sending a spreadsheet to do a RDB's job.
- I don't know anything about this "Filemaker" of which you speak. If it's one of those idiot programs that doesn't export CSV, and you have to get that implimented, make sure the export is real CSV. You wouldn't believe the number of times I've seen programmers screw things up by not quoting fields with commas in them.
- If anybody tells you that nobody uses CSV anymore, and that you must do this in XML, slap them. CSV is so damn near universal that there'd better be a specific reason for using XML. Be extra sceptical of anything with an acronym more than three letters long.
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..
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.
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'.
![[User Picture]](http://l-userpic.livejournal.com/65411257/13045758) | From: paul_e_hoffman
Tue, 29-May-2007 4:51 PM (UTC)
Filemaker is probably good enough | (Link)
|
"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.
![[User Picture]](http://l-userpic.livejournal.com/52586670/1508713) | From: krick
Tue, 29-May-2007 5:51 PM (UTC)
| (Link)
|
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.
| From: jwz
Tue, 29-May-2007 6:18 PM (UTC)
| (Link)
|
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.
![[User Picture]](http://l-userpic.livejournal.com/446278/20554) | From: vordark
Tue, 29-May-2007 7:58 PM (UTC)
Filemaker | (Link)
|
"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. | |
