In FileMaker 12, you can do this with ExecuteSQL instead of List using SELECT DISTINCT.
Without SQL, you can define a conditional value list and get this list using the ValueListItems function.
I think that you have this relationship:
Orders::Docket Number = OrderDetails::Docket Number
If so, you can define a value list of these values with the "use values from a field" option. Select the zone field from OrderDetails and then select "Include only related values, starting from Orders".
Then you can use ValueListItems ( Get (
FIeldNameFileName) ; "ValueListNameInQuotesHere" ) in place of List ( Order Details::Zone).
Thank you Phil.
You answer is super.
I tried your solution, works beautifully. Just you had a typo, it should read : ValueListItems ( Get ( FileName ) ; "ValueListNameInQuotesHere" )
You idea works perfectly with when the zone is entered only one store number. But my case is : it is entered with store numbers separated by a space for each orer detail line. So I made a small enhancement. I did create another calculation field: Zone_Cac in Order details: = substitute ( Order Details::Zone & " "; " "; ¶). then, when I define the value list, I select Zone_Cac field instead of the Zone field.
Just to update.
Thank you very much.