Spouse Phone Import

Dariel Dixon 2

I'm beating my head trying to import a set of spouse phone IDs. After creating an import file, I have been getting a SPPhoneImpID Record does not exist error. I've looked through the knowledgebase, but I'm not really seeing what I need. I used the same import to create the import file, so I don't know why it's not working.

Faith Murray

@Dariel Dixon, it will be difficult to troubleshoot without seeing a sample of the data file (anonymized) and the import settings.

Here are the KB articles I could find with a similar error:

• Phone record is inactive
• Phone Import ID is too long
• Phone extensions are mismatched in Fields
• Doublecheck required fields
• Make sure Relationship Link field comes first
• Make sure Import IDs are unique

Other obvious troubleshooting idea that I'm sure you have already tried, but warrants listing:

Make sure you are using a Constituent-Individual Relationship Phone import type. Set it to “Import New Records” rather than “Update Existing Records”. The new records it is importing are the phone numbers, so setting it to update existing records will make it try to update existing phone records, not update new numbers on existing spouse records. I think! I've never done one of these specific import types before, so test on a single record first before doing the whole batch.

Dariel Dixon 2

I think that's the craziest part @Faith Murray. I am trying to update existing phone numbers. As I'm using the phone import IDs, there shouldn't be any reason for me to get that error. Especially since I just pulled them out using the import file. I've also got the relationship import IDs in the file as well.

This is a new import for me as well, but I think there's something with these imports that doesn't work. I'm using the Constituent - Spouse Phone Import type.

Here is the screenshot of the import settings. I used this same import type to create the import file. Since these are updates to existing phones, I wouldn't be able to create a data file for you, but you could create one using this import, and use that to test.

I saw the first knowledgebase article, and it comes the closest. However, there's something about it that's off. I don't want to have to change the phone type and delete the phones to just change a flag on them. However, if that is the solution I'll have to determine another way because that seems somewhat counterproductive.

Faith Murray

@Dariel Dixon, if you would like to send me some snips of your import settings and the first 3 lines of an anonymized data file, I can try to replicate on my system and help troubleshoot? I'm afraid imports are so touchy, I usually have to get my hands into them.

Faith Murray

@Dariel Dixon, can you send the field headers you are including?

Dariel Dixon 2

@Faith Murray It's a very simple file. It should be have been relatively straightforward, or so I thought. Granted, I've tried this numerous ways. I've added in Constituent IDs (it's important to note that not all of these records are constituents, but since I have the relationship import ID…)

I've tried this as a constituent import (failed miserably). I haven't tried it as a Individual relationship import, but that's next on the agenda.

Faith Murray

@Dariel Dixon, okay thanks! I'm almost off work now, so I'll tackle it in the morning and try to replicate it. If it's something inherent to the import itself, it should glitch on my end as well. I'll try it with Active and Inactive settings and see what happens.

Faith Murray

@Dariel Dixon, I ran some tests and this is what I found. Much as in the KB articles below,

• Any phones marked Inactive produced the error during import regardless of how I had my import set up. These will need to be queried and global changed before they can be updated via Import, or else imported as new phone numbers altogether.
• All records on the import produced the error if I did not have my SPPhoneIRImportID header as the first column in my spreadsheet, even the Active phone numbers. You have this field as your last column. Moving this field to column A in the csv file may at least allow Active phone numbers to be imported successfully.

Let me know if this helps any or if I should keep experimenting.

Dariel Dixon 2

@Faith Murray These are all interesting findings, but I can't replicate them at all. In my file, I have 80% of my records are marked inactive, but less that 5% are able to import. Even after moving the files around, I still got the same results.

Global change is not an option, as these fields aren't even available. If I could mark them as inactive, I wouldn't have to do the import. It's frustrating that I would have to delete them all and re-enter them, but if that is the case we may have to rethink the whole process.

Faith Murray

@Dariel Dixon, I ran it on a larger test file, and I also encountered the error when a spouse was also a constituent. Almost none of our spouses are independent constituents, so I didn't have this error on my smaller test file.

Do you normally create independent constituent records for spouses? Would you be able to identify if this accounts for the problematic ones?

Dariel Dixon 2

@Faith Murray Unfortunately, there's no way to know. Some spouses are constituents, some are not. The only thing I know for certain is that they are in this query. I haven't been able to determine what the ones that are going through have in common. It doesn't appear that the common trait is in the file, as it's only has a handful of fields. I don't want to have to update them all by hand, and if I have to try to change them all to a different phone type to delete and re-enter, I guess that's what has to be done. I'm just saying that it shouldn't have to be done.

