4 Replies Latest reply on Jan 2, 2014 5:21 PM by JohnLong

    Finding Data in Related Fields

    JohnLong

      Title

      Finding Data in Related Fields

      Post

           Help!

           I would like to perform a find within a layout where one field doesn't equal another.

           (ex. I have a cost field and would like to find each record where the cost field does not equal another cost field from a linked table)

           Is that possible?

            

           Thanks,

           John

        • 1. Re: Finding Data in Related Fields
          philmodjunk

               And what is the relationship like between the two tables? One to one, one to many?

               It sounds like you can do something like this:

               Define a calculation field in the layout's table with this expression:

               CostField = RelatedTable::CostField

               select Number as the result type for this calculation

               Then perform a find for the value 1 in this calculation field.

               But the nature of the relationship between these two tables could keep this from working unless this is a one to one relationship or the Cost from the related field is a total of costs from the related records.

          • 2. Re: Finding Data in Related Fields
            JohnLong

                 Won't I just replace the in the new field?

                  

                 The relationship is 1 to 1.  

                  

            Table1

                 Sku; xyz (linked data)

                 Cost: 1.25

            Table 2

                 Sku: xyz

                 Cost: 1.24

                  

                 I want to know if the value in table 1 is different then in table 2.  Does that make it clearer?  Thanks for your help.

                  

                 John

            • 3. Re: Finding Data in Related Fields
              philmodjunk

                   I missed the word "doesn't" in your original post.

                   Define cCostCompare as a calculation field with this expression:

                   Cost ≠ Table 2::Cost

                   This will not modify any data in any of your other fields in either table. It's a Boolean expression that will return the number 1 (True) if the values are not equal. So finding all records in Table 1 where cCostCompare is 1 will find all records where the values are not equal.

              • 4. Re: Finding Data in Related Fields
                JohnLong

                     That worked great.  Thanks for that.yes