Batch Ticket Import

From KeystoneIntranet
Revision as of 20:19, 2 April 2024 by Pmorey (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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
Retrieved from "http://in.compucrete.com/index.php?title=Batch_Ticket_Import&oldid=975"