What have I done?

[AlanStone]

I am trying to set up a dynamic value list and played around with some of the relationships to get there but I think I messed up somewhere!

I have 3 relevant tables, Companies, People and Job Orders. I am trying to set up the Job Orders table in this way: There is a drop down of Companies that I can select when setting up a new record in the Table. Once I select the Company I would like to set up Contact 1 and Contact 2 (they need to be separate people) from a filtered value list that only shows those people who work at that Company. Once I select the Contact, I would like that person's email and work phone number to autofill.

I am attaching a copy of the File, and as you can see, I think I have screwed this up pretty well! I have tried lookups and pulling the info from the People file all to no avail.

Is there an easier way to fix this mess?

Thanks,

Alan

BPI_Copy.fp7.zip

[aldipalo]

Hi Alan:

Welcome to the Forums. It would be helpful if you add a few bogus contacts and companies etc. so we can see what lists you are talking about.

Looking at your relationship graph I have a few comments:

I would suggest you read about the Anchor-Buoy method by Kevin Frank.

http://www.kevinfrank.com/demo-files-78.html

As your application grows your current graph will look like a spider web and will not only confuse anyone who tries to help you, but, in the end you will lose track of what you have done. (I learned that lesson the hard way.)

Your primary relationship should be Company -> People which is a one to many relationship. The tables should be related by companyID not Company Name. If the company is sold or merged you will have a mess on your hands trying to change all the related records. By using the ID as the Key Field, you can easily change the Name and all relationships will remain in tact.

Same with Job Orders. Company -> Job Orders. Again a one to many relationship.

And again they should be related by CompanyID.

You are using too many conditions in your relationships. It would be helpful to know exactly what you want to do with those relationships to better guide you.

HTH

Btw, I've been a Recruiter for 40+ years.

[AlanStone]

HI Al,

Thanks for the info! I will review the article and try to reconfigure my relationships and see what happens.

FYI, I've been recruiting for about 10 years and have tried lots of ready made solutions none of which do what I want so I have been trying to build my own on and off for the past few months (whenever I have time!).

Alan

[aldipalo]

You're right. I have never found one that does what I want. That's because they're usually built by someone who doesn't understand our business. If you need help send me a private message. Also, you're in the right place. The folks on this Forum are awesome. They are always willing to help no matter how dumb your question.

Al

[AlanStone]

HI Al,

Thanks. I agree the people here really know their stuff and are very helpful! I posted a problem before and had a lot of help in fixing the issue. I have learned a ton just by reviewing other posts (that's what probably helped put me in this mess too!) and trying out the ideas.

Alan

[AlanStone]

Okay, I have done some more research and am still stuck. I have added some bogus records into the tables so that you all can see what's happening.

Basically, every time I try to add a contact to a new Job Order I either get a blank drop down (for Contact 1, b/c I tried to make a related field that is not relating?) or I get an entire list of People (for Contact 2, which is a drop down of the People table) but then Contact 1 is populated with the same name as Contact 2, but in neither event do the phone and emails autofill.

Any help would be greatly appreciated.

Alan

BPI_Copy.fp7.zip

[Fenton]

OK, this is more how I'd do it. A serious relational database uses auto-enter IDs instead of names. If you have them (which you do), use them. Otherwise there is not much point in having them.

Since FileMaker 8, which you have, there has been an option to "[x] Show only values from 2nd field" in a Value List. This makes it look like you're using the name, but you're really using the ID; best of both worlds. It has one (serious) gotcha, which is that you can never have 2 names exactly the same. So you should validate for that in People.

Relationships (simple ones, and that's what we're doing) are built from parent to child. There is usually on 1 criteria, the ID. Usually [x] Allow creation is only 1 way, from parent to child (there are tricks to do otherwise, but that's more advanced, for special circumstances only). Take a look.

BPI_fej.fp7.zip

Edited April 16, 2008 by Guest

[AlanStone]

Hi Fenton,

Many thanks! I will review it and try to learn from it!

Alan

[aldipalo]

Here's your file back with changes. I set up your People, Job Order and Planner tables with fk_CompanyID which now relates all the tables to company. You do not need to set the fields, in the options area, to value lists. You can assign them at the layout level.

I set the fk_CompanyID field on the Job Order and People layouts with a drop down value list. When you create a new record in Job Order, People or Planner first enter the fk_CompanyID. All of the related dsata will then be entered.

Additionally, I set a people portal on your Job order layout. So, you can either do a quick entry of a new contact there or if you enter the contact on the People layout it will automatically show in the Job Order layout.

This is nor 'Perfect' it's just to show how you can set this up. In our Practice we tend to work from the contact form not the job Order, but, everyone works differently.

Let me know if you have any questions.

Al

I did this kind of quickly, so forgive any bugs. I hope you get the general gist.

BPI_Copy_revised.zip

Edited April 17, 2008 by Guest

[AlanStone]

HI Al,

Thank you very much! Between your revisions and Fenton's it looks as if I now have a much better database!!

I'm going to look it over carefully and try to understand what you did.

Thanks again,

Alan

[AlanStone]

HI Fenton,

I studied your version of what I was doing and understand some of it. I spent most of the day yesterday working on this and still have not figured out a few things! I tried to emulate what you did, by redoing all of the relationships and fields and calcs, but it does not seem to be working on my version.

1) It seems that a drop down list doesn't populate the company name on the co_People table but a pop-up does. The drop down only give the companyID. Why?

2) In co-JobOrders, the Contact 1 and 2 fields are giving me a "no values defined" entry. Did I miss a relationship or lookup somewhere?

