Implementing PayPal exports with EFT for Payables
Recently I had a chance to look into a post on the Microsoft Dynamics GP newsgroup where the consultant required the ability to upload hundreds of payments into PayPal as a business process using the EFT for Payables module.
At first, I thought the issue was more related to the interfacing abilities with PayPal, but soon discovered this was not API-related -- PayPal also provides an API that can be called via web services.
It turns out, PayPal allows its customers, in its simplest form, to upload a mass payment tab-delimited text file containing the email of the payee or recipient, the amount to be paid, currency, a unique identifier for the recipient (which can easily be the vendor ID from GP), and a note or transaction description. For more information on PayPal's formatted file click here.
Knowing this, the first limitation with EFT Payables is its ability to generate tab-delimited files. However, it does provide the ability to create fixed length files and XML files which can certainly be converted to tab-delimited with either Microsoft Excel. Nonetheless, I decided to explore what it would take to implement PayPal and here are the steps I came up with.
Setting Up the EFT Payables Options for PayPal
NOTE: the following steps are for Microsoft Dynamics GP v10. However, they can easily be adapted for previous releases.
1) Setup a checkbook ID for PayPal. Go to Cards > Financial > Checkbook and enter minimal information as shown below.
2) Click on the EFT Bank button to add EFT information for PayPal. Choose Other 1 from the Bank Country/Region drop-down. Also, set the ISO Currency Code field to one of the supported PayPal currency codes and preferably that of your functional currency. For a list of PayPal supported currencies click here.
NOTE: PayPal only supports payments made in one currency code per file.
Leave all other fields empty.
3) Click on the Payables Options button to open the Checkbook EFT Payables Options window to setup the file format and the default file output options for the EFT file. Set the EFT Payment Numbers radio group to Use EFT Numbers -- this information is irrelevant to PayPal, but necessary to configure EFT; setup the Default Output Files as needed.
In the File Format radio group select Single Format and type PAYPAL in the field. Add the new format when prompted. This will open the EFT File Format Maintenance window. You will add 5 fields at the Detail line type level as shown in the following illustration.
NOTE: I used a flat file format, but XML can also be used as a format defining the tags and mapping the data in a similar manner.
For each field map the following data:
* Email Address maps to Address Electronic Funds Transfer Master.EFT Bank Account
* Amount maps to PM Paid Transaction History File.Document Amount
* Currency ID maps to Checkbook Transaction Electronic Funds Transfer.ISO Currency Code
* Vendor ID maps to PM Paid Transaction History File.Vendor ID
* Description maps to PM Paid Transaction History File.Transaction Description
4) Click on the Save button to continue. Click here to download a copy of the PAYPAL file format that can be used to be imported into EFT File Format Maintenance window.
5) On the Checkbook Payables Options window, click OK to save and exit the window.
6) On the Checkbook EFT Bank Maintenance window, click OK to save and exit the window.
7) On the Checkbook Maintenance window, click on Save to save the changes.
Setting Up the Vendor Card
1) Open the Vendor Maintenance window. Go to Cards > Purchasing > Vendor and select a vendor ID.
2) Click on the Address button, then choose an address ID (typically the one used for remittances).
3) Click on the EFT Bank button to open the Vendor EFT Bank Maintenance window. Choose Other 1 from the Bank Country/Region drop-down list. Since PayPal uses emails instead of bank accounts, the Bank Account Number field will be replaced with the Vendor Email address.
NOTE: Internet Address information is not exposed to EFT; hence the email address associated to the Vendor Address cannot be used.
This should complete the setup! Save all information and close all windows.
Testing it All!
Use the EFT Prenotes window to generate the prenotes. This will pick up all transactions created in the past and will allow you to get a glimpse at the new PayPal file. Use Microsoft Excel to convert the generated payments file to a tab-delimited file, required by PayPal.
Until next post!
MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
At first, I thought the issue was more related to the interfacing abilities with PayPal, but soon discovered this was not API-related -- PayPal also provides an API that can be called via web services.
It turns out, PayPal allows its customers, in its simplest form, to upload a mass payment tab-delimited text file containing the email of the payee or recipient, the amount to be paid, currency, a unique identifier for the recipient (which can easily be the vendor ID from GP), and a note or transaction description. For more information on PayPal's formatted file click here.
Knowing this, the first limitation with EFT Payables is its ability to generate tab-delimited files. However, it does provide the ability to create fixed length files and XML files which can certainly be converted to tab-delimited with either Microsoft Excel. Nonetheless, I decided to explore what it would take to implement PayPal and here are the steps I came up with.
Setting Up the EFT Payables Options for PayPal
NOTE: the following steps are for Microsoft Dynamics GP v10. However, they can easily be adapted for previous releases.
1) Setup a checkbook ID for PayPal. Go to Cards > Financial > Checkbook and enter minimal information as shown below.
2) Click on the EFT Bank button to add EFT information for PayPal. Choose Other 1 from the Bank Country/Region drop-down. Also, set the ISO Currency Code field to one of the supported PayPal currency codes and preferably that of your functional currency. For a list of PayPal supported currencies click here.
NOTE: PayPal only supports payments made in one currency code per file.
Leave all other fields empty.
3) Click on the Payables Options button to open the Checkbook EFT Payables Options window to setup the file format and the default file output options for the EFT file. Set the EFT Payment Numbers radio group to Use EFT Numbers -- this information is irrelevant to PayPal, but necessary to configure EFT; setup the Default Output Files as needed.
In the File Format radio group select Single Format and type PAYPAL in the field. Add the new format when prompted. This will open the EFT File Format Maintenance window. You will add 5 fields at the Detail line type level as shown in the following illustration.
NOTE: I used a flat file format, but XML can also be used as a format defining the tags and mapping the data in a similar manner.
For each field map the following data:
* Email Address maps to Address Electronic Funds Transfer Master.EFT Bank Account
* Amount maps to PM Paid Transaction History File.Document Amount
* Currency ID maps to Checkbook Transaction Electronic Funds Transfer.ISO Currency Code
* Vendor ID maps to PM Paid Transaction History File.Vendor ID
* Description maps to PM Paid Transaction History File.Transaction Description
4) Click on the Save button to continue. Click here to download a copy of the PAYPAL file format that can be used to be imported into EFT File Format Maintenance window.
5) On the Checkbook Payables Options window, click OK to save and exit the window.
6) On the Checkbook EFT Bank Maintenance window, click OK to save and exit the window.
7) On the Checkbook Maintenance window, click on Save to save the changes.
Setting Up the Vendor Card
1) Open the Vendor Maintenance window. Go to Cards > Purchasing > Vendor and select a vendor ID.
2) Click on the Address button, then choose an address ID (typically the one used for remittances).
3) Click on the EFT Bank button to open the Vendor EFT Bank Maintenance window. Choose Other 1 from the Bank Country/Region drop-down list. Since PayPal uses emails instead of bank accounts, the Bank Account Number field will be replaced with the Vendor Email address.
NOTE: Internet Address information is not exposed to EFT; hence the email address associated to the Vendor Address cannot be used.
This should complete the setup! Save all information and close all windows.
Testing it All!
Use the EFT Prenotes window to generate the prenotes. This will pick up all transactions created in the past and will allow you to get a glimpse at the new PayPal file. Use Microsoft Excel to convert the generated payments file to a tab-delimited file, required by PayPal.
Until next post!
MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Comments
Thank you for your GREAT post. I'm anxious to try this out.
It will be a little bit until I have time as I'm fully involved with two client implementations at the moment. But I've sent a link to the post to the client and he'll take a peek and decide when they want to move forward.
Wonderful work - The world will be grateful.
It's always a pleasure when someone can reap the benefits of the work I strive so hard to get out in the clear. I would love to hear back from you if all possible after you hear back, of course, from your client.
Best regards,
MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
I was wondering if i can add extender fields to the file i am generating. how can i do that? or if i can add tables related to the predefined list i already have.
Leonidas Arrascue
leo.arrascue@cliebs.com