Help

Re: Comparing of two values in rolled up fields and showing TRUE (if identical) / FALSE (otherwise)

Solved
Jump to Solution
911 0
cancel
Showing results for 
Search instead for 
Did you mean: 
RoW
5 - Automation Enthusiast
5 - Automation Enthusiast

Dear all,
With my team we are using airtable to do systematic reviews and extract data from science literature papers. For each study we created 2 rows and each one is assigned to a reviewer. So, for example, we have the coloumn “sample size” and reviewer 1 writes 89 in his record and reviewer 2 writes 98 in her record.
Let us suppose that there is an incrugruence. I am using rollup function to ask Airtable to show me the content of the two cells.
First I used rollup function ARRAYUNIQUE(values). It was ok but if a reviewer did not report the value he should had reported, I cannot see it and it might be useful to see this discrepance between filled and unfilled cells.
Thus I used the ARRAYJOIN(values) to see both the values. It fixes the problem above because I can see what both reviewer have extracted, but for long text it might be difficoult to spot differences.
So, here it comes my question. Is there any direct or indirect way to compare with (or without) rollup function two cell of two records and have a TRUE if they are exactely the same and FALSE if they are different or for example one is filled and the other is empty?

1 Solution

Accepted Solutions

Here is a sample base that

  • Checks for at least 2 reviews (more are okay)
  • Checks if all reviewers agree about a numeric value
  • Checks if all reviewers agree about a text field
  • Checks if an individual review is complete (has a value for both the number and text field)
  • Has an overall status that checks for
    • enough reviewers
    • all reviews are complete
    • all reviewers have the same values for both the number and text field

image

image

See Solution in Thread

3 Replies 3

Checking for missing reviewers

Create another rollup that counts the number of records, using the COUNTALL aggregation function.

You can then create other formula fields off that count. If the count is too small, then you know someone hasn’t completed the review.

Checking for discrepancies in numeric values

If the values are numeric, you can have two more rollups, one use MIN and another MAX to get the minimum and maximum values. Then use another function to compare those rollups. If both numbers are the same, then the reviewers all entered the same value.

Checking for discrepancies in non-numeric values

If you will always have exactly two reviews, you can have a formula field that compares the results of the ARRAYJOIN rollup with a doubling of the ARRAYUNIQUE rollup.

IF( ARRAYJOIN({unique}, {unique}) = {join}, 
  "same", 
  "different"
)

Here is a sample base that

  • Checks for at least 2 reviews (more are okay)
  • Checks if all reviewers agree about a numeric value
  • Checks if all reviewers agree about a text field
  • Checks if an individual review is complete (has a value for both the number and text field)
  • Has an overall status that checks for
    • enough reviewers
    • all reviews are complete
    • all reviewers have the same values for both the number and text field

image

image

Angela_Howard
4 - Data Explorer
4 - Data Explorer

I looked at your base! I'm just trying to create a formula that alerts me if "TextJoin" and "Text Unique" are different. How can I do that?