Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 7846 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

I'm hoping this will be simple ... apologies in advance for my learning curve.

I have 6 DBs which track commercial real estate. Properties (everything you ever wanted to know about the building or land) is the main file and contains layouts with relationships and portals to ALL the others.

The others (again, accessed through portals in the Properties file) are:

- Images (you never see this DB except through the portal)

- Availabilities (this file sees great activity, because the avail space changes all the time)

- LeaseComps (lease deals, when the space is leased)

- SaleComps (sale deals, when the prop is sold)

AND

- Parties (people and companies)

and in the Parties DB, herein lies the rub.

A person can be a listing broker (Avails DB) or a broker for a sale (SaleComps) or a lease (LeaseComps) ... etc. A company can be the owner of a property (Owner layout in Prop DB) or the manager of the prop (ditto) or the architect, or developer, or even the company that the aforementioned broker works for ... and I have the people AND the companies all in the same DB, like a contacts DB. It seemed like a good idea at the time ...

But now it seems to me that perhaps I haven't set up these DBs correctly. I am over my head. Do I need a join file? I have all these portals (yet to be created) in the prop DB for "architect", "owner" etc. and in all the other DBs (where all the other portals are already working well, except for this people/company = parties DB issue).

Please help! I can't wrap my mind around this one.

Posted

Bonnie,

It is a good choice to group all the contacts into a unique db. There's no need for a join file here (in that particular db) in my opinion.

Use checkboxes as Multikey fields and set these checkboxes for relationship inside the contact db itself.

Relationship g_architects ::checkboxes, .... should be OK.

You will need a line item for your sales, but the seller would have an ID, whatever his category.

Posted

Have I posted this in the wrong topic? No replies - and I'm not sure how to go forward -

I should have titled this thread "6 DBs with multiple relationships" ...

I am just looking for some help/discussion about my Parties (read Contacts) DB and how it relates in all the ways it has to be related to the main DB (Properties). Since there's a checkbox value list (in Parties) to note whether these people/companies are brokers, owners, architects, etc. or all of the above, should I be using this feature to script, or create a join file, or calculate, or what? Am I out of my head to think that I could use portals to display all this info in the main DB?

I just need some guidance at this point because all the other DBs are functioning really well and it's time to add the players (people/companies) into the mix so I can get started adding data (more than just the test data), and I don't want to make a false start.

I hope someone will post ...

Thanks - Bonnie

p.s. I posted this at the same time Ugo wrote - bravo Ugo! - so am editing - we are reading each other's minds about the checkboxes, I see. I can also see that I will need some guidance about how to use the global multikey (in the portals?)

Posted

Well, this may not be the case at the moment, but it is likely that one contact may be part of multiple categories, and that is where the Multikey is useful.

As you used a checkbox, you can check this out with a little test.

Place a copy of your checkbox field, just next to the other on the layout.

Change the format for this field as a plain text field, and just wide it scrowling the rectangle down.

Now, populate the checkboxes and see what happens to the field next to it.

Each line would be a key for a realtionship as a carriage return is a way to separate indexed values.

So if one contact is either an architect and a broker (not sure this is a good example), you'd be able to filter your Contact db by using a global field for the left side of a relationship, populated with a value list of all possible categories, and the checkboxes field for the right side.

Posted

ciao Ugo - I am glad I am on the right track - even if I am still overwhelmed by all the different combos and my lack of FM experience -

Also, when you mention a seller ID, that would be a PartyID right? The sale comps (sale deals) have an ID already but that is for the sale record. Can one party (contact) ID show up in so many different portals or do I have to use the checkboxes to send those IDs to globals like gArchitect and gDeveloper and gSellingBroker and gListingBroker - and how to get that info to the global in so many different ways?

: Bonnie

Posted

Hi Guys

I don't want to take you two off track - especially as you are about to help Bonnie solve this outstanding issue (You don't mind if I watch do you). But quickly, Ugo what is a multikey exactly. I may know what they are, but I may not be using the correct terminology. Maybe you can describe a multikey as part of another post pertinent to Bonnies request.

Thanks - Pete

Posted

we are posting at the same time!

Are you saying to create the global fields and put them in the main DB layouts? or the parties layouts? and then watch them while they populate? how does the carriage return get in there?

(If you post at the same time again ... I guess I will wait a little bit!) smile.gif

Posted

and now Pete is here! hi - and thanks, you two! let me think about this a bit, and I can hardly wait to hear what Ugo has to say about multikeys. Currently I only use one, for a find layout I have going (thanks Fitch), so I don't know much about them. Except that a script populates that particular one, with the carriage return scripted in.

