1 2 Previous Next 15 Replies Latest reply on Jul 6, 2016 11:13 AM by RPeters

    Calculation Confusion - and/or/xor

    RPeters

      Hello Filemaker Friends,

       

      I'm having some issues with my and/or/xor statements, and I've come to realize that I really don't understand how to use them like I thought I did!  Oh boy, I'm getting myself good and mixed up!

       

      I'm trying to hide the _EventsIDfk field when the Account Number field is not one of the four listed below.  In addition, I want the field to hide if it's empty.  I thought I had it sorted out, but after about half an hour of playing around in the "Hide when" option box with switching "and"s to "or"s and "xor"s to "or"s, and throwing in brackets here and there for good measure, I've accepted the fact that I really don't know what I'm doing.

       

      Income::Account Number ≠

      "41 120 Box Office - Other Concerts"

      or

      "41 100 Box Office Summer Festival"

      or

      "44 100 Fundraising Events Revenue"

      or

      "Concert Host"

      xor

      IsEmpty ( Income::__EventsIDfk )

       

      Any ideas on why this isn't doing what I want it to be doing??

        • 1. Re: Calculation Confusion - and/or/xor
          beverly

          the 'or' needs to test the field EACH time:

           

          AccountNumber ≠ "abc"

          OR

          AccountNumber ≠ "def"

          ...

           

          But in reality, I believe you need and instead of or because you are using the not equal (≠):

           

          ( AccountNumber ≠ "abc"

          AND

          AccountNumber ≠ "def"

          ....

           

          )

           

          I'm not sure what the XOR is trying to accomplish.

           

          beverly

          • 2. Re: Calculation Confusion - and/or/xor
            BruceRobertson

            One problem is that the construction of your test cases is wrong.

            Each section has to be a complete statement.

            You cannot write:

            Contacts::firstName = "Sam" or "Mike" or "Sally" or "June"

             

            Given the above, this would work:

             

            Contacts::firstName = "Sam"

            or

            Contacts::firstName = "Mike"

            or

            Contacts::firstName = "Sally"

            or

            Contacts::firstName = "June"

            • 3. Re: Calculation Confusion - and/or/xor
              RPeters

              Thanks beverly!

               

              I gave it a whirl so it looks like this now:

               

              Income::Account Number ≠  "41 120 Box Office - Other Concerts"

              and

              Income::Account Number ≠  "41 100 Box Office Summer Festival"

              and

              Income::Account Number ≠  "44 100 Fundraising Events Revenue"

              and

              Income::Account Number =  "Concert Host"

              and

              IsEmpty ( Income::__EventsIDfk )

               

              But for some reason, that doesn't seem to hide the field... at all.  It shows up on every record.  Any other thoughts??

              • 4. Re: Calculation Confusion - and/or/xor
                RPeters

                Thanks for the suggestion BruceRobertson - okay, I now understand that it has to be a complete statement every time.  That makes sense.  I changed it so now it looks like this, but now it's hiding the field on every single entry.  ...No idea what it didn't like about this!

                 

                Income::Account Number ≠  "41 120 Box Office - Other Concerts"

                or

                Income::Account Number ≠  "41 100 Box Office Summer Festival"

                or

                Income::Account Number ≠  "44 100 Fundraising Events Revenue"

                or

                Income::Account Number =  "Concert Host"

                or

                IsEmpty ( Income::__EventsIDfk )

                • 5. Re: Calculation Confusion - and/or/xor
                  PeterDoern

                  I find it helpful when dealing with multi-criteria hides or conditional formatting to break it down into a Case statement. It makes it easier to decipher.

                   

                  e.g.

                   

                  Case (

                       Income::Account Number ≠  "41 120 Box Office - Other Concerts" ;

                            true ;

                       Income::Account Number ≠  "41 100 Box Office Summer Festival" ;

                            true ;

                       Income::Account Number ≠  "44 100 Fundraising Events Revenue" ;

                            true ;

                       Income::Account Number =  "Concert Host" ;

                            true ;

                       IsEmpty ( Income::__EventsIDfk ) ;

                            true ;

                   

                        // Else

                            false

                  )

                  1 of 1 people found this helpful
                  • 6. Re: Calculation Confusion - and/or/xor
                    beverly

                    Is this in the 'Hide object when' calculation?

                    you have "=" when you want "≠" in the Concert Host test

                     

                    I'm still not sure what the IsEmpty is for. It may be an "or" here.

                    beverly

                    • 7. Re: Calculation Confusion - and/or/xor
                      DamianKelly

                      I love this approach because you can then add comments too. Comments are a good thing:

                       

                      Case (

                           Income::Account Number ≠  "41 120 Box Office - Other Concerts" ; //We need to include all of these because

                                true ;

                           Income::Account Number ≠  "41 100 Box Office Summer Festival" ;//These need adding too

                                true ;

                       

                      etc.

                      Maybe slightly more informative than my examples; comments should say what you want to happen and why  rather than simply parroting the code in English(or your chosen language).

                      • 8. Re: Calculation Confusion - and/or/xor
                        RPeters

                        Thanks for the suggestion PeterDoern,

                        Does this case goes into the "Hide object when" box??  I like the idea, but I'm just not quite sure how to use it in my situation... sorry!  I'm really new to FMP!

                        • 9. Re: Calculation Confusion - and/or/xor
                          RPeters

                          beverly!

                           

                          It worked!  Thanks for catching my error!  It now reads like this:

                           

                          Income::Account Number ≠  "41 120 Box Office - Other Concerts"

                          and

                          Income::Account Number ≠  "41 100 Box Office Summer Festival"

                          and

                          Income::Account Number ≠  "44 100 Fundraising Events Revenue"

                          and

                          Income::Account Number  ≠   "Concert Host"

                          and

                          IsEmpty ( Income::__EventsIDfk )

                           

                          Phew!!  Could you explain why "and" is correct and "or" isn't??  I thought "and" meant that it would hide if it matched all of the criteria at the same time (which of course it never would, because one account number would never be 4 things at once.)

                          • 10. Re: Calculation Confusion - and/or/xor
                            PeterDoern

                            Thanks; to be fair, you can add comments to any calculation formula. I just find that Case() is more explicit and easier to understand. It might even be more efficient, insofar as it stops processing after the first true result.

                            • 11. Re: Calculation Confusion - and/or/xor
                              BruceRobertson

                              "I thought "and" meant that it would hide if it matched all of the criteria at the same time"

                               

                              But that is EXACTLY what you're asking. That the test must match ALL of those conditions.

                              It is just that you have created a convoluted way of expressing it.

                               

                              Account# is not equal to A

                                and

                              Account# is not equal to B

                                and

                              Account# is not equal to C

                              etc.

                              • 12. Re: Calculation Confusion - and/or/xor
                                BruceRobertson

                                Here is another way of expressing it.

                                Shorthand description: "If EventIDfk is empty and the Account# is not in this list"

                                Actual expression:

                                 

                                IsEmpty ( Income::__EventsIDfk )

                                  and

                                IsEmpty(

                                FilterValues(  Income::Account Number;

                                List(

                                "41 120 Box Office - Other Concerts" ;

                                "41 100 Box Office Summer Festival" ;

                                "44 100 Fundraising Events Revenue" ;

                                "Concert Host"

                                )

                                )

                                )

                                2 of 2 people found this helpful
                                • 13. Re: Calculation Confusion - and/or/xor
                                  Stephen Huston

                                  Yea, Bruce!

                                  By far the simplest list format to maintain when a list needs updating down the road.

                                  1 2 Previous Next