Batch Ticket Import

In Keystone, input fields are specified with the following parameters: (where xxx specifies the field name)

FIELD.xxx=<col>,<len>[,alt_specifier>][,<col>,<len>[,<alt_specifier>]]*

LOOKUP.xxx=<search_field>[,<match_field>,<result_field>]* Lookup search field in a list of match fields, if found use <result_field> value

TRANSLATE.xxx=<batch_value>,<CC_value>[,<batch_value>,<CC_value>]* Translate field value to <CC_value> if field matches <batch_value>. (*Note: not to be used for Tax_Authority)

CONV.xxx=<conv_rule> Use <conv_rule> to convert data

IF.xxx=<compare_data>,<result_value> Force field to <result_value> if field matches <compare_data>

IFVAL.xxx=<compare_data>,<result_value> Force field to <result_value> if numeric value of field matches <compare_data>

IFNOT.xxx=<compare_data>,<result_value> Force field to <result_value> if field does not match <compare_data>

IFNOTVAL.xxx=<compare_data>,<result_value> Force field to <result_value> if numeric value of field does not match <compare_data>

FORCE.xxx=<result_value> Force field to have <result_value>

(two single quotes indicate blank)

VALIDATE.xxx=Y Validate that the item exists in the CC master files

DEFAULT.xxx=<result_value> Assign <result_value> to field if blank

(two single quotes indicate blank)

REQUIRED.xxx=Y The field cannot be blank

<alt_specifier> syntax is as follows:

[<string>] (*NOTE: The square brackets here are LITERALS, they do NOT indicate that the string is optional!)

If specified, the <alt_specifier> indicates that if the contents of the field segment (previously specified with <col> and <len>) match the <string> (which can be the empty string), that field segment is ignored and remaining field segments are processed. If the field segment does NOT match the <string>, then that field segment is concatenated to the current field value, and all remaining field segments are ignored.

<compare_data> If the value begins with an at sign (@), the remainder of the value specifies the import record field name whose value will be used instead. Otherwise the value is used as-is.

<result_value> If the value begins with an at sign (@), the remainder of the value specifies the import record field name whose value will be used instead. Otherwise the value is used as-is.

<conv_rule> may be one of the following:

[A]HH:MM[A] Convert field value as time ([A] specifies position of AM/PM indicator)

MM/DD/YY Convert field value as a date of the format MM/DD/YY

MM-DD-YY Convert field value as a date of the format MM-DD-YY

MMDDYYYY Convert field value as a date of the format MMDDYYYY

YYYYMMDD Convert field value as a date of the format YYYYMMDD

DATE(<format>) Convert field using Delphi DateFormat.

Ex: conv.TICKET_DATE=date(DDMMYY) converts the value 020517 into May 2nd, 2017.

(<format> follows the syntax of the Delphi FormatDateTime function) See: http://www.delphibasics.co.uk/RTL.asp?Name=formatdatetime

TIME(<format>) Converts time field value according to <format>

(<format> follows the syntax of the Delphi FormatDateTime function) See http://www.delphibasics.co.uk/RTL.asp?Name=formatdatetime

UPPER Converts field value to uppercase equivalent

NUM(<format>) Converts numeric field value according to <format>:

0 = digit, zero padded

# = digit, zero suppressed

. = decimal point

, = thousand separator)

DEC(<n>) Converts numeric field value with <n> implied decimal places

Ex: field value 15000 converted with DEC(2) results in the value 150.00

UMCONV(<prodid>,<prodtype>,<sourceUM>[,<targetUM>]) Converts numeric field value from one U/M to another

<prodid> should be an at sign (@) followed by the product id import field name

<prodtype> is the product type that must match the product type of the associated product (use asterisk (*) to match all product types)

<sourceUM> should be an at sign (@) followed by the unit of measure import field name (or a U/M code if there is no U/M in the import file)

<targetUM> is the target U/M to convert the field value to (if not specified the U/M of the associated product is used)