Lots to think about - am trying not to type over you guys - B.

Posted

I am assuming the multikey has a valuelist attached to it with items such as: architect, broker, developer, etc. So a particular contact could be various types of contact. I am interested how you are going to resolve a situation when, for example you have to specify the architect for a particular job - do you specify Mr Mies van der Rohe or Rohe Architects Inc. One is a company, one is an individual. What is more relevant in properties, or is both. Is this often going to be an issue when you need to say the relevant company is "x" and the person from that company is "y". If this is often the situation, then 2 databases are looking like a better option. I look forward to hearing Ugo's comments.

I just wonder, since you seem to want to achieve a high level of data accuracy, whether you shouldn't (in various locations of database) be specifying a relevant company and this effects a second value list showing personnel thereof.

Pete

Posted

Pete, this is exactly why I am asking about this issue. Sometimes it's a person, sometimes it's a company (in almost any case, owner, developer, architect, etc.) And in the case of a lease, the company that the broker works for is almost as important as the broker person, because they hold legal responsibility for the sale, but the broker person actually did the deal. A value list in Parties already exists (the aforementioned check box) but I obviously don't know quite how to use it - yet ...

Posted

Since Ugo's gone AWOL I am going to continue in this vein. Surely most parties involve a company and at least one relevant individual. If this was the situation you could very easily implement a 2 database party system (ie: contact persons and companies). They will be tied together by the companyID. In properties database you need to specify the leasing agent, for example, which you select as Leasers-R-Us. This value will effect other options, such as: Managing Agent, Accountant, etc (for example).

I am being repetitive, but Bonnie, what level of "depth" do you want with your party entries?

Pete

Posted

Hi again Pete - wonder where Ugo went - he will dazzle us soon with his presence, or perhaps is sleeping as befits the hour in Paris ...

The depth question is exactly why I posted in the first place. It's very complicated. Here are some scenarios.

1. A lease deal which is co-brokered by 2 different companies and 2 or 3 or 4 different brokers for those companies.

2. A sale in which one party is an individual and the other is a REIT.

3. A property which is owned by a private partnership made up of individuals who already exist (they pull this stuff all the time).

4. A person who acts as a developer, or his newly created company does - but he is also one of the owners (or his newly created partnership is) ...

I could go on. That is only a few. You see my confusion (or maybe this is really easy and you don't - if that's so, please help!)

Posted

This is how I would do it. You have the 2 party databases. You have different links to the companies database. By different I mean, each potential reference to companies is a seperate field (for developer, architect, etc.). This is a simple text field (assuming the match field (the company serial nr) in companies is also a text field.) You define relationships for each and every occurence. You will also create various value lists from the companies database showing SerialNr and Company Name in second field (sorted by second field). There are various value lists as these will be shortened by there relationships taking into account what type of company is specified in the multikey in companies database. Obviously you only want to show architects when choosing a company in the architect on project field.

Now you would create further relationships between these new fields and SerialNr in Contact Persons. This will enable you to create value lists to populate fields that are effectively subsets of the company chosen (as an architectural firm, for example) - subsets being the relevant people.

Once all this is done you could create a further layout in properties with a portal that displays all contacts, across all types of contact type showing their name, company and job specification, and some.

Pete

Posted

I know there is a relatively easy solution here. i am pretty sure the 2-database route (at least) is the best option. I am also pretty sure you are going to need to create lots and lots of relationships. But that doesn't matter - they are pretty straightforward. i am going to quickly work on something in Filemaker which I will attach here.

Give me half an hour.

What was that quote again? "The world of...."

Pete

Posted

Ok, I'm late here but Pete assumptions were good.

Well of course I'm not saying to store both the Contacts (adresses, names) within your Party File.

I have odd situations in my proper files, where a buyer could also be a seller, where an individual is also a salary man from one of my supplier, where an agent can be part of 2/3 companies,....so it is obvious that you'll need to separate the "contacts" from your "Parties".

And if a contact may be attached to separate Parties, you'll surely need a line item (join file).

About the Multikey, I like to define it as such even if it not the good terminology. Well it acts as such because when you involve a checkbox value list, you'll have multiples keys in it, and you may filter your "Parties" using these checkboxes.

In my Operator File for example, which is your Parties File, I have separate tabs for Customers, Suppliers, and Others. Tabbing in the Customers would exclude any other Categories, but those contacts that are both Customers and Suppliers would show up.

Now when I enter the Line Item, the Ids used for seller and buyers are coming from the same db. As I said, I could use ABC Inc as seller_Id in line 1 when I'm buying from him, but ABC Inc would also appear as a Buyer in next line, when I'll sell him some goods.

Posted

Well I really don't know how much this could help you, but I've posted my first attempt Contact db (very grey and ugly) in the Database theory section about a year when I started here.

There are lots of adjustments that have been made from then to the structure, but you'll surely fin d something close to what you are facing in it.

Go and see.

Posted

OK, so now it looks like the Parties DB (currently with only a few test entries in it, thank God) is now becoming 2 DBs. Companies and Contacts. Companies will hold the companies who are parties to a deal (or management agents, architects, etc.), Contacts will hold the people (ditto), and who can be related to several companies. Every record has a serial# -

Ugo, you lost me around the tabbing even though I have seen your Operators file. Part of the issue I think is that what you call a multikey is not what I understand (or have learned) yet. But I think I am getting glimmers of where this is going. It seems very complicated at the moment to think of all those relationships to create before all the portals get going...

How about some structural suggestions, now that I realize I need to create a 7th DB and rename the 6th? I could use some discrete suggestions about where to go from here before I get lost in a fog (that's how my mind works, I guess)

Have I said lately - you guys are the best?

Posted

Ugo, this confused me too: (I have no clue how to use this Quote feature on the page, so I just cut and pasted)

"And if a contact may be attached to separate Parties, you'll surely need a line item (join file)."

Expliquez s'il vous plait?

Posted

Company A

Mr John Smith - Area Manager

Company B

Mr John Smith - Export Manager

I'm not talking about duplicates. But it happens in my case that a contact is linked to 2 different companies, where he has 2 adresses, 2 phones,...

In that case, you need a join file where this contact_id would be attached to 2 companies_ID.

Well, xe're in the odd part of it, let keep it simple at start.

Posted

It definitely sounds like I need a join file (people can be related to many companies) and I'm not sure how to deal with this. Is this another DB altogether? A join DB? I don't get it frown.gif

Posted

I don't even know which Operator File I posted, but I don't remember having posted it somewhere since I've tried in the sample section with the "Check duplicate".

If not, I could send you a brief by mail, even if I'm worried it could get you confused. Just start and we'll be here. There's no secret though. I'll give an eye and see on Monday, what I can do to de-tach it from the rest of my files and makes it work in a sample.

Posted

I will start this weekend. I know better than to play around with this tonight when I am tired and all I was doing was trying to think about what to do. At this point, I already know I must split my PartiesDB into 2. This is a good start. Not sure about a 3rd "join" but perhaps I am being obtuse.

Ugo, you had sent me your Operaters DB via email on a question from another thread (I guess I get around) - you are not losing your mind.

Perhaps instead of reporting back on this thread I will start a new one. We've lost Peter (hope he's sleeping, what are you guys in Europe doing awake at this hour anyway?)

GRAZIE THANK YOU - Bonnie

Posted

You haven't lost me - I was working on an example database. Bonnie, tell me, the contacts are always part of one company. Could you set up companies. IE: Create the company record and add all contacts for that company. Or do you want to add the company when making the entry in properties.

Pete

Posted

Would that life was that simple ... no, it's not that easy, so I am coming to realize that there MUST be a people DB separate from the companies but the myriad of ways in which they would connect still boggles the mind.

Let me give you an example. This is by no means a one-of-a kind example, because it happens all the time: One of the principals in my co. is named Chad. Chad is a person. He has one set of email/phone #s/ address info etc. He also develops land. He also is a broker for my company, and brokers deals. He also owns many properties. Some of them he owns by himself. Some of them he owns in partnership with others (and he populates the world of NH real estate with new partnerships rapidly). He, and they, are all legal entities.

Do you see why I am confused? : Bonnie

Posted

OK, would you be happy with one more field on your layout (per relevant occasion) that effectively asks the user to choose what type of entry. This would be a radio button: "use company" and "use individual" would be the options. This would then effect the value list.

Very Important: could you enter all the contact people and companies relevant to a company in 1 layout via 1 portal? You would specify: type (ie: company or individual); name; designation on this project (roughly something like this). The entries here could effect various other property layouts.

Pete

Posted

As to the first question, I don't care how many fields are on the layout as long as the users don't create duplicates. (There can only be one Chad, even though his fingerprints are all over companies and deals and properties.) It seems to me that there should be a "search people" or "search companies" button, or a scripted value list, or both, beside any field, or any layout, where users are adding people or companies. (to prevent duplication)

And I still don't know how to deal with people like Chad, who has affiliations with multiple companies, multiple deals, and is a multiple type (broker/developer/owner etc.). Perhaps is where that checkbox value list comes in again ... somehow ...

Posted

Hi Bonnie,

I just went back and checked that old ugly file that I had posted....

