7 Replies Latest reply on Feb 20, 2009 12:49 PM by LeoCA

    Retrieving Records with Condition

    LeoCA

      Title

      Retrieving Records with Condition

      Post

      Fellow FileMaker Pro 8.5 Professionals:

       

      We set up a layout based on Table A, which is related to Table B.  We want to retrieve records that have [Table A]::itemA > [Table B]::itemB.  We tried to develop a script to do this and can't get the condition properly specified in the Perform Find command.  Your help is very appreciated.

       

      LeoCA

        • 1. Re: Retrieving Records with Condition
          Orlando
            

          Hi LeoCA

           

          Are you wanting to search for records where the value in TABEL B::Item B is greater than TABLE A::Item A? Or display these records in a portal?

           

          Also what is Item A and Item B, numbers Text??? 

          • 2. Re: Retrieving Records with Condition
            LeoCA
              

            Table A has many to one relationship to Table B. We want to see all records in Table A (many items in Table A on the layout.  That is why we have it based on Table A) that meet the condition of "Item A greater than Item B", NOT in a portal.

             

            Both items are numbers. For our future reference, any difference in numbers or text?

             

            Thanks.

             

            LeoCA

            • 3. Re: Retrieving Records with Condition
              LeoCA
                

              I did the followings to solve the problem:

               

              Create a item "Difference" on Table A with calculation = ItemA-ItemB. Perform Find with condition Difference<0.

               

              My followed question is "Can FileMAker specify condition of comparing two items (variables), even both items are in the same table?  We solve this problem because both items are numbers.  What can we do if comparing Text?

               

              Thanks.

               

              LeoCA

              • 4. Re: Retrieving Records with Condition
                TSGal

                LeoCA:

                 

                Thank you for your post.

                 

                You can definitely compare text.  If the contents of Field1 is "CAT" and the contents of Field2 is "DOG", then if you evaluate for Field1 < Field2, it will return true.  It looks at the text field as it would be sorted.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: Retrieving Records with Condition
                  LeoCA
                    

                  TSGal:

                   

                  Thank you for your response.

                   

                  Could you elaborate more on how to specify this condition when trying to retrieve records?

                   

                  In Find Mode of a Layout:

                  Goto Field1, then type "< Field2" with or without quotes will not work (always get no match).

                   

                  In a Script:

                  Enter Find Mode

                  Set Field... OR Perform Find(with option)  <-- How to specify?

                   

                  Your help is appreciated.

                   

                  LeoCA

                  • 6. Re: Retrieving Records with Condition
                    TSGal

                    LeoCA:

                     

                    Thank you for the clarification.

                     

                    When you type in "< Field2", it is looking for the value "Field2"; not the contents.

                     

                    Create a new calculation field, "CALC", with the formula:

                     

                    Field1 < Field2

                     

                    For any record where this occurs, the result will be 1.  Otherwise, it will be 0.  You can then search for 1 in this field.  You would not need a script unless you plan to automate this.  In that case, the script step would be:

                     

                    Perform Find [ Restore ]    (where the condition would be CALC = 1)

                     

                    TSGal

                    FileMaker, Inc. 

                     

                    TSGal

                    FileMaker, Inc.

                    • 7. Re: Retrieving Records with Condition
                      LeoCA
                        

                      TSGal,

                       

                      Thank you for the explanation. I have better understandings on how FileMaker works now.  It seems to me we always need to create a fictitious item with option of calculation on the table if we want to Query with condition (such as WHERE clause in SQL).

                       

                      LeoCA