one DB multi relationship (Thread 2)

Ugo DI LUCA · May 22, 2003

Try to remove Chad_ID. Delete it. In the broker layout.

Then filter with F, and select Fennerton from the value list.

Then go in Kane Building.

Look at the Portal

Chad is here, but you are here also with 4 Fenneton records.

broberts7usa · May 22, 2003

Who better than the one who started the thread?

I'll say it before anyone else does:

WHO CARES

I think those of us who are watching this forum would like to see solutions.

I don't expect you two to solve my problem. But this is getting ridiculous.

Bonnie

Peter Fenner · May 22, 2003

I see what you mean Ugo - I do not know why (yet) - I deleted all my contacts and companies and started again - I can't get it to make the error again. Did I not erroneously leave files behind when creating solution?

Also, suppose there are errors - what are your thoughts on the fundamental approach taken?

Pete

Ugo DI LUCA · May 22, 2003

Bonnie, I'm not sure I'm that qualified for any lesson, nut basically here is what can be defined a Line Item or Join File.

As you said, as a join, this file is created to relate several records each other, when there are many records related to many others.

In your case, one contact may be affililiated to many companies and many companies may be affiliated to many properties.

The Join will create a relationship from the company file to the contact file.

For this, each record would have both Company ID and Contact ID

Comp001 Cont002

Comp002 Cont007

Comp001 Cont001

Comp003 Cont003

Now you can see that Company Comp001 has 2 related records in the Contact file. This could be easily done without a line item if there wasn't these latest records

Comp004 Cont001

Now Cont001 relates to 2 companies.

So we have a Many To Many relationship. Without a line item, it would have been difficult to make it work.

The records from the line item will be displayed in a portal, and the portal set to create related records from the company file will add the contact ID to the Company ID.

If we were to add a contact to Company Comp001 with a portal, entering a new line in a portal set to create related records would do the following :

1. Create a new record in the Join File

2. Populate the Company ID with COmp001

