10 Replies Latest reply on Jul 23, 2013 8:46 AM by philmodjunk

    Help with a CASE calculation, please?

    BonnieHammond

      Title

      Help with a CASE calculation, please?

      Post

           I am trying to write a formula that will calculate points for a horse show. Points are based on the placing in the class and the number of exhibitors in the class. The formula I've written works perfectly for first place (ie. the point value goes up as the number of exhibitors increases, just like I want it to) but it doesn't quite work for second through fifth place (ie., it returns the number of points for up to 8 exhibitors no matter how many exhibitors are in the class) 

           I can't for the life of me figure out what is wrong. Can anyone help, please?

      Point System Used to Calculate High Points

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                          

                                

                     
                          

                               Number of exhibitors in the class

                     
                          

                                

                     
                          

                                Up to 8

                     
                          

                               9-16

                     
                          

                               17-24

                     
                          

                               25 +

                     
                          

                               1st Place

                     
                          

                               10

                     
                          

                               15

                     
                          

                               20

                     
                          

                               25

                     
                          

                               2nd Place

                     
                          

                               6

                     
                          

                               10

                     
                          

                               15

                     
                          

                               20

                     
                          

                               3rd Place

                     
                          

                               4

                     
                          

                               6

                     
                          

                               10

                     
                          

                               15

                     
                          

                               4th Place

                     
                          

                               2

                     
                          

                               4

                     
                          

                               6

                     
                          

                               10

                     
                          

                               5th Place

                     
                          

                               1

                     
                          

                               2

                     
                          

                               4

                     
                          

                               6

                     

           Here is the formula I am using:

           Case(Placing = 1 and Entrants <=8; "10"; Placing = 2 and Entrants <=8; "6"; Placing = 3 and Entrants <=8; "4"; Placing = 4 and Entrants <=8; "2"; Placing = 5 and Entrants <=8; "1"; Placing = 1 and Entrants >=9 and Entrants <=16; "15"; Placing = 2 and Entrants >=9 and Entrants <=16; "10"; Placing = 3 and Entrants >=9 and Entrants <=16; "6"; Placing = 4 and Entrants >=9 and Entrants <=16; "4"; Placing = 5 and Entrants >=9 and Entrants <=16; "2"; Placing = 1 and Entrants >=17 and Entrants <=24; "20"; Placing = 2 and Entrants >=17 and Entrants <=24; "15"; Placing = 3 and Entrants >=17 and Entrants <=24; "10"; Placing = 4 and Entrants >=17 and Entrants <=24; "6"; Placing = 5 and Entrants >=17 and Entrants <=24; "4"; Placing = 1 and Entrants >=25; "25"; Placing = 2 and Entrants >=25; "20"; Placing = 3 and Entrants >=25; "15"; Placing = 4 and Entrants >=25; "10"; Placing = 5 and Entrants >=25; "6")

            

           Here's the same formula broken into lines so it's easier to read:

           Case(

           Placing = 1 and Entrants <=8; "10";

           Placing = 2 and Entrants <=8; "6";

           Placing = 3 and Entrants <=8; "4";

           Placing = 4 and Entrants <=8; "2";

           Placing = 5 and Entrants <=8; "1";

           Placing = 1 and Entrants >=9 and Entrants <=16; "15";

           Placing = 2 and Entrants >=9 and Entrants <=16; "10";

           Placing = 3 and Entrants >=9 and Entrants <=16; "6";

           Placing = 4 and Entrants >=9 and Entrants <=16; "4";

           Placing = 5 and Entrants >=9 and Entrants <=16; "2";

           Placing = 1 and Entrants >=17 and Entrants <=24; "20";

           Placing = 2 and Entrants >=17 and Entrants <=24; "15";

           Placing = 3 and Entrants >=17 and Entrants <=24; "10";

           Placing = 4 and Entrants >=17 and Entrants <=24; "6";

           Placing = 5 and Entrants >=17 and Entrants <=24; "4";

           Placing = 1 and Entrants >=25; "25";

           Placing = 2 and Entrants >=25; "20";

           Placing = 3 and Entrants >=25; "15";

           Placing = 4 and Entrants >=25; "10";

           Placing = 5 and Entrants >=25; "6")

        • 1. Re: Help with a CASE calculation, please?
          jmw

               I tested your calculation and all seems well; provided both Placing and Entrants are of type Number and the calculation returns a number. Are they all Numbers? If so, I'd say your calculation should work.... 

               Trying different things I have to say that the calculation either worked or it did not, I could not get it to work for placing 1 and not for the others. So there might be something else amiss. If the types are correct I'd suggest a quick try by testing the three fields in a brand new table, which is what I did and there it worked just fine. Leave all the field options set to the default values, except of course the type of the field (set to Number).

               As a side note, you can have the 'broken-in-lines-version' of the calculation entered exactly like that in the calculation. It will make an easer read indeed when you revisit the fhe field sometime in the future.  

               Cheers,

               Jan Martin Wagenaar

          • 2. Re: Help with a CASE calculation, please?
            philmodjunk

                 Like Jan, I suspect that your Entrants field is either text or a calculation field defined with text as a result type instead of number.

                 Three additional comments:

                 1) You can orgainize your calculation in the specify calcualtion dialog just like you have it where you have reformatted it in your post to make it easier to read. That can make it easier to work with should you need to make changes to this calculation in the future.

                 2) You can simplify your calculation to be:

                 Case(

                 Placing = 1 and Entrants <=8; 10;

                 Placing = 2 and Entrants <=8; 6;

                 Placing = 3 and Entrants <=8; 4;

                 Placing = 4 and Entrants <=8; 2;

                 Placing = 5 and Entrants <=8; 1;

                 Placing = 1 and Entrants <=16; 15;

                 Placing = 2 and Entrants <=16; 10;

                 Placing = 3 and Entrants <=16; 6;

                 Placing = 4 and Entrants <=16; 4;

                 Placing = 5 and Entrants <=16; 2;

                 Placing = 1 and Entrants <=24; 20;

                 Placing = 2 and Entrants <=24; 15;

                 Placing = 3 and Entrants <=24; 10;

                 Placing = 4 and Entrants <=24; 6;

                 Placing = 5 and Entrants <=24; 4;

                 Placing = 1 and Entrants >=25; 25;

                 Placing = 2 and Entrants >=25; 20;

                 Placing = 3 and Entrants >=25; 15;

                 Placing = 4 and Entrants >=25; 10;

                 Placing = 5 and Entrants >=25; 6 )

                 Please keep in mind, however, that this should not change the results you are getting. It just removes uneeded details that do not change what results are returned.

                 3) Finally, you may not want to use a case function for this at all. If the criteria that control what results are returned should ever change, you have to redefine your calculation inorder to update the results returned. You can make a table where this data is stored and use a looked up value setting to look up the correct result from this table. Then changes to the criteria become a data entry task that does not require redefining the calculation field to implement.

            • 3. Re: Help with a CASE calculation, please?
              BonnieHammond

                   Thank you both so much for your help. I used the revised calculation that PhilModJunk provided and now I find it only works for the 5th place values. I did try it out in a new database file, and it works fine there. If this were a website, I'd say that there is a bug or a conflict in there somewhere! :) 

                   I do like the idea of using a Looked-Up Value setting instead of a CASE function, so I am going to try to puzzle out how to make that work instead. Again, thank you both so much for your help, I really appreciate it!!

              • 4. Re: Help with a CASE calculation, please?
                philmodjunk

                     If if works in your new file, this is most likely due to one of the fields invovled being defined in the new file to have a number data type where your original file defines it as a field fo type text.

                • 5. Re: Help with a CASE calculation, please?
                  BonnieHammond

                       The fields for Entrants, Placing, and Overall Points (where this calculation lies) are all set to Number. Here's a screenshot of the settings.

                  • 6. Re: Help with a CASE calculation, please?
                    BonnieHammond

                         As an experiment, I changed Overall Points from a Number to a Calculation...and it still works the same way it originally did: it returns results for 1st through 5th place, but only 1st place changes when the number of Entrants changes.

                         Just in case this matters, I made sure to put the number of entrants in first. It delivered 1st place correctly, but 2nd-5th it gave me the value for 8 riders or less. So, same problem.

                    • 7. Re: Help with a CASE calculation, please?
                      philmodjunk

                           Having a number field for the number of entrants instead of a calculation field seems very problematical as this requires you to enter the correct number of entrants repeatedly, once into every record. A calculation field that computes the number or entrants and has number specified as the result type would seem a far better way go.

                           Are you still testing this in your original file or are you getting problems in your test file also?

                           If this problem occurs only in your original file, check some things for me:

                           Is the data entered into entrants ONLY a number or might other text also be entered into this field?

                           Have you checked your layout design to make sure that you are modifying the correct field when you change the number of entrants and the calculation field fails to produce the correct result?

                           If you still can't get it to work, Recover the file and see if the recovered copy correctly evaluates this calculation field. If that works, please note the following:

                           Things to keep in mind about Recover:

                           While Recover almost always detects and fully corrects any problems with your file...

                             
                      1.           The recovered copy may behave differently even if recover reports "no problems found".
                      2.      
                      3.           Recover does not detect all problems
                      4.      
                      5.           Recover doesn't always fix all problems correctly
                      6.      
                      7.           Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.

                            

                           And here's a knowledgebase article that you may find useful: What to do when your file is corrupt (KB5421).

                      • 8. Re: Help with a CASE calculation, please?
                        BonnieHammond

                             I FIGURED IT OUT!!! Take a look at my form and maybe you'll see the problem right away:

                              

                              

                              

                              

                        • 9. Re: Help with a CASE calculation, please?
                          BonnieHammond

                               Please forgive my rudimentary explanation...I'm not an advanced FMP user, just a very big fan of the software :)

                               The problem is not that only 1st place works...it's only the first set of results that I enter works, because only that line is picking up the number of Exhibitors in the box above. Probably since I'm working in a Portal, I need an Exhibitor field to go with every Placings field. So I need to add that to the Portal instead of having it outside the portal. To avoid having to put in an Exhibitor count for every single Placing, I'll set up one field outside the Portal where the data is entered and a second field inside the Portal that copies that number. 

                               PS The number of Entrants has to be entered manually for each class because we have people entering the class at the gate who won't be in the computer. So the judge writes the actual number of horses in the class on the results card which is then sent back to the show office where the results (and the final number of competitors) are entered into FMP.

                               EDITED TO ADD: 

                               ...so what's the best way to get the Exhibitor field value into every line of the Portal? I tried setting it up by just putting the Exhibitor field into the Portal, but it only copies the number on one of the lines. 

                          • 10. Re: Help with a CASE calculation, please?
                            philmodjunk
                                 

                                      PS The number of Entrants has to be entered manually for each class because we have people entering the class at the gate who won't be in the computer.

                                 Yes, but you should not be entering this data for each ENTRANT, only once for each CLASS.

                                 I am guessing that you have this relationship:

                                 Classes-----<Entrants

                                 Classes::__pkClassID = Entrants::_fkClassID

                                 Your layout refers to Classes and your portal refers to Entrants.

                                 Of course your names for these table occurrences and fields may be different from what I am using here. For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                                 From earlier posts, I see that the field for the number of Entrants is defined in Entrants and this is what creates this issue with your calculation. Define the field in Classes and refer to this field in your calculation instead of the entrants field in Entrants and this should work correctly for you.