12 Replies Latest reply on Apr 2, 2012 11:45 AM by comment

    Problem With Function

    gregmerriman

      Hi,

       

      I think my question may be too basic to bother you guys with but here's hoping !

       

      I have a small database which is used to evaluate examination results for students. They must pass five different subjects in order to qualify and there is a field for the status of each individual subject which shows whether or not (based on the mark awarded) they have passed. I also have a field for their overall status which should indicate that they have passed the entire course provided that the status of each individual subject is Pass.

       

      I have tried using the Case and If functions but have had no success in having the Overall Status field indicate the correct status (in fact it remains blank).

       

      What I suppose I am trying to achieve is for the calculation to evaluate the field Status_1 and if this indicates a pass to move on to Status_2, and so on through Status_3, Status_4 and Status_5. If it reaches the end without encountering a result other than pass then it would display the word "Pass" in the overall status field. If at any stage of the evalutaion process it discovered a status of Repeat then it would display the word "Repeat" in the overall status, or if it encountered to sit as the status during evaluation then it would enter "To Sit". Finally if there is no data in all of the evaluated fields then the calculation would return no result.

       

      Can anybody help with which function I should use to accomplish this ?

        • 1. Re: Problem With Function
          karendweaver

          Hi Greg

           

          One way would be to create a calculation field using the List function  and then look for the "Repeat" or "To Sit" statuses - that could be

           

          combinedStatusField = List ( Status_1 ; Status_2 ; Status_3; Status_4; Status_5 )

           

          Then you could evaluate using a case statement:

           

          Case (

          //1st test

          PatternCount ( combinedStatusField ; "Repeat") > 0 ; "Repeat" ;

          //2nd test

          PatternCount ( combinedStatusField ; "To Sit") > 0 ; "To Sit" ;

          //3rd test

          PatternCount ( combinedStatusField ; "Pass") = 5 ; "Pass" ;

          //default result - if none of the conditions are met

          "Something" )

           

          Remember that Case evaluates in order.  Whenever one of the tests returns True, the calculation does not continue - returns the result of the true statement and stops.  This may be an issue for you - what if a student has Repeat on one status and To Sit on another?  Should the status = Repeat or ToSit or something else?

           

          If you don't want to create an extra field - you could use the Let function to plan out your calculation - that is worth learning if you are not familiar.  But I am suggesting Case and List because they are foundation functions and you should get very comfortable with using them before moving on to more complex calculations.

           

          Hope this helps!

           

          Karen

          • 2. Re: Problem With Function
            Stephen Huston

            Another way to do this is with an auto-enter via calculation as the field definition for Overall_Status (a stored text field):

             

            Evaluate ( Quote ( Case ( Status_1 = "pass" and Status_2 = "pass" and Status_3 = "pass" and Status_4 = "pass" and Status_5 = "pass" ; "Pass" ; "" ) ) ; [ Status_1 ; Status_2 ; Status_3 ; Status_4 ; Status_5 ] )

             

            This will allow Overall_Status to be an Indexed/Stored text field which will update/edit itself as each of the other status fields is edited. This assumes they are all in the same table.

            • 3. Re: Problem With Function
              comment

              Stephen Huston wrote:

               

              Evaluate ( Quote ( Case ( Status_1 = "pass" and Status_2 = "pass" and Status_3 = "pass" and Status_4 = "pass" and Status_5 = "pass" ; "Pass" ; "" ) ) ; [ Status_1 ; Status_2 ; Status_3 ; Status_4 ; Status_5 ] )

               

              This will allow Overall_Status to be an Indexed/Stored text field which will update/edit itself as each of the other status fields is edited. This assumes they are all in the same table.

               

              If the fields are all in the same table, then the Overall_Status field can be a stored Calculation field. I can see no reason why you would want to make it an auto-entered Text field - and certainly no reason to complicate things by using the Evaluate() function.

               

               

              Of course, the other thing is that those fields should not be in the same table; instead, they should be individual records in a related one.

              • 4. Re: Problem With Function
                Malcolm

                I'm presuming that you have the subjects in a separate table to the students.

                 

                Let(

                results = list ( subjects::status ) ;

                case (

                not isempty( FilterValues ( results ; "Repeat" ) ) ; "Repeat" ;

                not isempty( FilterValues ( results ; "To Sit" ) ) ; "To Sit" ;

                "Pass"

                )

                 

                1 of 1 people found this helpful
                • 5. Re: Problem With Function
                  ian.moree

                  THe ever Diligent Michale Horak! Seems you preach the religious aspect of "DATA NORMALIZATION" everywhere man.

                   

                  LOL

                  • 6. Re: Problem With Function
                    gregmerriman

                    Hi Karen,

                     

                    Firstly, thanks to everyone for their help.

                     

                    Your solution worked beautifully. Thank you so much.

                     

                    Regards,

                     

                    Greg

                    • 7. Re: Problem With Function
                      FCallanan

                      gregmerriman wrote:

                       

                      They must pass five different subjects in order to qualify and there is a field for the status of each individual subject which shows whether or not (based on the mark awarded) they have passed. I also have a field for their overall status which should indicate that they have passed the entire course provided that the status of each individual subject is Pass.

                       

                      I have tried using the Case and If functions but have had no success in having the Overall Status field indicate the correct status (in fact it remains blank).

                      Hi Greg,

                      In approaching a similar problem, I built the rules into the relationships. The rules included: GED students need to pre-test, to pass each post-test, and to exceed a minimum total on all 5 exams to qualify. The point is the rules, like yours, were a bit complex.

                      As you did, I separated each individual subject's result from the overall result calculation (e.g. 6 Student table fields). Because of the relationship, the subject mark field is only evaluated within the context defined by the "rules" (in my case, the "GED_post" context). I'm not sure of your structure, but this did require me to "normalize" the GED scores: that is, I converted the db's original structure --a repeating field with 5 reps-- into separate "GED" records. (An added benefit here was the ability for any student to sit for the same exam multiple times.)

                      GED_Post_test.png

                       

                      Then the result formula (in the Student table) is simplified. First evaluate each test, then get the overall result:

                         If(GED_writ::Score_ss ≥ 410 and

                           GED_soc::Score_ss ≥ 410 and

                           GED_sci::Score_ss ≥ 410 and

                           GED_math::Score_ss ≥ 410 and

                           GED_lit::Score_ss ≥ 410 and

                           GED02total ≥ 2250; "Pass";"Fail")

                       

                      Because the scoring is in its own table, the status thing for each exam is less of a problem. It can be calculated or entered in the (GED) exam line item via a portal. In my example, the "status" is represented by the "≥ 410" test, although another field reports "Pass" to the interface.

                       

                      My hope is that this gives you a manageable path to solve your problem.

                       

                      Frank Callanan

                      Data Pro North East

                      Camden, Maine, USA

                      1 of 1 people found this helpful
                      • 8. Re: Problem With Function
                        Stephen Huston

                        Hi Michael,

                         

                        I pointed out that my option would  work only if all fields were in the same table, and, while I agree with you that those status fields should be one field in a related child table, the fact that those 5 fields have different names strongly suggests that they actually are in that same table -- not optimal but very common in spite of the beauty of data normalization.

                         

                        You and I have differed over using the Evaluate function before in other threads. I prefer to use it over a simple calculation, for the same reason I always use Case instead of If functions, even when I have only 1 test, because Evaluate allows the addition of trigger fields which are not named in the functions themselves -- an option that may be valuable down the road (just like adding other tests later to a single-test Case function.

                         

                        We differ in our approaches, but both work. One of the joys of FileMaker -- more than one way to accomplish most results. The more methods get passed along in this forum, the better armed we all are for our next problem. The fact that something can be done more than one way, doesn't make any method that works wrong. It depends a lot on one's reasons or preferences for the methods. I like to use things that can be scaled or expanded for the future even before I know when or why that may be needed; admittedly not always the simplest to write, but I prefer it to be in place in case.

                         

                        Any obvious example of differing approaches in FM is peer-to-peer hosting of files. I happen to feel strongly that it should never be done, but FMI supports it, the product supports it, and there is a forum here sponsored by FMI to help manage the issues of using it. I think it's a terrible approach to hosting, and I probably say so more often than people want to hear, but it's clearly not wrong in any absolute way -- just another method, though one I prefer to avoid. That being said, I have actually installed a peer-to-peer "server" for one client back in FMv5 days. It worked beautifully for them on a dedicated machine.

                         

                        Meanwhile, you are probably right... but, in the FM world, that doesn't make other ways wrong. 

                         

                        Stephen Huston

                        • 9. Re: Problem With Function
                          comment

                          You can always argue that there are several ways to reach the same result. But some ways are more efficient than others, and sometimes - as in this case - the "other way" is merely an added complication that serves no purpose. In particular, I took issue with your claim that:

                          Stephen Huston wrote:

                           

                          This will allow Overall_Status to be an Indexed/Stored text field

                           

                          There is nothing in your addition of Evaluate() that "allows" the field to be indexed or stored. It can be stored and indexed just as well using only this part =

                           

                          Case ( Status_1 = "pass" and Status_2 = "pass" and Status_3 = "pass" and Status_4 = "pass" and Status_5 = "pass" ; "Pass" )

                           

                          Note also that all the "trigger fields" are already referenced in the function. Even in your version, adding the optional trigger fields is entirely redundant.

                           

                          All  these "extras" come with a cost: they need to be processed. For the user, it means more time and higher electricity bills. There is also cost to the developer, as superfluous calculations take more time to debug and maintain.

                          • 10. Re: Problem With Function

                            Stephen Huston wrote:

                            Evaluate allows the addition of trigger fields which are not named in the functions themselves -- an option that may be valuable down the road (just like adding other tests later to a single-test Case function.

                             

                            I like to use things that can be scaled or expanded for the future even before I know when or why that may be needed; admittedly not always the simplest to write, but I prefer it to be in place in case.

                             

                            Sure.  I believe in contingency as well.  So always wrap your calculations with Let ( trigger = "" ; (calc) ) and only specify a trigger if you need one.  Triggers can be Field1Field2Field3 even.

                             

                            But using Evaluate() is very costly. 

                            • 11. Re: Problem With Function
                              Stephen Huston

                              I am unclear why you think using Evaluate for auto-entry options is more costly than using any other auto-enter by calculation method where the result will be a stored regular field, not a calculation field. I would agree if the field were to be a calculation field, but this is for auto-entry only.

                               

                              Auto-enter by an Evaluate-calculation is the only method I know, other than script triggers, which will allow a regular text, date, or number field to store the result of a calculation which requires data from a look-up table (related data) to be evaluated to obtain the stored  results. As such, it is a powerful and important tool, which has no higher overhead for auto-enter than any other method, as far as I can tell.

                               

                              I am not saying it is always the right choice, but it certainly offers power not otherwise available.

                              • 12. Re: Problem With Function
                                comment

                                Stephen Huston wrote:

                                 

                                Auto-enter by an Evaluate-calculation is the only method I know, other than script triggers, which will allow a regular text, date, or number field to store the result of a calculation which requires data from a look-up table (related data) to be evaluated to obtain the stored  results.

                                 

                                Stephen,

                                 

                                I am afraid you are very much mistaken about this. There is no "Auto-enter by an Evaluate-calculation" method. The behavior of a field set to auto-enter your formula will be exactly the same as another field set to enter the same formula without the Evaluate() part. 

                                 

                                Actually, I already said the same thing in post #9, didn't I?  Anyway, if you disagree, please post an example that would demonstrate a difference between the two.