3) The portal located in the Events tab in co_People and Co_Companies is not populating like it does in your version. Again did I screw something up there as well?

4) Also, on the co_People and co_JobOrders tables you left the Company Name and People Contact 1 and 2 fields (from my original version?) on the tables. Was that for illustrative purposes or do I need them to remain there?

I am attaching my revised version for you to look at.

Thanks again for your help. I really appreciated it!

Alan

BPI_Copy1.fp7.zip

[Fenton]

The problem is that you're still trying to use the Company Name as the key, instead of the Company ID. That's not going to work, and it screws up the relationship for the filtered value list.

It is a bit confusing on the layout, because of the value list option to "Show only values from the 2nd field." It is the Company ID field, using a "CompanyIDs" value list, but it looks like the Company Name field, especially if you use a pop-up menu. If you use a drop-down list, the company names still drop down, but after making the choice you will see the Company ID in the field. Which kind of spoils the effect. There are ways to cover it, but that's a little more, and I was trying to give you the simplest method. We clamored for this feature for years, and FileMaker added it in 8.

Once you get thousands of entries value lists start to be too slow and clunky. One needs to create a dedicated "choice" window (kind of like Spotlight). But that's another level.

An observation and question. It seems to me that your Events portal on the People form view should be looking at that person's Planner events, not at his Company's events, as it is now. You would need to add a table occurrence from People directly to Planner, based on People ID.

Because a person can be either a Contact or a Candidate in Planner, Who is looking in the portal? Either? Because right now you've got the Contact in the portal, but not the Candidate. Should both be in the portal? Or should something fancy happen (a calculation in Planner), so that they don't see themselves repeated in the portal?

A corollary question, Can a person be both a Contact AND a Candidate? Obviously not on the same event, but overall; is a person either one or the other, or can they be both? That would make it tricky to have 1 portal show both types of events for them, probably best to have 2 portals.

Edited April 18, 2008 by Guest

[AlanStone]

"The problem is that you're still trying to use the Company Name as the key, instead of the Company ID. That's not going to work, and it screws up the relationship for the filtered value list."

I will try to fix that!

"It is a bit confusing on the layout, because of the value list option to "Show only values from the 2nd field." It is the Company ID field, using a "CompanyIDs" value list, but it looks like the Company Name field, especially if you use a pop-up menu. If you use a drop-down list, the company names still drop down, but after making the choice you will see the Company ID in the field. Which kind of spoils the effect. There are ways to cover it, but that's a little more, and I was trying to give you the simplest method. We clamored for this feature for years, and FileMaker added it in 8.

"Once you get thousands of entries value lists start to be too slow and clunky. One needs to create a dedicated "choice" window (kind of like Spotlight). But that's another level."

I thought that might be the case, but I want to get it up and running and then tweak it later as the db grows, and I hopefully become better versed in Filemaker.

"An observation and question. It seems to me that your Events portal on the People form view should be looking at that person's Planner events, not at his Company's events, as it is now. You would need to add a table occurrence from People directly to Planner, based on People ID."

You are right, the person's Planner events should show up in the portal, not the company events. I can probably fix that by adding the TO.

"Because a person can be either a Contact or a Candidate in Planner, Who is looking in the portal? Either? Because right now you've got the Contact in the portal, but not the Candidate. Should both be in the portal? Or should something fancy happen (a calculation in Planner), so that they don't see themselves repeated in the portal?

"A corollary question, Can a person be both a Contact AND a Candidate? Obviously not on the same event, but overall; is a person either one or the other, or can they be both? That would make it tricky to have 1 portal show both types of events for them, probably best to have 2 portals."

To answer both paragraphs, a person can be a candidate and contact and I thought of adding a 3rd value of both and adding that to the mix, but then thought that as long as the info was being pulled from the same list, maybe it might be better to filter the contact list based upon where they worked. Most of the time, the candidates and contacts are just that. I tried to set up the portals in both Company and People to reflect relevant info without being redundant, i.e. not needing Company Name in Company Table, etc. I'm not sure if I succeeded though!

Let me try to revisit it and I'll get back to you if I can't make it work!

Thanks again,

Alan

[aldipalo]

Well, you actually have three types of contacts, don't you?

Source (Contact)

