Counting Unique Entries in a Field

[Roark Holz]

Hello,

I am still on FileMaker 11 and am having a problem I'm hoping someone might be able to assist me with.

I have a very simple database as an example with one table and two fields, date and customerid. I would like to create a field that counts the number of unique customerid's in my current selection and have been following the FileMaker knowledge base article here...

Counting the number of unique values in a field

The article describes 3 methods to do this, the 1st uses the ExecuteSQL method which won't work for me because it requires a newer version of FileMaker. The 2nd requires a sort I'd rather not do so I have been trying to do this using the 3rd method titled "Self Join Method".

1. I created a self-join relationship matching the customerid field with a table ocurrance named selfjoin

2. I created a calculated counter field using this expression: counter = 1 / Count(selfjoin::customerid)

3.  I created a summary field totaling the counter field.

If I have every record in the database selected then this method seems to work fine. But if I perform a search on just a specific date I am finding that the calculation seems to break down if there happens to be any omitted records that have the same customerid defined.

Can anyone give me a hint as to what I might be doing wrong?

Thank you.

[comment]

46 minutes ago, Roark Holz said:

Can anyone give me a hint as to what I might be doing wrong?

You are not doing anything wrong. Relationships ignore found sets. If you want to summarize the found set, do not use a method that relies on a relationship.

 

46 minutes ago, Roark Holz said:

the 1st uses the ExecuteSQL method which won't work for me because it requires a newer version of FileMaker.

There are plugins to enable the use of SQL in earlier versions. But keep in mind that SQL ignores the found set, too.

 

46 minutes ago, Roark Holz said:

The 2nd requires a sort I'd rather not do so

Not sure why you don't want to do the sort. If necessary, you can sort the records as required, set a variable to the resulting count, then sort the records the way you want them.

 

Edited December 19, 2015 by comment

[Roark Holz]

1 hour ago, comment said:

You are not doing anything wrong. Relationships ignore found sets. If you want to summarize the found set, do not use a method that relies on a relationship.

 

There are plugins to enable the use of SQL in earlier versions. But keep in mind that SQL ignores the found set, too.

 

Not sure why you don't want to do the sort. If necessary, you can sort the records as required, set a variable to the resulting count, then sort the records the way you want them.

 

Thank you, you confirmed what I thought was happening. I didn't word what I said about not wanting to sort very well, I just meant that the other method seemed simpler to me. I'll go ahead and try it with the sort method and see if that works.

[Roark Holz]

On 12/19/2015 at 4:02 PM, comment said:

You are not doing anything wrong. Relationships ignore found sets. If you want to summarize the found set, do not use a method that relies on a relationship.

There are plugins to enable the use of SQL in earlier versions. But keep in mind that SQL ignores the found set, too.

Not sure why you don't want to do the sort. If necessary, you can sort the records as required, set a variable to the resulting count, then sort the records the way you want them.

 

I went ahead and tried to make this work using the "sort" method but am still running into problems. When I first open up the database and sort it by the proper field it does work. However, if I perform a search to limit to a date range, and then sort by the proper field the field that should contain the number of unique entries in the found set goes blank. Also, the field I am trying to calculate needs to be displayed in the footer of a layout that contains a list of all the records above it, however, the list needs to be sorted by date, not by the field I need to sort on to calculate this number. 

It seems as though I am trying to do something that seems very simple to me but I just can't get my head around what I need to do to make this work. I'm just trying to find a simple way to calculate the number of unique entries for one specific field in a found set.

Thanks again for your help.

[comment]

Could you post a simple file demonstrating the problem?

[Roark Holz]

8 minutes ago, comment said:

Could you post a simple file demonstrating the problem?

Absolutely, I am about to leave for the day and will need to customize the DB a bit to get rid of some personal information but I'll put something together and post it tomorrow. Thanks again for your generous willingness to help with this.

[Roark Holz]

21 hours ago, comment said:

Could you post a simple file demonstrating the problem?

I went ahead and created a simple file to demonstrate the problem but everything seems to work as expected in it. So I just added the additional fields I needed to it and so far so good. Not sure what I messed up originally but I think I'm pointed in the right direction now so won't waste any more of your time with this. Thanks again for your help.