Epicor Paste Insert Feature
To facilitate getting data imported into Epicor end masse and quickly, Epicor provides a ‘Paste Insert’ feature into List Views. This way users can copy & Paste Insert data from a spreadsheet into Epicor. This can reduce redundant data entry and speed data entry when done correctly.
In this example we will Paste Insert into PO Entry based on a Quote received from a Supplier.
Note: The key to getting Paste Insert to work properly is matching up the columns between the Excel copied data and the Epicor List View.
1. Open Epicor PO Entry & The Excel Spreadsheet
We first need to open PO Entry & the spreadsheet that contains the data that is to be entered into Epicor. This way we can compare the column order between the two so that the columns in either Excel or Epicor can be adjusted to match.
2. Enter The PO Header Data
a. Select ‘New PO’ from the New Toolbar Menu
b. Enter the Supplier ID & PP of the supplier.
c. Select the Attn contact from the drop down if applicable.
d. Select the appropriate Buyer ID, if the default is not correct.
e. Select Save from the toolbar to save the PO, a PO number will generate.
3. Select The Summary Line Type for the PO
On the Summary tab towards the bottom of the screen select the type of PO Line(s) that will be entered.
In this case these are non-inventoried items, so we will select the ‘Other’ tab.
4. Delete Unnecessary Data From The Excel Spreadsheet
a. On the Other tab we can note the order of the columns: Line, Part, Description, Unit Price, Cost Per, Our Qty, UOM, Type, etc…
If we compare this to the column order of the spreadsheet we see they do not line up:
Line, Date, Location, Sub Location, Qty Ordered, Quantity Shipped, Quantity Back Ordered, Description, Control No., Part No, Price/Hundred, Amount.
By comparing the two sets of columns and the data in the spreadsheet we can first determine which columns in the Excel Spreadsheet are not required. In this case we do not need:
Line, Date, Location, Sub Location, Qty Shipped, Qty Backordred, Control No. & Amount.
Line & Date are auto generated so those are not needed.
Amount is not needed but we will want to keep it on the Spreadsheet for verification later.
b. We can now select these columns in the spreadsheet and delete them.
c. You can discontiguously select columns by holding down the Ctrl key and clicking on the column headers.
d. Then you can let go of the Ctrl key and right click and select Delete from the context menu.
5. Add Missing Data Columns
Epicor PO Lines have several required fields and several fields that can affect how pricing is calculated:
a. Part Class – this is required so that the expense writes against the correct GL account – The spreadsheet does not contain this data, so we need to enter the appropriate Part Class Code, not the description, that should be used.
b. Cost Per – this Epicor field controls how the Unit Price & Order Qty are calculated. Valid values are ‘/1’, ‘/100’ & ‘/1000’ these translate to Epicor codes ‘E’, ‘C’ & ‘M’ Respectively. Notice on the Spreadsheet that it says ‘Price/Hundred’ so that means that we should select ‘/100’ for the Cost Per Code.
6. Align PO Entry Columns To Excel Spreadsheet
a. With all the columns determined in the spreadsheet we can now move the column headers on the PO form to match. This can be easily done in Epicor by just clicking and dragging the column to the desired position.
b. After moving the columns to the desired order you can Right Click in the list view and select ‘Save Layouts’ so that the list view will stay the same for the next time.
c. If some columns are not visible, you can add them via ‘Personalization’ from the Options Menu.
If you need help with moving columns and adding columns, see ‘Epicor 101: Grid/List View Tools’
7. Copy Spreadsheet Data
Now that the source data and the data targets match we can copy & paste insert:
a. Select ONLY the cells that contain the DATA to be added to the PO, not the column headers.
b. Right click and select ‘Copy’ from the context menu.
8. Paste Insert Into Epicor
Switch back over to Epicor PO Entry and right click anywhere in the List area and select ‘Paste Insert.’
If everything in Excel is setup correctly you will then see the PO Lines paste into the list view. If everything in the spreadsheet is NOT setup correctly, you will probably receive an error message that says:
‘Unable to complete Paste Operation’
This is usually be cause the columns are not aligned properly to match the grid, or in some cases there can be a missing required field. If you need help, let me know.
9. Verify Paste Insert Data
Trust But Verify.
Review the data that was pasted into the PO and validate it against the source.
For instance, in this case the source Excel file included a Sub Total as the sub totals match.
10. Finish PO Entry
In addition to the PO Lines, there may also be a need to add Misc. PO Charges like ‘Freight’
And don’t forget to mark the PO for Approval and print if necessary.
This same process can be used in most other Epicor grids to quickly input data that has already been entered into a spreadsheet, for instance:
- Sales Order Entry
- Quote Entry
- RMA Entry
- GL Journal Entries
If you found this post helpful, be sure to like, share & comment. If you need help with using Paste Insert in Epicor or are interested in remote or on-site Epicor training, please contact me and let’s get something scheduled!
“You Don’t Know, What You Don’t Know”
Let’s Get Started: