Hi,
    I have one field that is not unique, How to define another
field to count the number of unique entry. for example,
have field Test, there are several reords with

R1: Test = "test1"
R2: Test = "test2"
R3: Test = "test1"
R4: Test = "test1"

the count number should be 2.

Thanks a lot
Dong

Dong wrote:
> Hi,
>    I have one field that is not unique, How to define another
> field to count the number of unique entry. for example,
> have field Test, there are several reords with
> 
> R1: Test = "test1"
> R2: Test = "test2"
> R3: Test = "test1"
> R4: Test = "test1"
> 
> the count number should be 2.
> 
> Thanks a lot
> Dong


Filemaker TECHINFO: Calculating Total Number Of Unique Values In A File
http://filemaker.com/ti/107969.html

I think the solution at http://filemaker.com/ti/107969.html IS WRONG!!!

I got some fractional number such as 6.5   That is definitely wrong,
and the way of this calculation doesn't make any sense at all. What
does "1/Count..." mean???

Marc-Andr=E9 Paiement wrote:

> Dong wrote:
> > Hi,
> >    I have one field that is not unique, How to define another
> > field to count the number of unique entry. for example,
> > have field Test, there are several reords with
> >
> > R1: Test =3D "test1"
> > R2: Test =3D "test2"
> > R3: Test =3D "test1"
> > R4: Test =3D "test1"
> >
> > the count number should be 2.
> >
> > Thanks a lot
> > Dong
>
>
> Filemaker TECHINFO: Calculating Total Number Of Unique Values In A
File
> http://filemaker.com/ti/107969.html

jiehuang001@hotmail.com wrote:

> I think the solution at http://filemaker.com/ti/107969.html IS WRONG!!!

No need to yell. The solution on that page works fine.

Create a self-join relationship from your Test field to your Test field.
Create a calculation field named Counter = 1/Count(SelfJoin::Test)
Create a summary field. In the options for the summary, choose "Total 
of" the "Counter" field. Make sure "Running Total" is unchecked.

Using your example data below, the summary field will show a 2.

> I got some fractional number such as 6.5   That is definitely wrong,
> and the way of this calculation doesn't make any sense at all. What
> does "1/Count..." mean???
> 
> Marc-Andr� Paiement wrote:
> 
> 
>>Dong wrote:
>>
>>>Hi,
>>>   I have one field that is not unique, How to define another
>>>field to count the number of unique entry. for example,
>>>have field Test, there are several reords with
>>>
>>>R1: Test = "test1"
>>>R2: Test = "test2"
>>>R3: Test = "test1"
>>>R4: Test = "test1"
>>>
>>>the count number should be 2.
>>>
>>>Thanks a lot
>>>Dong
>>
>>
>>Filemaker TECHINFO: Calculating Total Number Of Unique Values In A
> 
> File
> 
>>http://filemaker.com/ti/107969.html
> 
> 

jiehuang001@hotmail.com wrote:
> I think the solution at http://filemaker.com/ti/107969.html IS WRONG!!!
> 
> I got some fractional number such as 6.5   That is definitely wrong,
> and the way of this calculation doesn't make any sense at all. What
> does "1/Count..." mean???
> 
"1/Count" is maybe a bit surprising at first, but look at the values it 
gives with your sample data. As you can see, the total for R1, R3 and R4 
is 1/3 + 1/3 + 1/3 = 1.

                                  1/count(selfjoint::Test)

>>>R1: Test = "test1"                      1/3
>>>R2: Test = "test2"                      1/1	
>>>R3: Test = "test1"                      1/3
>>>R4: Test = "test1"                      1/3
                                            ---
              summary field = total          2
						
>>>the count number should be 2.

Hi,
    Thanks a lot for your reply, I tried this method. But it seems it 
doesn't work. I am not sure what's the problem. the calculation of
the 1/count(selfjoint::test) is good. but the total of 
1/count(selfjoint::test) is just not updated. when I add new records
to test it. sometimes, it is updated, sometimes it doesn't. my version
is filemaker 7.

Thanks again.

Dong
Marc-Andr� Paiement wrote:
> jiehuang001@hotmail.com wrote:
> 
>> I think the solution at http://filemaker.com/ti/107969.html IS WRONG!!!
>>
>> I got some fractional number such as 6.5   That is definitely wrong,
>> and the way of this calculation doesn't make any sense at all. What
>> does "1/Count..." mean???
>>
> "1/Count" is maybe a bit surprising at first, but look at the values it 
> gives with your sample data. As you can see, the total for R1, R3 and R4 
> is 1/3 + 1/3 + 1/3 = 1.
> 
>                                  1/count(selfjoint::Test)
> 
>>>> R1: Test = "test1"                      1/3
>>>> R2: Test = "test2"                      1/1   
>>>> R3: Test = "test1"                      1/3
>>>> R4: Test = "test1"                      1/3
> 
>                                            ---
>              summary field = total          2
>                        
> 
>>>> the count number should be 2.

Hi,
    This method won't work for the search result either. Is there anyway 
which can be worked on search result. Like the
R1: Test = "test1"
R2: Test = "test2"
R3: Test = "test1"
R4: Test = "test1"

if with some search criteria, R1 is omitted from the record set. the 
count number will be 1 2/3, is there any way to make it 2, I think
round is good option here.