Client

Candidate

You would want to distinguish between them. This can be done by having the background or the Name change colors depending on the type of Contact. You can also have your planner sort either by Type of event or type of contact. I prefer event, but, it's an individual decision.

Since my planner (Activity List) is what I ultimately rely on to steer my day it is an important part of the application.

Take a look at Data4Life's Activity setup. Actually, there entire template can help you go in the right direction. I wouldn't suggest you try to implement theirs though since I think you may get lost. But, it will help you with your set up and design.

http://www.data4life.net/

HTH

Al

[AlanStone]

HI Al,

You are actually correct, but I focus on the Company, Contact and Candidate dynamic. I too need my planner to work right otherwise I lose track of who is interviewing where, who is talking to whom, who is getting an offer, etc. It needs to work correctly otherwise it's a big mess!

I will download that template and take a look see.

Alan

[Fenton]

I think I'd do separate portals for a person's "I'm the contact" Planner records and their "I'm the candidate" Planner records. Less confusing. Whether or not they should be separate tabs is another question.

Another question, similar to "contact vs. candidate" one. The "company" in the Planner table? That is the company of the Contact?

BPI_fej2.fp7.zip

Edited April 18, 2008 by Guest

[AlanStone]

HI Fenton,

Yes the company in the Planner table is the Contact's Company. I will try to make separate portals and need to figure out the layout.

On another note, following your advice, I changed the Company Name to Company ID on the co_JobOrders table and the right Contacts populated their fields, so thanks for that. The new issue is getting their work email and phones to autofill and it's not happening!

Alan

[Fenton]

I used your later file for the my last upload, so you could use it. Though it's always good if you can recreate something; then you know you understand it, somewhat anyway.

One thing about emails and phones. They belong ONLY in their parent table, and can be read from there. There is no reason to ever copy them to child tables. They are not "historic" data, they should always be current.

[Fenton]

Other data, such as names, well, it's something to think about. Bringing their date into child tables, rather than just reading them from the parent table creates redundancy. Whether it's needed (usually not) or acceptable is always a question.

Personally I prefer to not bring them in unless required (maybe on invoices, etc.). I've worked quite a bit on a "recruitment" database solution, and, IMHO it is not a good idea to copy the names to the child tables. If there was a misspelling (and there will be), the fix will not magically move down the line. The speed loss of using related fields is not bad with later versions of FileMaker.

I left your name fields in the child tables, because that's the way you had them. But I'd point them to the parent table on any layout that had them, then delete the fields from the child tables. Keep it lean and mean :-]

[AlanStone]

Hi Fenton,

I changed where the data comes from the other TO's to the co_People TO (I guess that is the Parent?) and it sort of works, but not completely. For one company, the same email address autofills no matter whose name is in the Contact 1 or 2 fields.

How would one delete the fields from the child tables, or is that so obvious that I am missing something right in front of me?

Alan

[Fenton]

Oops. There are both based on the Person1 relationship; make the other one the Person2 relationship.

[Fenton]

Oops 2. They are based on Contact_1 and Contact_2, when they should be ContactID_1, etc..

I deleted the Contact and Company names fields from the child tables. You won't see the difference on the layouts. I had to add another TO to Company, at the far right, to see the company name from inside the People::Planner_Candidate portal.

I suppose this could have been done via a calculation field in People. But I will usually add relationships where needed, rather than rely on "tunneling" calculations. Yeah, it's one heck of a long name. I tend to put the whole path into TO names, though I will often abbrev. names. I have two general rules of thumb:

1. I must be able to tell what the path is by reading the name.

2. The longer the name, the further it is down the relational line, hence the less important (in a general sense).

So I don't really mind the long names. I seldom need to know exactly what those TOs are anyway; and if I do, it's all there.

One more thing :-] Your IDs fields were generally Number fields. But they had text prefixes. So, looking at the data, I assumed they were Text fields. If you use Number fields in calculations, you will lose the prefix. So, to avoid later confusion, I changed them all to Text fields.

BPI_fej2.fp7.zip

Edited April 18, 2008 by Guest

[AlanStone]

HI Fenton,

Thanks again! I will review what you did and try to "reverse engineer" the layouts, etc. so that I can learn how you did it. Between you, aldipalo and the Missing Manual, I should be able to get through this first phase.

I appreciate your patience, this is my first real attempt at doing this and I am trying to get it done on and off in between an incredibly busy work week and 3 kids under the age of 4 at home! I very much like FileMaker and want to build as robust and scalable a Database as I can "on my own (with a little help!)" because it is pretty cool stuff and challenging for me at least in staying logical and vertical in my thinking!

Alan

[AlanStone]

HI Al,

That template is pretty robust and looks great! There are definitely some aspects that I would like to incorporate into my db, but that is a long term goal. First up, I would like to get the main stuff up and running, and in proper form so that I can add modules/features as I learn more.

Thanks for passing on that db!

Alan