Filemaker v Access (v Excel?)

[jacg]

Hi, I wondered if any database users could advise me.

I manage exams scores and other assessments at my school using some excel spreadsheets. They do some cool things (including calculations that bring my PB (1.25 GHz) to its knees. However, my colleagues tend to make a mess of things when entering data (thanks to the unpredictability of Excel on multiple machines). It is likely I should be using a proper database.

I own Filemaker 7 and I understand database basics but I found it hard to get into. The interface is unlike any other OS X app (more like OS 9 or Amiga OS). I miss being able to copy blocks of text from list view like in Excel.

My IT guy said it would be easy to set up a database on our network using Access (like the app, he is Windows only). Is Access easier to use? Is FM 8 any better looking or usable than FM7? Can I really do complex calculations in FM, like in Excel? Can FM make graphs of data?

Sorry for the long post. Hope someone can help!

 

[CanadaRAM]

Well, first and foremost, Access is Windows only. Forget about using it on the Mac.

I don't know anyone who would say that Access is easier to program than Filemaker, esp. for newbies. Whether ANY database is easy to use depends entirely on how well the programmer has set up the user interface.

Filemaker is quite easy to set up a friendly user interface on.

"copy blocks of text from List view" -- you can certainly copy and paste text between fields in Filemaker, so I don't know what you are saying, here. One thing is you don't have the exact equivalent of a "Fill Down" command that Excel has, for visually copying the same data across a range of records. A similar function in FM is available by doing a FInd for the set of records, and a Command-= to propogate one field of data across all records in the found set. Like Excel, this is a destructive edit, and in Filemaker it is NOT un-doable, so use with extreme caution.

Like any database, Filemaker is subject to bad data entry by users, any input that is legal will be accepted and the data permanently altered, even if the input was 'wrong'. So it is up to the programmer to create protections against users who do stupid things.

You can do complex calculations, yes.
Graphing is not built-in, but there are some Filemaker Plugins that can be bought. Or, you can export the final data back to Excel for graphing, which is quite easy. Or if you really want to dig into it, you can link Filemaker and Excel together so that the data is passed automatically from one to the other.

 

[jacg]

Clarification...

Thanks CanadaRAM. What I meant by copying blocks is, for example, I have a list of names in an Excel database, filtered perhaps, and as a one-off I want to paste that list into another document. I can do this.

However, in FM7 I have to set up a data export (or report, or whatever it is called) even if I want to extract some data as a one-off. Does this make sense? Is this the same in FM8?

I know Access is Windows only. Does this mean there is no way to enter data into an Access database held on our server from a Mac without Virtual PC?

If we went down the FM route would client computers need FM installed to enter data? Or can it be installed on the Win2003Server network?

Sorry... More questions!

 

[ahunter3]

Thanks CanadaRAM. What I meant by copying blocks is, for example, I have a list of names in an Excel database, filtered perhaps, and as a one-off I want to paste that list into another document. I can do this.

However, in FM7 I have to set up a data export (or report, or whatever it is called) even if I want to extract some data as a one-off.

No you don't. You define a global text field, GlobalText Field A, then take 90 seconds to write a script:


Go to Record [First]
Set Field [GlobalTextField A, ""]
Loop
Set Field [GlobalTextField A, Left("¶", Length(GlobalTextField A))&FullName]
Go to Record [Next, Exit after last]
End Loop
Copy [Select, GlobalTextField A]


Add GlobalTextField A to your layout (it can be 1 pixel by 1 pixel and set to not allow entry and not to print, if you wish to make it unobtrusive).

 

[CanadaRAM]

Thanks CanadaRAM. What I meant by copying blocks is, for example, I have a list of names in an Excel database, filtered perhaps, and as a one-off I want to paste that list into another document. I can do this.

However, in FM7 I have to set up a data export (or report, or whatever it is called) even if I want to extract some data as a one-off. Does this make sense? Is this the same in FM8?

See ahunter's reply. There are several ways to do what you want. Another is to script an export. Either of the scripts can be attached to a button so you can fire it off with one click.

I know Access is Windows only. Does this mean there is no way to enter data into an Access database held on our server from a Mac without Virtual PC?

Correct

If we went down the FM route would client computers need FM installed to enter data? Or can it be installed on the Win2003Server network?
Sorry... More questions!

Yes, each computer would need the Filemaker Pro 7 or 8 Client on their machine -- or Filemaker clients can be installed on a Terminal Services server with appropriate number of licenses.

Or if you have a gazillion people, you can create a Web interface to Filemaker Server Advanced that allows for Browser access to enter and view data (not all functions of the program are available by browser interface, but most are).

There are 4 different products, Filemaker Pro, FileMaker Advanced, Filemaker Server and Filemaker Server Advanced. You will use one or more of these depending on the number of users, network considerations, types of data access required, requirement for Web browser access, etc.

There are whitepapers and documents and maybe movies at www.filemaker.com that cover all this and much more.

 

[stevep]

As far as I can gather this is a problem for all schools - a typical secondary has 100 staff (most of whom are fairly computer-illiterate) who all have to transfer data from their own records into a central 'repository' (which might be a spreadsheet or a database of some sort). This isn't a gradual drip-feed of data, but a tidal wave which occurs a few times a year.
If you have to manage the data with Excel you mustn't let anyone else get their hands on your 'master' spreadsheet, so you have to get each teacher to give their own little spreadsheet to you, and you copy it into your master spreadsheet.
If you use a database you need to set up forms for users to input data, along with data validation rules so that they don't input rubbish. You also have to allow for the fact that several users might be trying to input data at the same time.
There are some specific school management systems for this, such as SIMS and Nova-T. They require a fair bit of training, and you're looking at a year or so to get such a system up and running - it would probably be the same for a database sytem as well if you're starting from scratch (ie as a database beginner).
If you're the only Mac user at your school, it might be wise to either stay with Excel or use Access on the pc, as there may be a few competent staff around to help you.
The college I work at uses a combination of Excel spreadsheets and Nova-T, and we're currently in the third year of the move to an IT-based recording and reporting system. Its still far from perfect, despite having a number of highly competent staff, including Excel experts and database programmers.

 

[jacg]

Thanks for all the advice, everyone.

We actually have a roughly equal number of Mac and PC-using staff. Only about 20 people have to use the shared spreadsheet at the moment. Trouble is that for unknown reasons the spreadsheet opens differently on other peoples' computers. For example, if I hide a load of critical calculated columns, they appear unhidden on the next user's computer; guess what happens next (remember, they are CRITICAL...)

Excel grumbles aside, I am also under pressure to set up a system to handle reports. This is where my database needs really kick in. So I guess I either spend £££ buying a solution or some serious h:min:s setting one up myself...

The advantage of Access is that many people will have it already, or can easily get to a computer with it. I don't have a PC though. Presumably I can export the basics of a database from FM to Access?

BTW, I really just rambling now.

 

[CanadaRAM]

Excel grumbles aside, I am also under pressure to set up a system to handle reports. This is where my database needs really kick in. So I guess I either spend £££ buying a solution or some serious h:min:s setting one up myself...

The advantage of Access is that many people will have it already, or can easily get to a computer with it. I don't have a PC though. Presumably I can export the basics of a database from FM to Access?

BTW, I really just rambling now.

While the export-import of data is pretty easy, the porting of database code, scripts, calculations. layouts and field definitions between FMPro and Access is difficult or impossible.

It's like saying: moving passengers from a Chevrolet to a Mercedes is easy; but moving the engine and transmission parts from one brand to another is a @%#%#.

 

[ahunter3]

Use FileMaker.

Switching folks from Access to FileMaker is at least as great an act of kindness as switching folks from Windows to MacOS.

 

[CanadaRAM]

Use FileMaker.

Switching folks from Access to FileMaker is at least as great an act of kindness as switching folks from Windows to MacOS.

Problem is, jacg will have to get approx. $300 per user x 20 users past the purchasing approval people... on top of the programming.

 

[ahunter3]

Problem is, jacg will have to get approx. $300 per user x 20 users past the purchasing approval people... on top of the programming.

Not necessarily. Shell out for one copy of the Developer SW. Design runtime solutions to distribute free of charge for the 20 users. They won't have networking and therefore cannot directly edit the main system, but they can have a script that exports their data to an export file that they attach as an email file attachment, which is then imported into the live system.

You can even create a script in the runtime to assemble and send an email containing the field-data with delimiters inserted, and then deploy a plug-in such as POP3it on the main system so that FileMaker itself directly fetches email sent to a specified email account, parses the result, and adds the parsed data into the system in real-time, thereby eliminating any need for someone to run an import script manually every time they receive such an email attachment.

 

[jacg]

Not necessarily. Shell out for one copy of the Developer SW. Design runtime solutions to distribute free of charge for the 20 users. They won't have networking and therefore cannot directly edit the main system, but they can have a script that exports their data to an export file that they attach as an email file attachment, which is then imported into the live system.

You can even create a script in the runtime to assemble and send an email containing the field-data with delimiters inserted, and then deploy a plug-in such as POP3it on the main system so that FileMaker itself directly fetches email sent to a specified email account, parses the result, and adds the parsed data into the system in real-time, thereby eliminating any need for someone to run an import script manually every time they receive such an email attachment.

Sounds amazing, but also sounds a bit beyond my capabilities. What exactly are runtime solutions? (By all means point me to a website or book!)

Is Developer SW a special version of Filemaker? (how much?)

 

[jsalzer]

FileMaker

If you've used the database in ClarisWorks/AppleWorks, you have a good start on FileMaker. It shares with its grandfather the ability to easily pick up new skills as you need them.

Runtime allows you to take a database you've set up and "package" it as its own application. You can give each user a copy to keep on his machine (or on his network drive). They can do all the data entry (and anything else you've programmed into it) without actually having FileMaker installed. You would have a script written for them to export their data into a file that you can easily import into the main system. And all for the cost of only one license!

I can't say enough for FileMaker. It has its flaws, but it has allowed someone as dumb as me to do amazing things. I'm actually in the process of creating a fairly complex database tracking students, workshops, registrations, finances, room usage, equipment checkout with dozens of tables and hundreds of scripts. And I'm enjoying doing it!!!



In any case, good luck!!!

 

[rdowns]

I'm a proficient Excel user. Picked up Access very easily although I don't do pretty stuff.