Grandparents Parents Children Aunts & Uncles etc

[ChrisH]

Steep learning curve from 0 to 7 for a new kid in the class.

Using the standard Contact Management template that I have expanded to 10 layouts for various types of information relating to various types of contacts. One layout I have developed for network marketing contacts and I am trying to find a 'simple' (as scripts etc make little sense to me at this stage) way of deloping a report or list that shows a hierarchy using a combination of ID's such as the Networkers Personal ID # and Sponsors Personal ID #.

Presently I can do a Find on the Sponsors ID and this will give me a list of all of the first level of networkers showing their Networker ID linked to the sponsor which is fine, Now to also show subsequent levels where the relationship is links to the Networker ID which has become a Sponsors ID with the obvious groups of networkers spreading out like a tree roots below is what I am trying to acheive and I really do not know how to begin. I have searched about through the forum but so far no joy, the book that I have by Jonathan Stars does not help and I have the loan of a training CD which is more about bakeries and schools. Some things get close to what I am wanting but not in a language that is clear, I have tried setting up a Report with grouped data using the Sponsors ID as the filed of choice as I felt this was in the right direction.

So if anyone knows what I am on about then I would really appreciate any guidence.

[stash]

If you have made in 'DefineDatabase-Relationships' separate occasions for each relation between tables via foreign keys' then you can use 'Go to Related Recrods' and specify:

1.) Get related record from: (put certain occasion from 'DefineDatabase-Relationships').

2.) Show record using layout: (can enter manualy the name of layout)

3.) Check box - Show only realted records.

I did try similar problem to solve with Find options but in navigation between Parents-Children and the rest of family I'd like rather 'Go to Related Recrods' because it's more 'natural' FileMaker provided option for this problem.

Good luck!

[ChrisH]

If you have made in 'DefineDatabase-Relationships' separate occasions for each relation between tables via foreign keys' then you can use 'Go to Related Recrods' and specify:

Hi Stash, I have created separate databases for my main complete contacts, another db for the networkers db and for another secific group I will create a separate db. The db's are defined and linked via the Networker ID# as I can create a Networker ID# in the main complete contacts db and then by creating a new record and inserting the Networker ID# into the correct field in the Networker Contacts db all of the details for the contact are then in place. I am trying to create a script for doing this automatically but no luck so far.

Okay I am unsure what is meant by "separate occassions" and "via foreign keys". Possibly the Networker ID# is the foreign key but the seaparate occassions is alien to me at present.

[stash]

Uf, to be honest I avoid to make one application on several FM files(databases) because FM7 provides option to create as many tables as you need in one file(what wasnt in FM6).

"Separate occasions" - If you open DefineDatabase Relationships then click on squere with green plus sign above, you'll get possibility to create a "virtual" table whose name can be entered in field 'Name of Table Occurrence'...therefore I should say "Separate occurrences" instead "occasions"...sorry if I said something you already know.

So, I suggested to create as many "virtual" tables(occurrences) as many relations between tables you have and then use them in 'Go to Related Records'. I missunderstood you were talking about relations between databases, not tables. Indeed, I have never made some application in FileMaker7 supported by several databases...might be good idea to organize your databases as tables in one file(database) and then make relations between them...should be easier.

[Ender]

Hi Chris,

With the structure you've described, it will be tricky to show the related contacts within a report. Portals may work, but they do not slide very well on multi-page reports.

A better structure may be to have all contacts within one table. To link them, you could use:

A. Multi-keys of Sponsor IDs and Networking IDs with a self-join to the Contact ID. Or

B. A Relationship table that acts as a join table between the Contact and it's related Contacts.

The nice thing about these structures is a Contact can be a primary contact, a networking contact, and/or a sponsor.

The report for option A would be built all within the Contact table (I have an example or this called 'Simple Employee Tree' in the Sample files forum.) Unfortunately, a Contact could only be listed once with this structure. So if you have Contacts that should be in more than one branch of the tree, this would not work.

With option B, the report would be run out of the Relationship table, using sub-summary parts. I haven't worked out all the details, but I think this would work.

[ChrisH]

Hi Stash & Ender,

