Hi,
I'm in a pickle...
Every year, when we send out our sub renewal forms, we include a 'suggested donation amount' that is automatically calculated depending on several factors. Basically, everyone gets a different suggested donation amount calculated on their previous donation history, performance attendance, etc...
In order to stay consistent with our donation asks, we want to start keeping these suggested amounts in Tessitura as attribute values. As of now, they are kept on an excel spreadsheet.
I'm looking for the easiest way to import these values and the attribute (which I've created in T_KEYWORD as id = 475). In addition to this, the account data to be imported should not create new duplicate accounts. I'm attempting to MATCH the records from the spreadsheet to the existing records. I vaguely remember the Contribution Import Utility being more flexible and is able to match up constituent IDs, but I hate the idea of creating a batch and what seems like a lot of work JUST to import an attribute and their values! It seems like it should be pretty simple... right?
Has anyone else tried something similar to this before and had success? I'd love to hear that the solution has been staring me in the face this whole time. :)
Any help would be great.
Thanks!
Hi Tiffany,
For what you are trying to do, Contribution Import is not going to give you much. If you want to load the suggested values as attributes from an external spreadsheet you'll need to do it in SQL.
Assuming you have both the suggested amount and the customer number in the spreadsheet, you can import the spreadsheet into the database in a temporary local table and then write something like:
insert into TX_CUST_KEYWORD (keyword_no,customer_no,key_value) select 475 ,customer_no ,suggested_amount from LT_TEMP_TABLE
This is a very simple example. You didn't mention, but if you have multiple year's of suggested values (or if you plan to store multiple years) you will probably want a separate attribute for each year.
- Levi
Oh, this is very helpful. :)
I think for now, we wanted to try it out for now as an attribute and see if we are still happy with it there for now. But yes, it's a good idea to add an indicator for the FY we are loading.
Seems simple! I'll give it a shot! :) Thanks so much for your help.
Just wanted to throw this out there as some food for thought if the attribute thing doesn't work out for you. :)
We also wanted custom ask amounts for our subscription renewals. David Woodall created an awesome stored procedure for us that calculates the ask amount and then populates it directly into the customer's subscription summary. This has made our lives a lot easier here at the ESO!