I have converted most of my pre-processing over to the API which is great! I can't wait to do a full import.
One last thing that I am having a lot of trouble with is spouse email addresses. Here is the situation and any suggestions are greatly appreciated.
We have an online database which is keyed on email address meaning that everyone with a different email address has a different account. This online database is what we use for online donations that get imported into RE through IOM. In RE, we use non-constituent spouses and in the record we store the main email as "email" and the spouse's email as "spouse_email". Sometimes both people donate, or one donates online and the other offline. Either way, when we get the data from our online db we have first & last name, email + (sometimes) the id of the RE record.
So the problem is this: How do I use IOM to put the email address in the correct type when it matches the ID of the main record?
Jane Doe: email@example.com, REID 1234
John Doe: firstname.lastname@example.org, REID 1234
John Doe (full constituent)
email = email@example.com
spouse_email = firstname.lastname@example.org
Spouse: Jane Doe (non-constituent)
Jane Doe, $100, email@example.com, REID 1234
Or, worse yet: John & Jane Doe, $100, firstname.lastname@example.org, REID 1234
How do I match that record & create the gift for John Doe but have the email address go into spouse_email? API ideas? Assume that I cannot just make all spouses constituent records. I am willing to have some manual interaction on this but I would like it to be contained in IOM. Like choosing he appropriate phone type in a pop-up or something.
> We have an online database which is keyed on email address meaning
> that everyone with a different email address has a different account.
This drives me insane. FYI, BSD is not the only online database that does this, tragically.
> Assume that I cannot just make all spouses constituent records.
Can you tell me a little more about that? Is that because of the work involved in promoting a bunch of existing non-constit spouses to full constituents, or is it PIH's way to enforce householding of gifts?
Another question I have is about storing the "spouse_email" on the main constituent... shouldn't that go on the spouse record?
In BSD you can actually have multiple emails on the same profile (recent feature...). The crux of the problem is that you can't have relationships between profiles. The way that I handle this now is by having two external IDs that I upload to BSD, one called RE7P and one called RE7S. RE7P is the id if the profile is the Main record in RE, RE7S is the id if the profile is the Spouse record in RE. Then, when I process the file before IOM I move the email address from the "email" column to the "spouse_email" column if RE7S is set. It works, barely... I know there is a better solution than that.
We have talked about converting to full constituent spouses but there is a lot of hesitation here about householding and consistency of tracking after the "upgrade".
I don't think they are worried so much about gifts on the new records since I believe you have a tool that does that. But they are worried about Salutation/Addressee, actions, notes, etc. that were all kept on one record that would either have to be duplicated or somehow sorted out between the two records.
There are also some questions about householding and reporting that I can't remember off the top of my head. Maybe we can talk more about what would need to happen to get this to work?
And having the email on the spouse address does make sense but I think people were confused about linking addresses and having them update through address finder. Now that you bring it up again I'll go back and figure out why this wasn't a good solution. It still doesn't really solve the problem of choosing where to put the email address that comes in from the donation unfortunately.