[NDBounce]

Okay, I am a convert from Access/SQL Server. I have never used a database on a Mac before.

So I am now trying an older version of Filemaker Pro (version 6) that someone gave me. Here is the deal, I have no clue how to run this program.

I am wanting to create a simple video library database. In Access I could do this in all of 15 minutes. I can't figure out Filemaker though. Can someone either direct me to a site that might have a free tutorial on Filemaker or give me some advice.

Here is what I would do in Access:

Create 3 tables. 1 with Videos, 1 With contacts, and 1 that would store the relational information as to which contacts have which videos,

I would then create a form that would allow me to check movies in and out. This would basically show all the contact's info, and all the movies that person has borrowed from me.

But for the life of me I can't figure this out in Filemaker Pro 6. I have gathered that each "table" needs to be a new file in filemaker (someone correct me if I am wrong). Then I have no clue how to enter a SQL Query or create the equivalent of a form.

Help please. I'm sure this will make sense to me once someone can help to convert me from MS thinking.

Peace,

O
B unce!

------

For thos who care in Access my structure would look something like this:

Table 1 Table 3 Table 2
MovieID (key)___ ID (key) ___ UserID
Movie Title \_________>MovieID / F.Name
Format UserID<_____/ L.Name
DateBorrowed
DateReturned


