Www.tradepointsystems.com



Vendor Ad-Hoc query to create csv file for initial load of vendors

[pic]

Click on the + button to enter a new Ad-Hoc query definition.

Choose Category Vendor.

Enter a name for the query.

Choose Export Format Delimited.

Enter a comma as the Delimiter.

Enter a description for the query.

Export file name will default. It can be changed if desired.

The columns to be chosen from the Vendor table are listed below. The columns must be selected in this order. Failure to select them in this order will cause the columns to not be uploaded properly into Sage.

Vendor

Name

Address 1

Address 2

Address 3

Contact Name

Country

Fax No Alpha

Phone No Alpha

State Province

Zip

Doing Business As

Currency

In order to select the entire Vendor table, no filter should be used.

Click on the Schedule Button.

Complete the Schedule and Config Distribution screen:

• Enter the Time to Run.

• Click on the Once Radio button.

• Enter the Start Date.

• Enter the Email Address, or click on the Email to Me button.

• Click on the OK button when finished.

• Close the Ad-Hoc Query screen and save the changes.

When the query completes, the email will be delivered with the csv file attached.

Phone and Fax numbers

Phone and fax numbers that are longer than 11 digits will not be formatted properly for uploading to Sage. Phone and fax numbers that are stored with formatting (parentheses and hyphens) will also not be uploaded properly into Sage.

Open the csv file attached to the email and expand the Fax No Alpha and Phone No Alpha columns. Sort the list of vendors by Fax No Alpha. Fax numbers that are longer than 11 digits will display with a + sign, i.e., “6.23214E+11”. Make a note of those vendors. Also make a note of vendors with parentheses and hyphens. Exit the spreadsheet and use Vendor Maintenance to access those vendors and change the numbers to be 11 digits or less, and remove the formatting.

Repeat this procedure for Phone No Alpha.

Headings

Once all phone numbers and fax numbers are fixed, access your Vendor Ad-Hoc query again and click on the Schedule button. Change the Date and Time to Run so that the query runs again. When the email is delivered, open the csv file and save it to your computer using the Save As option.

Create a new spreadsheet. Create 1 row with these as the column headings. Type the headings exactly as they are listed here:

VendorNumber

VendorName

AddressLine1

AddressLine2

City

ContactName

Country

FaxNumber

PhoneNumber

StateProvince

ZipPostalCode

LegalName

CurrencyCode

Save this spreadsheet as Vendor csv Headings.xlsx.

Open the saved csv file with the Vendor data. Make a note of the first Vendor (row 2).

Open Vendor csv Headings.xlsx. Select the top line and Copy it. Paste that line into the Vendor csv file as line 1. Check the Vendor name in row 2, to make sure you haven’t accidentally pasted the heading line on top of the first vendor.

Close and save the Vendor csv.

The Vendor spreadsheet is now ready to be emailed to Accord. Be sure to email Accord the spreadsheet with the modified Heading line.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download