Importing AutoPay data into the Workbook Converter
Using the Workbook Converter, you can import existing recurring enrollments.
Auto Renew Members
Create one worksheet that contains a list of the members for whom you want to generate an enrollment. If you have an external token with a gateway, you can import the ExternalToken into the Pay Central Service; you can then use that token to process your credit card payment.
Before you begin:
- You must set up AutoPay and the Pay Central Service before importing Auto Renew Members data.
- It is recommended that you only include account details that should be active for future renewals. Even if inactive, a member defined on the Auto Renew Members worksheet will create an enrollment.
Add the following information in your Auto Renew Members worksheet:
- Id(required)
- StartDate (required) – The Id and StartDate information is used to create a membership enrollment tied to the account.
- PaymentMethodName (required)
- AccountType(required)
- ExternalToken
- Last4Digits
- CCExpDate
- BankAccountType
- BankRoutingNumber
- BankAccountNumber
Note: If the StartDate is 29, 30 or 31, the system will use a default date of 28.
Enter the Name of the payment method with which these payments will be applied. Ensure the gateway tied to this payment method is the gateway to which the ExternalToken is tied.
If the Name does not result in a match, the Workbook Converter will instead attempt to assign the value in this field as a payment method Code.
Note: If the Name value results in duplicate payment methods, the Workbook Converter will display the duplicate payment method Codes. Select the correct payment method Code. You must then update the value in the PaymentMethodName field in the worksheet.
Note: The PaymentMethodName must match the payment method DESCRIPTION specified in iMIS. The payment method must also be tied to the correct gateway. For example, if you have a VISA credit card that is tied to the PayPal gateway, use a code VISA. If you need to specify a VISA credit card tied to a second gateway, such as Moneris, use a code VISA_M. For more information, refer to payment methods.
Note: If the PaymentMethodName does not match one of the following credit card names:
-Visa
-MasterCard
-AmericanExpress
-DinersClub
-Discover
-JCB
then the credit card is described as Card in the AutoPay enrollment.
This information is used to create an AutoPay account. The ExternalToken, Last4Digits and CCExpDate are used to create a credit card payment method summary. The BankAccountType, BankRoutingNumber and BankAccountNumber are used to create a bank account payment method summary.
Recurring Donors
Create one worksheet that contains a list of donors for whom you want to generate an enrollment.
If you have an external token with a gateway, you can import the ExternalToken into the Pay Central Service; you can then use that token to process your credit card payment.Note: You must set up AutoPay and the Pay Central Service before importing Recurring Donors data.
To generate a recurring donation enrollment, add the following information in your Recurring Donors worksheet:
- Id (required)
- StartDate – (required) The StartDate is the date on which the recurring donation will begin.
- GiftItem (product code) (required)
- SourceCode (set up in Campaign) (optional)
- Amount
- Frequency – The Frequency is how often the recurring gift is renewed. For example, a Frequency of Once means that the payment is not renewed (the payment happens Once). A Frequency of Yearly means the recurring pledge is renewed annually. Options are Yearly, Quarterly, and Monthly.
- PaymentTerms (optional) - The PaymentTerms define the payment schedule for a recurring installment pledge. Options include Annually, Quarterly, and Monthly.
- Amount - 120
- Frequency - Yearly
- PaymentTerms - Monthly
- PaymentMethodName (required)
- AccountType (required)
- ExternalToken
- Last4Digits
- CCExpDate
- BankAccountType
- BankRoutingNumber
Note: If the StartDate is 29, 30 or 31, the system will use a default date of 28.
For example, to create an annual installment pledge of $120 that is to be paid off in 12 monthly installments, enter the following values:
Note: The Frequency option Once is used to create AutoPay accounts for externally tokenized credit cards or bank accounts to pay for partially paid, non-recurring installment pledges. When using the option Once, AutoPay does not generate invoices.
For example, consider a customer who originally pledged to pay your organization $120 for the year, spread across 12 non-recurring installments. If the pledge has only been paid across six installments, and you are intending to import this payment information, you can select Once for the Frequency and Monthly for the PaymentTerms. The remaining six invoices with the remaining balance must be created or imported.
Enter the Name of the payment method with which these payments will be applied. Ensure the gateway tied to this payment method is the gateway to which the ExternalToken is tied.
If the Name does not result in a match, the Workbook Converter will instead attempt to assign the value in this field as a payment method Code.
Note: If the Name value results in duplicate payment methods, the Workbook Converter will display the duplicate payment method Codes. Select the correct payment method Code. You must then update the value in the PaymentMethodName field in the worksheet.
Note: The PaymentMethodName must match the payment method DESCRIPTION specified in iMIS. For example, if you have a VISA credit card that is tied to the PayPal gateway, use a code VISA. If you need to specify a VISA credit card tied to a second gateway, such as Moneris, use a code VISA_M. For more information, refer to payment methods.
Note: If the PaymentMethodName does not match one of the following credit card names:
-Visa
-MasterCard
-AmericanExpress
-DinersClub
-Discover
-JCB
then the credit card is described as Card in the AutoPay enrollment.
This information is used to create an AutoPay account. The ExternalToken, Last4Digits and CCExpDate are used to create a credit card payment method summary. The BankAccountType, BankRoutingNumber and BankAccountNumber are used to create a bank account payment method summary.
When all entries are complete, Save and Close the workbook. The workbook must be closed when running the conversion.
Notes
- The workbook must be saved with an extension of .xlsx. You will receive an error if you attempt to convert using an .xls extension.
- If you have a column of data in a spreadsheet that should be empty, but instead contains a blank or space character (whitespace), then you will get an error during the TestOnly run. To clear a space from a cell in Excel, right-click the cell, and select Delete. To clear an entire column, it might be easier to copy the column header to the clipboard, then highlight the entire column and delete it, and then copy the column header into the first available cell at the end of line 1.
Importing recurring enrollments
When importing recurring donations or membership renewals, you can do so without a corresponding spreadsheet for the donor, as long as the donor already exists in iMIS and you reference the iMIS Id on the appropriate worksheet (Recurring Donors or Auto Renew Members). If you only need one of the worksheets, you can delete the other one.
Note: The name from the contact’s account is used to populate the card holder name for AutoPay enrollments. For clients using Global Payments, the name is truncated in the Workbook Converter if the total characters exceed 26.
To import recurring enrollments, you must use a specific version of the Workbook Converter sample workbook (iMIS 20-300 Conversion-Importing RecurringDonors data using existing iMIS ID.xls). You will find the sample workbook in the following location:
iMIS install directory\iMIS20-WorkbookConverter.zip
Note: The Workbook Converter successfully imports recurring enrollments tied to bank accounts, external bank accounts, and external credit card accounts.
When importing recurring enrollments using the Workbook Converter, consider the following Options:
- CreateAccounts – Set this option to FALSE.
Note: If CreateAccounts is set to TRUE and valid email addresses are provided, user accounts are created in iMIS. As you are attempting to import additional items for existing recurring enrollments, be sure to set CreateAccounts to FALSE.