9 Replies Latest reply on Mar 24, 2016 10:21 AM by fitch

# Calculation Help - Calculation (Case Function)

Hello,

I need to create a calculated Pass Fail Field based on a passing grade located in another Table by course. ( I am open to a better way if there is one)  I am not sure how to create the case function to put Pass or Fail in the field based on data from another Table.

Example:  Student 1 passes Performance test based on the score in Table two.

Student 1 Passes Written test based on the score in Table Two.

Overall Passed (because they passed both Tests)

Student 2 Failed Performance test based on score in Table Two.

Student 2 Passed Written test based on score in Table Two.

Overall Failed (based on failing one part of the test.)

Student 3 Passed Performance test based on score in Table Two.

Student 3 Failed Written test based on score in Table Two.

Overall Failed (based on failing one part of the test.)

Table One

I have fields named:

Last Name

First Name

Test Name

Test ID

Performance Score

Written Score

Pass or Fail Preformance

Pass or Fail Written

OverAll Pass or Fail

Table Two  ( Note -  Test Name and Test ID are the same in table one)

I have fields named:

Teat Name

Test ID

Performance Score

Written Score

Thank You in advance for the help.

Jeremy

• ###### 1. Re: Calculation Help - Calculation (Case Function)

looks like:

- passing means passing both tests;

- passing one test is not concretely related to a specific score.

Is this correct ? (as opposed to a total score, maybe weighted with written = 1.25 * performance, which would be compared against a threshold)

• ###### 2. Re: Calculation Help - Calculation (Case Function)

I'm not sure about your structure being correct (or flexible), but you can try this: use calculation fields, type number, as

Pass or Fail Performance =

Performance Score >= Table Two::Performance Score // True if passed

Pass or Fail Written =

Written Score >= Table Two::Written Score // True if passed

OverAll Pass or Fail =

Pass or Fail Performance and Pass or Fail Written // // True if both are True

You may want to change the field names to “xyzPassed”; now you can use layout formatting in the Inspector, Data tab, option Boolean, to display Pass for True, and Fail for False.

Note that these fields are par force unstored (referencing related fields) which can be a performance drag; since the result won't change after the facts (the score is a fact, and the pass threshold at that time is a fact), you may want to use regular, storable number fields and set them for once and all via script.

btw, if your tables are really named One and Two, please come up with something better ;-)

• ###### 3. Re: Calculation Help - Calculation (Case Function)

Seems like the Table 2 scores are related to 'tests' and not to specific names or am I missing something? A person can take a test and one test can have many students? or was that an abbreviated list of table field names and the Testee ID was left out?

• ###### 4. Re: Calculation Help - Calculation (Case Function)

Like others, I suspect you have a many-to-many situation between TableOne (students) and TableTwo (tests). To resolve this you will need a join table (Performance). With this structure you can include a pass mark for each test in the Tests table, and an actual mark attained by each student in the Performance table; then a simple comparison between the two will reveal whether any given student passed a given test at a given sitting. Back in the Student table you should then be able to make a simple calc to show an overall pass.

• ###### 5. Re: Calculation Help - Calculation (Case Function)

That is correct. Not passing one test is a failure.

• ###### 6. Re: Calculation Help - Calculation (Case Function)

Table 2  contains just the Test name, Test Number, and Passing Scores of Written and Performance Tests.  There are multiple tests.  A Student can take more than one test.

Jeremy

• ###### 7. Re: Calculation Help - Calculation (Case Function)

lovelandj wrote:

Table 2  contains just the Test name, Test Number, and Passing Scores of Written and Performance Tests.  There are multiple tests.  A Student can take more than one test.

And since one test can be taken by many students, you have the classic many-to-many scenario – which leaves you one table short. Consider:

Student --< StudentTest >-- Test

where Testperformance holds foreign keys for Student and Test, and of course the score(s).

Now failed or passed is not an attribute of a student, but of a student in a test. You can compare these scores with the threshold values in the Test parent record to calculate the individual (written/performance) and overall status.

• ###### 8. Re: Calculation Help - Calculation (Case Function)

Good morning lovelandj,

I hope your day is going well. Many others have already made good suggestions regarding ways you may want to restructure the schema of you solution, so I won't add to that pool of advice. However, here is a Case() function that should do what you want after you have sorted out any other issue you may be having - use the following as a calculation in your OverAll Pass or Fail field:

Case (

Pass or Fail Performance = "Passed" AND Pass or Fail Written = "Passed"; "Passed";

"Failed"

)

You'll have to adjust the syntax to match your actual field names, and it assumes the values in your Pass or Fail fields are text, but I think this would work. Have a great day!

God bless,

Bill

• ###### 9. Re: Calculation Help - Calculation (Case Function)

This can be done in all kinds of ways. E.g.,

Pass or Fail Performance = Performance Score > 70  // or whatever your cutoff is

Pass or Fail Written = Written Score > 70

OverAll Pass or Fail = Pass or Fail Performance + Pass or Fail Written = 2

The first two fields will evaluate to 1 if true, and then you just add them up.