Duplicate Record WithOUT Duplicate Inventory Number

[Alba_Machado2]

Hi old Airtable friends,

It’s been a while. I’ve been using a formula that’s worked up until now:

IF([Book Number - Old}, {Book Number - Old}, “BN” & {Autonumber})

But now I see that when a book record is duplicated, the same old book number is duplicated right along with it and we need each and every book in our inventory to have a unique inventory number. Any ideas? Please and TIA. :slightly_smiling_face:

[Justin_Barrett]

Assuming that {Autonumber} is actually an autonumber field, this shouldn’t be possible. I just tested it myself, and duplicating a record still creates a unique number in the autonumber field. It doesn’t duplicate the original record’s generated number. Now, if {Book Number - Old} is what you’re referring to when seeing the duplication, that’s another issue entirely.

Could you please share a screenshot showing the problem?

[Alba_Machado2]

You’re right, a new unique number is created in the autonumber field. But I’m blanking on how to get that new number in the Book Number formula field. How do I work Date Added into the mix so that the {Book Number} formula field will pull from {Autonumber} if the record is created (or duplicated) after June 1, 2021? This formula switched ALL {Book Number}s to {Autonumber}s:

IF({Date Added}=“5/14/2021”,{Book Number - Old},{Autonumber})

[Justin_Barrett]

Alba_Machado2:

IF({Date Added}=“5/14/2021”,{Book Number - Old},{Autonumber})

Date fields—including calculated fields that returns dates—store and return a data value known as a datetime, which can’t be compared against a string like “5/14/2021”. That’s why your formula is failing. What Airtable does when comparing a datetime against a string is convert the datetime into a raw date string, which looks like this: “2021-05-14T00:00:00.000Z”. Even knowing that, though, this doesn’t make it possible to actually compare datetimes in string format (other than exact equality).

To compare a datetime from a field against a specific date of your choosing, parse your chosen date string using DATETIME_PARSE(), which will return a datetime data item that can be used in datetime comparisons.

Alba_Machado2:

How do I work Date Added into the mix so that the {Book Number} formula field will pull from {Autonumber} if the record is created (or duplicated) after June 1, 2021?

That detail that I marked in bold helps a lot. Using the DATETIME_PARSE() function that I mentioned above, this should work:

IF(
  {Date Added} >= DATETIME_PARSE("06/01/2021", "MM/DD/YYYY"),
  Autonumber,
  {Book Number - Old}
)