The second attachment from the thread you quoted.

Print this post and follows the steps to understand how the file works, as it was my first attempt, and not very intuitive.

Open the Operator File, and go to the records for "Ceramdiffusion Sarl" in that file.

Then, click in the Small Contact Tab at right side and select Raimond, Georges.

You'll end to a detailed layout for this contact and you'll have 3 options : Personal, Professional and Attachments.

You will notice when choosing Attachments that Georges is either attached to Operator Op59EL and Op60EL.

Select the Operator Id and you'll notice that he is either President of Ceramdiffusion Sarl and a Sale Agent for Ceramic Design.

...But he still has the same Contact_ID.

Now, let suppose that Georges would buy from me as an individual. As you noticed, each company has an Account_Id. Therefore, Georges would be either a salatyman from Op59 and Op60 and would also be a new Operator with his own account N

Posted

Just a Quick one on MultiKeys... You can have these on both sides of a relationship. For example, if you have a field in your child file which is calculated to return the multiple lines needed for a clairvoyance find, like:

B

Ba

Bak

Bake

Baker

This is a very common usage of a relationship using a multiline key, so, in the parent file, the user could type in "B" and get all the names starting with B, or they could type in "Bak" and get only the names starting with Bak.

Now, for the field on the left (Parent) side of the relationship, you can also have a multiline key, so if you take the name-finding example. You could type in:

McD

Mc D

MacD

Mac D

and you would be guaranteed of getting all the forms of spelling of hamburger makers in the USA. I have attached a sample file which demonstrated this multikey situation for finding names (you've already got it Bonnie).

Now, how this might apply to your situation...

In your parties file, you could create a multi-line key which contains all the roles of an individual (Architect, Developer, Builder - whatever) then in the parent file the multi-line key could contain the calculated field for what you want to find - So, if you entered:

Owner

Architects

you would then see a list of all people who are either owners OR architects for that property.

Posted

Hi Russ,

The URL wouldn't work, I got the following error.

Not Found

The requested URL /threads/files/67640-Russ NameFinder.fp5.zip was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

------------------------------------------------------------------------

Apache/1.3.26 Server at fmforums.com Port 80

Lee

Posted

Bonnies initial request was complex as it meant individuals or companies could be specified as parties to the main properties database. The attached solution (28k) is just one part of a possible solution. An important part not included would be the ability to link individuals to companies within the same database file (shown in a portal). I would like to hear from filemaker experts and enthusiasts any possible shortcomings of such an approach, as well as better alternative concepts.

When opening the database (either file) you will be taken to the "Property Info" screen. There are 3 links at the bottom: to Architect Info, Broker Info, and Parties database. I have added 6 parties (individuals and companies) to play with specifying parties in the Property database. Go to, for example, the Architect Info screen. Choose to "Add" (See button next to Architect Field). For test purposes specify the first letter as "P" (I have made all my test parties start with the letter "P". I have also only added architect and broker type parties. So use these settings to "test" the database) You will also need to specify whether you want to add an individual or a company as well as the party type. The 3 buttons (which are badly labelled) are:

Cancel - which will clear any entries and take you back to original screen.

Add New Entry - if the party you desire is not available in value list you can choose to add a brand new entry.

Use Chosen - input the value as specified in the value list.

Once you have made a selection the "Add" button will change to "Edit". This newly "chosen" party will also show in the portal as displayed in "Property Info". Use the navi buttons (bottom right) to scroll records.

Also go to the parties database and view records with the navi buttons (bottom right). You will see how the correct layout, depending on whether party is company or individual, is displayed. Linked companies are displayed in portal. Link to companies, if desired, from portal.

This is just a simple example, with many rough edges, to show a possible solution.

I look forward to hearing from FM Forums members of any of their thoughts.

Thanks - Pete

Posted

Me too.

This thread started out last Friday night with my optimistic "this is probably simple, but" - hoping I could decide more about the relationships/ER diagram, and take things from there. But it obviously isn't a simple problem, and as always in FM things can be accomplished many different ways. Pete's files and Ugo's files and Russ' NameFinder and all the discussion of multi-keys make for a lot for me to learn and absorb all at once, and they all contain wonderful potential solutions. (Not to mention the generosity of these guys - wow. I can't decide if I've been blessed or cursed with this particular DB to develop as my very first one, but I've definitely been blessed with the Forum!)

Er - as I was saying - if anyone can assist in looking at all these files and helping me get started on an outline of the solution - I eagerly await ... meantime I am looking at files and re-reading posts and trying to decide the easiest route out of the morass!

Pete! thanks once again! the man is tireless.

This topic is 7846 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.