Does value exist in related set of values?

[davidnickerson]

Sorry, I couldn't think of a more accurate topic title for this.

 

And I'm annoyed, because I'm sure this is something simple, but I just can't come up with it at the moment.

 

I'm looking for calculation to determine if a value in a particular field is equal to an existing value from a second field in a related table.

 

For example, in one table there is a set of invoice records.  In another table, a user is inputting invoice numbers.  If the user enters an invoice number that already exists in the other table, I'll do some conditional formatting to change the color of the field.

 

I thought I had done this before with either the ValueListItems function, the List function or PatternCount, but I can't seem to make anything work at the moment.

 

Any pointers/reminders would be great.  Thanks!

[doughemi]

If the relationship is based on invoice numbers, then Count(RelatedTable::id) ≠ 0 ought to do it.  If not, make a new TO with that relationship.

[davidnickerson]

Yes, Count, that's it!  Not PatternCount!  Sheesh.

 

Thank you!

[dansmith65]

You might be looking for something like this, but i'm not clear how your two tables are related, so I'm not sure.

not IsEmpty ( FilterValues (
    List ( Related::Field ) ;
    Local::Field
) )

This calculation will be true if your Local::Field exactly matches a Related::Field, where there can be multiple matching records in the Related table.

[comment]

I believe you mean FilterValues(), not Filter().

[dansmith65]

I believe you mean FilterValues(), not Filter().

Thanks, yes I did mean FilterValues. I also missed an opening bracket. I edited my post, so the calculation should be correct now.