Finance | Project Operations, Human Resources, ...

How to Export Vendor Bank Information from Purchasing

(0) Share

Report

Posted on by PattiK323

281

We are installing a new ERP and need to get our vendor data (Vendor ID, bank account, bank routing number) out of Great Plains into a CSV file. How can we do that?

Thank you.

Categories:

Microsoft Dynamics GP

I have the same question (0)

All responses (12)

Answers (0)

Mick Egan 3,561 on at

Like (0)

Report

Patti,

Use either SmartList Designer or Smartlist Builder, add the "PMCreditorMasterFile" under Purchasing and the (Address Electonic Funds Tranfer) under Company Tables, and join by VENDORID to VENDORID.

Regards,

Mick

Was this reply helpful? Yes No
PattiK323 281 on at

Like (0)

Report

I do not see the table that you are referring to.

Was this reply helpful? Yes No
Mick Egan 3,561 on at

Like (0)

Report

Patti,

I would login in as 'sa' or at least a POWERUSER Role.

Mick

Was this reply helpful? Yes No
PattiK323 281 on at

Like (0)

Report

Even when logged in as sa, we do not see the tables that you mention.

Was this reply helpful? Yes No
Suggested answer

Mick Egan 3,561 on at

Like (0)

Report

Patti,

This means the GP Security Task is not setup.

There was a new Task ADMIN_SYSTEM_SL06* created a few years back.

Check this Task Exists and the Smartlist Vendor Object is Marked.

The other Option is to create a new Security Task, i.e. Custom_SmartlistSecurity and add the Task to one of the Roles the Users have access to like DEFAULT as an example, or add a new Role and assign the Role to the Users.

The other check is to run the GRANT.sql script against the Company located in the SQL >> Util fodler in the GP Directory.

Merry Christmas,

Mick

Was this reply helpful? Yes No
Kerluke 20 on at

Like (0)

Report

Select the vendor that you want to create a bank account for, and then click the link on the Vendor account ID field.
On the Action Pane, click Vendor. My eClass Parent Portal

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

I don't suppose you can back-end SQL scripts to extract this information? You would just need to know the table names.

Was this reply helpful? Yes No
Almas Mahfooz 11,009 User Group Leader on at

Like (1)

Report

Hi PattiK323,

Follow below steps.

1. Open window , you want to export data. Go to Tools >> Table Finder

2. Select table you want to export data for and then press create smartlist button, it will ask for new smartlist name. Write name and Save.

3. Smartlist Builder window will open with the new smartlist information. Change series according to the window and mark all Default fields and save the smartlist.

4. Open Smartlist , newly create smartlist will be visible. now export to Excel or csv file.

Was this reply helpful? Yes No
PattiK323 281 on at

Like (0)

Report

I don't have Table Finder. Is this part of SmartList Builder? We don't that that.

Was this reply helpful? Yes No
Suggested answer

Mick Egan 3,561 on at

Like (1)

Report

Patti,

Open SQL Studio Manager.

Select a New Query, and chnage to the company to export the bank details.

Copy the beloe code into the Query.

SELECT RTRIM(a.VENDORID) VENDORID, RTRIM(b.ADRSCODE) ADRSCODE, RTRIM(b.BANKNAME) BANKNAME,
RTRIM(b.EFTBankAcct) EFTBankAcct, RTRIM(b.EFTBankBranch) EFTBankBranch,
RTRIM(b.EFTBankCode) EFTBankCode, RTRIM(b.EFTBankBranchCode) EFTBankBranchCode
FROM PM00200 a
LEFT OUTER JOIN
SY06000 b
ON
A.VENDORID = B.VENDORID
WHERE
a.VENDSTTS = 1
AND
b.VENDORID IS NOT NUll
ORDER BY a.VENDORID

On the menu bar, change the Option to "Export to File".

Execute the Query, a Save Results To Window will open, select your preferred Directory, and in the "File Name", add Bank_Export.csv, and in the "Savce Type As" chnage from rpt to All Files.

Mick

Was this reply helpful? Yes No