Hi. Someone else might have a better way to do it, but I have done it like this:
Grab the UniqueValues custom function from the Brian Dunning site: FileMaker Custom Function:UniqueValues( values )
Then you can use this calculation to get the value you want:
UniqueValues ( List ( Orders::CustomerID_fk ) )
You can use this formula :
no_of_days = 30 ;
startdate = Get ( CurrentDate ) - no_of_days ;
query = "SELECT DISTINCT Cust_ID FROM Sales WHERE SalesDate>'" & startdate & "'" ;
cust_id_list = ExecuteSQL ( query ; Char (9) ; Char (13) ) ;
no_values = ValueCount (cust_id_list)
Cust_ID = the field you want to count the unique values in the found selection
Salesdate = the field that contains the sales date
Sales = the name of the table
It works without any summary field or script...
Thanks for all the help guys. I will give it a shot.
LucThomaere I want to select the disctint customer IDs from the group where myflag is = 1
SELECT DISTINCT __kf_Customer_Id FROM Finance WHERE _b_has_unit_sold_trailing_month ="1"
_b_has_unit_sold_trailing_month flag is set when the sale falls in the last month.
Can I use Distinct Count instead?
You can use whatever name you want that FileMaker allows.
I just use the ExecuteSQL function, part of a normal calculation field
If the field _b_has_unit_sold_trailing_month is a Text Field, you should use ='1'
if it is a number field you should use =1
You can also use "SELECT DISTINCT COUNT ( Cust_ID) FROM..." and
then you don't have to use the ValueCount function
Okay here's what I have.. but I only get a ? as a result..
ExecuteSQL("SELECT COUNT (DISTINCT __kf_Customer_Id) FROM Finance WHERE _b_has_unit_sold_trailing_month=1"; ""; "")
_b_has_unit_sold_trailing_month is a calculated field that returns a number..
I'm not sure where my syntax error is ... since I am getting a question mark as my result.
ALso curious since I will use this on a subsummary part whether I can group by the subsummary field in the ExecuteSQL statement to get the unique count by sales person?
I don't know where is the error, but COUNT(DISTINCT is very slow on FM, may be unusable.