Problem with Related Value List

[Withan]

I am trying to create a pop-up value list to insert an employee ID into a field on an unrelated table (Mail Boxes), so the ID of the employee who opened the mail can be verified and stored. Problem is, employees are in the Contacts table, with all the other contacts. (I thought this would help reduce the number of layouts, fields, etc., especially when hiring an employee already in contacts.)

When I make the value list use the fields from contacts, everything is fine, but I get a list of ALL contacts, not just employees.

Everything I have done to try to reduce it down to just employees results in .

Every contact has a field called Personnel, which contains a "1" for employees and nothing for other contacts. I have made a "Global employees for Mail Boxes" relationship between Mail Boxes and Contacts, using a calc = 1 field in Mail boxes connecting to the "Personnel" field in Contacts.

I then made a value list by field to Contacts, and restrained it by the above relationship.

I have tried all types of deviations, to no avail. I can get a full list of all contacts, but I cacn't get it narrowed down to those contacts with a "1" in the Personnel field.

[bcooney]

I think it's Conditional Value List month here at the forum. I added the Name_lu in Mailboxes, because if you deselect the employee checkbox, the mailbox record loses the name. So the lookup field retains the name info. (Soren will have a better way, for sure!) :)

Topic_192820.zip

[Withan]

I see what I did wrong. I should have selected "Mailboxes" for "Include only related values starting from...!" I was trying to limit the returned values with the same file I was coming from.

And to think I already wrote a huge sript and two layouts to do it the 1998 way, with a portal selection scheme! I kind of like how it came out my way, but your way sure is easier. Good idea about the lookup, so if an employee gets deleted from contacts for some reason you stilll have the name recorded.

Going from 1998 to 2008 will contain many challenges! I still have to rewrite the program that took 13 years (and counting) to write that compares automobiles. Boy am I going to have my work cut out for me. But then, some parts will be much easier to write and less complicated than with FMPro 4.2. The learning curve will be high. That program has one table (file) in it with over 3000 relationships and probably about 700 pages of calculation fields.

Makes me wish there was an option to still create relationships the old way. Especially when you could duplicate one and just change the key field on one side or the other.

[bcooney]

700pp of calculations! OMG.

I think that you'll find that FM7+ multi-criteria relationships will reduce the need for many of those calc fields.

I wish you the best. We've all been there. It's really a whole new world. You might want to read up on the different methodologies for using the Relationship Graph. I favor what is called the anchor-buoy approach. Also, may I suggest naming conventions for fields, layouts, scripts. FMI has a white paper that is a great start FMDev Conventions