3. Populate the Contact ID with the Contact ID entered in the portal row (let say Cont002.

So now Cont002 relate to Comp005 and (from former example) also to Comp001.

You may not like test or sample files, but it is quicker to see it works though.

Peter Fenner · May 22, 2003

Ugo, can you affiliate a contact with a property? Even if this individual is not affiliated with any company.

Pete

Ugo DI LUCA · May 22, 2003

Pete,

As I said first, your relationship from Property to CompCont is false, because there are no keys for Properties in the CompCont.

You are working at left with a "All" Contact ID, while there should be a relationship based on the Property_ID. How could we do that another way ?

As I said, I'll take the time tomorrow (today at lunch time)....and drop a sample test.

Peter Fenner · May 22, 2003

But I am only relating from Properties to Compcont with fields that have party id's in them.

Pete

broberts7usa · May 22, 2003

Finally someone answers my question. You guys were getting off on Peter's file.

So the answer to my (initial) question ((at the beginning of this thread) is: there needs to be a join file(Parties?:?), which joins contacts and companies, so that they (whoever they are, contacts or companies) can be joined to the properties (the main file)?

Ugo DI LUCA · May 22, 2003

"Can you affiliate a contact with a property? Even if this individual is not affiliated with any company."

OK, you hit the top of the problem.

That is exacly why I would defined the Company File to hold either a Company or an Individual, and why I said in a previous post that some Individual Contacts may have a record in the Company File.

I think we rounded all problems now. Sorry Bonnie for that last sentance if you feel I'm turning around myself

Peter Fenner · May 22, 2003

In my solution I could have got rid of compcont (which effectively duplicates records from both Contacts and Companies). The reason why I didn't is due to the fact the user may want to use the status bar to scroll records and companies need different fields to individuals. I could redo my solution with just 1 party database - thats clear right? The question will aplly to that scenario as it will to this - what is fundamentally wrong with such a simplified structure?

Pete

Ugo DI LUCA · May 22, 2003

Yes, but you are in the Property file. There need to be a relationship on the Property_ID.

broberts7usa · May 22, 2003

At the risk of seeming TOTALLY STUPID, I'll still say this:

PLEASE ACKNOWLEDGE THIS TIME (you guys get on a roll and ignore half of what is written):

The "Parties" file is a "join" file for the "companies" and "contacts" files --- or not?

This may seem like a basic question but SOME of us are still learning and it's possible a more simple solution could arise, other than the scenarios you two are trying to fix.

ciao - Bonnie

Peter Fenner · May 22, 2003

Quote: "Yes, but you are in the Property file. There need to be a relationship on the Property_ID."

No there doesn't - or if I am wrong. Why?

Bonnie, We haven't forgotten about you. If we can resolve some issues it will be easier to help you out.

Pete

Ugo DI LUCA · May 22, 2003

Pete,

It's getting very late...

You won't be able to make it work that way as parties would need to relate eachother.

You really need 2 separated database and a join file to affliate the contacts to the company.

When a contact, isn't an employee from a Company, but acts as a "Company", he should have either a "Company_ID" and a Contact_ID.

You really need a join file to affiliates the "companies" and the selected contacts from that company to a Property_ID.

So

Company (Operators (either individuals and companies)

Contacts

Join CompanyContacts

Property

Join CompanyProperty

Peter Fenner · May 22, 2003

Bonnie, can I hide the status bar in the parties screens? IE: If scrolling party records buttons (such as bottom left of my solution) would be used instead of Filemakers rolodex in status bar. Can I hide the status bar?

Pete

Peter Fenner · May 22, 2003

Yes it is late. But I still cannot agree. The method I am using for affiliation is fine. A relatively simple solution can be found where one just needs one parties database (not even 3 like in my solution) I just cannot see what is wrong with having a self-join in effect.

Pete

Ugo DI LUCA · May 22, 2003

Bonnie,

First post on this thread...

"We have a main database (properties) which has various parties that need to be applied to it in various locations. These parties are either individuals or companies. "

The parties = participants. = Company File IMO

The parties = participants = Company File and Contact File for Peter.

That is also our "disagreement" as it seems to me that the contacts affiliated to a Property should come from the Company file.

If we only need to identify those contacts from Company file that are related to the Property, this should be done by filter in my opinion.

Peter Fenner · May 22, 2003

When creating my solution I allowed for the fact that individuals will also be affiliated with properties. This is the crux of my solution. If it is just companies relating to properties then of course the whole scenario is much more straightforward

Pete

broberts7usa · May 22, 2003

Ugo you are the MAN!

You just answered my question!

How long have these two threads gone on?

I am sure with all the stuff you and Peter have gone through tonite, something will come of this, but what's important is that I learned something (finally, after we all argued about it) -

-phew -

Bonnie

wait - I saw your post - I'm not sure about the filters let me post again OK?

Ugo DI LUCA · May 22, 2003

Pete,

Because some words could have different signification, give me an example of the kinds of individuals that you may like to have linked to the Properties....

Peter Fenner · May 22, 2003

Bonnie, what was your question?

Peter Fenner · May 22, 2003

Bonnie needs to be able to link individuals to properties. Chad is an individual (he is not a company) he may be a broker, developer, etc. That was the crux of her initial request as I see it. This has been the basis of my structure since the start.

Pete

Ugo DI LUCA · May 22, 2003

A broker is an Individual COMPANY ! I AGREE that Chad should be listed here. No Problem, nor disagreement, just love.

But if we had a Company called BROKERS.Inc, we would enter "Brokers.Inc", and then by filter, select the brokers from BROKERS.inc that should be affiliated to the Property.

That's what individuals are for me. Contacts...

Peter Fenner · May 22, 2003

Chad is an individual who may well be affiliated to a company. However Chad does some things on the side - he leases property and he designs buildings (architect). OK, so we could make various companies for Chad (ie: Chad the Architect, and Chad the Leaser) - but Chad also works for a company that Develops buildings.

See my point? Pete

broberts7usa · May 22, 2003

Simmer down, you guys. Peter's eg2 doesn't work for me but I can see that you two are working it out.

And (see above) BRAVO UGO for FINALLY answering my question about having a "join" DB. So it seems I'm dealing with "Parties" as a join and "Contacts" (for people) and "Companies" to feed into that - right? PLEASE ANSWER!!!

and then those relationships will feed into Properties - RIGHT? PLEASE ANSWER!!!

Peter, your question about the status bar: well, I guess we should assume the user is stupid. What do you think we should do? (Hide it? Sounds good.)

I can't believe I'm hanging in here.

Peter Fenner · May 22, 2003

You will only be able to bring companies OR individuals into properties. For each instance you need to specify a party you will need to decide whether to bring-in individuals or companies - you can't have both.

Peter Fenner · May 22, 2003

Bonnie, what doesn't work for you about my solution? i honestly don't mind - but would still like to know which part you don't like.

Pete

Ugo DI LUCA · May 22, 2003

I see, I understand and confirm that the following may be handled with cross relationship.

Chad could be a Company, with record_ID Comp001, category Architect and Broker (checkboxes).

Chad would have a record as Contact002 in the Contact file.

Contact002 may be attached to Companies Comp002 (as a broker), Comp003 (as a waiter) and is formerly attached to its own company Comp001, these records being entered through a Join File CompanyToContact.

Chad would be attached to Property Prop001 using Comp001.

Because Comp002 is related to Prop002, Chad COULD be affiliated to Prop002 (by filter on value list of contacts from Comp002), and not be affiliated to Prop003 even if COMP003 is related to Prop003 (being a waiter, we don't need him to appear as an affiliated contact of COMP003 for affair Property Prop003.

Is it clearer ?

broberts7usa · May 22, 2003

p.s.

Peter is right. Chad is a person. (What a good example he is for this conundrum! even if we have totally lost everyone else through this exercise).

UGO - Chad is a person. He is a rich person because he is constantly brokering deals or developing properties or buying land and building buildings on the land and reating new companies to own the land/buildings ... that is why he is a good example for this ...

Peter Fenner · May 22, 2003

OK if it is Okay to make chad a company then the basis of my structure is unrequired. I agree with you - if Chad is doing "company-type" things he may as well be a company.

Pete

Ugo DI LUCA · May 22, 2003

"You will only be able to bring companies OR individuals into properties. For each instance you need to specify a party you will need to decide whether to bring-in individuals or companies - you can't have both. "

I knew from my rugby experience that you were tough guys !!! !

You'll see.

Peter Fenner · May 22, 2003

See what? I suppose the user will need to specify whether thet want to input a company or an individual and if so then chad may as well be an individual.

Pete

PS: I am not so sure our rugby guys are that tough anymore!

broberts7usa · May 22, 2003

Take pity on me. I see Chad every day. He is an an individual, he is a principal in many companies, you've got the gist. Can't he be both?

There ought to be a way that one person can be affiliated .... never mind, you guys know what I am talking about.

Peter Fenner · May 22, 2003

OK, finally for tonight - I am adamant (or stubborn) that the fundamental basis of my system will be easier for users and designers alike. I should have stuck with version 1 of my solution with some aspects of version 3. I am keen to work on version 4 that combines the best of both - but I am going to wait and see what comes off your solution. Please send me when you have a chance.

Now go to bed!

Pete

Peter Fenner · May 22, 2003

Yes he can be both in Ugo's scenario. He wouldn't need to be both in mine. If you are OK that he is also a company then Ugo's logic will be better.

Pete

Sign In

one DB multi relationship (Thread 2)

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

Important Information