Tutorial for complex relational database structure

[Neville310]

I need some advice on creating a complex contact management solution in FM7. The main objective involves tracking my resume submissions. It serves as a resume, contact, company, and project tracker. My questions revolve around defining the database's relational structure. I have not found and advance tutorial on database relational structures. Let me start with the simple part.

How do I create a relational structure for multiple contact points (phone numbers)? Say you have a contact record with multiple phone numbers, but you do not want 50 fields for potential phone numbers like home, mobile, email, personal email, website, intranet web address, etc. You create a table for the contact data; a table for the phone numbers data. How do you link them together? Do you create an intermediary table? Do you place corresponding key in each table and forget about the intermediary table?

Now, let me make the scenario more complex. Add multiple addresses like west coast office, east coast office, production department, and accounting department. How do you create this relational structure?

This database should track submittal activity or any activity like a phone call. But this activity could relate to a contact, company, or project. How you account for this relational structure?

These questions seem fairly elementary, yet I am beyond the fundamentals. I need a resource for illustrating complex relationships, table occurrences, and attaching them to layouts. Think about the business tracker solution on the Filemaker.com website. Does a tutorial exist for creating a similar database?

[Søren Dyhr]

You would need to make an autoenter key on one of the sides of the relationship that's out of the users sight. The usual way is to use an ID from primary side ...but you're actually free to do it on the other side instead.

Say you have a contact record with multiple phone numbers, but you do not want 50 fields for potential phone numbers like home, mobile, email, personal email, website, intranet web address, etc.

But a lot of these matters could allow an entirely different approach, saving a lot of quite similar fields if done this way:

http://previews.filemakermagazine.com/videos/513/DataTagging_full.mov

When you talk of recording phone calls does it however suggest yet another relation in the many2many dept. I would guess that you have encountered such before in other tools. Perhaps you should upload an ER to let us suggest which way we think best would manage the data structure you have at hand!

--sd

Edited September 3, 2005 by Guest

[Fenton]

Multiple phone numbers can be in their own table, with 3 fields, ContactID, Type of phone, and PhoneNumber. Each phone# is a new record. Emails similar, in their own table, web addresses similar. FileMaker 7 makes it easy for each to have their own tables.

You do not want separate message or activity log tables however, as you may want to see all calls for a Company or Project from that parent table, regardless of which particular Contact was called.

So there would be several IDs in Messages, CompanyID, ContactID and ProjectID. It would depend where the call was made from, and what the business logic was, as to which IDs were populated. The CompanyID would always have a value.

New call records could be scripted, button-driven. This is how I do it, so the portals can be sorted descending by Date & Time (or Timestamp). You really want the most recent calls on top.

