Spares Imports: Saved Table Mapping Field Templates and error messages

If you see this error message: 

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. 

However, and here comes the disclaimer, importing Spares should always be approached with caution. Spares Imports are not plug and play. One misstep can wipe out all of your spares data. We always advise customers to 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. We strongly recommend this 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, 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.