Processing Contribution.csv not picking up IndividualID

[ElimAdmin]

Transactions are being loaded to the batch correctly, but for Giver Anonymous, not the person in the row specified with IndividualID is the person's Person table Id value.

I have a Person with Id=2010. I have the Contribution.csv row with IndividualID=2010. When importing the contribution, the ProcessByPersonAliasId and AuthorizedPersonAliasID values in Financial Transaction table are set to the Giver Anonymous person ID.

Bulldozer.CSV/Maps/Financial.cs line 693 onward.

I have even tried this with a person with only one record in the PersonAlias table, but same thing happens.

[treyhendon]

The Person Id has to be in the individual's Foreign Key and/or Foreign Id columns on the Person Alias table. Bulldozer does not evaluate Rock Person Ids.

[ElimAdmin]

@treyhendon Ah! Of course! Thank you for your answer. That would be expected for a full person plus transaction import. In my case I'm just important financial transactions out of QuickBooks. We need a mechanism to keep the two in sync. (Is there a better way?). So since I no longer need the Foreign Key values in PersonAlias (we are not importing people), I guess I could just reset them to be the same as PersonId?

[ElimAdmin]

@treyhendon Another way would be to add a column to the Contribution.csv table, PersonId, and if this is set then select from PersonAlias looking for Person Id, instead of the foregin key id.

I think I like option 1 above better (quicker, no enhancement work :) ). I'm guessing that once importing is complete, there is no ongoing need for the foreign key values so there is no problem in them being reset?

[treyhendon]

Sorry for the delay @ElimAdmin I've been onsite with a church and am just getting caught up. Regarding the options here are a few thoughts.

• Option 1 (update FK of people to be a valid Person Id). That would work, however, you'll always have to keep up with that in Rock as the FK on new records and then add that Id in QB as well for the QB export. (See my first suggestion below for a way to "flip" that which might still work and save some headache ongoing.)
• Option 2 (new map column). We've talked in the past about ways to possibly "switch" Bulldozer from Foreign Id to the Rock Id. So far I've held off on that because we have intended Bulldozer to be a one-and-done kind of tool for final migrations.

It sounds like you're using QB Non Profit to record the transactions and then sending the transactions back to Rock. If that is correct, two thoughts:

• You could find the person't QB Id and set that as their Foreign Id in Rock. Then use the QB Id moving forward. (I would say this is probably my best suggestion at this time.)
• Could the finance team change to recording all transactions in Rock and then only enter Journals of the Rock Batches?
  • We have written a few Rock to GL plugins so far. QB is on my radar, but so far no one has been able to fund it. But plugins like this allow for transactions to be recorded in Rock and then at the Batch level, a simple import file is generated that would automatically create the Journal Entry in the GL software.

[ElimAdmin]

Thanks @treyhendon . We use a name for our donors like 'Tithe002' - where the 2 is our envelope number. We have this envelope number entered into Rock. So I'm currently testing the idea of using EnvelopeNumber (2) as the PersonId, and copying the EnvelopeNumber to the Foreign Id in Rock to match on. We plan to move to Xero at the end of the year - so this is a short term fix. We are about to start entering Sunday giving batches in Rock. But we still need a way to get automatic payments, and direct bank deposits, which will be entered into QB first, back into Rock to give donors a complete view of their giving. If the EnvelopeNumber doesn't work, I'll look at the QB PersonId.

What you have confirmed is: once the migration is "one-and-done" the Foreign Id can be modified and used like this. Thanks.

[treyhendon]

To use Tithe002 as the foreign id, you'll need to put that in the ForeignKey column, not ForeignId. Foreign Id is integer (whole number) only, while Foreign Key is string. For Bulldozer we expect distinct values in all Foreign Key columns. Other migration utilities use the Foreign Key column as an previous system identifier coupled with the Foreign Id as the value. For example Key = F1 Id = 2 but with Bulldozer, we would expect Key = 2 Id = 2. So for your example, Bulldozer would see Key = Tithe002 Id = null and this would work exactly as you'd expect for the linkage.

[ElimAdmin]

Awesome. The clarification of Key and Id helps. I was going to use just '2' as integer in ForeignId. Would this also work? So ForeignKey = NULL, ForeignId=2?

[treyhendon]

That would totally work! Or you could do both for good measure.