7 Replies Latest reply on Oct 19, 2010 10:37 AM by Nerico_1

    Need Help with script to create records for histogram

    Nerico_1

      Title

      Need Help with script to create records for histogram

      Post

      I'm very new to scripting and need help creating a script that will generate records one table based on the data on an existing table.  The end result is to be able to create e histogram of exam grades.  Since each exam has a different number of questions, the possible scores (percent correct) for each exam will vary.  The main grades table has all student scores.  The second table just has 3 fields, a foreign key to identify the particular exam, a field to capture each of the actual scores and a field to keep count of each score.

      For example, the most recent exam has:

      3 records with 66.67%

      4 records with 68.75%

      2 records with 70.83%

      etc... till

      6 records with 100.00%

      these are the values that would go into the count and score fields on this second table, respectively.

      Any help on creating a script for this will be much appreciated.  Even help in letting me know whether I'm even going about solving this problem in a useful manner vs. making things more complicated than they need to be would be great.  I've tried so many dead ends, my head is about to explode.

        • 1. Re: Need Help with script to create records for histogram
          philmodjunk

          Heres a demo file of one possible approach:  http://www.4shared.com/file/WhIXK_hZ/HistogramDemo.html

          HistogramDemo is a table limited only to fields that serve as relational keys to link to specific records in scores. You can delete all records in this table without affecting any real data. You enter the minimum and maximum scores, an exam ID and specify the desired interval. You then create records in this table until the last record created is greater than or equal to the maximum score. Then switch to the last layout to see a chart of the resulting historgram. The scores table lists sample data of possible scores.

          To see the chart, you must have FileMaker 11.

          • 2. Re: Need Help with script to create records for histogram
            Nerico_1

            I downloaded the demo.  I looks good but my question is, how do you add records to the HistogramDemo table for each new exam?  manually or thru a script?

            • 3. Re: Need Help with script to create records for histogram
              Nerico_1

              Please disregard the last post.  I looked further into the table and field definitions and realized you are using a global field.  It works beautifully.  But I'm having a hard time wrapping my head around how it works.  The cCount calculation works almost like a countif() function which I have not been able to easily duplicate on FM.  Can you explain the mechanics behind it?

              • 4. Re: Need Help with script to create records for histogram
                philmodjunk

                All aggregate functions such as count(), Sum(), average() etc. when used to reference a field in a related table, compute the specified aggregate value of all related records. So Count returns the number of related records were the specified field is not empty.

                The inequalities in the defined relationship, will match a given histogram record to exam scores in a given range of values--that gives you the "if" part of the process here.

                This demo is a very stripped down proof of concept. You may need to modify it to better suit your purposes. You could, for example, make ExamID a local field and use replace field contents to put the same ID value in all the records of the current found set. This would enable you to create sets of histogram records that each pull data for a given exam from scores. That way a simple find to find all histogram records with a given exam ID can be used to pull up a histogram chart for each exam once you've created the necessary histogram records for it.

                • 5. Re: Need Help with script to create records for histogram
                  Nerico_1

                  I've already incorporated into my Db and I'm very happy with the result.  I put the graph on a tab on a layout based on the exams table.  I created a script to update the gSelectedExam field to match the current exam on record load.  I will have to show it to the course director and make a few more tweaks, but otherwise it's perfect.  BTW.  Do you share these as freeware or shareware?

                  On the "if" part.  Do you mean that if the relationship had been set with an equal sign and a single field on each table, the count function would return 1 but setting up the <= an >= ranges in the relationship makes each record on the histo table look at several records in the scores table and therefore the count function returns a true count?

                  • 6. Re: Need Help with script to create records for histogram
                    philmodjunk

                    If you used an = operator instead of two field pairs with < and >, you'd match only scores with exactly that value rather than a range of scores falling in that record's interval so count would then have a much smaller number of related records to count.

                    Any link I post to a File I have uploaded to a share site is Freeware.

                    Heres a link to a database file of known Filemaker Pro and GO bugs that has become my most popular download: 

                    http://www.4shared.com/file/8orL8apk/FMP_Bugs.html

                    And yes, it's also "freeware" ;-).

                    • 7. Re: Need Help with script to create records for histogram
                      Nerico_1

                      That's very generous of you.  Thanks again!