Thanks
Dong
Marc-Andr� Paiement wrote:
> jiehuang001@hotmail.com wrote:
> 
>> I think the solution at http://filemaker.com/ti/107969.html IS WRONG!!!
>>
>> I got some fractional number such as 6.5   That is definitely wrong,
>> and the way of this calculation doesn't make any sense at all. What
>> does "1/Count..." mean???
>>
> "1/Count" is maybe a bit surprising at first, but look at the values it 
> gives with your sample data. As you can see, the total for R1, R3 and R4 
> is 1/3 + 1/3 + 1/3 = 1.
> 
>                                  1/count(selfjoint::Test)
> 
>>>> R1: Test = "test1"                      1/3
>>>> R2: Test = "test2"                      1/1   
>>>> R3: Test = "test1"                      1/3
>>>> R4: Test = "test1"                      1/3
> 
>                                            ---
>              summary field = total          2
>                        
> 
>>>> the count number should be 2.

Based on this and your other email, I'm guessing that you've done this 
calc as an auto-enter number field.  It must be done as an unstored calc 
field; otherwise, how *would* it update itself when another related 
record is added or deleted?

Dong wrote:
> Hi,
>    Thanks a lot for your reply, I tried this method. But it seems it 
> doesn't work. I am not sure what's the problem. the calculation of
> the 1/count(selfjoint::test) is good. but the total of 
> 1/count(selfjoint::test) is just not updated. when I add new records
> to test it. sometimes, it is updated, sometimes it doesn't. my version
> is filemaker 7.
> 
> Thanks again.
> 
> Dong
> Marc-Andr� Paiement wrote:
> 
>> jiehuang001@hotmail.com wrote:
>>
>>> I think the solution at http://filemaker.com/ti/107969.html IS WRONG!!!
>>>
>>> I got some fractional number such as 6.5   That is definitely wrong,
>>> and the way of this calculation doesn't make any sense at all. What
>>> does "1/Count..." mean???
>>>
>> "1/Count" is maybe a bit surprising at first, but look at the values 
>> it gives with your sample data. As you can see, the total for R1, R3 
>> and R4 is 1/3 + 1/3 + 1/3 = 1.
>>
>>                                  1/count(selfjoint::Test)
>>
>>>>> R1: Test = "test1"                      1/3
>>>>> R2: Test = "test2"                      1/1   R3: Test = 
>>>>> "test1"                      1/3
>>>>> R4: Test = "test1"                      1/3
>>
>>
>>                                            ---
>>              summary field = total          2
>>                       
>>
>>>>> the count number should be 2.

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

Dong wrote:
> Hi,
>    This method won't work for the search result either. Is there anyway 
> which can be worked on search result. Like the
> R1: Test = "test1"
> R2: Test = "test2"
> R3: Test = "test1"
> R4: Test = "test1"
> 
> if with some search criteria, R1 is omitted from the record set. the 
> count number will be 1 2/3, is there any way to make it 2, I think
> round is good option here.
> 
> Thanks
> Dong

Calculations through relationship are not affected by the found set: 
that's why 1/count still gives 1/3 even when one of the 3 records is 
omitted. A Summary field's result, however, will vary according to the 
found set, and that explains why you get 1 2/3 when 1 of the record is 
omitted.

There is a way around that, and again it relies on a self-joint 
relationship, in that case a so-called "constant" relationship (well, at 
least, that's how I call them). To build that constant relationship, you 
will need a new field - a calculation that simply returns the value 1 
(stored) It doesn't really need to be a calculation, by the way, but 
let's not get into that. You then define the relationship using that 
constant as the match field on both sides.

Instead of using a summary field for the total, you can then define a 
calculation through that constant relationship:
                        sum(constant_self_joint:: counter)
The result of that caluclation will always be 2 (with your sample data), 
no matter what records are in the found set.

Please note that these are intermediate/advanced techniques (or 
workarounds) and maybe I'm not the best person to explain them in 
English.  Also, there may be other ways to accomplish that in Filemaker 
7, which I don't know very well. For instance, I know that there is no 
need to define a constant relationship to acces globals in other tables 
or files. I suspect that you'd still need that constant relationship in 
this case, but I'm not sure.







> Marc-Andr� Paiement wrote:
> 
>> jiehuang001@hotmail.com wrote:
>>
>>> I think the solution at http://filemaker.com/ti/107969.html IS WRONG!!!
>>>
>>> I got some fractional number such as 6.5   That is definitely wrong,
>>> and the way of this calculation doesn't make any sense at all. What
>>> does "1/Count..." mean???
>>>
>> "1/Count" is maybe a bit surprising at first, but look at the values 
>> it gives with your sample data. As you can see, the total for R1, R3 
>> and R4 is 1/3 + 1/3 + 1/3 = 1.
>>
>>                                  1/count(selfjoint::Test)
>>
>>>>> R1: Test = "test1"                      1/3
>>>>> R2: Test = "test2"                      1/1   R3: Test = 
>>>>> "test1"                      1/3
>>>>> R4: Test = "test1"                      1/3
>>
>>
>>                                            ---
>>              summary field = total          2
>>                       
>>
>>>>> the count number should be 2.