11 Replies Latest reply on Jul 26, 2013 6:39 AM by Tusquittee

    Case Statement Help Needed

    Tusquittee

      Title

      Case Statement Help Needed

      Post

           HI again...Here's the case statement I'm working on but it doesn't evaluate properly.

            

           Case(
            
           Registrations::Session Name  =  ("ACOS"  and Status = "Enrolled"); LPR Table::ACOS Scholarship;
            
           Registrations::Session Name  =  ("ACOS"  and Status = "Withdrawn"); LPR Table::ACOS Scholarship;
            
           Registrations::Session Name   ≠   ("ACOS"  and Status = "Enrolled"); LPR Table::Basic Scholarship;
            
           Registrations::Session Name   ≠   ("ACOS"  and Status = "Withdrawn"); LPR Table::Basic Scholarship;
            
           "0" 
            
           It is giving everyone the Basic Scholarship, and no one gets the ACOS Scholarship.  I think the and statement isn't working the way I imagined it would.  Any suggestions, and thanks for all the wonderful filemaker help... I'm almost done with my project, and never would've made it without the forum.

        • 1. Re: Case Statement Help Needed
          schamblee

               Your case statement is incorrect.  It should be something like

               Case(

               Registration::Session Name="ACOS" and Status="Enrolled" ; LPR Table::ACOS Scholarship;

               Registration::Session Name="ACOS" and Status="Withdrawn"; LPR Table::ACOS Scholarship;

               Registration::Session Name≠"ACOS" and Status="Enrolled";LPR Table::Basic Scholarship;

               Registration::Session Name≠"ACOS" and Status="Withdrawn";LPR Table::Basic Scholarship)

                

          http://www.filemaker.com/help/html/func_ref3.33.2.html

                

                

                        

          • 2. Re: Case Statement Help Needed
            Tusquittee

                 Thanks S. Chamblee... Were the extra spaces and parentheses all I needed to omit? Because I tried that and it's still not working... The Basic Scholarship statements are returning correct values... but the first two lines aren't being recognized...They return the info from Basic Scholarship.

            • 3. Re: Case Statement Help Needed
              gcatnine

                   About parentheses consider that all logical operator returns a logical value:

                   ("ACOS"  and Status = "Enrolled") is always or True or False

                   therefore in comparing a logical value with a value, the system has to transform the value in Registration::Session Name in a logical value (True or False; only 0 or null is False).

                   If Registration::Session Name has a value, it is always True and the result will depends on the value of the other part of the test.

                    

                   In any case, it seems that you do not care about the Status (in both cases "Enrolled" or "Withdrawn" you get the same result).

                   So if you want to test if the Registration::Session Name is “ACOS”, you can have:

                   Case(

                   Registration::Session Name = "ACOS" ; LPR Table::ACOS Scholarship;

                   Registration::Session Name ≠ "ACOS" ;LPR Table::Basic Scholarship;

                   0)

              • 4. Re: Case Statement Help Needed
                philmodjunk

                     Good point, but only if the only possible values for Status are "Enrolled" and "Withdrawn".

                     If your case function still isn't working, check the fields in Manage | Database | Fields and make sure that both (Session Name and Status) have been defined as fields of type Text and not Number.

                     It's also helpful to describe HOW the calculation is not working instead of just posting that it didn't work. The actual results that you got can be a useful clue as to what went wrong.

                • 5. Re: Case Statement Help Needed
                  Tusquittee

                       Thanks for all the help...

                       Here's the calculation as I have it now...

                        

                        

                       Case(
                        
                       (Registrations::Session Name≠"ACOS" and Status="Enrolled"); LPR Table::Basic Scholarship;
                        
                       (Registrations::Session Name≠"ACOS" and Status="Withdrawn"); LPR Table::Basic Scholarship;
                        
                       (Registrations::Session Name="ACOS" and Status="Enrolled"); LPR Table::ACOS Scholarship;
                        
                       (Registrations::Session Name="ACOS" and Status="Withdrawn"); LPR Table::ACOS Scholarship;
                        
                       "0")

                        

                  And here's some more information to explain what I'm trying to do:

                        

                  Session Name will either have “ACOS” in the title... or it won’t have “ACOS” in the title.

                  Status can be “Enrolled”, “Withdrawn”, “Cancelled”, “E-Self Pay”, “W-Self Pay” 

                        

                  If Session name contains “ACOS” AND the student remains enrolled or has withdrawn... then return an ACOS scholarship value.

                        

                  If Session name contains anything other than "ACOS" AND the student remains enrolled or withdrawn.. then return a Basic Scholarship value.

                        

                  If Session name is anything at all and they have cancelled, E-Self Pay, or W-Self Pay in their status field.... then return a value of zero.

                        

                  The first change I made was to move the parentheses to evaluate the statement when both conditions are met.

                        

                  and, As you can see, I also switched the order of the lines from the first time I posted, to see if that made a difference... but not so. 

                        

                        

                  In terms of what exactly is happening........

                        

                  In the example from this post, the first two lines return correct values from the Basic Scholarship field.  The last two lines do NOT return an ACOS value, instead... they return the Basic Scholarship value.  I think this means that somehow the statement I’m writing in lines 3 and four must also be true in line one and two which is why they're returning that value instead.  For example.. even when, 

                        

                  Registration::Session Name=ACOS Fall 

                       Reg Data::Status=Enrolled

                        

                       This example returns a basic scholarship value instead of an ACOS scholarship value.

                        
                  • 6. Re: Case Statement Help Needed
                    Tusquittee

                         Also.. i just double checked and both of the fields.. Reg Data::Status and Registration::Session Name are set to be text fields.

                    • 7. Re: Case Statement Help Needed
                      philmodjunk
                           

                      If Session name contains “ACOS” AND the student remains enrolled or has withdrawn...

                      Contains, reveals the issue. Your case statement requires that the exact text in Session be "ACOS"--not a session title that includes "ACOS" along with other text.

                           Use this expression to get your case function to work for you:

                           PatternCount ( Registrations::Session Name ; "ACOS" )  and ....

                      • 8. Re: Case Statement Help Needed
                        Tusquittee

                             Please know that I appreciate all the help ..

                             so, I understand now how to change the lines that were referencing containing ACOS.... but, three questions...

                             a.  Does order matter in a case calculation? As in, does the order of lines change the outcome?

                             b.  How do I refer to the lines that are saying aren't containing ACOS?

                             b.  Do "OR" statements work with case calculations? and if so.. should i rewrite this calculation so for example the bottom two lines could become one line that looked like...(PatternCount ( Registrations::Session Name ; "ACOS" ) and Status="Withdrawn" or "Enrolled"); LPR Table::ACOS Scholarship;

                              

                              

                             Case(
                              
                             (Registrations::Session Name≠"ACOS" and Status="Enrolled"); LPR Table::Basic Scholarship;
                              
                             (Registrations::Session Name≠"ACOS" and Status="Withdrawn"); LPR Table::Basic Scholarship;
                              
                             (PatternCount ( Registrations::Session Name ; "ACOS" ) and Status="Enrolled"); LPR Table::ACOS Scholarship;
                              
                             (PatternCount ( Registrations::Session Name ; "ACOS" ) and Status="Withdrawn"); LPR Table::ACOS Scholarship;
                              
                             "0")
                        • 9. Re: Case Statement Help Needed
                          Tusquittee

                          smileysmileysmiley

                               Woooohooooo... this is what I have now..

                                

                               Case(
                                
                               (PatternCount ( Registrations::Session Name ; "ACOS" ) and Status="Enrolled" or "Withdrawn"); LPR Table::ACOS Scholarship;
                                
                               (Registrations::Session Name ≠ "ACOS" and Status="Enrolled" or "Withdrawn"); LPR Table::Basic Scholarship;
                                
                               "0")
                                
                               and it appears to be working.  I need to double check to see if any students have actually withdrawn so I can check which value is being reported under that condition... but so far.. so good...
                                
                               Does this look better to ya'll...?
                          • 10. Re: Case Statement Help Needed
                            philmodjunk

                                 Better Try again. wink

                                 You can use the or operator in the Case function but your syntax is incorrect.

                                 The order does matter in Case Statements. FileMaker evaluates each boolean expression in turn and goes with the first one to evaluate as True.

                                 Rewrite it as:
                                  
                                 Case(
                                  
                                 PatternCount ( Registrations::Session Name ; "ACOS" ) and (Status="Enrolled" or Status="Withdrawn"); LPR Table::ACOS Scholarship;
                                  
                            Status="Enrolled" or Status="Withdrawn"; LPR Table::Basic Scholarship;
                                  
                                 "0")
                                  
                                 to check to see that Session Name does NOT contain the text "ACOS", you'd use Not PatternCount ( Registrations::Session Name ; "ACOS" ) , but since FileMaker never gets to the blue line unless the field does not contain this text, you don't need that test.
                            • 11. Re: Case Statement Help Needed
                              Tusquittee

                                   Thank you Phil, I only thought it was working... a few switches to the enrollment status of a test student showed that I still didn't have it quite right.... 

                                   Just used the info from above and it works beautifully.  Now back to my GetSummary fun.  Thanks again for ALL the help.