La Donna Borth

Hi @Dariel Dixon and @Faith Murray - not sure if this will help, but when in a Constituent Query, if you look in the Available Fields under Spouse, you'll see “Constituent ID." If you select this as criteria, it displays as “Spouse ID” in the Filters. So, a blank Spouse ID should be spouses that do not exist outside of the Constituent record. At least, that's what I see in my query output.

Not sure, but, maybe being able to split out the non-constituent spouses will help (caveat - I have not queried for those with a Spouse ID).

Hope this helps.

Faith Murray

@Dariel Dixon, at this point I agree with you. I think you could, as @La Donna Borth suggests, identify them through the Spouse ID field as an additional column in an export/import file and then match/sort in excel. But to what end? Even if you split spouse constituents and invalid phone numbers to get to the ones you CAN import, you still have the majority that you can't import.

Deleting and re-entering seems the most direct option. But BB does need to fix all these errors. Maybe you can launch an Idea request or bring the matter to the attention of Support, and get it included on a future patch update.

Alex Wong

@Dariel Dixon
I have done a lot of global change and importing various different types of record, including phone/email import. That said, can you tell me what you trying to do? You did mention in the OP that you trying to “import a set of spouse phone IDs”. Through reading the replies, I deduce that the phone records exists already and you are trying to “update” the existing phone record's information. Which piece of info on the phone record are you trying to change?

Dariel Dixon 2

@Alex Wong:

@Dariel Dixon
I have done a lot of global change and importing various different types of record, including phone/email import. That said, can you tell me what you trying to do? You did mention in the OP that you trying to “import a set of spouse phone IDs”. Through reading the replies, I deduce that the phone records exists already and you are trying to “update” the existing phone record's information. Which piece of info on the phone record are you trying to change?

Great question @Alex Wong. I'm simply trying to change the inactive flag on these records. It should be relatively straightforward, but it's not. Ideally, I would be able to make this change as a global change, but the field isn't available. Therefore, I turned to import to try to change this flag.

Alex Wong

@Dariel Dixon
Ok, looks to be pretty easy, but I think I know why you running into trouble.

A few things to clear out of the way:

• the order of column does not matter, so import id (relationship import id nor phone import id need to be first column)
• you cannot use import to update a phone record that is inactive, you will get record not found error. This sounds more a bug to me than how it should work, you should report this to Blackbaud
  • I did not read any of the KB posted on the post, so maybe one of the KB mentioned it, but I just went and tried it. I was able to import and change an active phone to inactive (SPPhoneIsInactive=TRUE), then using the same import file (updating SPPhoneIsInactive=FALSE) I will get the record not found error as well
• you cannot use Spouse Phone import to update a phone record of spouse where spouse is a constituent record, you will need to use Constituent Phone import to do that. However, BB error message isn't helpful here, it also tell you record not found.

That all said, to do what you want to do without problem, you will need to properly prep your import file, it is a few steps:

