Another Dynamic Value List Puzzle

[Mindi]

I need a dynamic value list but have not seen any examples of exactly what I need.

I have 2 tables... LEADERS & GROUPS

Each GROUP needs to be assigned a LEADER.

I get the LEADER value list from the LEADER table... not a problem.

But once a LEADER has been assigned to a GROUP I no longer want them to display on the value list.

I have tried various things... but nothing seems to quite work and I am at my wits end!

[stanley]

Mindi:

Not sure I understand exactly. Do you mean that the two value lists both draw from the same table? Do you also mean that essentially you want to draw two leaders from the Leader table, but they have to be unique?

-Stanley

[aaa]

Construct your own value list by LEADER file such that if you click at field opens LEADER window with coresponding finding. This will be difficult, but it is possible.

[-Queue-]

You want a dwindling value list. See here for a sample file.

[Mindi]

This is SO the perfect answer! I don't have time today to totaly implement it but I think it will be perfect! I have looked EVERYWHERE for something exactly like this... what a great solution! Thank you so much!

[Mindi]

Ok... So I thought this would be the answer.... After tearing up my database to trying & make this work I realized that in the sample database that the same staff members could be assigned to different projects. They just could not have the same staff member on one project... I need to totally remove a person from the value list once they have been assigned... Any thoughts?

[comment]

How about something like this?

GroupLeaders.fp7.zip

[Mindi]

It's so close! But if there has not been at least one leader assigned the value list is not defined. I feel like an If statement should fix it but I'm not quite sure how....

[comment]

OK, if you insist...

GroupLeaders.fp7.zip

[Mindi]

Ok... that works like a champ! Now here is the clincher... How do I make this work if there can be two Leaders per Group?

[comment]

Like this?

GroupLeaders.fp7.zip

[Mindi]

I feel like I'm pushing my luck here but you have had all the answers so far.... What if I want more than 2 leaders? Can this work with a link table?

[comment]

I believe that as long as a Leader can hold only one appointment, you can keep adding Leaders to a Group.

All you need to do is add a new Leader_n_ID field, add this field to the cLeadersIDs concatenation calc, duplicate a script and modify it to pop the new field.

Whether this is the optimal solution for the problem is a separate question. It is difficult to advise when you keep changing the rules.

[Mindi]

I guess I just never fully explained it. But each of your solutions have been great... and going step by step has made me understand what you are doing. So it really has been very helpful to me even if it has been time-consuming and frustrating for you. So thank you very much for your help!

You can have a maximum of two leaders per group and a leader can only have one assignment. Your last database example would actually work but having 2 LeaderID fields prove to be problematic later in reporting. Which is why I thought that a link table would be the solution...

[comment]

A link table is only necessary in a many-to-many type of situation. If a leader could lead more than one group, AND a group could have more than one leader, then you would need a link table.

If I knew we were to going to end up with multiple leaders per group, I'd say wait a minute - you call them leaders, but if I decide to call them members, then it's pretty clear what needs to be done: instead of assigning a leader to a group, we should be assigning a group to a leader. And suddenly everything is much easier, as you can see.

LeadersGroups.fp7.zip

[Mindi]

One more thing... the solution that -Queue- offered from NightWing used a link table and you could use a portal to display information. It worked except that you could re-use staff members on different projects... which was why I liked your solution better... yours was simpler & more direct.

[Mindi]

So apparently the only problem I have is communication... Because if I can figure out how to ask you... You can have FM stand on it's head & spit nickels!