Value List to draw from different tables

[logocop]

Hi all--

I have a job search database where I want to track three different categories of communications, depending on whether the focus is on (a) a specific job opening, ( contact with a specific individual, or © contact with a recruiting firm. These need to stay separate even though there might be some overlap (for example, an e-mail about a job might or might not be to a specific individual).

My DB has 4 tables that matter here: Jobs, People, Recruiters, and Contact Log. This last one tracks each communication.

For each Contact Log record, I'd like to be able to choose the specific subject of the communication from a drop-down list, and I want the choices in that list to change depending on the focus of the communication, which I choose in a "ContactRelatesTo" field. For example, if ContactRelatesTo contains "person," I want the drop-down list to contain only the contents of a specific value list in People. I could do this if I only had 2 tables, but I'm stumped with more. (There's no practical way to combine any of the tables.)

I can create a text calc field in Contact Log that displays the appropriate value list items (as determined by ContactRelatesTo), but I can't create a new Contact Log value list based on it without combining the values from every Contact Log record (meaning I get all 3 value lists together). If I make the text calc field global, FMP won't let me base a value list on it.

Is there perhaps some way to use a portal to extract the values I need? I don't want to use the kind of screen space a portal uses, but perhaps there's a trick.

My humble apologies if this has already been addressed in these forums -- I couldn't find anything discussing it.

I'll be glad to answer any questions, and thanks in advance for your help!!

[logocop]

Well, either my question was so boring or so obvious (or maybe both?) that no one was interested enough to answer it. I did find a solution, though, which I'll be happy to describe if anyone would like to hear about it, especially since someone could probably do better than what I came up with.

Otherwise, thanks for... um, well, reading, I guess. :P

[sbg2]

How about a sample file so we can see first hand what you are trying to describe.

[Lee Smith]

As SBG2 stated, a file would probably help us a lot.

If I'm understanding you, and I'm not sure that I am, it sounds like you are wanting a Conditional Value List. If so, there are many, many post about how to do this on the Forum (including several sample files).

If you don't find what you need, or what I suggest isn't what you are talking about, then the file, and a more information on what you have now and what you trying to accomplish.

Lee

Edited February 20, 2006 by Guest