# Re: Check if a record is linked in two different fields

2082 1
cancel
Showing results for
Did you mean:
6 - Interface Innovator

I have to following situation :

How to create a formula that will return “True” or “1” if at least one record appears in both fields ?

7 Replies 7
18 - Pluto
IF(
AND({Question 1},{Question 2}),
1)
6 - Interface Innovator

This does not seems to work, unfortunatly. It returns 1 when both fields are populated (Whatever the values), and 0 when either field is empty.

6 - Interface Innovator

I realize my orginal question was unclear ! I would need the formula to check if the same record appears in both columns, not if any record appears in both columns.

Told differently, those are questions for a form. I need to check if any answer given to question 1 was the same as the answer to question 2.

So if question 1 has [1,2,3] and question 2 has [2,5,6] it should return true.

18 - Pluto

To solve that problem, create that formula in the OTHER table instead of the CURRENT table.

Then, back in the CURRENT table, create a rollup field that points to the formula field with this formula:

ARRAYUNIQUE(values)

And be sure to set the condition to only show you values when the formula field is 1.

6 - Interface Innovator

Unfortunatly, I can’t do that, Q1 and Q2 are linked to two different records (ie, i’m trying to check if Participant 1 question 1 has any common answer with participant 2 Question 2).

I’ve been trying to jerryrig something with ARRAYUNIQUE and ARRAYJOIN, but that won’t work. I’m going to write a script, sadly, as this doesn’t feel like an edge case.

18 - Pluto

Oh, I see… those 2 lookup fields are coming from 2 completely different tables? Yeah, that would be more challenging to solve. Maybe someone else has a quick solution for that.

6 - Interface Innovator

They are coming from the same table, but from different records. The structure is :

• A participant table (all participants)