I look at what you have written and think huh huh, maybe what I am wanting to do is above my level at least in terminology so rest assurred my novice level is real on all levels but maybe the results of this work will also be useful to others. I am trying to develop this thing for a business that I am hoping to launch in the near future. I have looked at the EmployeeTree.fp7 and if this is not the same as the Simple Employee Tree I shall look further.

My setup is

DB 1: a table - Full Contact Management, table - Networker Details, table - Sponsor Networker Details, table - Business Details, table - Personal Details, table Executive Business Programme, table - Charity Gift Club, table Employee Details, table - ID Nimbers, table - SponsorNetworker Resources.

DB2: a table - Networker Contact Management (which is a duplicate of the Full Contact Management with all the same subsequent tables but is designed only to hold specific Networker Contacts).

DB3 : a table - Personnel Records. This is linked to the previous db's by the EmployeeID# only.

Presently only the Full Contact Management db, Networker Details table, Networker Contact Management db and Personnel Records db are linked via Networker ID# or Employee ID#. The complete range of fields are in the Full Contact Management db and the Networker Contact Management db with duplicate specific fields in the other tables/layouts/forms as is appropriate. So essentially there is the master table (Parent) with all info available and then the subsequent tables (Children) which have their fields linked showing appropriate data for the specific table/layout.

So based on the info that you have given me I possibly have the elements available to work with but not the knowledge of how to pull it all together. My thought is that I possibly have 10 tables which have the same info on them as in Networker ID# and SponsorNetworker ID#, this ID# is actually the same ID# so when the networker begins to sponsor those that are directly sponsored share the same number but beyond that the down line expands using the next sponsoring networkers ID#. So if it is suggested that I create a table for each SponsorNetworkerID#, then in my simple language I can see the logic but it would potentially mean about 50,000 SponsorNetworkerID tables or more as I shall be based in India. I know that my Full Contact Management db will possibly hold in excess of 250,000 records within about 5 years so another solution may be required before then.

In some ways the Similars Tables look an interesting option but I cannot find how to create new customised ones. Clutching at straws. Simple solutions for a brain dead dreamer.

[Ender]

Hi Chris,

I don't know how your tables are all related, but I'll try to offer how I would do this. If most of the information about a Sponsor is the same kind of thing that you track about a Contact, and most of the information about a Networker is the same kind of thing that you track about a Contact, then these should all be combined into one Contact table. The trick then is figuring out how to relate these Contacts that reside in the same table.

My suggestions before were the two techniques that I like for this kind of thing. The basic idea of option A is to have two key fields in each record. The primary key acts as the match field, and a multi-key acts as a parent key. A second TO (table occurence) of the Employee table is then used to make the self-join relationship between the supervisors and supervisees.

It may be easier to work with and understand the second option. It still uses only one Contact table, but then uses a second table to remember the relationship between any two Contacts. Lets look at an example:

Suppose we have a Contact table, with fields ID and Name, and a Relationship table with fields ID From, ID To, and Relationship. In this case we will also need a second TO of the Contact table so that we can see who the Contact is that we're linking to. The three TOs are then linked like this:

Contact <=> Relationship:

Contact::ID = Relationship::ID From

Relationship <=> Contact 2:

Relationship::ID To = Contact 2::ID

We can use a portal of Relationship in a layout based on the Contact table, and show the ID To and Relationship fields from Relationship, plus the Name field from Contact 2.

If we were to have the 'Allow creation of related records' option on for this portal, we could enter the Relationship directly into the portal by typing the ID of an existing Contact that we wanted to specify a Relationship to for this Contact. This is a one-way relationship, meaning that simply creating a Relationship record under one Contact, will not create the opposite relationship to the Contact you are linking to.

In the enclosed demo, I used a script to create the Relationship record and the inverse Relationship. You can ignore this if the inverse relationship is not needed. The structure that I'm talking about for option B is illustrated in the 'Individual <=> Relationship <=> Individual 2' tables in the top of the table occurence graph. The other TOs are used for showing the Individual that's selected in the data entry field and creating the inverse relationship record.

As I said before, I'm not clear on how your existing system is structured (maybe a screenshot of your table occurence graph would help.) But hopefully understanding these Contact structures will help you consolidate tables. By consolidating your Contact tables, you should be able to get the tree report you're looking for, and it can make file maintenance easier.

RelationshipTree.fp7.zip

[ChrisH]

Hi Ender,

I will work my way through what has been suggested, for me it is a matter trying to find examples with all these terms for me to absorb, trouble is things like portals don't seem to work or at least about 6 months ago they did and containers for holding pdf's etc but now when I try to do them zipp zero. So I am finding previously I was understanding and now I am starting again from scratch, it does not pay to turn your back on these things for to long.

I have attached my Full Contact Management, it works very well in many ways and I have tried to set it up based on ACT which I used to use extensively but had to drop once I shifted to Macs, such a shame, best contact management system going. Well anyway a few on my scripts do not work and others do when the appropriate other db's are near by but this will show you the set up and may help in understanding better my direction and problems.

FullContactManagementcopy.fp7.zip

[Ender]

Let's take a step back from the implementation of the tree part and iron out the overall structure. There seems to be unnecessary duplication of data and some key fields that I can't quite figure out. So here's some questions that may clarify things:

1. For each Employee (Personnel Record), should there be one Contact or many?

2. For each main Contact, should there be one Employee or many?

3. For each main Contact, should there be one Networker or many?

4. For each Networker, should there be one main Contact or many?

5. For each main Contact, should there be one SponsorNetwork or many?

6. For each SponsorNetwork, should there be one Contact or many?

7. Could Sponsors and Networkers be considered Contacts as well?

8. Are some of your Contacts from the same Company?

9. Do you wish to track multiple addresses and phone numbers for your Contacts?

[ChrisH]

Hi Ender,

The purpose of this db is to have a one comprehensive db of all contacts whether they are business, personal, employees or networkers as the case may be, this is the Full Contact Management (FCM) a one stop access point. Some areas such as Employees Details will have that layout password secured as will parts of Networkers Details be blocked from view by password. There is a separate db for Employees Details that is to be used for the future Personnel Department. There is a separate db for Networkers Details that is to be used for the future Networkers Services Department.

Now the point of all this is that being in India people will be pushing to know me and the business this is a fact of life, so any contact firstly is just a contact, they may provide me some personal information and or business and as we get to know them more information is transferred, they could remain just personal contacts or business contacts. It also becomes interesting because they may wish to become Networkers and so while they are personal contacts, that are involved in a business but also become part of my business as a Networker. The plot thickens, some of these contacts may become Employees as well as being Networkers, or becomes Networkers while continuing their business operations as well.

So let us see and please forgive if my understanding of your meanings are off.

1. An employee will have one record that will show employement details, personal details and possibly networkers details and if they work for me part time or casual they may have business deatils as well. This is still one record.

2. Each main FCM there is one record with many layouts for specific details of each Employee. So one to one I think. But on 2 db's, FCM plus Personnel Management.

3. Each main FCM there is one record with many layouts for specific details of each Networker. So one to one I think. But on 2 bd's. but also on Networker Contact Management db.

4. Each Networker one Contact FMC but also on Networker Contact Management DB and maybe Personnel Management db.

5. Each Sponsor also being a Networker has one record possibly on all three db's.

6. Each Sponsornetworker essentially has only one record, shared over the 3 db's.

7. Everyone is a contact, they could all be one in the same. Ravi has a partime business, is becomes a networker and joins the business partime and also becomes a SponsorNetworker, someone that has signed up nertworkers to join his downline etc.

8. Some contacts are from the same company and many other things as well.

9. If it is possible to track mulitple addresses and telephone numbers and alias's, family members (as family involvement in India is important for social occassions and good relationship building then all this would be very helpful.

This all sounds complicated and it is I suppose, but that seems to be the way it unfolds in the type of business that I am setting up. Confused join the club.

[Ender]

Usually when you have a one-to-one relationship, the tables should be combined. I definitely think your Contact, Sponsor, and Networker tables should be combined. You might simply use a checkbox field to indicate those Contacts that are Sponsors or Networkers.

It sounds like you can benefit from a few more tables to track your companies, addresses, and phone numbers. By putting these in separate tables, you'll be able to reuse data, simplifying data entry.

I would recommend you download and study the Business Tracker solution on the FileMaker web site. It shows how a typical company-contact solution works with mulitple locations. I use a similar structure in my contact database. You can view the attached section of my table occurrence graph. Notice how few fields are needed with this relational approach.

