Hi,

Here is my situation.

I have a table called tb_manager which has manager_id and manager_name
fields.
Another table called tb_project which has project_id, main_manager_id,
and bk_manager_id.
I have relationships on manager_id from tb_manager to tb_project's
main_manager_id and bk_manager_id.

I would like to have a value list when I select a manager_id, it will
show me all project_id that this manager handles. This value list
should include all projects which the manager_id is either in
tb_project's main_manager_id or bk_manager_id.

Example:

tb_manager: manager_id: A
B
C
D

tb_project:
record 1 - project_id: 1, main_manager_id: A, bk_manager_id: C
record 2 - project_id: 2, main_manager_id: B, bk_manager_id: A
record 3 - project_id: 3, main_manager_id: C, bk_manager_id: D

if I select manager_id A, the value list should show me bother
project_id 1 and 2.

I am use FM7.

Any suggestion is good.

Thank you very much for your help,

Jack

In article <1163724878.705709.219970 (AT) f16g2000cwb (DOT) googlegroups.com>,
gwklocker01 (AT) gmail (DOT) com wrote:

Usually I would just use two relationships / portals - one for the
"main" and one for "bk". That way it's easier to know which is which.

If you really want to combine them into one relationship, then you can
create a new field in the tb_project table that combines both "main"
and "bk" into one field using an auto-enter calculation.
ie.
Manager_IDs Text
auto-enter
= main_manger_id
& If (IsEmpty(bk_manager_id),
"",
"{return char}" & bk_manager_id)

You can then create a relationship in the tb_manager table that links
to this new field.
ie.
Projects tb_manager -> tb_projects
when manager_id = tb_projects::Manager_IDs

This is often called a "mutli-key" relationship. FileMaker matches with
any of the paragraphs within the child field, so simply spearating each
ID by a return character gives the ability to link multiple parent
records to the same child record - a very handy trick at times, but one
that isn't very widely known about.

The problem of course is that it uses an auto-enter calculation because
a relationship can't have a normal unstored calculation on the child
side ... unfortunately that means it won't update itself in older
versions of FileMaker if you later change one of the managers. You'll
need to remember to change it manually, although you may be able to get
around this with the option in newer versions of FileMaker to let
auto-enter calculations update themselves.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Hi Harry,

Thank you very much for the answer. I will try it out. One quick
question, when you mentioned that the newer version of FM can let
auto-enter calculations update themselves. I am using FM7, and I did
not see this update option. So, I assume this auto update option is in
FM8?

Thank you,

Jack


Helpful Harry wrote:

In article <1164065745.649162.140190 (AT) j44g2000cwa (DOT) googlegroups.com>,
gwklocker01 (AT) gmail (DOT) com wrote:

Sorry, I'm not sure which version that option was added to ... I'm
still stuck back at FileMaker 4, 5, 5.5 and occasionally 6 since that's
what's used by people I do work for.

I would guess that it's a tick box at the bottom (or somewhere) of the
window where you type in the auto-enter calculation itself.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)