7 Replies Latest reply on May 6, 2014 10:55 AM by philmodjunk

    Show who has more than one value in field

    Imre...

      Title

      Show who has more than one value in field

      Post

           Hi,

           I have one Table with two fields: Deliveries::client_id and Deliveries::product_organisation_name

           It is an excerpt of a bulky export which shows for what organization_name a client had a registration.

            

           I would like to have a script or find command which shows me which client_id's have more than one sort of product_organisation_name. The result should be a list of client_id's. So, client_id 297026 should show up, and 297015, 297022 and 297029 should not be on the list. (see screenshot)

            

           Please help, tnx.

      Schermafbeelding_2014-05-01_om_13.43.22.jpg

        • 1. Re: Show who has more than one value in field
          philmodjunk

               What version of FileMaker are you using?

          • 2. Re: Show who has more than one value in field
            Imre...

                 I am using Filemaker Pro Advanced v.11

            • 3. Re: Show who has more than one value in field
              philmodjunk

                   Then that rules out using ExecuteSQL with the DISTINCT key word to get a list of distinct values. But there is another way that works in FileMaker 11.

                   There are two similar ways to set this up. If you have a table of clients with a client ID and only one record for each client--which would be a very useful table in your database, use it. But if not, you can set up a self join with the table that you have described here.

                   If you have a clients table:

                   Clients::Client_ID = Deliveries::Client_ID

                   If you do not:

                   Deliveries::Client_ID = Deliveries|SameClient::Client_ID

                   Deliveries|SameClient is a second table occurrence of Deliveries.

                   Define a value list, Client Organization Names, with the "use values from a field" option. Set it to list values from the right hand table (Deliveries if you use a client table, Delieveries|SameClient if you do not.)

                   Select the Product_Organization_Name field as the source of values.

                   Select the "include only related values, starting from" option and select the left hand table (Clients if you use that table, Deliveries, if you do not).

                   Now define this calculation field in the left hand table:

                   ValueCount ( ValueListitems ( Get ( FileName ) ; "Client Organization Names" ) )

                   and select number as the result type. Make this an unstored calculation field to be sure that it updates when new records are added to the Deliveries table.

                   This field will return the number of unique values in Product_Organization_Name for a given ClientID.

              • 4. Re: Show who has more than one value in field
                Imre...

                     Thanks Phil!

                      

                     It sounds a little like jibberish to me, but following your steps did the job!

                     Thanx! I will read the directions over a few times and hope to understand what just happened.

                      

                      

                • 5. Re: Show who has more than one value in field
                  philmodjunk

                       What we set up is a conditional value list. If you added a portal to the related table and changed to list or form view, it would list all the same organizations, but with all the duplicates. Value lists that are set up to show data from a field, automatically filter out the duplicates. So we just exploited that fact and use the ValueListItems function to get a return separated list of values that we can count with the ValueCount function.

                       If you are unfamiliar with conditional value lists--which can be a very useful way to keep value lists reduced to more mangeable sizes, see these links to learn more:

                       There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                       The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                       Forum Tutorial: Custom Value List?

                       Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                       Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                       Hierarchical Conditional Value lists: Conditional Value List Question

                       Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                       Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                  • 6. Re: Show who has more than one value in field
                    Imre...

                         Phil,

                          

                         my company has FMP 13 licenses.

                         What would be the SQL solution you mentioned? With the "distinct"-key?

                         Tnx

                    • 7. Re: Show who has more than one value in field
                      philmodjunk

                           See this thread: A new way to count unique values in FileMaker 12

                           Other useful resources for working with ExecuteSQL:

                           SeedCode's free SQL explorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree

                           FileMaker's current reference document on SQL used with FileMaker (ODBC, JDBC and ExecuteSQL): https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf