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