Conditional Value list not working?

[mfl]

I have a table relationship between.....

MeasurementChart and (TO) MeasurementChart_Defaults_List_Gender_Customer

MC_STYLE_GENDER = MCDL_GENDER and

MC_ID_CUSTOMER = MCDL_CUSTOMER_CODE and

MC_WORD_RETIRED != MCDL_USAGE

MC_WORD_RETIRED will always have "Retired" as a value, automatically entered when user creates a measurement chart.

MCDL_USAGE can either have "Retired", "Current", or be blank.

My drop down value list is located on 2 layouts which are based on MeasurementChart.

The value list itself is based on the Defaults list and is set to "Include all Values" and "Show only values from second field".

My question is does the "Include all Values" override the relationship because currently the drop down brings in all records from the default list regardless of what MCDL_USAGE has for a value.

My other question is how do I make the value list display only the non-retired records?

Thanks

[comment]

does the "Include all Values" override the relationship

Yes. More correctly, "Include all values" ignores all relationships.

how do I make the value list display only the non-retired records?

If I understand your description correctly, set the value list to use values from MeasurementChart_Defaults_List_Gender_Customer, include only related values starting from MeasurementChart.

[mfl]

No its give me everything from MeasurementChart_Defaults_List_Gender_Customer except any charts that are designated "Retired" in the default list regardless of relationship to MeasurementChart.

I've already been pointed to the ExecuteSQL as a solution and I am looking into this but I don't have the time slice right now to implement this.

[comment]

No its give me everything from MeasurementChart_Defaults_List_Gender_Customer except any charts that are designated "Retired" in the default list regardless of relationship to MeasurementChart.

It's giving you all the values that exist in related records. If you're getting anything else, check your implementation - esp. the context of the layout you're on.

[mfl]

Sorry, probably should have worded that differently.

What I need for this value list to show is all records from the MeasurementChart_Defaults_List_Gender_Customer except any charts that are designated "Retired" in the default list regardless of relationship to MeasurementChart.

[comment]

Well, then you need to define another relationship for this purpose in the form of:

MeasurementChart::MC_WORD_RETIRED ≠ MeasurementChart_Defaults_List_Gender_Customer 2::MCDL_USAGE

and set the value list to use values from MeasurementChart_Defaults_List_Gender_Customer 2, include only related values starting from MeasurementChart.

Alternatively, define a calculation field in the base table of MeasurementChart_Defaults_List_Gender_Customer =

Case ( MCDL_USAGE ≠ "Retired" ; TheFieldYouWant )

and set the value list to use values from this field (including all values).

[mfl]

Thanks comment worked like a charm.