Help with Conditional Fields

[Joseph31]

I have a simple example attached of what i need help with.

Setup:

There are three tables: (Main, Company, Employees)

The main table shows the company you want, the company table shows all of the companies in the data base with there employees

The Problem:

As you can see you can pull down any company you wish and then select the employee you want from that company... Cool I know ... it only shows the employees for that company.

But I also need it to show the email address on the main page for that companies employee.

This way I can do a auto email for what I need.

Can some one add this this and show me how this is done.

Thank you,

Joseph

Example__1.zip

[SlimJim]

You are choosing a company contact but there is no corresponding relationship into the employee table - other than via company and that will alwasy choose the first record. I would add another table occurrence of the employee page and relate it to the main page company_contact match Eemployee_full_name and then drop the corresponding related value of email onto your layout

[Joseph31]

Can you show me what you mean by this... b/c I think I have tired that sevral different ways and it never seems to show up the email address with the name I have selected....

Joseph

[Fenton]

I think you need to use your IDs and their relationships, rather than the names. That is how related database work best.

I redid your example how I'd do it. I'm assuming that there is some reason that you're using the "Main" table, and that it has individual records? Otherwise you could do this choice in Companies (or Main), with global fields for the choice.

The thing that you were missing was a direct relationship from Main to Employee. That is the only way to show the employee name correctly, and also their Email.

I used FileMaker 8's option to "only show the 2nd field" in the value lists. It allows you to use the IDs properly, but only show the names in the drop down lists. (Works in Find mode also.)

The ID fields are hidden under the related fields, which show the names after the ID choice is made. ONLY the IDs are in the Main table. The names are NOT enterable. If you click on one, you will drop straight through to the ID field, and the list will pop up. You can add an optional [>] down-arrow graphic, to show a hint. I didn't find 8's "Include arrow to show the list" useful here; you'd always want it to drop down.

Showing only the 2nd field in value lists means that those names MUST be unique. So I added a Validation option of Unique to the Company Name. The Employee name is a calculation of 2 fields, First and Last, hence non-enterable. You cannot validate a calculation field directly, since data is not entered in it. You must validate its source fields instead; with a calculation requiring uniqueness of a self-relationship of the Full Name.* These precautions are necessary in 8.

*You validate that the self-relationship IsEmpty. It seems counter-intuitive; because logic says that the self-relationship is not empty, it matches itself. But because of the timing involved at the exact time of validation, it is still empty until the edit of the current field is committed; which triggers the validation.

(If you think this is bad, wait until you see "audit logs" solutions -)

Example__2.zip

[Joseph31]

Ok so I tired all of this and I see how you did it.. but I just can not get it to work in my exsisting data base...

I am alittle afraid b/c if I was to go from scrach i would wipe out all of the past cases and there relationship with there companies.

Is there a way to do it so I do not have to wipe out all of the past 1500 entires to show the email address with the contact name.

As you see in my example... my contacts works fine with the company name. There must be a way to just add email address to the contact names without redoing the relationships (which would wipe out all of the past entires)

Thank you again,

Joseph

[SlimJim]

I have changed your sample in a minimal fashion - I think when you are not tired you would benefit from looking at Fenton's example.

Example__1.zip

[Joseph31]

Thank you for that example...

I have now done everything just like you have... I triple checked it. But the Feild ::Email... still does not show up?

Is there something that I could have over looked?

Joseph

[SlimJim]

The field was showing up OK in the example so I guess that somewhere in the translation into your solution there is a problem. I'm going to suggest a number of tests to make sure its not something simple - you may well have done these already in which case ignore my post. They come from my long experience of messing things up!

First simple test select the field in layout mode and change its background colour to something that will show up on your layout - to ensure that it is not hidden behind something else in bropwse mode or tucked away in an invisible portal. At the same time make sure the text colour is set to show up.

Second make sure that there is an email available on the record that you are looking at.

Thirdly change the field to something else in the same table and see if that shows up - in particular, use the match field. If that doesn't show then there are no matching records.

[Joseph31]

You are the best man... Your simple check list did it... the field for some reason was hidden.

Thanks so much.

Joseph