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

# Finding Data in Related Fields

### 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

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

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

The relationship is 1 to 1.

Table1

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

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

That worked great.  Thanks for that.