Questions from an Access developer

[Sparrowhawk]

Hi,

I have been using Access almost daily now for... ooh 10 years? (can it be that long now!). Access continues to make me a good living in the corporate Windows world, but my home preference is for OSX.

There is a Windows application that I wrote on Windows in Access recently that is providing me with very good client feedback, and I am considering porting it to Mac. I have investigated REALbasic as an option, but to be honest the data controls leave something to be desired (although plugins exist).

As such I am now looking at using Filemaker 8.5 Advanced or possibly 9 when that is released. I was wondering whether the experts here could answer some questions - I have done quite a bit of research, but have read some conflicting reports so thought I'd try here:

1. How powerful is Scripting in FM compared to the flexibility and power of VBA in Access? I need a lot of control to write custom code, as much of my work is is niche software for corporates (eg Anti-Money Laundering), and I need to model algorithms as well as quite complex rules-based workflow

2. Somebody told me that FM only supports inner joins. is this really true? I am pretty sceptical about such a claim, so would appreciate a definitive answer

3. Does filemaker work in the same way as Access in terms of defining your tables, your relationships, and then building forms and reports on top of these (or on top of queries based on these).

4. Is there an equivalent control to Access' SubForm and SubReport. I'm pretty sure there is, but I'd like to be sure :

5. Are applications written in FM for Mac compatible with FM for Windows, and vice-versa?

6. I see SQL is coming to FM with version 9. Will that be a native engine or drivers to popular database systems such as Oracle, MySQL, SQL Server etc?

7. Please don't take this as a troll, but would somebody like myself needing a high level of control and flexibility, quite happy to delve deep into the inner object model etc be better suited going down the 4D route, or is FM the beast for me? 4D does not seem to have been updated for quite some time, a fact which worries me quite a bit.

Many thanks for any help and guidance

[Genx]

I'm going to try my best here...

1) -- Hmm, At first glance, FM Scripting is quite limited, but once you know what you're doing it's fairly powerful. It's native interaction with the Windows OS is frankly quite bad limiting you to cmd scripting (apple scripting possible on mac) and with a cheap plugin you can also vbscript in windows.

2) There's a lot of differing terminology b/w db programs as you probably know, so define what you mean in relation to an inner join.

3) I've worked to a minimal level with access however defining tables -- Yes to a large extent but 1) It's a lot quicker, 2) It's a lot less picky about data types e.g. there is a number field type and that's about it as opposed to integer, long etc. Relationships, yes though after working with FileMaker, Access leaves a lot to be desired.

The term query doesn't really exist in FileMaker -- Reporting works over foundsets -- whatever records you happen to be looking at at the time, however there are many different ways of isolating the particular records you're after and storing those foundsets for future use.

4) SubForm and SubReport -- Portals do the trick here, work a lot better as SubForms but not as great for SubReports (but still usable in the same fashion).

5) The apps will run on both FM and Mac almost exactly the same. Because FM is cross platform, you can have the server on windows, clients on mac, PDA's on windows mobile -- whatever mix you can really think off and everything interacts quite happily. Seeing as it wouldn't be very useful if the application didn't run in the same fashion on both platforms the answer should be obvious here.

6) FileMaker already interacts to some extent with SQL via ODBC (can't tell you more here you might have to look it up).

7) Can't really help you there.

My Opinion:

FileMaker, rapid application development, almost anything achievable in Access is achievable in FileMaker with much less effort. At first glance, FileMaker might appear to have limitations when compared to Access, but I assure you almost anything is possible if you know what you're doing or come to these forums to ask.

The one thing FileMaker really sucks at is event scripting but from what i hear, new functions will be added to this in FM 9.

Okay, finally seeing as this explanation was probably absolutley no use to you :

http://www.filemaker.com/downloads/pdf/fm_access_comparison.pdf

Keep in mind that the past 4 FM releases have brought it a lot further.

[Sparrowhawk]

Thanks for your help. Some food for thought there - I guess the only true way to be sure is to download the trial and try to replicate some existing systems I have written - see how I get on.

Inner joins: by this I mean that only records where the foreign key in Table B matches the Primary Key in Table A are returned in a query (foundset?). LEFT join would be where all records in Table A are returned and all those which match in Table B. If there is no match, null are returned instead. For example a Client may have zero to many accounts, but you still might want the client details returned even if it has no accounts. RIGHT joins I'm not too worried about right now.

