Start a new topic

Importing disbursement statements from 3rd party agencies

I'd just like a quick sanity check before I spend any more time in the manual!

We receive a good number of donations via third-party giving organisations, and transcribing their disbursement statements into RE is a chore. We get documents like this (anonymised):

Foolandia Charitable Giving - disbursement statement - 20/Jan/2015 - paid to IoMUser Charities
Foo ID Donor Name Donor Address 1 Donor Address 2 Donor Address 3 Donor Address 4 Donor Address 5 Donor Postcode Donor Country Donation Amount Number of items
998877 Jo Bloggs 123 Example Street London AA11AA United Kingdom 2.5 1
887766 Davina Donator 42 Eureka Drive Feltham BB22BB United Kingdom 100 1

and I expect that digesting this kind of document is what ImportOmatic is built for. I would like to build a workflow where my colleague will take this statement, set the default date on the template batch to '20/Jan/2015', load the statement into IOM using the appropriate profile, and get a batch file with new gifts for pre-existing constituents ready to review and commit.

However I am at a loss - the profile seems to expect a constituent code, but "Foo ID" is not the same as our constituent ID (and cannot be). I used "ODS - Full name for parsing" on the Donor Name column and set up virtual fields for the ODS Title/First Name/Last Name, linked to RE Constituent Title/First Name/Surname, but this does not work in the 'test' mode (as indicated in the screenshot) nor when importing a file in 'validate' mode (it generates exceptions, as there is no Constituent ID in the import file).

image

 

I tried another approach, I created a constituent attribute, type text, named "Foo ID", and manually set it to the Foo ID of the donors in the test batch. But I cannot find how to get IOM to identify constituents by matching a field with a constituent attribute, and in fact saw a post somewhere that says it's not possible to match on attributes.

If anyone can give me any pointers it would be greatly appreciated!


I seem to have solved the problem!

per 3rd party agency:
1 - set up a text constituent attribute, "Foo ID"
2 - manually populate Foo ID on each constituent record who donates via Foo
3 - set up a query that returns Constituent ID and Foo ID on everyone who has a Foo ID
4 - export the result to a spreadsheet
5 - copy the two columns to a dictionary in IOM
6 - apply the dictionary to the Foo ID column to convert the data to Constituent ID
7 - import the gift value from the relevant field
8 - set up virtual fields for the invariants (fund ID etc) which are static. (Batch and gift defaults didn't work; fields I'd set defaults for would be blank or have different values.)
9 - also set up a static field for the date

User then edits the virtual field for the date to match the paydate, and imports the disbursement file. A bit cumbersome to update though, but it works!

Could you store "Foo ID" as a Constituent Alias then use the auto-pick option on Alias match?

 

image



Thanks Jeffrey. That would risk false positive matches where the same ID number is used for different people by different 3rd-party organisations. This could be fixed by storing the 3rd-party ID with an organisation-specific prefix and using a formula to add that prefix to the incoming disbursement statement but we really want to minimise the Excel-wrangling. Unless there's a way to create a virtual field which concatenates the prefix with the Org ID field in the incoming file?
Hi Jack.

We have this problem and solve it using aliases as Jeff describes. To prevent collisions / duplicates, we have a dictionary for each disbursement agency that prefixes their ID with a code. Here's the one we use for CAF GAYE:

Value to match - "(.+)"
Value to replace - "PG_CG_$1"
Regex - ON


Additionally, you could create different alias types for each agency, just to keep things neater.
Login or Signup to post a comment