Formula to reveal duplicate rows

[Sam_Bradley]

Hey,

I hope you’re well and might be able to help.

I’ve created a table which records safety test results for some equipment. Each time a test is completed, it’s created as a new record and there’s a lookup field to link each test back to the piece of equipment that has been tested. It’s all working nicely.

However, as we start to add more and more tests, there’s times where the same piece of equipment is tested more than once. This is causing some problems with some of the reports we generate, as the reports should only show the most recent test result.

Does anyone know of formula I can create that can check if there are ANY other records in the table with identical values in the lookup field and IF SO, display a “TRUE” or “FALSE” result according to whether the particular record is the latest one?

Thanks in advance,
Sam

[ScottWorld]

For a manual solution: You can manually run the Dedupe Block whenever you want, to check for the presence of duplicate equipment numbers within your safety testing table.

For an automated solution: Back in your Equipment table, add a count field. A count field is actually a special type of field in Airtable which will automatically give you the count of your linked safety testing records. Once you have this field, you can create a formula field that displays true or false based on the value in the count field.

[kuovonne]

ScottWorld:

Once you have this field, you can create a formula field that displays true or false based on the value in the count field.

However, Scott’s method will not automatically tell you which of the two records is the most recent one.

I wrote a script to identify the first/last linked record. You might be able to adapt the script for your purposes.

There is an automatic option to identify the last linked record, but it involves a complex system of rollups, lookups, and formula fields.

[Justin_Barrett]

EDIT: See my latest note below. I don’t know what I was thinking when I wrote this, but I jumped through some unnecessary hoops here. There’s no need to do date-to-number conversion. Just roll up the dates and run MAX(values) on them directly.

---

There’s a way to automatically mark the latest test on a given piece of equipment. Here’s the base setup I created, which I believe is similar to yours. Here’s my [Tests] table:

And the [Equipment] table:

In the [Tests] table, I added a Formula field that generates a number based on the date:

In the [Equipment] table, I added a {Latest Test} Rollup field, which isolates the highest of those unique numbers. Because that number is built from the date, that corresponds to the date of the latest test:

Back in [Tests], I added a Lookup field named {Most Recent} to pull this value back in:

Finally, I added a Formula field named {Latest?} that looks for a match between {Test Number} and {Most Recent}, displaying a green check where there’s a match.

With the {Test Number} and {Most Recent} fields hidden, it’s easy to see which test is the latest. The {Latest} field can also be used in view filters and elsewhere to isolate the latest test for each item.

NOTE: If you have equipment that is tested multiple times per day, and capture the test time in the {Test Date} field, you can modify the {Test Number} formula to include time as well, and the rest of the process will still work:

IF({Test Date}, VALUE(DATETIME_FORMAT({Test Date}, "YYYYMMDDHHmm")))

[Sam_Bradley]

Thanks Justin, this is super helpful. I followed the step by step you outlined and have now been able to use that to setup a view that shows only the latest tests, which is great, thanks!

One last little thing I’m struggling with though (and I wonder if I can pick your brain on this) is whether I can show only the latest test in a linked table.

For context…

On my PAT Tests table I now have a field, like yours that identifies whether the record is the latest test or not.

This has enabled me to create a view which has a filter applied to show only the items in that collum which are marked “YES”. In other words, the filtered view shows only the latest tests.

You can see this works nicely. On the first screenshot, there was an item that FAILED when first tested, then on the latest test it PASSED. So when I view my filtered view, the initial fail is not shown since it’s not the latest test.

So now on my ‘Physical Inventory’ table, I just want to have a collum that shows when the item was last PAT tested as per the filtered view I have on the PAT tests table.

Is there any way to do this?

[Justin_Barrett]

Sorry that I didn’t see this sooner. To alert users that you want their attention, type @ followed by their full user name. Example: @Sam_Bradley (the community interface will display a helpful list of users as you begin to type the name, so you can pick from the list and ensure that the correct user is tagged).

Sam_Bradley:

So now on my ‘Physical Inventory’ table, I just want to have a collum that shows when the item was last PAT tested as per the filtered view I have on the PAT tests table.

You’ve already got this data as part of the system we just built to identify the most recent test. Remember, that {Latest Test} field in my [Equipment] table (that would be the [Physical Inventory] table for you) is a number based on a date, so it represents the date of the most recent test. To convert it back into a date, use this formula:

IF({Latest test}, DATETIME_PARSE({Latest test} & "", "YYYYMMDD"))

[Justin_Barrett]

I feel like an idiot. There’s no need to convert the date to a number. Just rollup the dates, and use MAX(values) directly on them, which will spotlight the most recent date. That eliminates the need to convert the number back to a date at [Physical Inventory], and also eliminates the date-to-number formula field in the [Tests] table.

[Orin_Hutchings]

Hi @Sam_Bradley,
As a third-party solution, you can use zzBots to set up a bot that will automatically flag any duplicate records in your table based on customized conditions. For example, you can have your bot check the name or date of the equipment checked in each record, and if one or more already exists, it will mark the chosen field in the duplicate record as “true.” This can be fully customized to your specific needs.

You can get this set up and learn more about it here: https://www.zzbots.com/community/how-to/automatically-flag-duplicate-records-in-airtable

Disclaimer: I am affiliated with zzBots