Spares Imports from your ERP or MRP System into L2L Spares Module and Potential Error Messages

The file name clearly indicates what import you are doing and when. Once you click 'Choose File' and select your appropriately named file, proceed to the 'NEXT STEP'. But before you go, please note the highlighted portion of my screenshot below: Your file must contain 1 Header Row, comprised of Column Headers, followed by data. Easy enough, huh? Just keep this in mind when we start looking at the preview of the data you're about to load.     

1 Header Row (of named Column Headers) followed by data

Remember the note about your xls or csv file containing 1 Header Row followed by data? The highlighted screenshot below lets you know and illustrates that the top 3 rows of your file are displayed just below the Header Row,  which is the row of named Column Headers. These named Column Headers are the focus of this article. Before we move on to those, note that the first three rows are always displayed at the top of the page. Why is it important for you to know this? Because as you proceed to map the Table Fields, you can return immediately to the top of the page, where you have a preview of both the Column Header names and the data populating the cells in the rows beneath each Column Header. 

Immediately below the Header Row, Step 1: Select Worksheet, a drop down is shown. That selection allows you to tell the system which tab of the file to import. 

Step 2: Filter by Columns/Values (optional) - Bypass this step as it is not needed in order for you to perform an import. (Filter Column-No filters selected, importing all records = just what you want to see.)  

Step 3: Describe the items to Import 

This is where you choose an option from the drop-down to specify the type of data load you are about to perform. For the purposes of this article, the presumptive data load is a Spares Full Sync. If you are importing spares data from an external ERP or MRP and uploading to L2L, this is precisely the type of dataload you are performing. Please note that the other types of spares loads are described in great detail in other articles on this website. 

The software used in your facility to help manage spare parts may be one of these: ERP | MRP | QAD | M2M | SAP or another software. For the purposes of this discussion, let's presume that all of these software sources provide the same functionality. 

Regardless of what your external ERP is, the Column Header names will undoubtedly vary from the names given to each of the Table Fields you will map to in L2L. Therefore, it's very important to understand the meaning and purpose of every Table Field. Two separate companies in the same or different industries can use the same MRP software, and yet their Column Header naming protocol is almost guaranteed to be different across each instance. Another reason why it's critical to understand what you are mapping to L2L and why. When importing data into L2L, the Table Fields in L2L are consistent. As an added benefit to the end user, all of the L2L Table Fields you need to map are listed in alphabetical order for your convenience and ease of use. You're welcome! 

You will also notice that alternating Table Fields are highlighted in different colors. This makes it much easier to line up your responses to the correct Table Field when mapping.  

Step 4: Map Table Fields to Columns 

Below you will find a detailed chart illustrating how to Map L2L Import Table Fields to sample MRP/ERP  corresponding Column Headers:  

Column A - L2L Full Spares Sync Table Fields to Map 

Column B > indicating options for Mapping the Table Fields 

Columns C & D: Examples of ERP/MRP/QAD/M2M/SAP or other software Column Headers 

Column E: Use as a guide to Map L2L Table Fields to their external counterparts 

Many of our customers integrate L2L Spares with their ERP System. In these cases, Spares Imports are performed daily using data extracted from that ERP System. Most of the people who perform these daily uploads have created and saved a template (Mapped Table Fields) which they use day in and day out.

Saved templates are available from the drop-down under Step 4: Map Table Fields to Columns. This is why they are referred to as Saved Table Mapping Field Templates. There is nothing inherently wrong with this practice and we don't discourage it. 

If you see this error message: 

Here comes the disclaimer, importing Spares should always be approached with caution. Spares Imports are not plug-and-play. We strongly recommend customers first export all current spares and retain that file, separate from, and in addition to, the file they are uploading, prior to performing a spares upload as a Best Practice. (Because one misstep can wipe out all of your spares data when performing a Spares Full Sync upload. This is so important, that it bears repeating.)  

One constant about software is that it is always changing. For this reason alone, the importer should be knowledgeable about the subject matter. While you may perform this type of upload practically every day, you cannot approach this casually. 

You may encounter error messages on occasion. Some of them are easily decipherable, i.e. an easy-to-understand message such as, 'Cost Center --ABC 123-- does not exist'. These are also easy to resolve: just create a Cost Center in your site and away you go.

The following are the most frequent causes of error messages during Spares Imports:

• The Table Mapping Fields are not mapped correctly 
• Something has changed so your saved Table Mapping Field Template is no longer accurate
• Columns have been added/removed/renamed on your import file
• Column Headers have been added/removed/renamed on your import file
• The file, formatting, or data from your ERP import file has been modified
• The Import Tool itself has been modified by L2L (while this happens extremely infrequently, it is always a possibility)

For the reasons cited above, it is critical that you understand how the Column Headers from the file you are importing correlate to the individual Table Field selections to which you 'map' when creating your Table Mapping Field Templates, i.e. the options available to select from the drop-down of each line item.  When you have a clear understanding of these Table Field + Column relationships, error messages are no match for you! 

Below you will find the following: 

# 1 - Screenshot of sample Saved Table Mapping Fields. These are named and then saved using the floppy disk icon immediately to the right when performing Step 4. 

# 2 - 'Select Worksheet': used if you are importing a file with multiple tabs. The Column Headers are displayed along with the top 3 rows of the worksheet to ensure you are loading the correct tab. 

# 3 - This screenshot depicts all of the line items to be 'mapped' in the Table Fields and their corresponding Column (Headers). 