• It appears you already have a csv file from the Spouse Phone Import > Create file, it has the Relationship Import ID (SPPhoneIRImpID), so you can use this ID as a “key”
• Create a constituent query with crtieria that you may already have (since you likely have a constituent query used to create the import file from), PLUS an additional AND condition of Relationship > Individuals > This Individual Is The Spouse = Yes. Output: Relationship > Individuals:
  • Import ID (will be used as “XLOOKUP” from “key” i mentioned (SPPhoneIRImpID)
  • This Individual Is A Constituent
• Use XLOOKUP (there are plenty of video or tutorial out there on how to use this, so I'm not going to go into it) from your import file, to bring in the “This Individual Is A Constituent” column into your import file.
• So now from your import file, you can filter out (move these rows to another excel to process differently using Constituent Phone import) the “This Individual Is A Constituent”=Yes.
• Given you are trying to change the phone from active to inactive, I'm going to assume your import file original spouse phone are active to begin with, so you won't run into problem of “record not found” b/c the phone record is inactive. However, if there ARE spouse phone record that IS inactive, then these being exception isn't a problem, b/c the record is already inactive and you are trying to make it inactive. no harm.

Dariel Dixon 2

@Alex Wong:

@Dariel Dixon
Ok, looks to be pretty easy, but I think I know why you running into trouble.

A few things to clear out of the way:

• the order of column does not matter, so import id (relationship import id nor phone import id need to be first column)
• you cannot use import to update a phone record that is inactive, you will get record not found error. This sounds more a bug to me than how it should work, you should report this to Blackbaud
  • I did not read any of the KB posted on the post, so maybe one of the KB mentioned it, but I just went and tried it. I was able to import and change an active phone to inactive (SPPhoneIsInactive=TRUE), then using the same import file (updating SPPhoneIsInactive=FALSE) I will get the record not found error as well
• you cannot use Spouse Phone import to update a phone record of spouse where spouse is a constituent record, you will need to use Constituent Phone import to do that. However, BB error message isn't helpful here, it also tell you record not found.

THIS!!! The error messaging for this could be more clear here. This makes sense, but the biggest issue that this import doesn't have any constituent information as an option, and there's nowhere where that is noted. I thought that may be part of the problem (as @La Donna Borth and @Faith Murray both surmised), but to have it return the exact same error code had me looking around for a common trait when there were two different issues at play. I agree that this does seem to be a bug, but Blackbaud per one of the KB articles does not seem interested in fixing it.

That all said, to do what you want to do without problem, you will need to properly prep your import file, it is a few steps:

• It appears you already have a csv file from the Spouse Phone Import > Create file, it has the Relationship Import ID (SPPhoneIRImpID), so you can use this ID as a “key”
• Create a constituent query with crtieria that you may already have (since you likely have a constituent query used to create the import file from), PLUS an additional AND condition of Relationship > Individuals > This Individual Is The Spouse = Yes. Output: Relationship > Individuals:
  • Import ID (will be used as “XLOOKUP” from “key” i mentioned (SPPhoneIRImpID)
  • This Individual Is A Constituent
• Use XLOOKUP (there are plenty of video or tutorial out there on how to use this, so I'm not going to go into it) from your import file, to bring in the “This Individual Is A Constituent” column into your import file.
• So now from your import file, you can filter out (move these rows to another excel to process differently using Constituent Phone import) the “This Individual Is A Constituent”=Yes.
• Given you are trying to change the phone from active to inactive, I'm going to assume your import file original spouse phone are active to begin with, so you won't run into problem of “record not found” b/c the phone record is inactive. However, if there ARE spouse phone record that IS inactive, then these being exception isn't a problem, b/c the record is already inactive and you are trying to make it inactive. no harm.

I'm going to try these steps. Now that I know that phones that are already inactive are going to cause an error/exception, I can account for that. Also, the error message needs to be cleaned up here, as there isn't a problem with finding the ID, but with the processing. Reading the error message itself and finding a “record does not exist” gives one a false impression.

Dariel Dixon 2

FINALLY!!! We have the results. So, here's the deal. As @Alex Wong, @La Donna Borth, and @Faith Murray mentioned, non-constituents and constituents need to be separated out. That seems pretty straightforward, but you'll see both in the file I was creating, and there's no data in the file that you can use to easily determine which records are constituents and which are not, without external data. Also, inactive records cause an error as well, which is a bigger issue as there in no way to update these records outside of manually editing each record as the fields are not available as a global change. So these records have to be removed as well.

To resolve this issue, I created the extra query with the fields @Alex Wong mentioned, but I also added to the output the field of Spouse > Spouse Information > Spouse ID. When I used the lookup function in Excel, I was able to append both the “Is a Constituent” field and the Spouse ID which is the Constituent ID for the Spouse record. Now that I think of it, you may be able to get away with just having the Spouse ID and may not need the other field. When I went to separate my data file into the constituents and the non-constituents, I had all the fields I needed for the constituent phone import now that I have the Spouse ID field which I mapped to the Constituent ID field. The PhoneImportIDs were also the same, so there didn't need to have any manipulation there.

I appreciate everyone's help here. I think I would not have been so confused if the error message wasn't so misleading and generic. Also, not being able to update inactive phones is a problem, but I wish they would at least mention that somewhere.

Alex Wong

@Dariel Dixon
That's great that you are able to do what you needed.

If you do have a LOT of records to import/change. Then for the “inactive” constituent record, you can do a separate import (or better yet, just do a constituent global change using a static constituent query targetting specific constituent that's inactive) to make these record active, ONCE you done with spouse phone import, THEN change the constituent back to inactive with the import file/global change.