Relational databases?

[newsouthoffice]

I understand about making a relational database, normalization and all that.... but what I am having trouble getting is how Filemaker (5) works inside. I am used to creating tables, then using PK's and FK's to link the data... but how exactly do I do something like that with filemaker.

 

I have three tables (in Filemaker they are three separate databases).

 

Customers

Projects

Consultants

 

Each of these has a PK which is basically Customer ID, Project No, and Consultant ID. All my tables /databases are completely normalized.

 

But when I try to create a relationship, it doesn't make since to me are there any other tutorials (free) that I can look at? I'm trying to fix our database here, which is already created using filemaker but it's just basically a flat database and not very useful, lots of wasted space and duplicate files. I want to eliminate that by normalizing all the data, and creating relational databases.

 

Thanks for any tips. I'm very new to FileMaker but I have used Oracle. (in school) I liked Oracle a LOT. It seems so much easier to understand and comprehend.

 

Thanks

 

Stephanie

[AHunter3]

You're going to be learing a lot of stuff you'll have to unlearn later by learning FileMaker 5 in a FileMaker 9 world.

 

Be that as it may:

 

You define a rel in FmPro 5 by picking a file (table; all files are single-table in the FmPro 5 world) then joining a field in current file to field in other file and giving the relationship a name.

 

That establishes the relationship from the perspective of the local file (table) but if you want the same rel to exist as far as the foreign (external) file (table) is concerned, you need to define it there also.

 

e.g.,

 

Invoices (current file) related to Invoice Line Items (external file)

 

Serial Number = Invoice Line Items::Invoice Serial Number

 

 

 

questions?

[newsouthoffice]

Thank you.

 

Well I doubt my employer will upgrade anytime soon. They're content with what they have. So I have to learn 5 so that I can make my job more efficient.

 

Thank you for the instructions. I am guessing I'm doing it right, now I need to put the data in and see if it works

 

Stephanie

[AHunter3]

Actually, although folks don't usually explain it this way, it is ALSO true for modern FileMaker that if you define a relationship between a table in one FILE and a table in another FILE, you also have to define that same relationship going back the other way in order for it to be "understood" by the table in the other FILE.

 

What's different is that modern FileMaker lets you have multiple tables in the SAME FILE, and when you define a rel between two tables in the same file, the rels go in both direction immediately. Thus, a rel between Invoices and Invoice Line Items can then be used to reference Invoice data from a layout that is native to Invoice Line Items and also for summing up the extended price values from all related line items to get a grand total from the standpoint of the invoice.

 

 

In FileMaker 5 where each TABLE is also a separate FILE you always have to define your rels on both ends.

 

When you read stuff on this forum, keep that difference in mind.

 

 

Relationships do not tunnel in FileMaker 5: that is, if Invoice Line Items records are related by SKU number to a Products table, you can't simply display the related Product Full Name from the Products file on the Invoices file's layout -- that is, you've got a direct relationship to Invoice Line Items, and Invoice Line Items has a relationship onwards to Products, but the Invocies file (table) doesn't know about it. So what you have to do for things like that is either define a calc field in Invoice Line Items, defined as Products::Full Name, and then you can put THAT field (which is actually now in Invoice Line Items) on your Invoice layout, or else have a text field in Invoice Line Items defined as a a lookup (look up value of Product Name from Products file when SKU in Invoice Line Items matches the value of SKU in Products).

[newsouthoffice]

It's nice to know that they fixed the file vs table issue in updated versions of filemaker, that is something I'm having trouble wrapping my brain around in filemaker 5.

[AHunter3]

One thing you should understand from the outset: FileMaker is not an incarnation of the Structured Query Language type of database architecture. That was not how it came into being (although it can speak that stuff as an acquired second language).

 

It is instead its own unique environment, one that began life as one of those flat-file simple databases like the kind bundled in ClarisWorks and Microsoft Works and AppleWorks and so on (remember those?). Unlike everything else originally in that category, FileMaker gained elementary scripting capability way back when, got savvy to moving data between different files in simple ways also way back when, and therefore at a time when SQL-type systems were powerful but arcane and user-hostile as hell for newbies to learn, you could use FileMaker for simple stuff that required more than flat-file & manual-only operation features.

 

And it grew from there.

[newsouthoffice]

I guess that is what messes up my head. I think of databases in terms of creating tables that are normalized, then relating them with Primary and foreign keys. Then THAT is a database. Maybe I'm making it too hard.

 

I thought about making a "master" database (flat) with Every LAST thing in it that I need for all categories, then simply making new layouts for each different report that I need. That goes against my brain, but I could do it. And it might be easier with filemaker to do it that way since I am stuck with filemaker 5.

 

What do you think?

 

Stephanie

[newsouthoffice]

of course that flat file idea might be a big as to data entry.

[Ender]

I guess that is what messes up my head. I think of databases in terms of creating tables that are normalized, then relating them with Primary and foreign keys. Then THAT is a database. Maybe I'm making it too hard.

 

This is still the best way to go when building solutions in FileMaker. The big difference with FM6 and below, is you have to use separate files for each table. Don't try to go flat just to limit the number of files.

[newsouthoffice]

I am excited to say that my employer is going to get me the new filemaker pro!!!!!!!!!

[AHunter3]

Much better. FileMaker 5 is a nice enough tool, great in its heyday, but learning it now as an FmPro newbie is like learning computers by studying up on MS DOS command line instructions and how to copy your operating system from one floppy disk to the next. Just not necessary and mostly not useful.

[newsouthoffice]

I hope we get it soon. I really want to get my database working. I've been telling them I can make it generate invoices too. Hopefully I am right!

[AHunter3]

You kidding? You can make it brew coffee for the company owner's nephew in a remote office in Cincinnati.

[newsouthoffice]

Well as someone new to FileMaker, but with some scary knowledge of database theory (school only)..... what books do you suggest I read to help me do this. I am being given free reign to redesign our entire system from A to Z in the office.