Not overiding current fields during import

Sheila Lutz

I have an import list with birth date years, but not the month or date. I don't want to override full birth dates already entered for a constituent when importing. Is there a way to import into a field only if it is blank and override entry for not blank fields?

Kylea Johnson

I would do two imports. One for records with a blank birth date, adding the birth year, then a second import for those who already have the birth date field filled out, adding the other information you wanted to update but not messing with birth date field.


My unprofessional opinion. Maybe somebody else knows how to do this in one import.

Sheila Lutz

That sounds simple enough, which is exactly what I'm looking for, so thank you for that! The only thing is I don't know how to split them out and the overlay I'm importing is 25,000 records, so I can't just check. I was hoping for some sort of "if, then" option in the import itself, but I'm not sure if that's even possible. If not, any ideas on how I could split them up into records with data in the field and records without? I've already uploaded the other information, so it would just be this one field.

Sheila Lutz

I should also add that the overlay spreadsheet came from a 3rd party, so I can't turn it into a query.

Kylea Johnson

There's no "If-then" for import (as far as I know... but there's a lot of magical gurus in Community). I'm assuming since you're updating records you have a method of ID'ing them in the database whether by social or constituent ID?


Let's say you're using constituent ID (this method will work for socials too, I believe). Copy all of the ID's from the spreadsheet that have only the birth year, then paste them into the "constituent ID" field in Query. You can do this by opening constituent ID in query, scrolling down a bit, then pasting into the bottom square. I know it sounds weird. I hear there is a limit on the number of ID's you can paste into the ID query field so keep an eye on it. I think it might be in the range of 3,000? If the method works we'll see if we can find where somebody posted that. Then do AND birthdate=blank.


Export this list and re-import with the new birth years!


If you're using another method of ID within the database we may need to brainstorm a little more about how you can export them.

Sheila Lutz

That's a great idea, I didn't know I could do turn a list of IDs into a query, and support never suggested it was possible. I think because of the immediacy I'm going to turn Birth Year into an attribute and upload them that way, but ideas on how to put them in the actual birthday field later on would be greatly appreciated. Thank you all for the support!

Sheila Lutz

Kylea Johnson:

There's no "If-then" for import (as far as I know... but there's a lot of magical gurus in Community). I'm assuming since you're updating records you have a method of ID'ing them in the database whether by social or constituent ID?


Let's say you're using constituent ID (this method will work for socials too, I believe). Copy all of the ID's from the spreadsheet that have only the birth year, then paste them into the "constituent ID" field in Query. You can do this by opening constituent ID in query, scrolling down a bit, then pasting into the bottom square. I know it sounds weird. I hear there is a limit on the number of ID's you can paste into the ID query field so keep an eye on it. I think it might be in the range of 3,000? If the method works we'll see if we can find where somebody posted that. Then do AND birthdate=blank.


Export this list and re-import with the new birth years!


If you're using another method of ID within the database we may need to brainstorm a little more about how you can export them.

I wish I could make this a "great answer"?

Kylea Johnson

?

Catherine Burns 2

Hi Sheila


Kylea's suggestion will work well, but the limit for pasting into a criterion is 500 IDs. You can have multiple "ID is one of" criteria linked with OR but if you have thousands of records this is a better option - create a query from Importing a column of ID numbers see https://kb.blackbaud.com/articles/Article/38639


I have heard that you can also get a third party product called Smart Paste to allow you to paste more than 500 records into a query but I have never bothered as the ID import works like a charm.

Cammi Derr

Use SmartPaste and RE "one of" to gather the constituent IDs or constituent import IDs and the birthdate in a query that you export.

If you are trying to "Overwrite", then I would parse the birthdate out in excel for month and date using excel formulas left, mid and right.

Then concatenate the correct year with the month and day.  You do not need to include the slash between each when importing.

Then import back into RE which will overwrite the current date.