Generic Sync

Overview

We have custom syncs for many third-party point of sale systems due to their unique requirements. However, there is also a need for a generic file exchange sync that can be used with point of sale and web cart systems. This specification describes our Generic Sync.

All data will be communicated via text files using the CSV format. These files will all exist in a shared network folder or FTP folder.

Data Exchange

The Generic Sync will follow these steps when syncing:

SyncYdiWithGeneric.exe will be launched using Windows Task Scheduler.

All files in the shared folder will be scanned. Sales transactions and POS codes will be pulled into Yellow Dog Software. If these transactions or codes have previously been imported they will be updated as necessary.

Sales transactions can be modified but will never be removed from Yellow Dog Inventory

POS codes will be removed from Yellow Dog Inventory if they are no longer found in the shared folder.

If problems are found with the file they will be placed into a new file with the same name but appended with ".log".

A list of inventory items will be pushed into a text file in the shared folder.

The file name is customizable, but is typically items.csv or newitems.csv.

Currently only new or changed items are pushed. That is, only items updated within the previous 24 hours are written out. If necessary we can modify the sync to push all items every time.

Miscellaneous cleanup routines can be performed.

Exporting Item Data

Item data will be exported periodically (based on a Scheduled Task) from Yellow Dog Inventory to a folder on either the local network or an ftp site. Item details will be placed in a file with any name. Item pictures will be placed in a seperate file for each picture.

At this time Item information will not be imported back into Yellow Dog Inventory. Any changes made to this file or any Item type records added to other files will be ignored.

Item information will be placed in a CSV file and will have the following columns. Column order is not guaranteed and may be subject to change in future versions.

Column

Data Type

Description

Type

"Item"

RevenueCenterID

String

A code that identifies the revenue center the item can be sold in.

ItemID

String

A code that uniquely identifies the item. This code will need to be returned with Transaction information.

MatrixID

String

A code that uniquely identifies a group of items that are all the same item but may have different Sizes, Colors or Retail.

SKU

String

SKU

UPC

String

A UPC that also identifies an item. Note that if there is more than one UPC multiple lines will be exported.

Description

String

Standard item description in plain text. This will typically be Extended Description or Short Description if Extended Description is blank in the YDI Item Editor.

Dimension1

String

It is recommended that Dimension1 be considered Size. This depends on how Dimensions are configured within Yellow Dog Inventory. A dimension is a string such as "Small" or "Green". There is no code/description pair.

Dimension2

String

Dimension3

String

Dimension4

String

Dimension5

String

Dimension6

String

UOM

String

ItemCost

Number

Current average cost of item.

ItemRetail

Number

Current retail for revenue center. This is set in the YDI Item Editor either in the Default Retail field or on the Item Retail 1 Schedule tab.

MSRP

Number

CurrentOnHand

Number

Current On Hand for revenue center. This can be negative if sales have exceeded receiving of a product. This can also be a decimal, for weighted items or partial cases, for instance.

ManufacturerCode

String

Manufacterur

String

The actual manufacturer of the item. Note that by default Yellow Dog Inventory fills this in with Vendor information.

ManufacturerSKU

String

VendorCode

String

Vendor

String

VendorSKU

String

DepartmentCode

String

DepartmentDescription

String

CategoryCode

String

CategoryDescription

String

SubCategoryCode

String

SubCategoryDescription

String

WebDescription

HTML

Short Description, found in the YDI Item Editor -> Web Properties tab.

Item pictures will be placed in individual files. Each file will be named using the SKU of the item. The picture will be saved using JPG format. The picture quality and maximum file size can be set. If the resulting .JPG would have a file size greater than the maximum its quality and dimensions will be stepped down until it fits. Thumbnails can also be generated automatically. These would have a file name of [SKU]_thumb.jpg.

Importing Sales and Code Data

The Generic Sync can pull data into Yellow Dog Inventory from the POS. This data includes sales transactions as well as POS-specific codes used to describe items. These files can have any name and can be placed on a network folder or ftp site. The first column of the file is used to identify what type of information the file contains. Multiple files can be used to store the same type of data. For instance, Transactions can be broken into multiple files with sequential extensions or date/time stamped file names.

SyncYdiWithGeneric.exe scans every file in the Shared Folder. It is assummed that all files are text.

For each file scanned, each line will be processed seperately.

The first row of every file must contain column headers.

The first column of a line determines how the line will be imported.

Sales transactions can be modified but will never be removed from Yellow Dog Inventory.

