value list - include only related values starting from...

[aeu]

I have a rather newbish question about value list relationships, specifically when using "only related values starting from...". If I have the value list on a layout that uses the same table as the value list's relationship is starting from, it works fine. But if I put the value list on a different layout/table, the value list shows no values. So I end up having to create many redundant value lists for each non-related tables and layout. I must be doing something wrong.

My example is this:

I want a value list of only those Contacts who are marked as "Vendor".

TABLE: Contact1

NOTABLE FIELDS:

* isVendor - a boolean field that has the value "1" for any Contact1 record that is a vendor, and "0" if not.

* constantKey - foreign key field with the constant value "1".

RELATIONSHIP: I have a relationship set up from constantKey (Contact1) to isVendor (Contact2).

VALUE LIST: The value list is set up to use values from Contact2. "Only related values starting from" is set to Contact1.

RESULT: When I use this value list in a layout on the Contact1 table, the value list works like a champ, showing me the vendor contact records. However, if I use this value list in a layout on a different or unrelated table -- such as Invoice or Quote -- the value list does not work, and the result is "no values defined".

Logically, since I have set it up to give me values from a specific relationship and a specific starting point, why should it matter what layout I use it on? It seems to me that the values should be the same regardless.

I need this value list on several different layouts with unrelated tables. The only solution I have come up with so far is to create redundant value lists with redundant relationships for each layout/table where I need it, with said relationships starting at each respective table. It works, but that can't be the best way to it. Any help or suggestions would be most appreciated!

[comment]

Change the constanKey field to an unstored calculation (or a global field).

[aeu]

That did the trick! Good call.

Do you know why it won't work when the field is not global/calculated, even though the value of that field for all records was still "1"?

[comment]

It cannot work, because a stored field can have a different value for each record. So a value list defined as "starting from…" would also need to know from which record to start. And since you don't have a related record, or even a relationship…