6 Replies Latest reply on May 8, 2015 4:12 PM by MT_2

    Report using unqiue ID



      Report using unqiue ID


      Hi, hoping someone can help....

      We have two tables "Contacts" & "Product" when a new client is received a new record in the client table is created, then each client could end up with multiple records in the product table. I am trying to create a report using Sub Summaries.

      The report I am trying to create needs to look at how many "Product" records have been created in a certain date range, sort if by the advisor that spoke to client and then look to see of the records created how many of them have a unique ID, the unique ID is the field "Client ID"  i.e if John Smith bought 6 products I only want to report 1 as the result as each record would have the same "Client ID" but a different "Product ID" therfeore the "Advisor" may have been given 5 clients sold to 4 of them so I am trying to do a report works out various bits but starts with the below....

      Number of Leads (equals total number of unique client ID's 

      Hope someone can point me in the right direction.

        • 1. Re: Report using unqiue ID

          I'm having trouble following your description.

          If you only want your report to list and count each client once, the simplest approach is to use a layout based on the client table. You only need to base it on products if you want to list each of the products linked to each client in your report and even then, there are some methods possible for listing them on a client based layout though it often works better on a products based layout. Aggregate values from products are still possible on a client layout, BTW. Totals, averages, counts, etc are quite easy to include. It's just harder to list all the individual products.

          So can you describe your report in a bit more detail? I can't really tell if a client based layout is the best option or not. There are ways to count each unique client from a products layout but it's a lot more complicated than a client based report so we don't want to go there unless we have no choice.

          • 2. Re: Report using unqiue ID

            HI, thanks for the quick reply.

            Yes I thought about doing it in contacts put I think it is more Product heavy in terms of other results needed, let me try an explain more and I have inserted information below.

            I need to create the report so it shows 1 line per advisor, but then looks at the product table and see

            How many leads the advisor had in total - How many of them did not meet criteria - How Many did qualify - How Many were of a certain status but not at a certain status on the other Products for that client.



            • 3. Re: Report using unqiue ID

              I have created the fields to create the FK counts I am now just trying to create the totals but where a client has say 5 products and there ID shows or is counted 5 times I can not work out how to only count that once?



              • 4. Re: Report using unqiue ID

                Looks like you have these relationships:


                I think it would be simpler to base the report either on Advisors or Clients since you do not appear to need to list the individual products, just report aggregate data about those products. ExecuteSQL calculations can produce those values from clients without need to base the layout on products.

                But if you really want to count unique client ID's from the context of products, there are several possible approaches and two are pretty weird:

                1) Use ExecuteSQL with a Count function and the DISTINCT keyword: A new way to count unique values in FileMaker 12

                2) "Sum the reciprocal" (I did say two were weird...): How to count the number of unique occurences in field.

                3) and if you can set up a conditional value list that lists all the clients you want to count, the resulting use values from field value list will automatically drop out duplicates. Thus, you might be able to use: ValueCount ( ValueListItems ( Get ( FileName ) ; "ValueListName" ))

                • 5. Re: Report using unqiue ID


                  Thanks again for quick reply I will look over your reply and try and work out which way is best.

                  Hopefully I will not bother you any further!!!!

                  Thank you


                  • 6. Re: Report using unqiue ID


                    Does this look right


                    ExecuteSQL ( "select count (Distinct client id#) from product" ; "";"" )