AnsweredAssumed Answered

Hi! I'm new on FileMaker and I probably have a very basic question. I have a table with...

Question asked by MarcStevez on Mar 28, 2015
Latest reply on Mar 28, 2015 by MarcStevez

Post

Hi! I'm new on FileMaker and I probably have a very basic question.

I have a table with salesmen info and another one with info about each salesman sales (please, see below). I want to calculate how many different customers each salesman had. I've been reading a lot on this forum and I see that I probably could do this with an executeSQL but I have failed so far (I don't know any about sql  :-( ).

Could somebody help me out on this? I hope what I want to do is feasible.

These are the two tables:

Table: Salesman_info                Table: Sales

ID    Name                                 ID_cust     ID_salesman   Customer

1     Ted                                          1                    1                ABC

2     David                                       2                    1                XYZ

3     Jhon                                        3                    2                PQR

                                                       1                    1                ABC                   

                                                       3                    2                PQR

                                                       3                    1                PQR               

                                                       2                    3                XYZ

                                                       1                    3                ABC

As I said, I am trying to get how many different customers each salesman had and add the result to each salesman record. Hence, the result should be something like:

Table: Salesman_info

ID    Name       Dif_Customers                      

1     Ted                3                   

2     David            1                         

3     Jhon             2  

I've created a calculation field on the table "Salesman_info" called "Dif_customers", with the following content: ExecuteSQL("SELECT ID_salesman, count(ID_salesman) FROM Sales GROUP BY ID_cust";"";"";""). Also, both tables are linked by ID_salesman.

Thanks a lot in advance!!!!

Outcomes