Very Complex Relational Queries - Is it possible?

[Roy33]

Dear All,

I am an access user and I need to make my access database work on the Mac. I am working with Filmaker 6 Pro trial version in order to see if this package will work for my needs. I am having quite a bit of trouble with more complicated queries (that were relatively easier to design in access) and I was wondering I what I am trying to do is even possible in Filemaker.

Again, I'm not looking for the details how to do these things (actually, I am) but for THIS question all I really want to know is if these things are possible. Once I know that it's possible, then I spend some time figuring it out. I don't want to waste anyone's time explaining something again that's probably explained somewhere else.

1. I can't seem to write and store SQL queries for my existing filemaker data. The program seems to only let me import from other data sources like access or sql serever --as if I only need queries from existing databases!

I tried to import my access queries, but those that span more than one table won't work. (I've used the SQL importer (don't know the name) in Filemaker to associate the fields correctly, but some of the fields in my query are associated with other tables (now filemaker files).

I can make a query for one file, but I can't seem to have a query that includes 3 or 4 filemaker files. In access I'd use a select statement, eg. SELECT * from tbl_1 AND tbl_2 AND tbl_3 WHERE etc. etc. Is there a way to write a SQL SELECT statement that spans mutliple files and can be stored as a layout?


2. Also, every time I create a query, it returns the data INTO the current file. Can I make a query as a script that can be called when I want it, not when I design it?

3. Does filemaker support queries upon queries upon queries? I have a situation where I 1) count the number of sales records for a specific department by each employee 2) I then use this query as the basis for a second query that returns the employee and the with the most sales in that department (e.g returns Joe Smith -- 5 Sales -- Record Department. Can this be done in filemaker?

4. Is Filemaker TRULY designed to be a relational database. Access, Oracle and SQL Server seem to be all about relational data, wherease filemaker's manual has one chapter on relationships. I've used Filemaker for other simple tasks, and I am finding it much easier to use for these simple things as compared to access. But when it comes to the tough stuff above, I wonder if it's the right way to go.

Thanks in advance for your help!
DavidW

 

[Schlogg]

David,
(Hope I'm not sticking my neck out too far here. Am sure to be corrected if I do.)
Moving from Access to FMP, you need to do a mind-shift.
SQL queries do not exist within FMP. You can access OBDC objects but that's about it. (Do you get the 'Help' with the trial version? If so, have a look.)
You can simulate queries quite readily but queries on queries on queries are a bit more difficult. Depending on the overall design, you might need to create intermediate files/tables. All of this can be scripted.
You could do the equivalent of "SELECT * from tbl_1 AND tbl_2 AND tbl_3 WHERE etc. etc." but you need to do a lot of the work.
In a nutshell, you can do just about anything in FMP that you can do in Access BUT you often ned to use little/big tricks.
There are books written on these tricks but you need to know a fair bit about FMP to get the benefit. Try "Special Edition Using FileMaker Pro 5" by Coulombre and Price with a list of what's new in V6 beside you. (Thee are some dramatic improvements in V6.
Good luck.





Cheers,
Paul J.

 

[Schlogg]

Dasvid,
I forgot to add that my experience with MS products on Mac is not good - mostly Word. Is there a Mac MS Access?
FMP is beatifully cross-platform. The whole database can be transferred seamlessly from PC to Mac to PC.

Cheers,
Paul J.

 

[Roy33]

Unfortunately there is no access for the mac. My only other choice is to take the access application, convert it to sql server, and web-enable it.

As for conversion, I found myself converting a filemaker database to PC to print (mac was having printing issues -- separate thing ) and I found myself exporting the data to CSV on the mac. Then on the PC I recreated the table structure, and imported from the CSV file I made on the Mac. I hope the way I did it is the long and difficult way. Please tell me there is an easier way to convert a mac fmp file to pc file.

 

[lebisol]

FM V7 is..... can't say when!

but for now....
". My only other choice is to take the access application, convert it to sql server, and web-enable it."
-WHY?
FM runs great on web...with PHP, HTML+CDML,Lasso...
anyhow

> need more info?
:: don't click HERE ::

 

[Roy33]

Why, you ask?

Because it is WAY to difficult to get things done in filemaker. All the advice I've been gettings is about tricks and workarounds. I think that filemaker has its place, but I am finding it quite impractical for very complicated relational databases.

again...just one man's opinion.

 

[lebisol]

ok, fair enough...I was refering to the web use (if that is the goal) and how inexpensive it can be. But, I feel your pain when one can beat 10 applescripts with 1 SQL statament....
Good Luck with the project!

> need more info?
:: don't click HERE ::

 

[davidanders]

No, Filemaker will not satisfy your needs (at least ver6)
these links are comparisons of access and FMPro

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

fm_access_comparison.pdf [PDF]FileMaker vs. Access:
*

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

fm_vs_access_overview.pdf [PDF]Comparing Database Tools: FM 5.5

By the same token, Access breaks down with many users and remote access (unless the database is very well designed) and SQL becomes the next step.

Filemaker can handle 90% of a users database needs, unless they're trying to create something in the other 10%

Each Filemaker database is equivilent to an Access table, to create more complex query is going to require an intervening database rather than a simple query between three or more tables.
Data Normalization and good design aside...

 

[sipps]

lebisol,

I have used PHP with MySQL and have now moved into a company where FileMaker Pro 6 is used. I am really not used to how it works (all those seperate files!). I want to create a web site that shows the FileMaker data. I normally use PHP/Dreamweaver, do you know what the PHP support is like with FileMaker?

I don't even know where to start, can I call the FM database just like I would with a MySQL database? Also, it is not SQL absed, so how are quieries written when web producing with FMP?

Thank you,

sipps

 

[lebisol]

hello sipps!
If u just want to jump into PHP+FM then take a look at:
PHP---------------------------------

http://www.iviking.org/FX.php/

-----------------------------------
but if ur still debating consider:
ASP--------------------

http://www.fmcdn.com

-----------------------
also, I have seen people use JDBC and JSP
u can also use CDML that are native to FM...althouhg u have to consider security and the fact that it will require some JS knowledge to get things done.
or even get the middleware (Lasso) whihc seems to be the way to go if u want to stay "native" to FM.
above all join:

http://www.fmforums.com

as it is FM ONLY community!
Good Luck!

> need more info?
:: don't click HERE ::

 

[chagme]

I've been an Access user for years and now have to deploy an FM datbase on FM server for LAN and remote users.

I've made the database available with FM Server (on Windows 2000 server) and have found that the remote users have to use Remote Desktop for OS X or Terminal Services Client for Windows (using FM Pro as a remote client over the internet is too slow).

I now realize that I could have used Access and simply have Mac users on the LAN use Remote Dekstop. So much for cross-platform.

 

[lebisol]

Hello chagme!
I have to say my 1st impressions of FM where
but I learned to like it. It is cross-platform (it runs on all 3 major platfroms). How well is a whole antoher story....
again, lets not forget that FM never really wanted to step up to Enterprise levels....it was always for WorksGroups!

" I could have used Access and simply have Mac users on the LAN use Remote Dekstop. "


so you would change the DB but keep the same remote concept....?
Access is just as bad over the WAN.....
if it is just serving the information than I would say Access will do but if ur looking into sharing DBs that can be further developed by users...well I think FM has A LOT BETTER and user friendly GUI....anyhow
All the best!

> need more info?
:: don't click HERE ::

 

[chagme]

Thanks for the feedback.

Not saying Access will perform better over the WAN. I'm saying that if you need to deploy a client/server database and feel Access is better all around, but you went with FM because of Mac/Win cross platform capability, you could have used Access and had your Mac users run the Access client on the server through Remote Desktop Client.

Your *remote* users will have to use RDC anyway, because at the kind of speeds most of us have, running FM as a client when accessing a remote database is slow, whether it's FM or Access. So, what you're doing with RDC is emulating a local Windows desktop and running the FM client on that remote machine.

Now, having said all that -- I've found that on the LAN you could also have your Mac users get there with RDC -- there's really no differnce in performance on the LAN whether you use RDC or run the FM client on your Mac. The point here is that the xplatform capability of FM becomes moot when you consider using RDC. IMHO, of courses.

 

[chagme]

I was just going over my last post and it seems kind of confusing.

What I meant to say was that running FM or MSAccess on your local desktop system at the home office to access data on a remote database is not as fast as using RDC or Terminal Server client, where you run the client software on the remote system. All that's coming across the WAN is a picture of what's happening on the remote side. I've tried it both ways.

Of course, you first have to establish a VPN connection before running RDC or TSC.

 

[lebisol]

Hello Chagme!
You are right on top of the things!
I never liked Applescript over VBscript or just plain old SQL. I think that SQL is a bit harder to start with but ones digested it is more powerful in its processing -querry logic than the AppleScript.....
I guess every tool has its palce.....some years ago Mac users had no "native DBs" so was it.
Anyhow, keep your eyes on the FM v 7 as it will have some improvements......
All the best and good luck with the project(s)!

> need more info?
:: don't click HERE ::