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.