Hi,

I just started working with FileMaker Pro and am really amazed at how easy it is to set stuff up. I've been trying to program stuff with Real Basic, and finally realized much of what I want done can be done with FileMaker. So, I've been working with it and also browsing this forum and other website for info.

But I still have a weird relationship problem.

Here's the deal. I have a three main databases. CONTACTS, JOBS, and ORDERS.

Contacts may or may not be clients. Some clients have jobs, others order from another client's job.

My problem is this:

I want to be able to easily relate contacts with another contact's jobs as well as have all orders for a job relate to that job. I can't think of an easy way to set that up.

Right now JOBS relate to CONTACT and ORDERS relate to JOBS. How can I make it work so that JOBS can have multiple CONTACTS (I assume that it would be a 'many to many' relationship) and what would be the best interface for that?

It seems really simple, but I just can't figure what to do. Thanks for your help!

Work out where "clients" comes into the picture first! Simplify the structure. Who orders from what? Why relate two contacts together?

A structure might look like this:

CLIENTS (company) might have more than one CONTACT who have JOBS. JOBS can have nore than one ORDER.

Get the picture? There is no reason why one CLIENT should be related to any other -- if they are then they are really CONTACTS and need to be re-arranged.

Who's on first, What's on second, I Dunno's on third...

So are you saying that contacts and clients should be separate databases?

I'm not sure if I explained it well enough. For each 'JOB' which is assigned to a specific CLIENT, there may also be others (clients) who order from that same JOB. (I'm a wedding photographer--the bride and groom make orders, but so do friends, relatives, etc.--they all order directly from us, so we want to keep their orders assisgned to them, but relating to the bride and groom's job.)

Is that too complicated? Or am I just not getting it?

It's so much easier to answer questions when we're given the *whole* picture, not just snippets.

OK it sounds like a decent data model would be...

WEDDING -> CUSTOMER -> ....

Now I'm not sure what *you* mean by "job" and how that fits into the scheme. What I call "wedding" might well be your "job."

You really need to sit down and work out the data structure. It sounds like you've got...

weddings

customers

orders

There might be some join files too. Can a wedding can have more than one customer? Can a customer have more than one wedding? Can a customer place more than one order? Can an order be for more than one customer?

Thanks for helping so far Vaughn. Sorry this seems a little ambiguous.

You're right 'Jobs' would be 'Weddings' although I thought I should make it jobs since I also want to have 'Portraits' another type of job. But maybe I should have a separate database for portraits since they are so different and require different sets of data (actually just MORE date for a wedding).

quote:

---

There might be some join files too. Can a wedding can have more than one customer? Can a customer have more than one wedding? Can a customer place more than one order? Can an order be for more than one customer?

---

I'm not exactly sure what you mean by a join file and/or how to execute that. A wedding is usually just one customer (or couple), but other customers will order from the same wedding, and they may place numerous orders (usually no more than 2 or 3). We separate orders so they are always for just one customer.

I'm wondering if I should simply use 'Weddings' or 'Jobs' as my main database and simply relate customers to it. I guess I could relate orders straight to the customers and then by a round about way have that all related to the wedding/job.

I could make enter a field which would indicate whether the person was active or just an inquiry.

The only other problem I can think of is if one of the related customers (not the actual wedding customer), hires me shoot their wedding or portrait. I've had to relate them to a new job, or would it be ok to simply start over with them on a new job?

Thanks again--this relationship stuff is really hairy!

It sounds like you don't really understand relational databases -- one-to-many and many-to-many relationships in particular. You've gotta work these out!

Take a quick read of the FMP manual, or some other good FMP book.

quote:

---

Originally posted by Vaughan:

Take a quick read of the FMP manual, or some other good FMP book.

---

Or better yet, take a slow read of the manual & other books.

Being single, I personally believe that you can't have too many relationships.

Dave, some of us studied the relative model for years or decades

In another words – FM is easy, but from time to time you need someone who knows all that relative stuff and can design proper model.

Oh yeah you can...how about more than 1 ex-wife???

quote:

---

Originally posted by danjacoby:

Originally posted by Vaughan:

[qb]Being single, I personally believe that you can't have too many relationships.

Being married, I can assure you you can.

(Steveinvegas-- you just beat me to it!)

quote:

---

Originally posted by Steveinvegas:

Oh yeah you can...how about more than 1 ex-wife???

---

I was speaking in the present tense only.