7 Replies Latest reply on Oct 26, 2012 3:20 PM by RogerAC

    easy question for empty field

    RogerAC

      Hi

       

      Could someone please show me how to test for an empty field in a calculation so that I can use it to display only those records.

       

      Thanks

       

      Playbyte

        • 1. Re: easy question for empty field
          JeffPenner

          IsEmpty (field)

          Jeff Penner

          • 2. Re: easy question for empty field
            RogerAC

            Thanks and now a follow on.

             

            Using the above in a calulation is it possible to check that a field in another table is not empty as part of the same calculation

             

            Playbyte

            • 3. Re: easy question for empty field
              gwinzeler

              Use ExecuteSQL  (

               

              "SELECT Table1.yourselectedCol, Table2.yourselectedCol from Table1 join Table2 on Table1.key1 = Table2.key1 where Table1.emptyCol is null and Table2.emptyCol is null" ; "" ; "" )

               

              The Table1.key and Table2.key hopefully are relationship keys that are not null.

              GARY

              • 4. Re: easy question for empty field
                comment

                playbyte wrote:


                Using the above in a calulation is it possible to check that a field in another table is not empty as part of the same calculation

                 

                You can check if a field in the first related record in another table is empty by =

                 

                IsEmpty ( AnotherTable::Somefield )

                 

                If that's not sufficient, provide more details about your situation.

                • 5. Re: easy question for empty field
                  RogerAC

                  Thanks for these suggestions but still not got it working.

                   

                  Situation is that I am unpicking a bespoke database designed for me but which has been adapted from another database that was doing similar tasks to what I required.

                   

                  I am using a copy of the database to learn how to use Filemaker and get a better understanding of the bespoke database so that I can tweak or imporve as my needs change.

                   

                  Main layout is for customers with a portal that shows the invoices on one tab and the orders on another. I am trying to use the portal filter tool to show the invoices in 2 ways

                   

                  a) all invoices for the customer that are outstanding

                   

                  b) all invoices.

                   

                  The portal is displaying information from related 3 tables where the invoices are listed from a table named orders and invoices that have been paid are listed from a table named payments and the invoices themselves in a 3rd table. Portal has : Invoice No. Date payment due and Date invoice paid.

                   

                  My fallback plan is to use a field to switch between differenet listing but I thought I could do it his different way which would also give me a wider knowledge.

                   

                  Thanks again.

                   

                  Roger

                  • 6. Re: easy question for empty field
                    comment

                    playbyte wrote:

                     

                    The portal is displaying information form related 3 tables where the invoices are listed in a table named orders and invoices that have been paid are recorded in a table named payments and the invoices themselves in a 3rd table.

                     

                    I am afraid you have lost me there.

                    • 7. Re: easy question for empty field
                      RogerAC

                      amended the paragraph