Ex: CONV.QTY_SOLD_1=UMCONV(@PRODUCT_CODE_1,T,@IN_UNIT_OF_MEASURE_1) converts the quantity sold for the first product if it's product type is Asphalt from the source U/M from the IN_UNIT_OF_MEASURE_1 import field into the target U/M specified in the product table

Keystone<->CompuCrete 3.9 Batch Import Parameters Comparison

Compu-Crete Parameter	Keystone Parameter	Keystone Default	Description/Comments	Done
	Add_Job=Yes|No, <batch_job_key_field>, <batch_job_name_field>	No	Specifies whether to add jobs automatically. Will only add jobs if <batch_job_key_field> is not blank.	Y
	Batch_Control_Type=Eagle8|Eagle7	Unknown	Activates special pre-processing for certain batch controls	Y
	CSV_Delimiter	(Double quote)	Specifies the character that encloses string fields for CSV format	Y
	CSV_Separator	(Comma)	Specifies the character that separates fields for CSV format	Y
	Desc_Method=Office|Batch	Office	Office descriptions override batch, Batch descriptions used always	Y
	Eagle_Converter	BIN\EXPS8.EXE	Specifies the location of the Command Data supplied Eagle 8 conversion utility.	Y
	Eagle_Converter_Options	/R /F /T~	Specifies the command-line options for the Eagle 7/8 conversion utility.	Y
	FIELD.CSZ		Used when importing a combined City, State, Zip field	N
	FIELD.DELV_INST_n			Y
	FIELD.SALESPERSON			Y
	JobPrice_Method=Office|Batch|Update|Ask	Office	When the job price does not match the batch price: Office – Use office price Batch – Use batch price Update – Use batch price and update office Ask – Ask for one of these options
	TaxCode_Method=Office|Batch|ExemptOffice [,<taxcode>]	Office	Normal office tax code determination, Batch tax code overrides if valid <taxcode> used as default ExemptOffice uses office tax code if both batch and office are exempt	Y
	TICKET_RENAME_EXT		Change the file extension of the ticket file after a successful import	Y
	UOFM_Method=Office|Batch	Office	Office u/m override batch, Batch U/M used always	Y
	USE_PLANT_TAXAUTH=Y|N	Y	Plant tax authority overrides ticket tax authority if present	Y
