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:
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.
Thank you Phil, I will try that.
The reason for all the fields is that its a scoring application for archery. Each record is a round of arrows shot on a certain date. For that given date, the user may shoot up to 144 arrows and each of those arrows gets a score between 0 and 11. There are also an additional 144 fields that keep track of where the arrow hits (determined by using numbers from 1-12 as in the face of a clock)
After all the arrows are shot, the user can see how many arrows scored 1, or 2 or 10 and where they landed.
I just had another thought.
Is it possible to check the value of each field on exiting the field and then, depending on that value, increment the field that shows the total for that value?
So, if the user enters "8" a script will increment the field that shows the total number of "8" scores by one.
The reason for all the fields is that....
To me, that argues even more strongly for setting up a related table where each related record is a record for one arrow fired by the user on that date. Such data very, very often needs to be used in statistical analysis and various summary reports and this will be difficult to impossible to do wit individual fields in the same record instead of as a set of related records.
Often, people want to use either separate fields or repeating fields for this because they want to design the layout such that you get a row or grid of fields and each such item (an arrow in your case) is recorded in a different column of that row. If you need that, it is still possible to set up with a table of related records by using something called a "horizontal portal".