Checkin Data Import Tool

Transactions and Line Items import

The Checkin Data Import Tool (CDIT) has the ability to import Customers, Products and Suppliers from other POS systems into CheckIn. It also has the ability to import Lightspeed/POSIM Line Items data and generate Invoices/Line Items into CheckIn from that data. The Line Items data process is a bit more involved and takes some cleanup of the data and ensuring things imported in properly. This document details the process step by step.

File Setup

• Begin by downloading the latest version of CDIT in box/dropbox. (https://app.box.com/s/eduypm8kh721bv8ax7v2gtk5843jky8i)
• Download the Official Current Checkin Release
• Create a folder locally for the current customer's import, with the Current Checkin Release. Replace the Checkin interface file with the latest version of the CDIT.

Data Review

• Open Customers, Products, Suppliers and Line Items. Find and replace all commas with blank. Find and replace all quotes (") with blank. This can sometimes mess with the import process.
• Check for any blank lines at the END of these import spreadsheets. Sometimes there are hundreds of blank lines at the end of these documents.
• In the Line Items data, make sure it references the Customer IDs and Product IDs properly.

Company Setup

• Launch all of the CheckIn files (by selecting all the files and right clicking open - some of the file references might be odd because of the CDIT not being the original interface file)
• Navigate into the company setup and select Define Tax Codes and create a Default Tax. Assign it to store ID 100
• Create a Store record with store ID 100 in the Stores list

Begin Import

• Navigate to the Import Layouts -> lightspeed_import layout
• Drag all the import files into the relevant section
• Select the import button.

Field Mapping

Customers - this is rarely ever changed. Typically we are not importing tax info unless specified but customer or notes. Sometimes there are extra fields at the end that the customer wants imported, but for the most part its very straight forward.

Suppliers - This one is done less often and people tend to not always use our template. It is a very simple import so typically we don't have issues here, but this may have to be mapped manually.

Products - This is one of the more complicated ones. Sometimes the field mapping gets messed up on this. Still unsure what the best practices are for item ID. When doing a Invoice Line Item import, you NEED to import their Product ID into our _Item_ID. However, in general it is best to let CheckIn create our own Item IDs and then have their Product IDs (particularly if they have duplicates) import into the Old_Barcode field. 

Inventory Location - Super simple and this happens after the Inventory one, its literally three fields and auto enter options can be left on. This one also needs the auto mapping fixed.

Invoices/Invoices Line Items - The Line Items transaction is used to generate Invoices. So, when importing Line Items you need to place it in the Invoices box on the import layout. This is a pretty complicated one and can vary depending on where the data came from. 

• POSIM - We have a POSIM reports template that Trish from Silk Road helped us generate. Using that report, we get the following data (which we're not using all of it)

LightSpeed - Haven't done one of these in a while, but when I do I will update this document.

Creating the Invoices - Once it imports all the raw data into the ls_import_temp table, it does a search on the "duplicate_id" field which is a field that checks if there is multiple records for an invoice. (this happens when theres more than 1 line on an invoice). It takes only ONE of those records per invoice, and does an export so that it can import that information to generate Invoice records (which we'll then hook up to the line item records). The export is pretty simple.

Creating the Line Items - Same thing here, it will export and import the information it needs to generate line items.

Cleaning Up - Currently, after importing the Line Items, it does a few replaces to clean things up, the store id, the company id, recID. But it also tries to automatically fill out the item_id properly using the ls_import_inventory table. This doesn't work currently because the relationship doesn't work perfectly. For the Invoices, it replaces the store ID and company ID and then sets the total of the Invoice.

Manual Clean-up - Shouldn't be much to do after unless something is found.

To-Dos

Duplicate checking for IDs

Delete MailChimp oncustomer layout script

Fix the automatic mapping of Products import. It currently is by default set to the wrong field delimiter so it needs to be manually remapped every time.

ditto for inventory location

Make the Department creation quicker. We dont have to loop through the whole found set to create departments, we can use the values in the Department field post import to just get a list of the unique departments and create them. you probably do need to re-assign them in a loop after though.

ls_import_inventory table needs to be fixed in terms of the linking, maybe create a new table or have a way to link this without all the weird work afterward.