This document covers converting the three major glass vendor price list files to a format we can import into GlasPacLX for vendor costs.
Creating the Datafile
Vendor Costs are loaded into Vendor Maintenance form (Purchasing > Vendor Maintenance > Edit Vendor) and is only available when the customer has ICPO. Without ICPO the Edi Vendor Cost button will not be shown.
- EXCEL Formulas reference
- PGW ALT FORMAT - Customer Price List
The EXCEL FORMULAS
Quick view of the EXCEL formulas –
Mygrant (Column C)
PGW Customer Price List (Column D)
=LEFT(A2,7) & MID(A2,9,3) & IF(B2="PRE", "Y", "N") & IF(B2="PRE", "", B2)
Pilkington (Column D)
=LEFT(B2,7) & MID(B2,9,3) & IF(MID(B2,8,1)="P", "Y","N") & IF(LEN(B2)=14,RIGHT(B2,3),"")
Vendor Cost List: Mygrant
- Format Part Number to confirm to 12-digit NAGS w/trademark format
- Remove non-NAGS parts
- Save as a .CSV file.
- Load into Vendor Maintenance
- Understanding exceptions
Step 1 – Format Part Number
Mygrant provides the part number in a base part + Premium indication + color/attachment group + trademark format. There is space between the base part, color group, trademark on most lines.
Here’s a detail of the format –
Position 1-7 – Base Part, example DB00003
Position 8 – Premium Part indication, “P”, or space for a non-premium part
Position 9-11 – Color & Attachment group, example CLN
Position 12 – space
Position 13-15 – Manufacturer trademark, example LOF
Here is a sample of some Domestic Backglass items from a Mygrant price file in Excel.
Note: Some of lines contain non-NAGS part numbers (36, 37, and 38). These items cannot be loaded into the vendor cost list.
Here’s the formula to use to format the Mygrant part line.
- Open the .csv file from Mygrant in Excel.
- Click in Column C in line 1.
- Paste the above formula into the function box for C1.
- Copy the formula for the run of the document by double clicking on the small black dot in the lower right corner of cell C1.
- Copy all of Column C
- Select Column A.
- Right click at the top of Column A and select Paste – Values (the clipboard icon with 123 on it). If you are using an older version of Excel, select Paste Special and select Values Only.
- Delete Column C. Right click on the C label at the top and select Delete.
Remove Non-NAGS Parts
There’s a number of Non-NAGS Parts in the Mygrant price file. These items that aren’t standard 12 character NAGS part numbers. Anything that does not begin with the letter D or letter F is non-NAGS.
- Review the file for groups of parts to remove. For example here, the first 38-lines of this price file are non-NAGS parts.
- Select the entire lines. Right-click and select “Delete”
- There’s a few parts between the Domestic and Foreign parts.
- As well as number items at the end of the list
Save the File as a CSV
You should now have a file that looks like this at the top.
- Select File, Save As…, and save the file with a new name in .CSV format.
- Click Yes to the subsequent dialog to confirm the save.