# 4 - Screenshot of individual Table Field drop-down selections

# 5 - Column Headers defined for all spares exports from L2L.

 

Screenshot # 1: Illustration of previously saved Table Mapping Field options available for selection: 

 

Screenshot # 2: - Step 1: Select Worksheet (showing top 3 rows [of the file you are importing]) 

Note that every Column Header in the import file has a corresponding Column Header reflected in the display of the 'top 3 rows'. Please also note that these are not in the same order, hence the mapping requirement. 

Step 3, Describe the Items to Import and Select the Table to import these items into, requires "Spares Full Sync" when you are performing a daily spares import. Note the warning as well: *Spares Full Sync will remove all locations and vendors not contained in the upload file.

Screenshot # 3: - This screenshot depicts all of the line items to be 'mapped' in the Table Fields and their corresponding Column (Headers). In this example, the majority of Table Fields to be mapped directly correlate with their Column Header counterparts. 

*Note that this will not always be the case. The import file from your ERP system may have varying nomenclature. For example, when L2L refers to 'Cost', this may be referred to as 'Price', 'Pricing', etc. Your site may have specified different labels as well, making it critical that the person performing the import understands the fields they are mapping. 

*Note also that you must not leave any Table Field mapping options without selecting a response from each individual drop down. Failure to map every Table Field will prevent the "DO IMPORT" button from being displayed for selection. 

*Note as well that a number of the Vendor related Table Fields do not have corresponding Column Headers as shown in the export of spares from L2L. While this information is very helpful, the Vendor specific Table Fields are not required mapping to successfully import spares. 

*Any Table Field that does not have a corresponding Column Header selection still requires a response. You must select either 'Empty String' or 'Null'. In general, use Empty String for those Table Fields where the expected response is numeric and use Null for those Table Fields where the expected response is text or alphanumeric. Likewise, you must check the corresponding 'Unique Values Do Not Import' box. If you do not, the "DO IMPORT" button will not be available for selection. 

Screenshot # 3 

Screenshot # 4: Individual Table Field drop down selections to which you map when creating your Table Mapping Field Templates. Again, the fields from both sources are not in the same order. When the names don't match exactly, you will have an easier time mapping them when you understand their meanings. 

# 5 - Below are the Column Headers for all spares exports from L2L. The number of items as well as the verbiage will likely not be the same in all imports. 

id - unique identifier created by L2L and used only within the database. For L2L use only. 

externalid - an external identifier for each part within L2L spares. Not required. 

site - identifies your specific plant when company has multiple locations. Required. 

partnumber - individual identifying name (numeric or alphanumeric) for each of your spare parts. Required. 

shortpartnumber - may be an abbreviation of the entire part number. Not Required unless specified for your site per your Spares Settings. 

quantity - Total number of parts physically on hand at the time of import. Required. 

description - Description of the spare part. Required. 

active - Is the part currently active? True / False response required. Required. 

created - Date when the part number was created in the System. Not required, system populated. 

obsolete - part is or will soon be no longer available to use, not available to purchase, superseded by a different part, sourced from a different vendor, etc. Required. 

cost - the price per issuance unit of measure. Required. 

minimum - minimum number of parts. Required. 

optimum - the preferred ideal number of parts on hand. Required. 

info - additional information about the part. Not Required unless specified for your site per your Spares Settings.  

manufacturer - name of the outside party who fabricates the part. Not Required unless specified for your site per your Spares Settings.  

manufacturer_partnumber - the number assigned to the part by the fabricator. Not Required unless specified for your site per your Spares Settings. 

lastupdated - the last date that part information was changed. System populated. 

lastordered - the date the part was last ordered. Not required. 

createdby - the name of the person, system, API who initially created the part. System populated. 

lastupdatedby - the name of the person, system, API who last edited any part details. System populated. 

critical - Requires a True / False response and is Required. 

image - is a photo been attached to the part, requires a True / False response. Not Required. 

unit_of_measure - how part is consumed on the shop floor: by the foot, each, box, pound, gram, etc. Required. 

refresh - Flag for Refresh forces the L2L spares records updated whenever the ERP data is updated. Not Required. 

part_category - classifications for spare parts. Not Required unless specified for your site per your Spares Settings.  

taxable - requires a True / False response but is Not a Required field. 

synchronized - transactions in L2L are mirrored in the ERP system. Not Required. 

external_master - reference outside of L2L with which the part is associated. Not Required. 

date_to_order - nearest future date to order part. Not Required. 

po_unit_of_measure - how parts are purchased: pallet, case, roll, drum, etc. Required. 

Qty By Bin - number of parts on hand by bin location. Not Required. 

Bin Location - where bins are located within your plant. Not Required. 

Vendor - outside party who supplies this part. Not Required unless specified for your site per your Spares Settings. 

Vendor Part Number - outside supplier identifying information. Not Required. 

Qty On Order - number of parts currently on order. Not Required. 

Warehouse - physical location information where parts is housed. Not Required. 

Associated Machines - machine(s) which can consume the part. Not Required. 

12 Month Usage - quantity of parts consumed over the past rolling 12 month period. Not Required. 

12 Month Cost - cost of parts consumed over the past rolling 12 month period. Not Required. 

YTD Usage - quantity of parts consumed during current calendar year to date. Not Required. 

YTD Cost - total cost of consumed individual part number during current calendar year to date. Not Required.