1 Reply Latest reply on Jul 23, 2013 1:24 PM by philmodjunk

    Calculation Field: How To Return Only Unique Values / Remove Duplicates From Related Record

    James_L

      Title

      Calculation Field: How To Return Only Unique Values / Remove Duplicates From Related Record

      Post

           I'm trying to make a calculation field that contains a list from a child-table:

           List (Abstract_Property_Join::Proj_Chain_Number)

           Easy enough, but the child-table's field contains a number of duplicates, so my list looks like this:

           1

           2

           2

           2

           3

           .  How can I tweak this so that it returns only unique values (or putting the same thing another way, removes the duplicates)?

        • 1. Re: Calculation Field: How To Return Only Unique Values / Remove Duplicates From Related Record
          philmodjunk

               Define a conditional value list based on the same relationship. (ie. list the values in the value list from Abstract_Property_Join::Proj_Chain_Number and select "include only related values starting from [select your parent table occurrence's name in the drop down]. Then this function call:

               ValueLIstITems ( Get ( FIleName ) ; "yourValueListNameInQuotesGoesHere" )

               will return the list of unique items.

               And if you are using FileMaker 12, you can use ExecuteSQL with the DISTINCT keyword and a "join" expression that recreates your relationship between these two tables to get the exact same list without having to also define a conditional value list.