ADDCUSTOMER=YES|NO[,<start_cust_no>(default 90000)]	Add_Customer=Yes|No Add_Customer_Starting_Cust_No=<cust_no>	N 90000	Specifies whether to add new customers automatically	Y
BATCHCUSTADR1=<col>,<len>	FIELD.ADDRESS_1			Y
BATCHCUSTADR2=<col>,<len>	FIELD.ADDRESS_2			Y
BATCHCUSTCITY=<col>,<len>	FIELD.CITY			Y
BATCHCUSTID=<col>,<len>	N/A (see FIELD.SEQUENCE_CODE)			Y
BATCHCUSTNAME=<col>,<len>	<a href="http://FIELD.NAME" target="_blank">FIELD.NAME</a>			Y
BATCHCUSTSTATE=<col>,<len>	FIELD.STATE			Y
BATCHCUSTZIP=<col>,<len>	FIELD.ZIP			Y
BATCHSYSFILE=<filename>	Ticket_File	<none>	Path/Filename of batch ticket file	Y
BATCHTAXCODEDEFAULT=<tax_code>	N/A (use DEFAULT.TAX_CODE_1)		Tax Code to use if all else fails	Y
CASHCUSTOMER=<cust_no>	N/A (use Cash indicator from Customer table)		Specifies the cash customer Id	Y
CHECKTRUCK=YES|NO[,<col>,<len>]	FIELD.TRUCK_NO and VAL.TRUCK_NO			Y
CODCODE=LOOKUP|,<col>,<len>	FIELD.COD_FLAG (LOOKUP no longer needed)			Y
CODTRANS=YES|NO	N/A (see Add_Transportation)			Y
COMMENT=<col>,<len>	FIELD.COMMENT_n			Y
CUSTOMERID=<col>,<len>	FIELD.CUST_NO			Y
CUSTOMERPO=<col>,<len>	FIELD.CUSTOMER_PO			Y
CUSTOMERSEQUENCE=<col>,<len>	FIELD.SEQUENCE_CODE_LOOKUP			Y
DEFAULTCUSTOMER=<cust_no>	N/A (use DEFAULT.CUST_NO)		Customer Id to use if all else fails	Y
DEFAULTPLANT=ASK|<plant_no>[,OVERRIDE]	N/A (see FIELD.PLANT_NO)			Y
DEFAULTPRODUCTCODE=<product_code>	N/A (use DEFAULT.PRODUCT_CODE_1)		Product Code to use if all else fails	Y
DEFAULTTAXAUTHORITY=<tax_authority>	N/A (use DEFAULT.TAX_AUTHORITY)			Y
DISCCODES=<col>,<len>	N/A			N
DRIVER=<col>,<len>[,NOLOOKUP|LOOKUP]	FIELD.DRIVER			Y
FILETYPE=FIXED|COMMA	Record_Format=Fixed|CSV Record_Delimiter=CRLF|LF|CR|<Hex> Record_Length=n File_Format=Single|Multiple (NOTE: When using File_Format=Multiple, field CONTROL_NO must be unique per ticket)	Fixed CRLF Delimited Records Single	Format of batch ticket file Fixed Length Records of length <n> Number of Records per ticket	Y Y Y Y
HEADER=Y	Header_Record_Count=n		Ignore the first n records of the import file	Y
JOBID=<col>,<len>[,<alt_job_id>]	FIELD.JOB_PO_LOC (use <alt_specifier> for <alt_job_id>)			Y
JOBPRICE=YES|NO	N/A (if job is specified, always performs job price lookup)
LOOKUPMETHOD=NUMSEQ|SEQNUM|NUMONLY|SEQONLY	Cust_Lookup=<batch_field>, <CC_field> [<batch_field>, <CC_field>]*		Matches CC fields against batch fields to lookup customers	Y
MULTIPLEPLANTS=ALL|YES|NO[,<mask>[,<plantlo>[,<planthi]]]	Append_Plant_No=All|First|None[,<mask>[,<plantlo>[,<planthi>]]] CONV.PLANT_NO=NUM(xx)	<mask> = -%s <plantlo> = all plants <planthi> = only <plantlo>	Append plant number to All, First or None of the products [If the plant number is in the specified range] <mask> is based on Delphi Format function.	Y
NONTAXAUTHORITY=<batch_non_tax_code>,<non_tax_authority>[,<tax_code>(default=2)]	Translate_Tax_Authority=<batch_tax_auth>, <CC_tax_auth>, <CC_tax_code> [,<batch_tax_auth>, <CC_tax_auth>, <CC_tax_code>]* -or- Translate.Tax_Authority Translate_Tax_Authority#n (where n starts at 1) can be used to concatenate multiple lines of tax authority translation		Provides a translation for tax authority	Y
PLANTID=<plant_name>	N/A (specified in Plants section of CCWBIMP.INI)			Y
PLANTNO=<col>,<len>	FIELD.PLANT_NO		use FORCE.PLANT_NO to force the plant number	Y
PRICEMETHOD=BATCHSYS|OFFICE|COD	Price_Method=Office|Batch|COD	Office	Price calculation method: Office price except for CASH, Batch price used always, COD uses batch price for COD & CASH customers	Y
PRODDESC=<col>,<len>	FIELD.DESCRIPTION_n			Y
PRODUCTCODE=<col>,<len>	FIELD.PRODUCT_CODE_n			Y
PRODUCTCODES=<col>,<len>	N/A (see FIELD.PRODUCT_CODE_n)			Y
QUANTITY=<col>,<len>	FIELD.QTY_SOLD_n			Y
SHIPADDRESS=<col>,<len>	FIELD.SHIP_TO_n			Y
SUPPRESSDEFAULTS=Y	N/A
TAXAUTHORITY=<col>,<len>	FIELD.TAX_AUTHORITY			Y
TAXCODES=<col>,<len>	FIELD.TAX_CODE_n			Y
TICKETDATE=<col>,<len>[,<date_mask>]	FIELD.TICKET_DATE			Y
TICKETNO=<col>,<len>	FIELD.TICKET_NO			Y
TIMEARRIVESITE=<col>,<len>[,<time_mask>[,NOWARN|WARN]]	FIELD.ARRIVE_JOB_TIME			Y
TIMEBATCHSTART=<col>,<len>[,<time_mask>[,NOWARN|WARN]]	FIELD.BATCH_START_TIME			Y
TIMEBATCHSTOP=<col>,<len>[,<time_mask>[,NOWARN|WARN]]	FIELD.BATCH_END_TIME			Y
TIMEELAPSED=<col>,<len>[,<time_mask>[,<col2>,<len2>[,<default>]] [,NOWARN|WARN]]	FIELD.ELAPSED_TIME -or- Calc_Elapsed_Time=<start_time_field>,<end_time_field>		use DEFAULT.ELAPSED_TIME to specify a default elapsed time	Y Y
TIMELEAVESITE=<col>,<len>[,<time_mask>[,NOWARN|WARN]]	FIELD.LEAVE_JOB_TIME			Y
TIMELEAVEYARD=<col>,<len>[,<time_mask>[,NOWARN|WARN]]	FIELD.LEAVE_PLANT_TIME			Y
TIMEMODE=TIMEOFDAY|ELAPSED,<col>,<len>[<time_mask>[,<col2>,<len2>[,<default>]][,NOWARN|WARN]]	N/A (see FIELD.TICKET_TIME, FIELD.ELAPSED_TIME and Calc_Elapsed_Time)			N
TIMERETURNYARD=<col>,<len>[,<time_mask>[,NOWARN|WARN]]	FIELD.RETURN_PLANT_TIME			Y
TIMESTARTPOUR=<col>,<len>[,<time_mask>[,NOWARN|WARN]]	FIELD.BEGIN_POUR_TIME			Y
TIMESTOPPOUR=<col>,<len>[,<time_mask>[,NOWARN|WARN]]	FIELD.END_POUR_TIME			Y
TIMETICKETED=<col>,<len>[,<time_mask>[,NOWARN|WARN]]	FIELD.TICKET_TIME			Y
TRANSPORTATIONSPLIT=YES[,<col>,<len>[,<product_prefix>[,<concrete_product_classes>]]]|NO	N/A (use AUTO PRODUCT)		Add transportation product to All, Charge or None of the tickets	Y
UNITDISC=<col>,<len>	FIELD.UNIT_DISC_n			Y
UNITPRICE=<col>,<len>	FIELD.UNIT_PRICE_n			Y
VERIFYTRANSPORTATION=<concrete_product_class_list>,<trans_product_class_list>[,<trans_product_prefix>]	Check_Transportation_Concrete_Product_Class=<product_class>[,<product_class>]* Check_Transportation_Trans_Product_Class=<product_class>[,<product_class>]* Check_Transportation_Product_Code_Prefix=<prefix>			N
VOIDINDICATOR=<void_code>,<col>,<len>[,NOWRITE]	FIELD.VOID_FLAG (NOWRITE no longer used)			Y
WARNNOJOB=YES|NO	N/A (use VAL.JOB_PO_LOC, REQ.JOB_PO_LOC)			Y
WARNQUANTITY=<qty_lo>,<qty_hi>	Check_Quantity=<qty_lo>[,<qty_hi>]		Issue warning if quantity of first product is outside the range given	N
ZONE=<col>,<len>	FIELD.ZONE			Y