Only Display Related Values

[cmartin]

Hello all-

I have a STAFF file and a STUDY file. A member of the staff can be a coordinator on many studies and vice versa, so it is many-many, which leads to my COORDINATOR file. Coordinator has a STAFFID and a STUDYID. I want a value list which shows the FullName (calc field in STAFF) of only those STAFF who are COORDINATORS (have a related record in COORDINATOR) as well. I have tried just about every combination of things, but the only way I have been able to get it to work is to have a text field in STAFF that designates whether or not the person is a coordinator. That's no good. Please help.

Thanks-

Courtney

[Razumovsky]

Hi Courtney-

I think I get what you are looking for, but am not sure. If all you want is a value list of all staff who have related records in CoordinatorDB, you have at least 2 options;

1. Create a calc field in Staff:

case(not is empty(coordinator::StaffID), FullName, "")

use all values from this field as your value list.

2. create a textfield t_fullname in coordinatorDB, and make this a lookup based on staffID::Fullname. Use all values from this field as your value list.

HTH

-Raz

[cmartin]

Hey Raz, thanks for the advice. However, here is my problem. I had tried something similar to the first method you describe, but the value list will not work because the calc field cannot be indexed (based on a related field). The second method seems to be a plausible solution (which I just tested and DOES work - yay!), but the only problem I have with this method is that if a person's name changes (ie they get married), this will not propagate through to my COORDINATOR value list unless I do a relookup. But if this is the best I can do, then so be it, I will just have to script a relookup in if the name fields change, I suppose. Unless anyone has another suggestion?

[Razumovsky]

Sorry bout that- forgot about the indexing problem.

You could definitely accomplish something similiar using a faked "pop-up" portal, but may be a bit akward and not worth the trouble. Aside from that, I'm fresh out of ideas tonight.

-Raz

[Ugo DI LUCA]

If you want to keep a clear relational design, you'd rather use the first calculation given by Raz, and have it indexed.

I also came accross this kind of problem. Keeping the gilded crown on BobWeaver's head !. Here is what he suggested in my case.

Suppose FieldU is an unindexable field and you want to transfer its value to FieldX which is an indexed lookup field. You will need to have these fields:

SerialNo -- autoenter serial number field

ModTime -- Autoenter modification time

LookUpKey -- Calculation = (ModTime*0)+SerialNo

Create a relationship called sjLookup with the primary key (left side) field: LookUpKey, and match (right side) field: SerialNo

Then define FieldX to lookup its value from FieldU via this relationship.

Now, here's the catch. It will only be triggered when something is changed within the current record. Changes in a related record won't cause the lookup to trigger, and changes to any other record in the current file won't cause a relookup either. So, this may not help you if you want the lookup field to update when one of those things happen. In that case, you need to use a script.

For your particular file,

1. keep Raz cCoordStaff = case(not is empty(coordinator::StaffID), FullName, "")

2. Create a tCoordStaff and set it to be a lookup from cCoordStaff using SjLookup

3. Use this new text value for your value list.

I now tend to always have these fields and relationship in every file.

[cmartin]

the weaver method works great. thanks for all the help, raz and dilucaugo!

-courtney