Physical Database Documentation
 
Library Physical Database

Commonly Used Tables

Products Salelines Sales User Data Accounts Contactlog Eftposreceipts Eftposreceipts2 Locations Payments Pricemaps Reference Data Statetable System Data R_paymenttypes Salelog Usagetoken Analytics Data Ds_customer_a Ds_customer_b Ds_product_a Ds_sales_a Other Tables Accountadjustments Accountallocations Accountchangeslog Accountshumanrequired Accountsupport Activitylog Advisor_actions Advisor_tests Allocatedrights Apikeys Apikeys_access Apikeys_lock Apikeys_resource_locks Apitxnlog Apitxnlogdata Asndetail Asnheader Asnheader2 Asnitem Attribute Attributedefinitions Authcodelog Autobalancerecon Autobalancereconlog Backgroundlog Barcode_patterns Barcodefailurelog Barcodeinvalid Barcodeoneoff Barcodeoneofflog Barcodepatterns Barcodes Barcodes_ean99 Barcodes_trade Barcodeusedlog Bisho_status Bistx Bookingres Bookingsales Bookingscans Bookingsessions Bulkloadlog Cabinets Callerid Cameradef Carriers Cashdraweractionlog Cashdrawerlog Cautionlist Ceres_productlist Chatheader Chatmessage Companies Configui Contact Contactdetails Contactdetailsmap Costchangesseen Counters Counters_log Createapps Customercardgroups Customercards Customermessages Customers Customersdcid Customersdcidrpt Customersdcids Customersdcidslinks Customerstate Customertracks Customertrackspending Decisiontreeheader Decisiontreelines Demographic Discountfilter Discounts Discountsmanual Docuheader Doculine Documentformats Documentparsematch Documentprocessing Documulti Doesnotexist Drivercashlog Ediauditlog Employees Eventaudit Expectedsales Expensetypes Faults_detail Fdlonline_enablematrix Feectl Fees Floatlog Flow_anchor_raw_string Fmea Fpuicontrol Fpuicontrol2 Franchise_barcodes Franchise_departments Franchise_products Franchise_products_original Futuredatachanges Futuretxns Gateways Geoareas Globalvalues Goodsinheader Goodsinhistory Goodszeropoint Headerreceive Headerreceive_head Heartbeat Hiregroup Httpurllog Idset2 Idsetctl Idsetdata Idsetdata2 Inventorylevels Ldflog Linkedserver Livepayments Livesalelines Livesales Localsaleflags Location_area Location_cab Location_tradingdays Longdatavalues Machinesuser Macro Maintlist Marketingprogram Measurements Media Mediacontrol Mediafiles Mediaresource Mediastorage Membership Membershipdata_all Messageformat Messageq Messageuser Messagingt Modifiergroups Modifierheaders Modifieritems Modifierpriceheader Modifierpriceline Modifierproductgroups Modifieruplifts Modififergroups Multichain_sourcemap Multilane Multilink_all Multiplu Networklane Networklane_environment Networklane_status Networklanecode Networklaneconfig Ordercategories Ordersreceived Ordersreceivedlines Orderstxns Overflow_gnap Overflow_tmpr Perfcounters Pidcoststatus Plicence Poheader Poheader_head Poheaderreceive Poheaderreceive_head Poline Poline_head Poms Posaudit Posendtypes Poshipping Poslat Posrelationships Prefsaleitems Pricebandmap Pricebands Pricebookcurrent Pricebooksource Pricebookspecialsheader Pricebookspecialsline Pricebufferdist Pricechangepending Pricegroups Pricehistory Pricemapsexceptions Priceoptions Pricerevision Priceused Printformats Printing_pending Printingformats Printingrules Procuserlogicline Product_cab_active Product_dispatch Product_dispatch_error Product_packsize Productattribute Productconnections Productconnectionstype Productcontrollog Productkitdef Productkits Productkitsext Productlocspacemap Productlocspacesets Products1 Products2 Products3 Products5 Products_category0 Products_category1 Products_costhistory Products_inventory Products_inventory_changes Products_inventory_point Products_manufacturer Products_overlay Products_properties Productsbom Productsbomheader Productsinst Productslocation Productslocationbis Productsordering Productspacesets Productssale Productsupplier Productsupplierdist Productvariant Profanity Purchasecontrols Purchaseorderprocessing Qoh_pub Qtydiscounts Quickcodeaudit R_colors R_departments R_departments2 R_departments3 R_departments4 R_departments5 R_departments6 R_departments7 R_departments8 R_fashiongroups R_pgm_periods R_returnreasons R_sizes R_styles R_writeoffs Rcvstock Rcvstockline Receiptextn Remotecommands Remotecommandslog Rewardmovements Rewardprogramoptions Rewardprogrampids Rewardprograms Rewardprogramspids Rewardtxns Rewardtxnslog Sale_delivery Salealloc Salechangesaftercomplete Salechar Salecomments2 Saleinfo Salelines_cost Salelines_deleted Saleloadtrace Saleloadtraceline Sales_errors Sales_picking Sales_recall Sales_standingorders Salesdcid Saleservercommands Salesprofilesingle Salestaff Salestatistics Salesunpaid Salevar Salevar2 Samplelist Scriptmap Scripts Seritem Seritemlog Servercommands Settings Settingsactive Settingsdist Settingskv Settingskvdist Settingsmembership Settingspolicy Settingspolicymap Settlecashdetail Settlepaydetail Shelfvisit Shifttrack Ss_pricebuffer Staff Staff_eauth Staff_magid Staff_ownership Staff_rights Staff_stats Stafftablecontrol Staffused Staffused_log Statementhistory Stickies Stock_fifo Stock_lifo Stockcount Stockholdrequests Stockmovements Stockpoints Stockreceipts Stocktakedetail Stocktakedetail2 Stocktakemaster Stocktakesnapshot Stocktakesnapshot_tracked Stocktxnhdr Stocktxnitem Subscriptions Supplier_inventory Suppliermessages Supplierpricebook Supplierpricebookline Suppliers Suppliers2 Suppliersautoorder Svcaccount Svcards Svctxnhistory2 System_status Tabledisthead Tablemgmt Taxrates Telephone_number_plans_override Ticketpricerequired Ticketpriceshowing Timeclock Timeclocklog Timeschedule Tmpr_log Tnotes Topology Tracelogic Trackeditem Trackeditemobservation Traffic_counts Transit_inventory Transit_inventory_lines Transit_request Transit_request_lines Triggers Tushlog Typesuser U_cw_cartridges U_cw_printers Upstream Usagerecord Userlogicvariable Valuedomainrule Vehicle_makes Vehicle_models Versioncontrol Verticalsecurity Voucher Voucherprogram Webgroup Webhooks Webpages Webpagesrules Worklist Workqueue Workqueuetx Internal Technical Tables B2backup Customers2 Customers3 Dbcomm Emailinrules Firewallrules Keylease Keyleasefreelist Localdb_datpq Localdb_datpq_inbound Localdb_lanetx_queue Localdb_membership_replicate Localdb_pinb_cache Localdb_receivequeue Localdb_replicationstatus Localdb_salestechnicalaudit Localdb_sendqueue Localdb_sendqueuedata Localdb_settings Localdb_values_write Localdb_version Loginkeys Products4 Replicationcontrol Salememory Sales2 Serverprocessing Serverprocessingdates Tubtlocal Wipheader Wipline Deprecated Tables Auditsystem Pictures Retired Tables Biscontrol Cjl Eftpos_txns Linkproductspictures Pomprodlink Experimental Tables Keytranslate Posscriptpage Posscripttrigger