I think the introduction of SQL in version 9 (I think I'm right in saying this is coming) will make FM a lot more attractive to me. The ability to use Stored Procedures would be great too, but I doubt that will be included :

Again, my thanks.

Edited February 6, 2007 by Guest

[Genx]

Not sure you'll be able to get far with the trial, even with your in depth knowledge of databases. As much as FileMaker doesn't have a steep learning curve, if you want to do something complex with it, it requires the use of unique methodologies in a lot of cases...

Inner joins: by this I mean that only records where the foreign key in Table B matches the Primary Key in Table A are returned in a query (foundset?). LEFT join would be where all records in Table A are returned and all those which match in Table B. If there is no match, null are returned instead. For example a Client may have zero to many accounts, but you still might want the client details returned even if it has no accounts. RIGHT joins I'm not too worried about right now.

So both are effectively query types?

An inner join returns all clients with or without phone numbers whilst a left join returns all clients that have phone numbers available?

Isolating those particular records would be fairly easy if what i'm thinking of is what i should be thinking of...

Just remember, if you think you can't do something in particular, bring the question here and we'll likely prove that you can : .

Edited February 6, 2007 by Guest

[comment]

There's no left outer join in Filemaker, at least not as such. It is possible to simulate it - with some limitations. Much depends on what you actually need to do. In general, Filemaker does a lot of things differently, and it can be very frustrating if you try to do them the SQL way.

I haven't heard of "SQL coming to FM with version 9". Right now, Filemaker can execute SQL on external sources, and serve as a source for ODBC/JDBC applications. If the rumors are correct, I suppose these abilities will be extended - but I don't think Filemaker's own internal database will go native SQL.

[comment]

An example of an outer join:

Parent 1

- Child 101

- Child 102

Parent 2

- Child 103

Parent 3 //is childless

Parent 4

- Child 104

- Child 105

IOW, it's what you would get if you generated a report from Parent with a sliding portal to Child.

[Genx]

Ah right, well, that's not too limited i guess -- you could just shove one giant 500 rowed portal on the layout : .

I seemed to be thinking just searching vs reporting for some strange reason.

[comment]

that's not too limited i guess

Well, that was an easy example. Here's a more difficult one:

[color:red]Computer A:

- CPU: Pentium

- HD: 120GB

- CD: Pioneer XYZ

[color:red]Computer B:

- CPU: Titanium

- HD: 180GB

- CD:

There is no record for Computer B's CD, but we still want that row in the report, AND in the portal in Browse mode.

Edited February 6, 2007 by Guest
Trying to make the structure more clear

[Genx]

I can't work that example out in my head because it looks flat file, can you give another please?

[comment]

It's not a flat file. There are 3 tables: Computers (2 records), Slots (3 records) and Parts (5 records).

A similar example: for each customer, show how many of each product they bought. Include each and every product, even if they didn't buy it.

Edited February 6, 2007 by Guest

[Genx]

Hmmm, i suppose that does become problematic. Lol, don't want to think about it to hard, but there might be some way of running it through the products table and a join table -- anyway what happens if they bought two of a particular product?

[comment]

In the last example, it would show 2 (sum) against that product. The point is that products with sum 0 would also be shown.

In the previous example with the computers, you would be prevented from installing two parts in the same Computer/Slot combination, so the question wouldn't come up.

[Genx]

Lol, just so you know i'm not in any way arguing with you, I'm just curious is all :

[Sparrowhawk]

Back again - thanks for the further updates with regards joins.

I think I may need to review using FM. Whilst it will doubtless be able to do much of what I want, I have a feeling from what you guys are telling me and from further reading elsewhere that it is simply too different for me to be up and effective quickly enough.

I do appreciate the ease and simplicity of FM though, and also that FM experts such as yourselves are clearly able to do great things with it, so please don't take that as a criticism of the product itself!

I am still not too sure about 4D. It certainly has the power (and SQL familiarity) that I seek, but it's still a Carbon app that has not been updated properly in ages it seems (or at least no major releases)...

I think that I am going to go back to developing using PHP/MySQL/Ajax for the enterprise solution, and use REALbasic for now for some smaller apps that need to be desktop based.

My thanks again for your help - I am sure to keep popping back here from time to time to see how FM progresses.

Lastly, FM users can be justifiably proud of these forums - very informative and friendly :