value list across related tables

[jageo]

Table called "Order" has fields "Serial#" and "Name" and a global field "ActiveYes" which is auto enter set to equal 1

Table called "CustomerList" has fields "Serial#" and "Name" and "Active"

The table is filled with customer data and "Active" is set to 1 or a 0 (to tell if the customer is active or not)

I can successfully make Order:Serial# pop up a value list showing ALL records from Customer:Serial# but I can not make it show me only the Active customers.

Following the instructions in Filemaker's help files, and creating a self-join works only if you are popping up a value list from field in the same table. How do I make it work across related tables?

If anyone can direct me to a solution it would be much appreciated.

Many thanks,

JA

Edited April 17, 2008 by Guest

[jstaphse]

Try this.

OrdersCustomers.zip

[comment]

I'm afraid that won't work with a new order. Try it this way instead:

OrdersCustomers2.fp7.zip

[jageo]

That's what I need. Many thanks.

Talking my way through understanding:

1) cTrue in your CustomerTable is a calculation as opposed to an auto enter because that is the easiest/safest?

2) Is it logic or just convention that has you create the self-join where the constant (cTrue) in the "All Customers" table and the variable (Active) is in the Table Occurance. Would it work the other way around?

3) I'm not sure why the value list is defined as 'using the values from the ActiveCustomer table' but where you indicate to include only related values you choose the 'Customers' table.

JA

[comment]

1. It can be a calculation or a global - it doesn't matter much. A global would enable user to toggle between active and archived customers, if that's a desired feature.

2. The two fields are in the same table. However, the relationship is NOT symmetric: there's an unindexable field (calculation or global) on the "parent" side, and an indexed field on the "child" side. Such relationship works only in one direction, from the parent to the child. Therefore, the value list MUST be defined to show only related records from the child occurrence.

3. The value list is defined as "starting from Customers" because that's the shortest path required to make the distinction between active and archived. Once that's established, the value list will work in any context - even in an unrelated table.

[jstaphse]

I'm blaming that one on the time of day it was created. Thanks for the correction Comment. :yourock:

[Ugo DI LUCA]

Ciao Mike,

Good catch about the unstored result.

I always considered this unstored result of 1 showing with no relation established a bug. That's why I'd prefer a global which behavior towards relationships is largely documented feature.

An as you said, we can make it vary according to our needs.

[comment]

Hey Ugo,

I don't perceive a bug here. True, a global can be toggled - but you also need to make sure it always has a value (preferably the correct one : ).