UniqueValue_Sum - Inconsistent inaccurate counts

[TrulySimple]

I have set up the following fileds:

ITM_item::c_UniqueValue_Category | Calculation | Min ( _ID ) = itm_ITEM__selfjoin_Category::_ID

ITM_Item::s_UniqueValue_SUM_Category | Summary | Total of c_UniqueValue_Category

I am using the result of ITM_Item::s_UniqueValue_SUM_Category to allow a script to navigate to the correct subsummary report layout. Where there are there are ≤ 3 unique occurrences in the field Category the script will navigate to the Item detail report, else navigate to the listing with Category & SubCategory Items to further drill down by SubCategory.

This is in a separation model with an interface and data file.

The trouble is that ITM_Item::s_UniqueValue_SUM_Category field is a large part of the time showing the correct count of unique occurrences in the field Category. Sometimes it is 0, others will show 3 when there are in fact 5 unique occurrences in Category.

I was having a similar problem with the respective version of the above mentioned fields based on SubCategory. I thought I fixed it on the SubCategory version by changing the relationship to a multi-predicated self join matching on: (SubCategory = SubCategory and Category = Category ). This has worked for the majority of the tests, but still has an anomaly here and there where the result is below the true count of unique occurrences in the field SubCategory.

What am I missing?

Any help in solving this would be very much appreciated!! : )

[comment]

Min ( _ID ) = itm_ITEM__selfjoin_Category::_ID

This expression will return 0 for all records in the category when the first related record is not in the current found set.

BTW, the expression Min ( _ID ) is the same as _ID - assuming _ID is not a repeating field.

[TrulySimple]

Min ( _ID ) = itm_ITEM__selfjoin_Category::_ID

This expression will return 0 for all records in the category when the first related record is not in the current found set.

BTW, the expression Min ( _ID ) is the same as _ID - assuming _ID is not a repeating field.

Thanks for you reply.

I am a bit confused. The formula "Min ( _ID ) = itm_ITEM__selfjoin_Category::_ID" for the field is actually in the data file since the interface file has no fields. So...I am not sure if this affects this. I got the formula from the FileMaker help Counting the Unique Values in a Field: http://help.filemaker.com/app/answers/detail/a_id/3423/kw/Subsummary%20Calculations I was not sure if "Min ( _ID )" was really doing anything in this case.

Is there a solution to this for me to get an accurate count on the unique occurrences of the field Category in my subsummary report?

[comment]

I got the formula from the FileMaker help Counting the Unique Values in a Field: http://help.filemake...%20Calculations

FileMaker's KB is, unfortunately, not the best source for techniques. You can test my theory very easily: go to any record that is flagged as unique and omit it from the found set.

See here for an alternative that respects the found set:

http://fmforums.com/forum/topic/61158-number-of-employees-from-payroll-report/page__p__289204#entry289204

[TrulySimple]

Wow! Thanks that works great! funny thing was I had the formula for the InverseCount in my data viewer from some experimenting. I had seen that the count of the actual records in the group would be consistent and the fraction there of ( InverseCount ) as well. I had just not put 2 and 2 together. :)