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:

  1. SyncYdiWithGeneric.exe will be launched using Windows Task Scheduler.
  2. 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".
  3. 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.
  4. 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.

ColumnData TypeDescription
Type"Item"
RevenueCenterIDStringA code that identifies the revenue center the item can be sold in.
ItemIDStringA code that uniquely identifies the item. This code will need to be returned with Transaction information.
MatrixIDStringA code that uniquely identifies a group of items that are all the same item but may have different Sizes, Colors or Retail.
SKUStringSKU
UPCStringA UPC that also identifies an item. Note that if there is more than one UPC multiple lines will be exported.
DescriptionStringStandard item description in plain text. This will typically be Extended Description or Short Description if Extended Description is blank in the YDI Item Editor.
Dimension1StringIt 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.
Dimension2String
Dimension3String
Dimension4String
Dimension5String
Dimension6String
UOMString
ItemCostNumberCurrent average cost of item.
ItemRetailNumberCurrent 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.
MSRPNumber
CurrentOnHandNumberCurrent 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.
ManufacturerCodeString
ManufacterurString The actual manufacturer of the item. Note that by default Yellow Dog Inventory fills this in with Vendor information.
ManufacturerSKUString
VendorCodeString
VendorString
VendorSKUString
DepartmentCodeString
DepartmentDescriptionString
CategoryCodeString
CategoryDescriptionString
SubCategoryCodeString
SubCategoryDescriptionString
WebDescriptionHTMLShort Description, found in the YDI Item Editor -> Web Properties tab.
WebExtendedDescriptionHTMLExtended Description, found in the YDI Item Editor -> Web Properties tab.
AvailableDateDate/TimeDate range for when an item is available
ExpiresDateDate/Time
Picture1AlternateString
Picture1FileMainString The name of the picture file associated with this item. Only the file name and extension are given.
Picture1FileThumbStringThe name of the thumbnail picture file. This will simply be a smaller version of PictureFileMain.
Picture2AlternateString
Picture2FileMainString
Picture2FileThumbString
Picture3AlternateString
Picture3FileMainString
Picture3FileThumbString
SaleRetailNumberThis is set in the YDI Item Editor, Web Properties tab.
SaleBeginsDate/TimeDate range for a sale price
FlagAYes/NoCustom-labeled Boolean. Defaults to New Item.
FlagBYes/NoCustom-labeled Boolean. Defaults to Last Chance.
FlagCYes/No Custom-labeled Boolean. Defaults to Taxable.
LevelAString
LevelBString
LevelCStringCustom values that can be set via the Interfaces tab of Level or Item editors. These can be set manually or imported automatically (see POS Codes, below).
CodeAString
CodeBString
CodeCString
CodeDString
CodeEString
CodeFString
CodeGString
ItemNumberString
Micros9700NumberStringThe Micros 9700 number of the item. This normally applies to menu item master, definition and price.
Micros3700NumberStringThe Micros 3700 number of the item. This normally applies to menu item and price.
WebPublishYes/NoIf the item is set to Available/Publish in the Item Editor Web Properties tab this value will be Yes.
ActiveYes/NoIf the item is deleted or inactive or Available is set to 0 in Interfaces this value will be No.
BinString

Example of newitems.csv:

Type,RevenueCenterID,ItemID,MatrixID,SKU,UPC,Description,Dimension1,Dimension2,Dimension3,Dimension4,Dimension5,Dimension6,UOM,ItemCost,ItemRetail,MSRP,CurrentOnHand,Manufacturer,ManufacturerSKU,DepartmentCode,DepartmentDescription,CategoryCode,CategoryDescription,SubCategoryCode,SubCategoryDescription,WebDescription,WebExtendedDescription,AvailableDate,ExpiresDate,Picture1Alternate,Picture1FileMain,Picture1FileThumb,Picture2Alternate,Picture2FileMain,Picture2FileThumb,Picture3Alternate,Picture3FileMain,Picture3FileThumb,SaleRetail,SaleBegins,SaleEnds,FlagA,FlagB,FlagC,LevelA,LevelB,LevelC,CodeA,CodeB,CodeC,CodeD,CodeE,CodeF,CodeG,Micros9700Number,Micros3700Number,WebPublish
Item,1,10462295007,,10462295007,282166,Three Pictures?,Display,,,,,,,1,2,0,6,AMERICAN TWIG,,20,Spa,21,Gifts,,,Short description for web item with three pictures.,Long description for web item with three pictures.,1/1/1900 0:00,12/31/2099 0:00,a,10462295007_1.jpg,10462295007_1_thumb.jpg,bb,10462295007_2.jpg,10462295007_2_thumb.jpg,ccc,10462295007_3.jpg,10462295007_3_thumb.jpg,2,,,FALSE,FALSE,FALSE,14,,,,,,,,,,,,1
Item,1,20240029,,20240029,281632,SpaTest E2,4 oz,Red,,,,,,5,5.75,0,-10,Burton,,20,Spa,24,Beauty & Beyond,,,SpaTest E2,SpaTest E2,1/1/1900 0:00,12/31/2099 0:00,SpaTest E2,,,SpaTest E2,,,SpaTest E2,,,5.75,,,FALSE,FALSE,FALSE,,,,,,,,,,,,8400461,
Item,1,20210029,,20210029,283531,SpaTest A2,Bottle,,,,,,,1,1.75,0,-9,1928 Jewelry,,20,Spa,21,Gifts,,,SpaTest A2,SpaTest A2,1/1/1900 0:00,12/31/2099 0:00,SpaTest A2,,,SpaTest A2,,,SpaTest A2,,,1.75,,,FALSE,FALSE,FALSE,,,,,,,,,,,,8400457,
Item,1,20230029,,20230029,283000,Spa Test Item B2,,,,,,,,2,2.75,0,-9,4 Seasons Swimwear,,20,Spa,23,Back to Nature,,,Spa Test Item B2,Spa Test Item B2,1/1/1900 0:00,12/31/2099 0:00,Spa Test Item B2,,,Spa Test Item B2,,,Spa Test Item B2,,,2.75,,,FALSE,FALSE,FALSE,,,,,,,,,,,,8400459,
Item,1,20240019,,20240019,283650,Spa Test Item C2,2.5 oz,Green,,,,,,3,3.75,0,-2,Action Accents,,20,Spa,24,Beauty & Beyond,,,Spa Test Item C2,Spa Test Item C2,1/1/1900 0:00,12/31/2099 0:00,Spa Test Item C2,,,Spa Test Item C2,,,Spa Test Item C2,,,3.75,,,FALSE,FALSE,FALSE,,,,,,,,,,,,8400460,
Item,1,20000019,,20000019,283528,Spa Test Item D2,,,,,,,,4,4.75,0,-4,Action Accents,,20,Spa,21,Gifts,,,Spa Test Item D2,Spa Test Item D2,1/1/1900 0:00,12/31/2099 0:00,Spa Test Item D2,,,Spa Test Item D2,,,Spa Test Item D2,,,4.75,,,FALSE,FALSE,FALSE,,,,,,,,,,,,8400453,
Item,1,20210019,,20210019,280651,More Colorful Rocks,,,,,,,,2.23333333,5.16530824,0,3,1928 Jewelry,,20,Spa,21,Gifts,,,More Colorful Rocks,More Colorful Rocks,1/1/1900 0:00,12/31/2099 0:00,More Colorful Rocks,,,More Colorful Rocks,,,More Colorful Rocks,,,5.16530824,,,FALSE,FALSE,FALSE,,,,,,,,,,,,8400456,
Item,1,20230019,,20230019,282303,Pure Essence of Crow,Tube,Brown,,,,,,6,19.91615199,0,4,Camp Trails,,20,Spa,23,Back to Nature,,,Pure Essence of Crow,Pure Essence of Crow,1/1/1900 0:00,12/31/2099 0:00,Pure Essence of Crow,,,Pure Essence of Crow,,,Pure Essence of Crow,,,19.91615199,,,FALSE,FALSE,FALSE,,,,,,,,,,,,8400458,

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.

