4 Replies Latest reply on Dec 10, 2013 9:32 AM by philmodjunk

    Counting fields with unique values



      Counting fields with unique values



           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?





        • 1. Re: Counting fields with unique values

               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 surprise

               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.

          • 2. Re: Counting fields with 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.


            • 3. Re: Counting fields with unique values

                   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.


              • 4. Re: Counting fields with unique values

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