If new record has duplicate "name" field, make original record "outdated"

[Henry_Denais]

Hello there everyone,

I have really been struggling with this and could really use some insight from the forum!
Any help appreciated.

I am trying to make a database that takes surveys from the same people on a yearly-biyearly basis.
I would like records sorted by “Current” or “Outdated”.

When a person fills out a record, the primary identifier is their (Name) + (Date Submitted).
I would like the same person to fill out the same survey later, and due to their (Name) being identical, make the older record “Outdated”, make the new record “Current”.

For a little more detail, IF Old Record’s (Name) = New Record’s (Name), then compare (Date Submitted) between the two. If the date of the old record is older, return (Current) = “Outdated”, else (Current) = “Current”.

This could also be a checkmark, so checkmark if “outdated”, empty if “current”!

What do you guys think, is this doable in Airtable?
Thanks in advance,
Adam

[Jeremy_Oglesby]

Definitely possible:

You’ll need to create a “People” table, and a new field, call it “Person”, in the “Surveys” table that links to it (this will be separate from the “Name” field in that table in which the respondent enters their name).

Then you’ll select the entire “Name” field (the one where the respondent types their name), and use the drag-and-fill handle to drag all the names into the “Person” field - this will populate the “People” table for you.

Now you need a Rollup field in the “People” table, call it “Latest Survey”, that returns the MAX(value) from “Surveys”–>“Date”.

Back in the “Surveys” table, make a Lookup field, call it “Latest Survey”, and lookup “People”–>“Latest Survey”.

The final piece - the formula for your “Current?” field is:

IF(
   {Latest Survey},
   IF(
      {Latest Survey}>Date,
      "❌",
      IF(
         {Latest Survey}=Date,
         "✅",
         "🔔 LINK RECORDS 🔔"
      )
   ),
   "🔔 LINK RECORDS 🔔"
)

You can change the alert to whatever you want to remind you that in order to find the latest survey, you must first link it to a “Person”.

Here’s an example if you need to see it all in context:

Airtable

Check Against Old Records - Airtable

Explore the "Check Against Old Records" base on Airtable.

[Henry_Denais]

Jeremy,

I am giving you a standing ovation. I really can’t thank you enough for the example, I will base what I do off of what you have provided.

I don’t know the names of the people responding; I kinda forgot to mention that 😜
It looks like the responder would have to choose their name from a bank of existing people and it looks like the administrator would have to manually link the records for this to work then?

I was hoping it could all be automated, but I understand Airtable has its limitations.

Thanks again Jeremy!

[Jeremy_Oglesby]

Ok, so the people responding don’t fill in their name?

I set it up where you have to manually link the records specifically so that the respondent would not see a list of all the names and choose theirs. I figure you don’t want survey respondents seeing all the names of your other survey takers.
It can work in an automated fashion if:

1. Each potential respondent to the survey already exists in the “People” table
2. You are ok with respondents seeing a list of the names of everyone who responds to the surveys (from which list they will choose their name

And in this case, you’d only need the “Name” field, which itself would be a link to the “People” table, rather than the duplicate fields “Name” and “Person”.

If they submit their name via the form, and they submit the same name each time, then what I linked will work to hide respondent names from the person filling out the form, but forces manual linking.

If they don’t submit their name via the form, how do you ID from whom the survey is coming?

[Henry_Denais]

The people responding do enter their name via the form.
I will never have a record beforehand of who is going to be responding, so I can’t type them out in a list.
All I know is that after a year or two they will be asked to resubmit the same survey.

But anyway, you are right. I wouldn’t want them to see a list of everyone who has taken the survey.

It looks like it may not work the way I intended… but this is highly useful knowledge to me none-the-less.

One workaround I may have is to just group records by year instead, so that if one filled out a survey every two years, their name could show up multiple times, and you wouldn’t know how many surveys someone had, but you could at least know that the information from the past two years is most up to date.

If I wanted to manually maintain these surveys by linking them every so often that may work.

[Jeremy_Oglesby]

This is true. It’s still a pretty fragile solution since a respondent could enter their name differently each time.

You might want to add some additional fields to normalize that:

1. Make sure there is a separate “First Name” and “Last Name” field, both “required fields”, that show on the form, so respondents only and always put those two (no middle names, etc - or, you may need a separate “Middle Initial” field that is also required to ensure you don’t have multiple John Smith’s responding and being lumped together)
2. Have a third field in your table that combines them and normalizes them in case respondents don’t capitalize, or use caps lock, etc - users do strange things sometimes.

Your “Normalized Name” field would have this formula:

CONCATENATE(
  LOWER(
    TRIM(
      {First Name}
    )
  ),
  " ",
  LOWER(
    LEFT(
      TRIM(
        {Middle Initial}
      ),
      1
    )
  ),
  " ",
  LOWER(
    TRIM(
      {Last Name}
    )
  )
)

This will cause all names to be formatted the same before being linked to a name in the “Person” table, to help mitigate some user error/stupidity/shenanigans. So your table looks like this now:

So now you’d highlight and drag-to-copy the “Normalized Name” field into the “Person” linked record field.

[Henry_Denais]

I just learned something new (normalizing). That makes so much sense! I really appreciate your explanations.
Do you think you could share this updated base with me?

I am just curious, what is your work/career background in?

[Jeremy_Oglesby]

Henry_Denais:

Do you think you could share this updated base with me?

Actually, I made those same changes in the base I had already shared. If you just refresh it, or reopen from the same link above, you’ll see the changes. You can then copy that base into your own workspace using the “Copy Base” button in the upper right (if I remember correctly).

Henry_Denais:

I am just curious, what is your work/career background in?

I have a pretty mixed background. I was a teacher for a while - so I may have a knack for explaining things. Currently, I work QA in the construction industry - but one of my main jobs is to keep all our records straight. I use Airtable for a lot of them, and I also am self-teaching/dabbling in web development and software engineering to develop some custom web systems where other systems don’t work - so picking up on the use of formulas and functions may come a bit quicker to someone used to seeing programming languages in use.

Glad to be of help, Henry. Now go show your boss that you are indispensable to him because of what you know!

[Henry_Denais]

I am actually in architecture, learning programming on the side. Airtable has been a very visual experience that is helping me understand all kinds of relationships!

I appreciate your time Jeremy, have a good day!