ColumnData TypeRequiredDescription
Type"Transaction"YesIdentifies record as a transaction record.
RevenueCenterIDStringYesA code that identifies the revenue center the item can be sold in.
ItemIDStringYes 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.)
Dimension1StringNo
Dimension2StringNo
DateTimeDate/Time
DateDateAt least one of the date fieldsWhen the check or transaction was closed/completed.
TimeTime
TransactionIDStringYesA code that identifies the transaction uniquely within the day/revenue center it was made.
TransactionLineIDStringYes A code that identifies the line within a transaction
QuantityNumberYesThe quantity sold. A negative value indicates a return. This can be a decimal value.
UnitRetailNumberYesThe actual retail value per item before taxes or discounts. This should always be positive.
UnitDiscountNumberThe discount applied per item. This should always be negative.
ItemString The description of the item.
DiscountDescriptionStringA description of the discount(s) applied.
TenderStringA general description of the Tender used. Typically values may be Cash, Credit, PayPal, etc.
EmployeeIDStringIf an employee was responsible for the sale their ID should go here.
EmployeeNameStringThe employee's name.
ItemTypeString0 = not a modifier; 1 = modifier. Modifiers must have the same transaction number as and directly follow the item they are modifying in the file.

Example of transaction20121228.csv:

Type,RevenueCenterID,Date,Time,TransactionID,TransactionLineID,ItemID,Item,Quantity,UnitRetail,ItemType
Transaction,1,12/28/2012,12:06:28,23,1,8.22372E+11,Insulated Chill Bottle Bag 6 Holder,1,15.95,0
Transaction,1,12/28/2012,12:06:28,23,2,20500229,Doll,1,11.95,0
Transaction,1,12/28/2012,12:08:46,24,1,23522456,Cheeseburger,1,11.95,0
Transaction,1,12/28/2012,12:08:46,24,2,23544569,Cheddar Cheese,1,11.95,1
Transaction,1,12/28/2012,12:08:46,24,3,11455263,Pepsi,1,11.95,0

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. 

ColumnData TypeRequiredDescription
Type"[Type]"YesSee below.
RenevueCenterIDStringYesThe revenue center that this code applies to. Leave blank or set to 0 if it should apply to all revenue centers.
CodeStringYesThe identifier used for this Code type. This is the value that will be returned to the POS in an item import.
DescriptionStringYes This is a label that is used only in the GUI.

[Type] can be any of the following:

ColumnDescription
LevelA, LevelB, LevelCLevels 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, CodeGThe POS may require additional information to sell an item. This may include Tax Types, Discount Types, etc. These can be mapped using these values.
UsedCodeYellow 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.

Example of levela.csv:

Type,RevenueCenterID,Code,Description
LevelA,web,GF,Gourmet Foods
LevelA,web,HD,Home decor
LevelA,web,A,Apparel
LevelA,web,JA,Jewelry / Accessories
LevelA,web,G,Gifts
LevelA,web,GC,Gift Cards

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:

Type,RevenueCenterID,Code,Description
CodeA,600,NT,No Tax
CodeA,600,TA,Tax Added
CodeA,600,TI,Tax Included

If you also needed to set LevelA as a department and define a group of existing SKUs that should not be assigned in Yellow Dog Inventory, you could add more lines:

Type,RevenueCenterID,Code,Description
CodeA,600,NT,No Tax
CodeA,600,TA,Tax Added
CodeA,600,TI,Tax Included
LevelA,600,10,Apparel
LevelA,600,20,Gifts
LevelA,600,30,Sundries
UsedCode,0,200001,
UsedCode,0,200002,
UsedCode,0,200003,
UsedCode,0,200004,
UsedCode,0,200437,

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.

  • Shared folder. This will be one of the following:
    • Shared network or PC folder.
    • FTP folder. Includes FTP site, folder, username, password and Active/Passive flag.
  • Inventory items file name. This is the file the item information will be exported to.
  • Picture properties. All pictures will be saved as JPGs for now.
    • Main picture name scheme. Will probably be something like [SKU].jpg.
    • Thumbnail picture name scheme. Will probably be something like [SKU]_thumb.jpg.
    • Main picture maximum dimension and maximum file size. If the stored picture has a greater dimension or filesize than specified it will be reduced in size and/or quality.
    • Thumbnail picture maximum dimension and maximum file size.

Additional Information

Troubleshooting

If an error file or log information is generated it will be placed into a file using this format:

ColumnDescription
Type"Log"
DescriptionA detailed log or error message. For now this is intended to be human-readable only.