4 Replies Latest reply on Aug 29, 2013 12:45 PM by jasongan

    how to filter unique values



      how to filter unique values


           Hello, Forum,

           thank you all I know now how to get  my store filter calculation by province.

           I am stuck in another requirement.

           I have Order Dtails table, and in that tabel I have 10 filtered stores by province. like  ON_stores, AB_Stores, BC_Stores ...

           From my Order Header table is associated with the Order Details with Docket Number.

           Here is an example of the two tables:

           Order Details: (only show 2 records as example)

           Docket Number: 110

           Part Number: a12

           Qty: 100

           Zone: 1 2 3 901 902 904

           ON_Stores:  1 3 901

           AB_Stores: 2 902 904


           Docket Number: 110

           Part Number: a14

           Qty: 100

           Zone: 1 2 3 901 

           ON_Stores:  1 3 901

           AB_Stores: 2 


           My Order Header table:

           Docket number: 110

           Zone List Summary :   1 2 3 901 902 904 1 3 901  /*( = substitute (List (Order Details::Zone); ¶; " ") :  */

           BUT, I want to get Zone List Summary = 1 2 3 901 902 904. I just want to show the unique store numbers.

           I don't know how to do it in a calculation. 

           Please help.









        • 1. Re: how to filter unique values

               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 ( FIeldName FileName) ; "ValueListNameInQuotesHere" )  in place of List ( Order Details::Zone).

          • 2. Re: how to filter unique values

                 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" ) 



            • 3. Re: how to filter unique values

                   Good catch on the typo. smiley

              • 4. Re: how to filter unique values

                     Hi Phil,

                     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.