POS Codes will be removed from Yellow Dog Inventory if they are no longer found in the shared folder.

If problems are found with the file they will be placed into a new file with the same name but appended with ".log".

Importing Sales Data

All Transactions are matched on by RevenueCenterID, TransactionID and TransactionLineID. If multiple lines or files have the same combination, only the last line read will be recorded. Sales quantities can be altered by re-generating files with same RevenueCenterID, TransactionID and TransactionLineID but different Quantity or other values.

It is recommended that files be named transaction_YYMMDDhhmm.csv as they are exported, and that only new or changed records be added to each new file.

Column

Data Type

Required

Description

Type

"Transaction"

Yes

Identifies record as a transaction record.

RevenueCenterID

String

Yes

A code that identifies the revenue center the item can be sold in.

ItemID

String

Yes

A code that uniquely identifies the item. This code will need to be returned with Transaction information. (Be aware, if you are diving into our database, this is NOT the ItemID from the Item table.)

Dimension1

String

No

Dimension2

String

No

DateTime

Date/Time

Date

Date

At least one of the date fields

When the check or transaction was closed/completed.

Time

Time

TransactionID

String

Yes

A code that identifies the transaction uniquely within the day/revenue center it was made.

TransactionLineID

String

Yes

A code that identifies the line within a transaction

Quantity

Number

Yes

The quantity sold. A negative value indicates a return. This can be a decimal value.

UnitRetail

Number

Yes

The actual retail value per item before taxes or discounts. This should always be positive.

UnitDiscount

Number

The discount applied per item. This should always be negative.

Item

String

The description of the item.

DiscountDescription

String

A description of the discount(s) applied.

Tender

String

A general description of the Tender used. Typically values may be Cash, Credit, PayPal, etc.

EmployeeID

String

If an employee was responsible for the sale their ID should go here.

EmployeeName

String

The employee's name.

ItemType

String

0 = not a modifier; 1 = modifier. Modifiers must have the same transaction number as and directly follow the item they are modifying in the file.

Importing Code Data

Yellow Dog Inventory allows Third-Party codes to be imported so that they can be assigned to items. These codes can also be added manually within Yellow Dog Inventory.

Column

Data Type

Required

Description

Type

"[Type]"

Yes

See below.

RenevueCenterID

String

Yes

The revenue center that this code applies to. Leave blank or set to 0 if it should apply to all revenue centers.

Code

String

Yes

The identifier used for this Code type. This is the value that will be returned to the POS in an item import.

Description

String

Yes

This is a label that is used only in the GUI.

[Type] can be any of the following:

Column

Description

LevelA, LevelB, LevelC

Levels in the POS may not match exactly levels in Yellow Dog Inventory. POS Levels can be mapped to Yellow Dog Inventory Levels using these codes.

CodeA, CodeB, CodeC, CodeD, CodeE, CodeF, CodeG

The POS may require additional information to sell an item. This may include Tax Types, Discount Types, etc. These can be mapped using these values.

UsedCode

Yellow Dog Inventory automatically generates SKUs. To ensire that there is not a conflict a list of existing SKUs and other numbers can be pushed into Yellow Dog Inventory. These values have no purpose other than to ensure a matching SKU or UPC is not created. You do not need to assign a Revenue Center, because a blocked code is global.

Multiple code types can be combined into a single file. The first column (Type) determines what type of code is being imported. The other columns describe the code. For instance, if you are importing into revenue center 600, and you've determined that CodeA will correspond to tax type, the import file might look something like this:

If it's easier, the above data could be broken into separate files, one for each Type. When data is imported, all files in the Shared Folder are scanned and each line in the file is handled according to the Type column.

Specific Files

Some notes about the file format:

All data will be stored using the CSV format.

The first row of every file must contain column headers.

For now, these headers must match the columns specified below. In the future we may add the ability to map columns.

If column headers exist that do not match those below they will be ignored. This allows you to publish additional data to what Yellow Dog Inventory requires.

The first column of every file will be the Type column. This column describes what type of data the file holds.

Booleans will be represented as "true" or "false". This can be customized.

Dates will be represented as "yyyy-MM-dd HH:mm:ss", where MM=month, mm=minute, HH=24 hour period. If the data is represented using another format, the sync will attempt to interpret it based on the local computer's cultural settings. This can be customized.

If a value is not explicitly set in Yellow Dog Inventory it will be written to the CSV as a blank. For example, a boolean might be "true", "false" or just blank.

Implementation

Configuration

Following is a list of configuration settings that can be made through the SyncYdiWithGeneric user interface.