We use 6 domains for evaluating our trainees. Each domain has 2-7 categories. As the requirements evolve from accrediting agencies it is reasonable to expect additional categories in these domains. I have to calculate the average, median and skew for each domain. Calculating median, which is possible with FM, requires creating an array of numbers in one single field of every record, sorted in ascending manner. Sorting is not possible in a single field.

There are calculations available at other websites but these didn’t work well; inexperience, perhaps. So I created an array of sorted numbers as follows:

I created a new FM Table and a Layout with Name (ID) of the trainee and a generic number field. Using the import function, I imported these into as many records as there are categories for one single trainee, yielding 4 records of individual categories in a domain for a trainee, say in Professionalism. Then I sort the number field in ascending manner and reimport these values into a single field using a comma and space as separators. So the values are sorted in an ascending manner. This works well for one record at a time but I could not make it work through a Loop function. Also, I need to write as many sets of steps as there are categories to reimport the sorted values. If the number if categories increases then I have to have as many steps at that time.

Example and Question (Please see the Table below using 7 categories in Intrpersonal skills):

I would be grateful for your help.

Kesavan

Actually, calculating the median just requires a sorted list of numbers; how you get this list and where you store it (or if you have to store it at all) are implementation details. The better normalized your data structure, the easier the solution

So I don't know your

exacttable structure, but you probably should have each numeric response in a record that is related to a Trainee (or even better, a TraineeAssessment) record and “knows” its category; then this can be done quite easily by using eSQL.See the attached sample file (where obviously a question should come from a Questions table, where each question belongs to a category; but that's beside the point here …)