Then in the form I would Show LName, FirstName and movies checked out (and probably would have them as lookups. Basically a mini Blockbuster is what I am going for. Simple enough in Access...I'm lost in FileMaker.

[torifile]

You should try out FM7. It's supposed to be much better.

[squilla]

Torifile is right-- FM7 is dramatically better. In FM7, create the same 3 tables you would in Access; then create a layout on the relation table, and add portals to the other tables to bring in needed information from them. This can be done in 5 minutes.

Generally speaking in FM you use layouts to create forms; the layouts should be attached to the tables that have the core information you want, with portals to connect information from other tables.

Good luck.

[Macpilot]

Originally posted by squilla:
Torifile is right-- FM7 is dramatically better. In FM7, create the same 3 tables you would in Access; then create a layout on the relation table, and add portals to the other tables to bring in needed information from them. This can be done in 5 minutes.

Generally speaking in FM you use layouts to create forms; the layouts should be attached to the tables that have the core information you want, with portals to connect information from other tables.

Good luck.

Sounds like you might be able to help me out. I purchased Filemaker Pro 7 and the documentation sucks. I have never used a database program before. The Genius at the Apple Store could not answer this question:

I have created a Contact List in FP7 (by importing data from Excel and matching the fields). I have figured out how to make and edit layouts for printing, but that is about it.

I want another file (an Invoice) to be able to access the information from the contacts in the Contact List file. So I created another file called Invoices (from the template), and I can not seem to figure out how all this works. The idea is to bring up somebody's info by typing in their last name, for example, or customer ID number, for the purpose of tracking invoices and printing them.

The whole "relational" database thing has be confused. I cannot grasp the difference between a "table" and a file. When I try to make a relationship, I have no idea what I am doing.

I would really appreciate some help if you don't mind.

Thanks!

[squilla]

Originally posted by Macpilot:
Sounds like you might be able to help me out. I purchased Filemaker Pro 7 and the documentation sucks. I have never used a database program before. The Genius at the Apple Store could not answer this question:

I have created a Contact List in FP7 (by importing data from Excel and matching the fields). I have figured out how to make and edit layouts for printing, but that is about it.

I want another file (an Invoice) to be able to access the information from the contacts in the Contact List file. So I created another file called Invoices (from the template), and I can not seem to figure out how all this works. The idea is to bring up somebody's info by typing in their last name, for example, or customer ID number, for the purpose of tracking invoices and printing them.

The whole "relational" database thing has be confused. I cannot grasp the difference between a "table" and a file. When I try to make a relationship, I have no idea what I am doing.

I would really appreciate some help if you don't mind.

Thanks!

A table is like a spreadsheet. There can be several tables in a file. Two tables are (or can be) related if the same column (say, account #) appears in each table. That column can be used to link ("relate") different information about the same things-- for example, addresses in one file, and items ordered in the other.

To make this work, the column that appears in both tables has to be unique in at least one of the tables; otherwise things get confused (for example, if 2 people have the same account #, you can't tell which items are billable to which account). When this happens, you have to disambiguate the duplicate information, either by creating a new column that is unique, or by creating a third table which relies on multiple columns (e.g., name and address) to tell things apart.

To do this in Filemaker Pro, first go to the File menu, choose "Define" and then "Database". This will pop up a window with several tabs, labeled "Tables" "Fields" and "Relationships". CLick on "Relationships", which will get a pane where you can add little icons representing the tables, and then drag lines between them to indicate which fields are common to both tables. So now, the tables are "Related" in a specific way.

A portal is used to associate the records in one table that are Related to the appropriate records in another table. For example, you can add a portal to the Contacts table which will let you see and edit the related records in the invoices table. THis involves a fair amount of clicking to get things formatted properly, but it's straightforward.

I agree the documentation is terrible. There are several books on the market now which explain things fairly decently. Just make sure they have "Filemaker Pro 7" in the title, because older versions of FM are quite different.

The real problem is not the complications of using the program (which is fairly easy) but in getting the hang of thinking about tables and the ways they can be related. This requires some study, careful thinking, and practice, no way around it. An introductory textbook on databases will be worth the effort.

Good luck--

[Macpilot]

Originally posted by squilla:
A table is like a spreadsheet. There can be several tables in a file. Two tables are (or can be) related if the same column (say, account #) appears in each table. That column can be used to link ("relate") different information about the same things-- for example, addresses in one file, and items ordered in the other.

To make this work, the column that appears in both tables has to be unique in at least one of the tables; otherwise things get confused (for example, if 2 people have the same account #, you can't tell which items are billable to which account). When this happens, you have to disambiguate the duplicate information, either by creating a new column that is unique, or by creating a third table which relies on multiple columns (e.g., name and address) to tell things apart.

To do this in Filemaker Pro, first go to the File menu, choose "Define" and then "Database". This will pop up a window with several tabs, labeled "Tables" "Fields" and "Relationships". CLick on "Relationships", which will get a pane where you can add little icons representing the tables, and then drag lines between them to indicate which fields are common to both tables. So now, the tables are "Related" in a specific way.

A portal is used to associate the records in one table that are Related to the appropriate records in another table. For example, you can add a portal to the Contacts table which will let you see and edit the related records in the invoices table. THis involves a fair amount of clicking to get things formatted properly, but it's straightforward.

I agree the documentation is terrible. There are several books on the market now which explain things fairly decently. Just make sure they have "Filemaker Pro 7" in the title, because older versions of FM are quite different.

The real problem is not the complications of using the program (which is fairly easy) but in getting the hang of thinking about tables and the ways they can be related. This requires some study, careful thinking, and practice, no way around it. An introductory textbook on databases will be worth the effort.

Good luck--

Wow. Thanks for all that info. Looks like I am off to the bookstore.

[larkost]

Ok, first off... FileMaker 7 is a real improvement on 6, but it is not nearly as drastic as people are making it out to be. You work with layouts a little differently, and the rules on relationships are nice, but the thinking has not really changed. I really like both products, and work with them every day (while wishing they worked together...*sigh*).

The second thing is that in FileMaker you want to avoid join tables (that third table in your example), especially when you are involving container fields (movies). So here is a quick layout for what you want in your database (this will work identically on FM 6 or 7, the only difference is that the two tables would have to be in separate files on FM 6):

Table "Contacts"

• Name - this is just a sample field... and representative of all the other info
• videos_id - a non-visible number field

Table "Videos"

• id - this is an auto-enter:serial number field, non-modifiable
• video - a container field with the video in it

Now you create a relationships between the two tables matching on video_id = id.
Then you simply put in portals with these relationships (for example including the video container in the portal on "Contacts").
Now here is the FileMaker trick that you need to know: in the videos_id field you enter in all of the id's from "Videos" that you want, separated by <return>'s (not <enter>).

There you go... once you know how FileMaker works, this is a 3 minute process.

Oh... and make sure you go through the "FMP 7 Tutorial" pdf, that will give you the background to the thinking that you need.

edit: ok... silly me videos_id has to be a text field in order to have multiple values, but the id should still be a number (it is weird, but works)

[larkost]

I can't seem to delete this duplicate...