contact.pdf

[ChrisH]

The reason or my logic for having separate layouts/tables for Sponsors and Networkers is that the DownlineGroups in each layout/table were to show once the script was worked out the downline to one level, a broad horizontal line for the Networker and it was hoped that for the Sponsor table possibly showing the Sponsored Networker and that the Networkers group below each. So that one could see those networkers that had sponsored or not sponsored and the groups that they belonged to. I kind of figured that this sort of thing would be possible and so the layout was designed to display it either inputted manually intially or automatically updated once a script was worked out. I am not sure of the benefits of having a checkbox field other than just as an indication of a networkers initial activity. There is another layout/table being Executive Business Programme which relates to intensive networker training and so from this programme the Sponsor Netwok table becomes important to monitor the growth of the networkers business for assessing the results of the training and commissions structures for their business activities. I am aware that there are specific network marketing type programmes available and I suppose that I am trying to design my own version but from those that I have seen they are just spread sheet based and pretty uninspiring from a design perspective, I also want it all to be within FM as I have a major redesign plan for the future if I can find the right person to do it or FM do it themselves.

Once I have the business set up in India I shall have to find a pretty switched on FileMaker IT person to keep everything working but that is my plan and for now just to get the basics running to see what is possible initially.

I have downloaded your table of occurrence graph and will add a couple of extra tables as suggested and am in the process of a 5 hour download of the Business Tracker, it seems from the promo to have many of the elements that I am using as additional db's which operate from another Office Management administration db that I have set up, this links into all the other db's. It is set up like a web site with 156 layouts/pages which has info on it and links to external db's, documents and resources that will be required to run the business and give everyone easy access to policies and procedures and is set out like an e-manual for business operation. The Business Tracker looks to have many of the external free 'mini' db's brought together as one so it will be interesting to see the diffrence and functionality. I have attached a screen shot of the Office Management db.

OfficeManagementDB.pdf

[ChrisH]

I kind of look at the Sponsor Networker thing like a product package group that contains a number of items and at one level the networker is just a list of various product names. The Sponsor Network may have the same or similar product package groups by product name but would also show the subsequent packages that go to make up the product package group, these are peoples names rather than product components. I think that this makes sense and may be a road to follow if anyone understands my simplistic way of looking at things.

And I messed up on the download and got the FM Pro 30 day trial, that will mean mine imploads in 30 days so now downloading the Business Tracker much faster this time.

[Ender]

Sorry Chris, now you've got me pretty confused.

A Product-Product Package thing would be a one-to-many relationship. You indicated in your answers to my earlier questions that the relationships between Contact, Sponsor, and Networker are all one-to-one relationships.

If you're trying to do groups for your Contacts, then this would be a one-to-many (each group has many Contacts.)

[ChrisH]

Well yes sorry, a novice talking is like grasping at straws for a language that one does not quite understand.

So there is a Contact - Everyone has the contact status

1. The Contact Status can/will have Business and Personal attributes or content.

2. The Contact may remain Business or Personal

3. The Contact may become Business - Personal - Networker - Employee

4. The Networker may become a Sponsor and Networker, be an Employee, be in business as of course a networker has their own business operation and also still has Personal content.

1. The Package Status is listed as an item Package ID# with attributes (colour)

2. The Package may remain red or green

3. The Package may be customised by the purchaser to include an additional attributes

4. The package may be included in an additional order which is made up of additional packages.

Yes this does look and sound like one to many, this is what I saw but my words were not adequate to convey it clearly. A Sponsor is a Networker in the first instance with one ID# and this relationship becomes one to many, one Networker Sponsors other Networkers, one ID# links to many ID#'s which link to many more ID#'s like a spreading virus. Each are individual Contacts with all of the colours and attributes as one might have.

As clear as mud, eh!

[ChrisH]

After creating all manner of extra tables for address and phone variables I am really no further forward and go back to Stash's suggestion of using Related Records to search but I am still unsure of how to formulate such a search as creating a new field to suit the requirements. I have noted that in the Relationships section there are 3 Similars tables but in the actual list of tables these do not appear and under the table for full contacts management there are a number of fields for Similars but but I have tried to create one for searching the SponsorNetwoker and it does not seem to happen.

Can anyone walk me through this one please?