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.
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.
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?
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" ))
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!!!!
Does this look right
ExecuteSQL ( "select count (Distinct client id#) from product" ; "";"" )