Trying to understand relationships!

[Rachel4444]

Hi, I am new to FileMaker Pro, we are thinking of using it for work and I am trying to build a database now.

Basically I have created two TABLES. One which is our Job Sheet and another which is our Customers.

I am trying to link the two so I can have a drop down box in the Job Sheet where we can choose the Customer and then the rest of the customer's fields (Tel no, address, email etc) will automatically load into the Job Sheet (to save us re-typing the information each time.

This is possibly quite simple, I have been trying to do it by going through the Help files but keep getting stuck!

I would be very grateful if anyone could help me.

Thanks for your time,

Rachel

Edited October 28, 2008 by Guest

[Kris M]

heres a way to do it using a lookup and a relationship

test.zip

[aldipalo]

Welcome to the forum.

You need to relate the 2 tables by a common field. The recommended way is to have a serial number field in you PArent table and a corresponding field in your Child table:

Customers::CustomerID

Jobs::fk_CustomerID

Connect the 2 fields in your relationship graph. You get there by clicking on the Relationships Tab when you are in File->Manage->Database.

Take a look at the files in this thread for a quick idea of how to set this up.

http://fmforums.com/forum/showtopic.php?tid/198949/post/307126/fromactivity/myposts/#307126

Also I would suggest the following reading that can be found at this site written by David Kachel:

http://www.foundationdbs.com/downloads.html

Read the White Papers and Database Design.

hth

[Rachel4444]

Hi, thanks so much for your quick reply! I have downloaded the file and had a look a it.

Am I understanding correctly that I need to have a Auo-entered serial ID on the Clients database? If so does that mean that when we go to choose the client from the drop down box on the Job Sheet we will have to remember the ID number for that client? (We have nearly 300 clients!)

Also, I have entered a Job_Number field in the Job Sheet which is Indexed Auto-enter Serial (We need this to generate the job numbers for our suppliers etc)

Does that make any sense?

Thanks again for your time,

Rachel

[Rachel4444]

Hi, Thanks very much for your response. I have downloaded the PGFs ad am going to have a look through them now.

Cheers,

Rachel

[aldipalo]

Am I understanding correctly that I need to have a Auo-entered serial ID on the Clients database?

You don't need to, but, that is the recommended method when developing a relational database. This guarantees that every record is unique. Additionally, your relationship is set by the ID not the client name. If the client or contact or whatever changes names it does not affect your relationship.

If so does that mean that when we go to choose the client from the drop down box on the Job Sheet we will have to remember the ID number for that client? (We have nearly 300 clients!)

Not at all. In your ValueList you can choose to show both the ID and the client name. This way you will see the clients name in the drop down box.

Also, you could set a button with script on the client layout that creates a new job automatically and you won't need to choose from a drop down.

If you need help on this let us know.

Also, I have entered a Job_Number field in the Job Sheet which is Indexed Auto-enter Serial (We need this to generate the job numbers for our suppliers etc)

That's correct, but, relate the Job to the Client by the ClientID. It is a one to many relationship. One client can have many jobs, but one job can only have one client. On the other hand if a job can have multiple clients then you would relate the tables by JobID.

hth

[Kris M]

Am I understanding correctly that I need to have a Auo-entered serial ID on the Clients database? If so does that mean that when we go to choose the client from the drop down box on the Job Sheet we will have to remember the ID number for that client? (We have nearly 300 clients!)

Antipaldo is correct. The Primary key field that i defined is not mandatory but is common practice. Please look up information on Primary keys, foreign keys, composite keys, surrogate keys, natural keys, etc. Its all about understanding the data that your working with.

Using drop downs and value lists generated from table values gets really interesting when you try to use first value, second value and try controlling the list members via a relationship. I used a very basic implementation in the example.

Also, I have entered a Job_Number field in the Job Sheet which is Indexed Auto-enter Serial (We need this to generate the job numbers for our suppliers etc)

Does that make any sense?

Thanks again for your time,

Rachel

Im my example the primary key was already a auto enter serial.. why create another one?

Enjoy your climb up the learning curve.

K

[Kris M]

Sorry for the misspelling of your nick Aldipalo

[Lee Smith]

Hi Chris,

You can Edit your own posts. Just use the Edit button that in located in the post that you want to correct.

HTH

Lee