Customers Database Table

Distrbuted Access: Lanes( read insert edit CanSync Edits-require-TUBT )

Fields

cid
#100, Datatype: Integer
Unique number assigned to this customer record. Negative numbers may be subject to automatic renumbering. Zero is never used.
physkey
#254, Datatype: String 44 bytes
Unique primary key assigned to this record.
name
#101, Datatype: String 120 bytes
The customers full name. This field is typically used on reports. If left blank the system will populate it based on other fields
lastname
#102 The last name of the customer.
firstname
#103 The customers first (christian) name.
address1
#104 Line 1 of the customer primary address
Address2
#105 Line 2 of the customer primary address
Address3
#106 Line 3 of the customer primary address
Address4
#107 Line 4 of the customer primary address
Address5
#108 Line 5 of the customer primary address
Address6
#109 Line 6 of the customer primary address
city
#110 Primary city or town this customer resides in. This value is expected to also be present in the AddressN fields and is extracted to aid reporting
postcode
#111 Post code for this customer. This value is expected to also be present in the AddressN fields if relevant and is extracted to aid reporting
phone
#112 Customer primary phone number
mobile
#113 Customers mobile phone number
fax
#114 Customers fax number
email
#115, Datatype: String
Customers primary email address
contactcomment
#116 A comment relating to communication with this customer. Designed to say things like 'Do not call after 6pm'
birthday
#117 Customers birthday. This can be either a precise date or a DD/MM pair. Used to trigger Point Of Sale messages on this aniversary
credit_limit
#122 Total credit limit for this customer. Typically customers belong to an account and the account provides the credit limit
floor_limit
#123 Maximum single transaction for this customer.
rid
#127 Internal use only.
customercard
#131 A unique barcode sequence assigned to this customer alone. This field should contain the complete barcode
accid
#132, Datatype: Integer
Account number this customer belongs too. Zero if customer does not have a charge account.
priceband
#136 Price band assigned to this customer if they have a specific pricing structure
receiptformat
#137 If this customer is to use a specific receipt format, this field contains the name of the format to override system defaults
custflags
#138, Datatype: Integer
ridutc
#140 Internal use only.
handness
#143 Is customer left or right handed
flybuy
#145 Customers Flybuys number.
applicationnumber
#147 Serial number from an application form. Used when customers complete an application or similar. Provides a reference back to the original document.
createdt
#152, Datatype: DateTime
Date this customer was created in the system.
phone2
#153 Secondary phone number of customer
companyname
#154, Datatype: String 60 bytes
Company name for this customer
keywords
#155 A comma or pipe seperated list of keywords assigned to this customer. Keywords and meanings are free for sites to assign.
store
#156 Store that originally created this customer
createtid
#157 Teller that created this customer record.
ReferredBy
#161 Referred By
CustomerRep
#162
comments
#163, Datatype: String 132 bytes
assigneddivision
#164
State
#166, Datatype: String 40 bytes
State/Province for address
iflags
#167, Datatype: Integer
Internal flags and status bits

