COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

How to count the number of unique entry of one field

• Follow

```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
```
 0

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

```
 0

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

```
 0

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

```
 0

```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.
```
 0

```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.
```
 0

```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.
```
 0

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

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
```
 0

```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.

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.
```
 0

8 Replies
530 Views

Similiar Articles:

7/22/2012 4:45:53 PM