2 Replies Latest reply on Mar 28, 2015 11:03 AM by MarcStevez

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

    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!!!!