10 Replies Latest reply on Nov 24, 2014 1:12 PM by josetorre162

    How to exclude a record value from a selected field in a calculation

    josetorre162

      Hello,

       

       

      When writing my case fucntions and I select the as District type to be a certain value, how can I tell the function to exclue one of the values of the field that contains the District type.

      For example, in the following function, I am saying that when the Customers::Distr_Type = "SA-1" (Customers table, field SA-1) perform the repective cases.

       

       

      Case (

      Customers::Distr_Type = "SA-1"; ( If ( Readings_SecondTable::Water_Used_cf >10000 ; Readings_SecondTable::Water_Used_cf - 10000 ; 0 )

       

       

       

       

      I would like to exclude a particular record (row) from the column field SA-1. How would I write this in the formula above?

       

       

      Thank you for your help.

       

       

      jose

        • 1. Re: How to exclude a record value from a selected field in a calculation
          erolst

          josetorre162 wrote:

          how can I tell the function to exclue one of the values of the field that contains the District type.[…] I would like to exclude a particular record (row)  from the column field SA-1

           

          If this means that you don't want the calculation being performed for a specific record (or, more to the point, have an empty result returned):

           

          Case (

            Customers::Distr_Type = "SA-1" and someField ≠ someUniqueValue ;

            Case (

              Readings_SecondTable::Water_Used_cf >10000 ;

              Readings_SecondTable::Water_Used_cf - 10000

            )

          )

           

          and while you're at it, why not try to get familiar with the Let() function, as suggested in the other post?

          • 2. Re: How to exclude a record value from a selected field in a calculation
            josetorre162

            Hello,

             

            I have tried your suggestion in the following calculation,

             

            Case (

            Customers::APN="054-187-013"; If( Readings_SecondTable::Water_Used_cf >21001; Readings_SecondTable::Water_Used_cf-21001 ;0 ) ;

            Customers::APN="054-184-029"; If( Readings_SecondTable::Water_Used_cf >105000; Readings_SecondTable::Water_Used_cf-105000 ;0 ) ;

            Customers::Distr_Type="SA-16"; If(Water_Used_cf>10000;Water_Used_cf-10000 ; 0 ) ;

            Customers::Distr_Type="SA-1"  and "SA-1"≠"054-184-029" ; If(Water_Used_cf > 42000;Water_Used_cf-42000 ; 0 ) ;

            Customers::Distr_Type="MD-73"  and "MD-73"≠"054-187-013" ; If(Water_Used_cf >10500 ;Water_Used_cf- 10500 ; 0 ) ;

            Customers::Distr_Type="MD-63A" ; If(Water_Used_cf > 78540 ;Water_Used_cf- 78540 ; 0 ) ;

            Customers::Distr_Type="MD-60A" ; If(Water_Used_cf > 10500 ;Water_Used_cf- 10500 ; 0 ) ;

            )

             

            It works. Thank you very much. Without this suggestion I wouldn't be able to continue on my calculation.

            Once again, thank you.

             

            Jose

            • 3. Re: How to exclude a record value from a selected field in a calculation
              erolst

              josetorre162 wrote:

              It works. Thank you very much. Without this suggestion I wouldn't be able to continue on my calculation.

               

              That's good, but let me repeat my suggestion: get familiar with Let() (and use some whitespace in your formatting).

               

              Also, you can replace If ( a > x ; a - x ; 0 ) with Max ( a - x ; 0 )

               

              Let ( [

                wu = Water_Used_cf ;

                wu2 = Readings_SecondTable::Water_Used_cf ;

                theAPN = Customers::APN ;

                theType = Customers::Distr_Type

                ] ;

                Case (

                  theAPN = "054-187-013"; Max ( wu2 - 21001 ; 0 ) ;

                  theAPN = "054-184-029" ; Max ( wu2 - 105000 ; 0 ) ;

                  theType = "SA-16" ; Max ( wu - 10000 ; 0 ) ;

                  theType = "MD-60A" or theType = "MD-73" and "MD-73" ≠ "054-187-013" ; Max ( wu - 10500 ; 0 ) ;

                  theType = "SA-1" and "SA-1" ≠ "054-184-029" ; Max ( wu - 42000 ; 0 ) ;

                  theType = "MD-63A" ; Max ( wu - 78540 ; 0 )

                )

              )

               

              which IMO is much easier to read, understand and, if necessary, expand upon.

               

              btw, what does this mean?

               

              … and "SA-1" ≠ "054-184-029" ;

              … and "MD-73" ≠ "054-187-013"

               

              A test comparing two different literal strings will always evaluate as being True (or False, depending on the operator). Are you sure you didn't want to say something else (like Customers::Distr_Type ≠ Customers::APN – if that makes sense …?)

              • 4. Re: How to exclude a record value from a selected field in a calculation
                josetorre162

                hello,

                 

                Thank you for your suggestion to get familiar with the Let() and using whitespace in my formatting.

                For me, the way I formatted the caluculation was more easier to understand.

                 

                Furthermore, regarding the following,

                 

                 

                "SA-1" ≠ "054-184-029" ;

                … and "MD-73" ≠ "054-187-013"

                 

                I am trying to say that in the SA-1 field exclude the record value "054-184-029".

                 

                That's why I put the specific value of the "054-184-029" to have another condition,  Customers::APN="054-187-013"; If( Readings_SecondTable::Water_Used_cf >21001; Readings_SecondTable::Water_Used_cf-21001 ;0 ) ;

                 

                Customers::APN is another field in the main table so Customers::Distr_Type and Customers::APN  are two diferent fields in a table.

                 

                and same goes for MD-73.

                • 5. Re: How to exclude a record value from a selected field in a calculation
                  erolst

                  josetorre162 wrote:

                  Furthermore, regarding the following,

                   

                   

                  "SA-1" ≠ "054-184-029" ;

                  … and "MD-73" ≠ "054-187-013"

                   

                  I am trying to say that in the SA-1 field exclude the record value "054-184-029".

                   

                  Yes, I thought as much; but to do that, you need to reference the fields SA-1 and MD-73.

                   

                  Wrapped in quotes, the characters "SA-1" and "MD-73" simply represent two unchanging, literal strings (and the fields that you intend to use are ignored, because they are not referenced), so these two tests will always be True (because the tested strings are always different).

                   

                  In brief: you need to write

                   

                  SA-1 ≠ "054-184-029" … MD-73 ≠ "054-187-013" etc.

                  • 6. Re: How to exclude a record value from a selected field in a calculation
                    josetorre162

                    Ok that makes sense but everytime I take away the Quotation marks out of SA-1 when I click Ok it Filemaker popup message says that it cannot recongnized the field name SA-1. it only works when the quotation mark is included.

                    • 7. Re: How to exclude a record value from a selected field in a calculation
                      BruceRobertson

                      You can't name a field that way.

                      Call the field SA1 or SA_1.

                      • 8. Re: How to exclude a record value from a selected field in a calculation
                        beverly

                        Bruce is correct, but the "can't" is more or less "shouldn't". FileMaker does allow naming in some places where it shouldn't.

                         

                        If OP sticks to alpha-numerics and underscore, then there should be no problems.

                         

                        Yes, spaces are ok in naming, but may break when connecting FM to external apps. Buyer be aware!

                         

                        Beverly

                        • 9. Re: How to exclude a record value from a selected field in a calculation
                          user19752

                          You see how you should write such a field name, if you select field from the list, not type it yourself.

                           

                          ${SA-1}

                          or

                          ${table::SA-1}

                          • 10. Re: How to exclude a record value from a selected field in a calculation
                            josetorre162

                            Hello,

                             

                            Yea ok. I will do that. Thank you all for help.

                             

                            jose