asked on

FM - Filtering a Value List for a Drop-Down Box

I have a Notes table where an agent can enter notes based on one of their customers. The only problem is that the value list that shows in the drop-down box shows ALL customers, not just their own. This is a problem due to confidentiality, not wanting to share customers, etc. I could relate the tables in such a way to only show their customer names; however, then this would prevent the customer code from showing in the various portals in different layouts. You want the customer code (but not the name) so the agent can navigate to the RR.

So, how would you filter a value list by agent (each customer does have an agent assigned to them marked in a field), but not causing the corresponding portal field to be blank? So far the only solution I can think of is for each agent to keep a record of their customers and cus codes and then only list customer codes (not names) in the drop-down box when the Note is first created.

SOLUTION

North2Alaska

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rvfowler2

ASKER

I know about the secondary field. I just tried to limit it by "include only related values starting from" but nothing I chose worked. What I want is if the agent editing the Notes record is LJ, I only want Contacts to show that have LJ in the "Assigned Agent" field in Contacts. If the agent editing the Notes record is TD, I only want contacts showing that are TD's contacts, so other agents cannot "steal" each other's contacts. Contacts are highly confidential among agents.

North2Alaska

It sounds like your relationship is in question. Is this relationship the X one?

rvfowler2

ASKER

No, see attached images where I show the relationships and the value dialogue box. I'm 90% of the way there: it now only shows contacts for a particular buying agent/notes author; however, if a contact has never been assigned a Note yet, it won't show up in the drop-down box. How can I fix that?
wrc-Relationships2.JPG
RelatedValueIssue.JPG

ASKER CERTIFIED SOLUTION

North2Alaska

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rvfowler2

ASKER

Just double checked, but that doesn't work, it comes up empty. I need to relate Contacts to Notes or else how does it know to limit the contacts by the Buying Agent in Notes?

rvfowler2

ASKER

Still have same problem that if I create a new contact, it does not show up in the drop-down list. Based on the Relationship graph you see above, what can we change to make this happen.

rvfowler2

ASKER

Played around his suggestion to make it work.

rvfowler2

ASKER

Forgot this as it was used in a seldom-used db and layout. Turns out North2Alaska was right, but I misunderstood him. A way of thinking about it is that whatever TO you use for the secondary field, it must be joined to the table you are using for the primary field, NOT to the main table on which your field and value list are used. The secondary table filters the primary table, so must be connected directly to it. Still learning.... :)

P.S. - Also, love your concept that TO's are "Stored Queries." Has really increased my versatility with portals, etc.