The ContactID would have a value if you phoned from a layout of a specific contact (or it could lookup based on the number called, if that was desired; but tedious). But it may not have a value if called from a generic Company or Project layout (unless there's only 1 contact for the project); because you don't know who was called.

On a layout showing the calls for a Company, or Project, a portal could show all calls for the company, with the Contact name on those who were known.

All of the above assumes that a contact only works for 1 company, and that there's only 1 company per project. If not then you'd need some join tables, and an interface method to choose who you're calling, but it would still be doable. Scripted creation of calls gives you some flexibility.

[Neville310]

Thanks for all the great insight. Comments on the actual database structure would help me a great deal (I'll pm you the URL address to d/l the file). I am starting from the beginning rather than converting a solution. This database will serve as a complex contact manager and resume tracker.

I hope to incorporate some of the features from the Filemaker's business tracker sample. Features like tab controls, multiple addresses per contact, multiple phone numbers, list view, detail view, etc. Instead of invoices, it should associate contacts, companies, and projects with tracking resumes, letters, and phone calls.

The complexity begins with the many-to-many relationships existing in this data structure. Every contact may have many addresses, which is a one-to-many relationship. Every address may have many contacts. These same addresses may have many companies and many projects. Now, you have a many to many relationship. Thus this situation may present a need for intermediary linking table.

These scenarios present a challenge when setting up the data structure and implementing new, delete, and find records functionality. Filemaker's native buttons do not always work for these portal records. In addition, Filemaker 7 does not have the native tab controls and require more forethought before creating the interface.

I need comments on my present data structure and how to link the data. The current version is a bare canvas with a rough sketch. Development begins with the contact data and does not include the company and project links. Assistance on setting up layouts for the multiple addresses and phone numbers would help as well.

Edited September 5, 2005 by Guest

[Søren Dyhr]

The complexity begins with the many-to-many relationships existing in this data structure. Every contact may have many addresses, which is a one-to-many relationship. Every address may have many contacts. These same addresses may have many companies and many projects. Now, you have a many to many relationship. Thus this situation may present a need for intermediary linking table.

Well as I see it, do you need to create two additional TOG's that both works in respective the join table as layouts, where the choise is to either pluck one of the previous entered addresses or phonenumbers or add new ones. Then is a more atomic approach required for the fields making the addresses.

Popups are not quite up to it, by being impractical when exceeding 10-15 instances. While a scrolled portal related via a carthesian product relation might be a better choice to get the needed ID.

It gets tricky if an addition actually is required, because the entry needs validation against duplication if the user wrongly assumes that it's a new entry, while not forgetting that typos are likely to make the data pass erroneous.

But getting into one of the new TOG's are straight forward, by making the portalrow a hidden button as well, that picks up the contacts ID as script parameter for the creation of a new record in the respective join table.

Now an actual table for the joinrecords, might not be needed every time you have a many2many structure, filemaker have the ability to link to a multiline key in either of the remaining tables, which gives another posibility namely to enforce uniqueness via fieldlevel validations while attempting to write a new instance.

All this might be correct by the book, but would I live with these impracticalities??? The stucture is many2many, and you're strictly enforcing no redundancy at all, so you at all times can grasp say an address and get all the contacts at that location.

But such matters could instead be optained with a selfjoin relation, with the entry field as key ...which admitted not is by the book, but could be made to work with ranges so you include the typos as well.

When you think you model through, does it become clear that phonenumbers are much, much easier to handle ...or rather a autoenter function is likely to produce uniform data that points out a dupe much more easy than the adresses. While your adresses lacks the feature to be as atomic in content as the phone numbers.

1 NF says one fact per field ...and taking the whole nine yards, might seem silly such as isolating both the streetnumber and Ave/Rd from the rest of the address. Zip and city and state seems more reasonable, but the more you abide to 1NF the easiers it goes!

This is really where you problem exist - by having loosly structured your addresses makes the strict many2many defeat the object in relational theory, and you might be better off with a one2many ...and should the desire to get all the contacts in one location occure ...do it in a loose search, and gather the ID's in a global field and issue a GTRR(SO) from it!

--sd

[Fenton]

The complexity begins with the many-to-many relationships existing in this data structure. Every contact may have many addresses, which is a one-to-many relationship. Every address may have many contacts. These same addresses may have many companies and many projects. Now, you have a many to many relationship. Thus this situation may present a need for intermediary linking table.

I'm going to have to requote this statement. I need more explanation. Especially: "every address may have many contacts." Is this a "company" or "company branch" address? If so then it is not a Contact address per se. It would be part of the hierarchy of a Company, as it goes down through Contacts (perhaps through Branches first). That is, there would be many contacts who work at one particular company branch, which has an address; but that address is not a Contact-Address, as it does not belong exclusively to one particular contact. It would not require a join file between Contacts & Addresses, because you would just go up the hierarchy to Branches.

In such a situation it would be rare for a Contact to have an address of their own; a phone of their own yes. Only Contacts non-affiliated with Companies would have addresses of their own. Do you have those also? Are they part of this process? It seems unlikely to me that unaffiliated "contacts" are involved in a Project.

If you have a mix of "companies" and single individuals, I think I would consider the latter to be a Company with a single Contact (or put their personal info into the Company record as "owner" info). It may seem a little redundant, but it keeps the hierarchy uniform for everyone.

There's more than one way to do this, depending on who you do business with. We need to know more about the actual business rules here.

If a particular Contact can work at several Branches or locations, you've got trouble. I've looked at your file. I believe it has more join tables than it needs, but less TOs for other things than it needs. We have to know definitely whether these relationships are one-to-many (as I think), or many-to-many (as you hint at).

(P.S. Are there Branches? Also, you can post your file directly here. It is small. That way you can get more feedback. As I said, I've made some modifications, but require elucidation.)

[Neville310]

Well as I see it, do you need to create two additional TOG's that both works in respective the join table as layouts, where the choice is to either pluck one of the previous entered addresses or phone numbers or add new ones. Then this scenario may require a more atomic approach for the fields making the addresses.

Before moving forward with the database, I want to gain valuable insight on this exact topic. As you mention, do I need the additional linking table? I do not have the same experience as a developer who creates 100 solutions a year where I may anticipate future challenges with layouts.

It gets tricky if an addition actually is required, because the entry needs validation against duplication if the user wrongly assumes that it's a new entry, while not forgetting that typos are likely to make the data pass erroneous.

In my experience, these scenarios about duplicated data were a significant challenge for me in other working environments. The solution and maintenance tools took me over a year to work out.

But getting into one of the new TOG's are straight forward, by making the portal row a hidden button as well, that picks up the contacts ID as script parameter for the creation of a new record in the respective join table.

I have no idea what you mean here. I am trying to visualize this concept, but do not have any reference points. Unfortunately from this point forward, you have lost me. Multi-lines keys are interesting, yet a new concepts for me. I need to open the debate on organizing my data and classifying it before learning individual tactical approaches.

My relational structure is very similar to my previous solution in another database environment; and has been battle tested. It was time for a complete redesign thereby adding resume tracking and converting to Filemaker. My objective in creating this thread relates to re-evaluating my data structure in the context of Filemaker 7. Hopefully, someone may lead me along the way since it is a complex scenario.

In such a situation it would be rare for a Contact to have an address of their own; a phone of their own yes. Only Contacts non-affiliated with Companies would have addresses of their own. Do you have those also? Are they part of this process? It seems unlikely to me that unaffiliated "contacts" are involved in a Project.

Contacts have individual addresses since my data deals with people who work freelance. They may even work at multiple companies and multiple projects at the same time.

I would consider the latter to be a Company with a single Contact (or put their personal info into the Company record as "owner" info).

This point is very relevant and viable. The insight from this thread will help evaluate the data again and make the hard decision whether to use this approach or split the data across multiple tables. A haste judgment on pursuing this design needs to be avoids at all costs.

If a particular Contact can work at several Branches or locations, you've got trouble.

Yes, in my scenario everybody may work at multiple locations (addresses) for the same project or different company.

There's more than one way to do this, depending on who you do business with. We need to know more about the actual business rules here.

Please message me if you need further specific details on the industry and other scenarios. An industry explanation will lengthen this thread a good deal and confuse casual readers.

Edited September 6, 2005 by Guest

[Søren Dyhr]

Alright I did yesterday talk of new TOG's ...It's about to be an established practice among certain developers to avoid too weeded and nested relations diagrammes. But instead for each layout the user sees to make a new TOG. This time is it layouts for picking the right linking data for the join table records.

For starters let assume that every instance is in the table related many2one ...now this isn't particular workable compared to a portal listing all instances. This can be done via an addition to the graph related via the carthesian product relation.

The portal then just needs a button in the portal row to choose one of the instances, and the linking is done because we turned to this new layout by setting the departure records ID into the script-paramter.

Now the carthesian product portal will soon become impractical if we're talking say thousands of hired hands, so you might have each instance cathegorized in possesed skills, so you by making a tabbed layout lists each of the relevant cathegories.

If make a checkbox field relational key for the tabbed layout, can you put more than one skill to each instance - so they can spread over several tabs. This is a way to get rid of yet another join table.

What if you can't find anything suitable in you table?? Which very well can be deliberately because you wish to assign somebody new to your table. Well we have arrived to a layout which you now attempt to leave half finished ...this should lead you to the hired hands table for creation of a new record. As soon as the record is commited should your user be taken back thru the join table, where the newly created ID is entered - for without hessitation to return to the original layout.

--sd

[Fenton]

But instead for each layout the user sees to make a new TOG.

I have to take issue with this statement. And I know Sø�ren did not make it originally, nor did he mean it to be taken literally (he can tell me if I'm wrong :-). It's more of a trend among developers, after struggling with spaghetti tangled relationship graphs. As a blanket statement however it both a little too vague, and misleading for beginners.

It does NOT mean that your Form view and List views, of the same base table, or their printing counterparts, should be different table occurrence groups (TOG)s. What it means is that your Clients table and your Invoices table should be in different TOGs.

The general realization is that the Relationship Graph is closely tied to the user layouts. And that there is no real reason why it all needs to be connected in one TOG; and many reasons why it should not be. Any portals on a particular layout need to have TOs connected to the layout they are on. But general navigation, via Go To Related Record [] does not. The destination of a Go To Related Record [] can be any layout of the base table of the targetted TO, effecting a kind of "jump" between unconnected TOGs in the graph. The found set of records of the relationship will become the found set of the final layout.

Though FileMaker 7 relationships are generally bi-directional, separate TOGs will require some redundancy if you want to do something like have a portal in one main layout, then also see "back" from the portal's base table; you'll need the same relationship in both TOGs. So, overall you end up with more TOs, but they are much better organized. And only the relevant TOs show up in the Related part of the TOs drop down list.

[Neville310]

Even with all my previous database experience, the points made in this thread are beyond my scope. The discussion is highly theoretical and academic, which may be on track, yet cloud my mind with nomenclature. Illustrations and samples would help me understand various scenarios.

My data scenario is complex. My previous development efforts have proven it. The data relational structure remains a subset of the challenges in my objective. I understand data relational design fairly well. I do not understand creating Filemaker layouts and portals with complex relational structures. Nor do Filemaker reference books help me beyond the fundamentals.

In Ms Access, the sub-form represents a portal. You base the sub-form on a query, which contains the intermediary table and the foreign key table. These queries allow one to create the many-to-many relationships outside the relationship graph (where they exist as well). In Access, these many2many relationships are a great challenge as well. But SQL and queries help you filter the data in many ways. Thus provide you with methods to find, update, and delete records.

One application, Jobtabs, represents my desired goal in features and data organization. The activity tabs easily cross-reference each task with contacts, companies, or recruiters. So Jobtabs let you know what people in your network led you to a job opportunity. Unfortunately, the application has several shortcomings like frequent crashes and the inability to export data (which ties you to one application). Presently, Jobtabs list layout is not ideal for numerous entries. In addition, it does not allow you to filter or search through the data.

These other databases solutions serve as inspiration, yet I need a Filemaker solution for cross platform support. A Filemaker solution will help me move with the times. Even though Filemaker is desirable, the learning curve is not. I have to appropriate development time from my job search efforts; so need a quick start guide in terms of my data structure and some guidance at various junctures along the way. At this point, I need to make some hard decisions about data organization.

I may not understand TO (Table Occurrences) and TOG (Table Occurrences Group). The concept seems simple enough, but my mind has trouble coming from other database packages. I am guessing that TOGs are similar to queries behind forms in other database packages. If I am on track, then the scenario would intimidate me since my previous database had hundreds of queries. The relationship graph in Filemaker would be huge with this many TOG.

Even though I have read about the TO and TOG, an illustration or sample would help me visualize different relationships and layouts. I am not looking for a ready-made solution, yet an intermediary development project with some data.

If you want to view my Access solution, pm me and I'll email you the file. It may help you understand the big picture from my viewpoint.

Link to Jobtabs

http://www.jobtabs.com/

Edited September 8, 2005 by Guest

[Søren Dyhr]

Alright I've made you a template, showing both forms of many2many relations, without any sort of graphical sophistication or any other type than equijoins...

--sd

ProjectMan.zip

[Fenton]

No, a Table Occurrence Group (TOG) is just an interconnected group of Table Occurrences (TO). It has to do with the lines (relationships) between TOs. Structural elements using relationships, such as portals and most relational field access require a line between the TOs involved (but not fields with global storage; they're available from anywhere).

It is true that a solution with complex structure and interacture requires a lot of TOs, and it can be intimidating. That is one reason why the concept of separate TOGs is important. So you can create separate groups of TOs, all starting from a base anchor TO. This base would be one of your main entities: Contacts, Projects, etc.. It would have a layout associated with it, which would be the main layout for that entity.

I believe that your main problem, as I wrote to you privately, is that the businesses you are needing to deal with have various configurations. This is actually not unusual. But a database has problems with such loose hierarchies. It is difficult to for even: Company-->join-->Contacts to handle situations where there could be other entities in between, or not; branches, departments, production companies, etc..

What I was thinking was to create a "hydra headed" structure, with each of the possible configurations. However, in operations, you are mostly dealing with a person, a contact (which would be the single "body" of the animal). So connections go to the contact. Each contact could then have various possible, or even multiple (many-to-many) paths "up" the hierarchy (or not). In cases where there are multiple valid paths, you would have to specify which. This is all doable in 7.

The 2 main situations I see to handle, which may not be the same entity are:

Communications: happens directly with the contact

Billing: happens with whoever's paying

While the above is somewhat unusual (and untested, by me anyway), I think it would work. It would free you from having to have one specific configuration for the organization of the contact's "company." It would however make the relationship graph look a little odd.

[Neville310]

The sample helps me a great deal. Thanks!

I will need a day to review the database and post more questions.

About cathegorization…

It is a table occurrence based on the person table. How would you classify and describe the purpose behind this relationship? Right now, I am just trying to understand the language used in this thread and switch my brain to Filemaker nomenclature.

[Søren Dyhr]

How would you classify and describe the purpose behind this relationship?

The purpose is that some might have several skills, so the global popup filters on the actual values, this a is parallel to a jointable ...it's so that behind the sceens in filemaker is a checkbox field a multiline key which is the omitted join's which would have demanded a record for each skill.

--sd

[Neville310]

The purpose is that some might have several skills, so the global popup filters on the actual values, this a is parallel to a jointable ...it's so that behind the sceens in filemaker is a checkbox field a multiline key which is the omitted join's which would have demanded a record for each skill.

Are you calling the joint table what I call the relational linking table? In my file example, this table is trelContAddr.

Where is the multi-line key? Do you mean the checkbox field which acts like an option group/radio buttons?

I am just discussing terminology here, so I can ask the appropriate questions in a language you understand. The sample file remains a big help.

[Neville310]

I believe that your main problem, as I wrote to you privately, is that the businesses you are needing to deal with have various configurations. This is actually not unusual. But a database has problems with such loose hierarchies. It is difficult to for even: Company-->join-->Contacts to handle situations where there could be other entities in between, or not; branches, departments, production companies, etc..

What I was thinking was to create a "hydra headed" structure, with each of the possible configurations. However, in operations, you are mostly dealing with a person, a contact. So connections go to the contact. Each contact could then have various possible, or even multiple (many-to-many) paths "up" the hierarchy (or not). In cases where there are multiple valid paths, you would have to specify which. This is all doable in 7.

The 2 main situations I see to handle, which may not be the same entity are:

Communications: happens directly with the contact

Billing: happens with whoever's paying

Based on Fenton comments, a hierarchy approach from the contact record seems doable. But his approach will not eliminate the need for many2many relationships.

Here are point of views necessary.

For a Contact, list all companies

For a Contact list all projects

For a Company list all contacts

For a Company list all projects

For a Project list all companies

For a Project list all contacts

For a Contact, Company, or Project list all activities

Now, comes the tricky part. The database serves as a networking tool. I need to keep track of emails, phone calls, and submittals for follow-up. An activity table may address this objective.

Some hard decisions need to be made like how to link the activity table to the Contact, Company, and Project table. Should the activity table have foreign keys from Contact, Company, and Projects table? Or should the Contact, Company, and Project contain the foreign keys for the Activity table?

Addresses and phone numbers remain another challenge. First, I want to separate the data into their respective tables. Phone numbers are no longer tied to addresses or just a company. In my database, phone numbers will include main & fax numbers, multiple emails, websites, and IM addresses. Let me create an overview.

Contacts have personal addresses and temporary office addresses; as well as company addresses.

Companies have addresses for various locations and divisions. Projects have addresses for various locations and do not always reside at a company.

Again, the database should help me to keep a mini-history and perform the necessary follow up for a freelance professional. It does not have billing and invoice objectives at this point, yet it should track resumes and their effectiveness. It should organize people and track opportunities for follow- up.

For example, Jane Doe has given me insight to 20 jobs over the last six months. She will be the first person on my email blast when looking for new opportunities. Some contacts serve as networking points and information providers. Another example, I have submitted 3 resumes to DD over the last year. Let me include this info in the next email to them.

My industry hires people on a per project basis; then releases them until the next project. Networking allows one to stay continually employed. It is a difficult art until you become well established. My goal relates to organizing these efforts in a more scientific manner and minimizing the downtime.

[Søren Dyhr]

Do you mean the checkbox field which acts like an option group/radio buttons?

Yes, more or less - the behaviour of a checkbox field indeed violates 1NF by storing several data in a single field which is iffy ...but if it's exploited as a key, are we just slighty less in a swamp.

--sd

[Fenton]

Example:

1) Company1 is huge. It has 3 branch offices. Each office has several people you want to keep in touch with. In FileMaker this would mean 3 tables, Companies, Branches and Contacts. Or, rather than "Branches," there are some other kind of entity. It is not critically important what the entity is named. Perhaps just have a Type field in the Sub_Company file and tag each as to what it is. But the above is basically 3 levels. If, as you say, some people work for more than one place, then you'll need join files in between also.

2) Another Contact you have is an independent business of their own, really just themselves.

Your structure has to deal with both of these situations. That is why I proposed the "hydra-headed" structure; with Contacts as the body. For most operations, such as communications, you're dealing with a contact (or contacts). No data entry above that level is needed for say a phone call. Viewing the data from above would be possible also.

One problem in this structure would be "where is this company?" Is it just a Company Name entry in Contacts (sole proprietor, no employees)? Is it a "sub-entity" (branch, production company)? Or is it a top-level conglomerate?

I would think that in most cases you would either already know that. Or that a scripted Find or relationship, with entry into a global field, could quickly locate the entry. Once you find it and go there you can look down the hierarchy. If you end up in Contacts, then look down to communications. If it's a "sub-company", then down to Contacts then to communications. Or look up the hierarchy, if possible, to a parent company.

Projects would be a little more complicated. Though once again, you are always dealing with people. Since all people are in the Contacts table (required), then it could work much like communications. Though you're likely to have multiple contacts per project, so at least one join table is needed.

Because, as you say, some people are affiliated with more than one higher level organization, a question like, "Which companies are involved in this project?" is ambiguous, if a contact involved could be associated with several organizations. You need to know which one. You could have a join table, for Project-Contacts, to pre-choose contacts for a project. And view Activities through that filter.

I would try to fit your companies, sub-companies or branches, and contacts into a maximum 3-level hierarchy. If there are a very few exceptions, perhaps a multi-line key could deal with those. Otherwise another level (gak).

I don't know what you want to do with Addresses. Since each level has its own table, perhaps each would only have 1 address. Otherwise an Addresses table could be attached to each. Then multiple addresses are no problem.

On the one hand, the set of TOs handling company configurations will be in more than 1 TOG (Projects being its own). So the less TOs the better.

On the other hand, 1 Address table means you only have to create address fields once.

 

Edited September 10, 2005 by Guest

[Neville310]

Now, I have a better understanding about Filemaker language, yet remember it does not mean that it represents my native tongue. I have been evaluating the suggestions thus far and have not made any decisions. Translating these suggestions based on previous database experience remains the biggest challenge for me.

I have many small stupid questions about comprehension. Do you mean this or that? I wish the chat module worked.

Let's start with the basics. We all agree that the tables will include the following.

Contact

Company

Project

Addresses

Communication Points

Activities

For the most part Contacts remains the body. I don't understand what Fenton means by a Branch table. The Company Join table has a field for Company Type, which designates the company as corporate, distribution, production company, etc. The Company Address Join table contains an Address Type field designating the location as branch, location, etc. This organization structure keeps three levels of tables.

The hard decisions remain linking them; and naming the primary and foreign keys. Table occurrences and TOGs are a new concept for me. Please specify when you refer to a TO, the original table, or a join table. I need an Access -> Filemaker dictionary.

Filemaker developers seem to have a different approach to naming primary and foreign keys. Filemaker Developers name the primary key ContID and the foreign key, recordNumber. What do you call a table that has a primary key and foreign with no join table? Let me know if a naming convention exist. Here's my hack approach.

TO_Cont ->TO_joinContComp -> TO_ContComp

TO_Cont ->TO_joinContAddr -> TO_ContAddr

TO_Cont -> TO_joinContComPt ->TO_ContComPt

TO_Cont ->TO_joinContProj -> TO_ContProj

TO_Cont ->TO_joinContAct -> TO_ContAct

The limitation in relational structure is that addresses and phone numbers are not tied together without an additional TO_joinContAddrComPt. This path presents a challenge in Contact layouts since its several levels deep.

The Activity table poses several considerations as well. Presently, my database has many2many relationships. Activities may be better suit for a one-to-many relationship. In addition, they may include a company, project, etc. I am still working on a better approach for it and feel that the main starting points (Contact, Project, and Company) should be addressed first.

One attachment shows the relational structure in a text documents. It serves as a quick diagram. The second attachment contains the database with an extensive relationship graph. This illustration is the starting point for me; and does not represent the best approach. The activity table has not been thoroughly thought out.

The layouts are mess. I am still fumbling with the graphics and organizing data in a tab interface. Filemaker 8 with tab controls would be handy at this point. Filemaker 7 will do for now.

I need help on the interface for displaying multiple addresses and phone numbers. I would like to create a tab interface for the addresses like Business Tracker; the scrolling portal could be cumbersome. Does this objective seem overly ambitious? Let me know you thoughts.

JobOrg_TableOccurrences.txt

JobOrg_1.02.zip

JobOrg_TableOccurrences.txt

Edited September 12, 2005 by Guest

[Søren Dyhr]

scrolling portal could be cumbersome. Does this objective seem overly ambitious?

Sorry for hessitating with answers, but yes it's something I in my bad english perhaps covered too briefly. You're right a chathesian relationtype is often slowing otherwise good solutions down... Read this thread:

http://www.fmforums.com/forum/showtopic.php?tid/167929/hl/scroll%7CLaRetta/tp/1/

--sd