Aligned Solutions Consulting

Epicor Paste Insert Feature

Rick
06/27/2017 11:30 AM Comment(s)

To facilitate getting data imported into Epicor simultaneously and quickly, Epicor provides a "Paste Insert" feature into List Views. This way users can copy and 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 and 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

1.   Select New PO from the New Toolbar Menu.

2.  Enter the Supplier ID & PP of the supplier. 

3.  Select the Attn contact from the drop down if applicable. 

4.  Select the appropriate Buyer ID, if the default is not correct. 

5.  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

1.  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 Backordered, 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.

2.  We can now select these columns in the spreadsheet and delete them.

3.  You can consecutively select columns by holding down the Ctrl key and clicking on the column headers. 

4.  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: 


1. 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.  we need to enter the appropriate Part Class Code, not the description, that should be used.  we need to enter the appropriate Part Class Code, not the description, that should be used.  we need to enter the appropriate Part Class Code, not the description, that should be used.  we need to enter the appropriate Part Class Code, not the description, that should be used. 


2.Cost Per - this Epicor field controls how the Unit Price and Order Qty are calculated. Valid values are "/1", "/100", and "/1000" - these translate to Epicor codes "E", "C" and "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

1. With all the columns determined in the spreadsheet, we can 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. 

2. 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. 

3. 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 and paste insert:

1.  Select ONLY the cells that contain the data to be added to the PO, not the column headers. 

2.  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 because 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

Apart from the PO Lines, it might be necessary to include miscellaneous PO charges, such as freight. Remember to choose the PO for approval and print it if required.

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 and 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!

Rick