1 2 Previous Next 23 Replies Latest reply on Jan 25, 2010 7:33 PM by deltatango

    group where/and statement

    deltatango

      Title

      group where/and statement

      Post

      In Mysql you can use this syntax:

       

      field1 = 2 and (field3 = "abc" or field3 = "def" or field3 = "123") 

       

      That way you don't have to do something like:

       

      field1 = 2 and field3 = "abc" or field1 = 2 and field3 = "def" or field1 = 2 and field3 = "123" 

       

      Can you do the same in Filemaker, for example in an if statement in a script? 

        • 1. Re: group where/and statement
          LaRetta_1
            

          deltatango wrote:
          field1 = 2 and (field3 = "abc" or field3 = "def" or field3 = "123")

          I'm unsure what you are looking for.  If you want a boolean true on each RECORD which meets the criteria, you would write it the same in FileMaker.  If[] script-step returns boolean results so this calculation would produce true on your If[] step.

           

          You could also write this as:  field1 = 2 and PatternCount (  " ABC 123 def " ; field3 )


          • 2. Re: group where/and statement
            deltatango
              

            LaRetta,

             

            In mysql, by putting the first statement outside the parenthesis, it makes this condition and any of the ones in the parenthesis evaluate as true if found. I've tried doing this in filemaker and it hasn't worked for me. 

            • 3. Re: group where/and statement
              Steve Wright
                

              There should be no problem  

               

              These will evaluate as true : 

              If ( 2 = 2 and (3 = "abc" or 3 = "def" or 3 = "3") ; True ; False )

              If ( 2 = 2 and (3 = "abc" or 3 = "3" or 3 = "ghi") ; True ; False )

              If ( 2 = 2 and (3 = "3" or 3 = "def" or 3 = "ghi") ; True ; False ) 

               

              or


              2 = 2 and (3 = "3" or 3 = "def" or 3 = "ghi")

               

              This will evaluate as false. 

              If ( 2 = 2 and (3 = "abc" or 3 = "def" or 3 = "4") ; True ; False ) 

               

               

              or if using as a script step, then you would simply have 

               

              If [  2 = 2 and (3 = "abc" or 3 = "def" or 3 = "3") ] 

               

              - Steps if true

               

              Else

               

              - Steps if false

               

              End If 

               

               

              Edited with embarrassment, for future readers.  

              • 4. Re: group where/and statement
                comment_1
                  

                SW wrote:

                you need to tell it what to return if using via a calculation


                No, you don't:

                 

                2 = 2 and ( 3 = "abc" or 3 = "def" or 3 = "3" )

                 

                returns True (i.e. 1) without needing anything else.

                 

                 

                Note that True and "true" are two very different things: the former is a Boolean value, while the latter is a meaningless text string.


                • 5. Re: group where/and statement
                  Steve Wright
                    

                  You are of course correct, what was I thinking.... (where's the embarrassed smiley when you need it)  
                  can I use the excuse of it being late :-P

                   

                   

                  • 6. Re: group where/and statement
                    deltatango
                      

                    don't worry. i make silly mistakes all the time. this is how we learn and the point is that i understood what you meant. by the way, can you use true and false in filemaker. as such i mean.

                    • 7. Re: group where/and statement
                      Steve Wright
                        

                      True / 1  and False / 0  (as correctly stated by comment) can be used yes

                       

                      A field named Result with a calculation containing

                      2 = 2 and (3 = "abc" or 3 = "def" or 3 = "3"

                       

                      Would result in 1

                       

                      You can then for instance use

                       

                      If [Result = True] do something  or

                      If [Result = 1] do something etc

                       

                       

                      • 8. Re: group where/and statement
                        LaRetta_1
                          

                        deltatango wrote: by the way, can you use true and false in filemaker. as such i mean.

                        Not in version 7 and greater.  As I said in my original response, the calculation (whether used in calc or script step) returns a result of 1 (indicating true) just as it would in MySQL (which I also use).   :smileywink:

                        SW wrote: True / 1  and False / 0  (as correctly stated by comment) can be used yes Would result in 1 You can then for instance use If [Result = True] do something  orIf [Result = 1] do something etc

                        I would re-read what Comment said.  True and False can NOT be used in FileMaker.  The result of a boolean test tells you whether it is True or False by producing 1 or 0 .  Any real number (other than 0) produces true.  Script step:  If [ dateField ] ... if there is any date, will continue with the step because the condition is True but those words will never appear nor can you search for them.

                         

                        You might have been saying that but that isn't what you said. :smileyhappy:

                         

                        Instead of "If [Result = True] do something ... it would be better to think, If [ (condition IS true ) ] do something. 

                        • 9. Re: group where/and statement
                          comment_1
                            

                          LaRetta wrote:

                          deltatango wrote: by the way, can you use true and false in filemaker. as such i mean.

                          Not in version 7 and greater.

                           

                          True and False are reserved words and as such CAN be used in a calculation instead of 1 and 0, respectively.

                           

                          I believe you are thinking of words beginning with "T", "F", "Y" and "N" - which in previous versions were interpreted as true/false when entered into a number field.


                          • 10. Re: group where/and statement
                            LaRetta_1
                              

                            I see!   I find this ( this site wouldn't take the actual table) …

                             

                            Number field content…… In 7 and up … In 6 and earlier

                            Blank field……………..…False……….. False

                            0……………………...….False ..………False

                            1……………………...….True…………True

                            True, Yes, Y, y, T, or t…...False…...……True

                            False, No, N, n, F, or f…...False………...False

                             

                            … where it indicates text with the word True is invalid in current versions.  So you are saying in an evaluation, it can be used.  But why would we ever want to?

                             

                            For instance, number field called Amount with 44 in it.  Then we create a calculation with:  Case ( Amount >15 ;  True )  … and it indeed produces 1.  But we could get the same thing with simple Amount >15When we always know it generates a result of 1 or 0, why write True or False at all?  I still can’t find anything in help about it specifically.  Can you tell me when you would ever use it?  I want to understand!  And do you know where this is referenced in Help?  Thank you! :smileyhappy:

                             

                            UPDATE:  Oh, and my apologies for almost misleading anyone.  I really try to be very careful when providing information.

                            • 11. Re: group where/and statement
                              Steve Wright
                                

                              OK just to clear up what I was saying...  Here's hoping I don't mess this one up !

                               

                              In a calculation, you can write an if statement in the following ways

                               

                              Condition = Value

                               

                              You cannot write If (Condition = Value) for this you need to specify the outcome / result  (this is what I meant to say)

                              Instead you would need 

                               

                              If (Condition = Value ; "CustomResult")   or  If (Condition = Value ; "CustomResultTrue" ; "CustomResultFalse")

                               

                              So why use the above example ?  Well I use it for things like dynamic text

                              If ( status = "completed"  ; "SOLD" ) whereby a 1 or 0 is of no use to me here (not something I actually use but an example)

                               

                              Now to clarify the 1 / True 

                               

                              If you write the following into a calculation 

                               

                              If ( 1 = True ; "display this" )   You would end up with the result being "display this" 

                              If ( 0 = True ; "display this" )   You would end up with the result being empty 

                               

                              1=True  // this would return 1.

                              0=True // this would return 0 

                              1=False // this would return 0

                              0=False // this would return 1

                               

                              Like wise, in an IF script step you can also use True or False in place of 1 or 0

                               

                              If[1 = True]

                                 Perform sub script  < This would happen

                               Else If [ 0 = True ]

                                 Perform sub script 2

                              End If 

                               

                              Why would you want to use the word True and False to do this ?  
                              Well, some times it can make it more human readable for others..  Its easier to think in terms of if something is true or false, than it is thinking is something 1 or 0. If anywhere, I may use it occasionally in script steps or more complex calculations but to be honest hardly ever.

                               

                               

                              Using True / False has its downfalls..  As LaRetta stated  if the calculation result is not of the boolean type, or to be stored in a field for later manipulation,  it will be a 1 or a 0, you cannot later check the field using True or False.  It only works during a calculation or script steps so to speak

                               

                              Therefore, on that note, my advice is to
                              Forget I even mentioned True / False since it may lead to confusion when you attempt to use it in the wrong context. 

                               

                               

                              • 12. Re: group where/and statement
                                comment_1
                                  

                                LaRetta wrote:
                                Can you tell me when you would ever use it?  I want to understand!  And do you know where this is referenced in Help?

                                 

                                I use it very rarely, if at all. I may have used it do define a constant 1 field, because I though it was more self-explaining that way.

                                 

                                It's mentioned here:

                                http://www.filemaker.com/help/html/create_db.8.7.html#1043324

                                but the list there is not exhaustive (try defining a field named "Bold", for example).

                                • 13. Re: group where/and statement
                                  LaRetta_1
                                    

                                  I have hesitated in responding until I can further evaluate but I had to follow the trail from naming conventions (message says cannot use if used as a function, parameter or keyword).  Of course it is not a function so I searched keyword and I got a hit.  What a roundabout method to get answers to a simple question.  Searching for keyword brings up this:

                                   

                                  Logical functions

                                  Logical functions test for a condition to evaluate it as true or false. This is known as a Boolean value. If the condition is true, FileMaker Pro returns a 1; if the condition is false, FileMaker Pro returns a 0. You can use the keywords True and False with logical functions and operators when a Boolean value is needed. Keyword True returns 1 and keyword False returns 0.

                                   

                                  FM Help didn't provide an example of using a 'keyword' in a calculation however ...

                                   

                                  It may be that some people never learned boolean logic (not everyone designing in FileMaker has true programming background).  And it might make things clearer for them (just like wrapping with needless quotes does).  But boolean logic is one of the basics in all true programming languages and I will NOT give up efficiency so others might understand basic boolean (a stance firmly taken by -Queue-, JT way back when and that I believe strongly in). 

                                   

                                  So it seems True and False were left in because of confusion and to protect from those that lack understanding.   FM even added a GetAsBoolean() function and the purpose?  I suppose it's so that we don't write something like:

                                   

                                  not not PatternCount ( field ; char )

                                   

                                  ... but really. Otherwise, we'd have to write If ( PatternCount ( field ; char ) ; 1 ).   I've tried to embrace the spirit and understand when it might be useful and so far, the results are very weak.  If you want to boolean test but NOT produce either 0 or 1, you might use it.  For instance:

                                   

                                  num > 6 produces 1 (if true) and 0 (if false) ... the field is never empty.

                                   

                                  GetAsBoolean (num > 6 ) produces 1 (if true) and 0 (if false) ... and again, the field is never empty.

                                   

                                  But we can't use TRUE unless we wrap it with a Case() or IF() test which isn't as efficient (unless someone can explain a useful situation)!  I see its only benefit being that it produces a single result, ie, 1 if the calculation is:  Case (  num > 6 ; True ) but then the person could write Case ( num > 6 ; 0 ; 1 ) just as well. 

                                   

                                  I seem to vaguely recall you using True (and also Genx using it on FM Forums once) but I cannot find those examples.  As SW indicates, it seems better to forget that True can be used at all unless someone has other suggestions.  But this doesn't mean I will throw it out of my toolkit; only that it'll reside under the main tools.   I really appreciate all the input on this thread and particularly your input, Comment. :smileyhappy:

                                  • 14. Re: group where/and statement
                                    comment_1
                                      

                                    How about:

                                     

                                    Exit Script [Result: True]

                                     

                                    instead of the more cryptic:

                                     

                                    Exit Script [Result: 1]

                                     

                                     

                                    Essentially, it doesn't matter if you use 1 or True or Greek or Strikethrough ... they are all symbols for the same thing.

                                    1 2 Previous Next