How to mass edit birth dates (birth year issue)

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered
We Have realized that Quickbase has automatically been changing birth years from 19XX to 2XXX. Because of that many of our birth dates on record are wrong. We want to mass edit them to the correct number since the amount of patients to edit is too large to do one by one.


Any way of doing this? Has anyone else experienced this issue?

Thanks
Photo of Christian

Christian

  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
This first thing I would do is back up your table data. Export the whole table in case something serious goes wrong and do a second export of just the [Record ID#] and [DOB] fields in case you just need to restore the [DOB] field to the appropriate record.

Then create a new text field [DOB2] with the following formula:
[DOB2]=
var Text Year = ToText(Year([DOB]));
var Text Millennium = Left($Year,1);
var Text CorrectYear = If($Millennium = "2",
"1" & Right($Year,3),
$Year
);
Month([DOB]) & "/" & Day([DOB]) & "/" & $CorrectYear
This formula should convert all the [DOB] fields that start with 2XXX to be 1XXX and leave other [DOB]s unchanged.

If you get the correct results in the [DOB2] filed, you can delete the [DOB] field and convert the [DOB2] field to a date field and all should be good.

If for some reason you need the correct data of birth data to land in the original field [DOB] and have the same fid value, you would instead convert the [DOB] field to a formula field with the formula [DOB2] and once converted perform a second conversion to convert the [DOB] field to a simple date field.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
I may have made a logical mistake in this code as I simply assumed the leading "1" in the year was changed to a leading "2". To correct the mistake I would need to know how QuickBase is changing the first two characters in a year. But the basic approach would be the same.