Possible Values

ValueDescription
1ContactDetail records exist
MarketingOptIn
#210
EUCitizen
#211
RecordType
#212
MarketingFlags
#213
StaffMember
#214, Datatype: Integer
Job Title
#215, Datatype: String 32 bytes
Occupation
#216, Datatype: String 40 bytes
assignedstore
#217, Datatype: Integer
JobStatus
#218, Datatype: Integer
editdtu
#219, Datatype: DateTime
Date/time UTC this record was last changed
geolatitude
#220, Datatype: Double
Customers primary Geographic latitude
geolongitude
#221, Datatype: Double
Customers primary Geographic longitude
geoaltitude
#222, Datatype: Double
Customers primary Geographic altitude
rve
#223, Datatype: Double
rvel
#224, Datatype: Double
contactinterval
#225, Datatype: Integer
Defines the number of days that can elapse before contact is made to this customer
crmflags
#226, Datatype: Integer

Possible Values

ValueDescription
1Sale counts as contact
lastcontactdt
#227, Datatype: DateTime
Date/time (local) this customer was last contacted.
OccupationFreeForm
#300, Datatype: String 60 bytes
OccupationANZSCO
#301, Datatype: Integer
SellingMessage
#302, Datatype: String 250 bytes
Simple text message displayed on screen when customer selected at Point Of Sale
emailtransactions
#303, Datatype: String 100 bytes
Customers email address for transaction information
emailmarketing
#304, Datatype: String 100 bytes
Customers email address for marketing
emailgeneral
#305, Datatype: String 100 bytes
Customers email address for general communication
abn
#306, Datatype: String 15 bytes
Australian ABN number
gst
#307, Datatype: String 24 bytes
GST number
nzbn
#308, Datatype: String 15 bytes
New Zealand NZBN Number
Replacementphyskey
#309, Datatype: String 44 bytes
Replacement Physkey of new customer when a customer is reassigned or merged
istate
#311, Datatype: Integer
Internal use only state flags
idate
#312, Datatype: DateTime
Internal use only
deletedt
#313, Datatype: DateTime
Date this customer was deleteed in the system.
externalid
#314, Datatype: String 64 bytes
A site specific external Id, used when a link to another systems id is required
myob_contactid
#315, Datatype: String 50 bytes
RmSystem
#316, Datatype: String 44 bytes
RmSytem that owns/defines this customer
addressraw
#501, Datatype: String 250 bytes
Complete multi line address
CreateAccount
#888 Request a new account be created for this customer
ocid
#899 Original cid allocated when created in distributed environment
stopcreditflag
#4000 Set to true if this customer is on stop credit.
u_invoicefrequency
#5000
u_paymentcard
#5001
u_allcredit
#5002
urucreg
#5003
prepay
#5004
accountstatus
#5005