Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Giving text (from a pop up menu) a numerical value


Recommended Posts


I'm new to this forum, and hoping someone can help me! I am a teacher, and as a Filemaker Pro novice, I've been asked to help my colleagues use Filemaker to create and use a report card. In my school, we give kids a letter grade on up to 20 criteria for each subject. Each of those criteria is a field, and we use a pop up menu to select the value of the field.


For example, the social studies report card is a page long and has 12 different grades on it:


1. Student is able to make sense of new information: A

2. Student is able to use evidence to support ideas: B

3. Work is complete and on time: D

Social studies grade=average of all categories.


I would like the database to automatically convert the letter grade into it's corresponding number (For ex: "B" = 85). I would like the database to then use the converted number in a calculation to calculate averages.


I've currently set this up using the "Case" function, but each field has to be created individually for each of those many different grades. Not efficient! Is there an easier way?


I'd appreciate any suggestions you have! Thank you,


Link to comment
Share on other sites

Well, you could create a table of courses, and then create a global field in your student table that has a dropdown value list of all the courses in Courses to pick from, and that would let you create records in yet a third table, a table linked to Students by the combination of StudentID and g.Course. A portal would show all the courses for that specific student and what grade the student received, and you'd only need the fields to be defined one time around. (And you add courses later, you don't have to dive back in and define yet more fields, either).

Link to comment
Share on other sites

Hi Heather,


I'm unsure of the grade scoring you use but here's another idea:


Create a calculation (number) called cPoints with:


Choose ( Position ( "FDCBA" ; Grade ; 1 ; 1 ) - 1 ; 40 ; 55 ; 70 ; 85 ; 100 )


The blue are your grades beginning with F. Determining the average grade per student would require a join between the unique identifier per student (EnrollmentID or a unique FM serial). It can be a join to another table or a selfJoin but either way would be something like:


thisTable::uniqueID = thatTable::uniqueID


Then use a calculation (number) :


Sum ( thatTable::cPoints) / Count ( thatTable::uniqueID)


Each criteria should NOT be a field but rather a RECORD. You are really restricting yourself (both on the number of questions, ie, a new added criteria must be added as a field and every calculation accordingly adjusted). In addition, reporting will be pain in behind. Are these repeating fields?


As pointed out, a good structure can make all the difference here and we have no idea what you have right now.

Link to comment
Share on other sites



I'm kind of understanding what you mean (and I know that the way I've set it up is NOT an efficient way), but I'm not sure how to change it.


Here's a better example of what we've got:


Student: Jane Smith

Social Studies



1. Work is Purposeful: A

2. Selects appropriate resources: B

3. Takes effective notes: B

4. Makes sense of information: B

5. Uses information effectively: A



1. Asks appropriate questions

2. Develops interpretations

3. Supports interpretations with evidence.


Section III: Work Habits

1. ...

2. ...

3. ...


The numbers above are each separate text fields, with a pop-up menu to choose the grade A,B,C,D,F.


Then, I've created a separate field to convert the text to a number:


NUMBER Work is purposeful= Case(Work is purposeful="A"; 100; Work is purposeful="B"; 90; Work is purposeful="C"; 80; Work is purposeful="D"; 65; Work is purposeful="F"; 55)


NUMBER Make Sense of Information= Case(Make sense of information="A"; 100; Make sense of information="B"; 90; Make sense of information="C"; 80; Make sense of information="D"; 65; Make sense of information="F"; 55)


and on and on for each of the 11 different lines. !!!


Then, each section gets its own average:

Research average= Average(NUMBER work is purposeful; NUMBER Select app resources; NUMBER take notes ; NUMBER Make sense of info; NUMBER uses info effectively)


Finally, the three sections are averaged together (they carry different weights) for the total Quarter Grade:

Round((Summary of Research*.3) + (summary of Analyzing Info*.4) + (summary of work habits*.3); 0)

And that's just the first marking period!


I know there is a better way. Could you explain more clearly how to set up the table? I'm confused about how each grade would be a separate record. Maybe there is a section of the guide you can point me towards to help me teach myself a new function? Thank you so much,


Link to comment
Share on other sites

A lot of times you start off putting individual fields in the local record because it seems like a good idea at the time, and/or it doesn't occur to you to do otherwise. Street address, city, state, ZIP, all separate fields, right? Or in your case, Work is Purposeful grade, Selects appropriate resources grade, etc.


But once you want to reference other related material or run categorized reports on the different fields you've created, having them as local separate fields like that just makes things horribly difficult. You have to get out of that architecture. You're hitting the wall now with the need to convert letter grades to numerical grades, but if you hadn't hit it here you would have hit it later with some other desired function that would just be too klunky with each graded category having its own dedicated grade field.


You need a separate Table, let's call it Grades, and it's going to have a Student ID field in it that joins it to the Students table: Student ID::Student ID. Set the relationship to allow records to be created through the relationship on the Grades side. The Grades table also has a Grading Category field, and a Grade field, of course, where you enter the grade itself, and one calculation field to convert the letter grade to a numerical equivalent.


Then you create a custom value list of Grading Categories, and you create a portal on the Students page, a portal to Grades, put the custom value list on the Grading Category field, and the Grade field next to it.


If you want to generate the list of grading categories for each student in the same order each time, you can create a script that executes when you're first working on the grades for each student, a looping script that goes to the portal and "walks through" all the values in the value list until it has made a related record for each of the grading categories:


Set Variable [$Pos, 1]

Set Variable [$Cats, "¶"&ValueListItems(Get(FileName), "Grading Categories")&"¶"]

Go to Portal Row [First]


Exit Loop If [GetAsNumber($Pos+1) > PatternCount($Cats, "¶")]

Set Field [Grades::Grading Category, Middle($Cats, Position($Cats, "¶", 1, $Pos)+1, Position($Cats, "¶", 1, $Pos+1)-Position($Cats, "¶", 1, $Pos)-1)]

Go to Portal Row [Next]

Set Variable [$Pos, GetAsNumber($Pos)+1]

End Loop

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use