### Title

Counting fields with unique values

### Post

Hello,

I need some help in figuring out how to do specific count.

I have a record with 144 individual number fields that each have a value between 1 and 12. I would like to get a count of how many fields have the value "1" or "2" or "8" etc. and then put that count in a separate field, so that the user can see how many fields have that value.

I only need this for the record being currently displayed

I can't seem to find any function that does that.

Can anyone help?

Thanks

Joe

Well your 144 individual fields should be 144 individual records in a related table. Then counting the unique values is pretty simple and you can gain a number of other advantages from the increased flexibility.

But now that I think about this and the morning caffiene kicks in, there is a way that you can do this from your current data model:

Let's assume that you have a field that can serve as a primary key--it must be a field such as an auto-entered serial number field that uniquely identifies each record in your table.

Set up this self join, one to one relationship using a new table occurrence of this table:

YourTable::__pkYourTableID = YourTable 2::__pkYourTableID

Define a calculation field, cListedValues as:

List ( Field1 ; Field2 ; Field3 ; .... Field144 ) ---> You have to list all 144 fields here

Select Text as the result type and it must be a stored indexed calculation field.

Define a conditional value list to use for counting unique values:

Select the "use values from a field" option and set it to list values from cListedValues from YourTable 2.

Select the "include only related values starting from YourTable option.

Now this calculation: valueCount ( ValueListItems ( Get ( FileName ) ; "YourValueListNameHere" ) )

will return the number of unique values.