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

Locations Database Table

Distrbuted Access: Lanes( read )

Fields

location_id
#100, Datatype: Integer
Internal reference number allocated by the system. Numbers greater than 0 are constant, negative numbers are reserved to Fieldpine may change without warning. Zero is an invalid value.
Name
#101, Datatype: String 50 bytes
Name of the location as shown on reports
latitude
#102, Datatype: Double
longitude
#103, Datatype: Double
city
#104
location
#105
address1
#106
address2
#107
address3
#108
address4
#109
address5
#110
address6
#111
Comments
#112
rid
#113
stockinserver
#117, Datatype: Integer
Are stock levels maintained in the server, or individual lanes as a source of truth
stockflags
#120, Datatype: Integer
Range of flags to control operation of this location. Despite being called stockflags it also controls non stock related functions

Possible Values

ValueDescription
1If set location cannot purchase stock
2If set location cannot receive stock, this includes store transfers and supplier deliveries
4If set store provided stock levels are forced into upstream servers. Rarely used, special cases only
1024Hidden stock levels. Stock held by this location may be hidden from some reports
2048Selling disabled. If set the store cannot raise sales
companyid
#121, Datatype: Integer
Company Id#. Used only in multiple brand configurations. Deprecated do not use for new development
active
#122, Datatype: Boolean (stored in integer field)
Active Store
publish
#123
Parent
#124, Datatype: Integer
Parent store that controls this store, if stores have structure, otherwise blank
OpenDt
#125
CloseDt
#126
postcode
#127, Datatype: String 14 bytes
taxregion
#128, Datatype: Integer
Region or State this store operates within. Only required for countries that have state or local taxes
taxid0
#129, Datatype: String 40 bytes
bankbsb
#130, Datatype: String 16 bytes
Australian BSB code
bankaccountno
#131, Datatype: String 30 bytes
bankaccountname
#132, Datatype: String 40 bytes
lastip
#149
phone
#150
Email
#151 Internal use email typically used for admin contact
Contact
#154 A published name of the person acting as primary contact for this location
descaddr
#155 A descriptive address for shoppers describing how to find the store
deladdr
#160
BuyerPhone
#161
FormalName
#162, Datatype: String 180 bytes
Legal name of store that might be used on documents such as purchase orders
OrderCC
#163, Datatype: String 120 bytes
Email address to receive a CC copy of all Purchase Orders emailed.
TypeCode
#164, Datatype: Integer
Actual type of location. See Locations database definition for acceptable values.

Possible Values

ValueDescription
1Retail Outlet
2Remote Outlet
3Mobile Store
4Non Physical Store
5Store in a store
6Warehouse
7Container
8Internet Storefront
9Head Office
10Branch Office
11Agency
12Offsite
PublicAccess
#165, Datatype: Integer
Public have access to this location

Possible Values

ValueDescription
0Public
1Restricted
2No
GeoReportArea
#166 Geographic area for reporting and management. Stores do not need to physically be in this area

Possible Values

ValueDescription
6400001NZL, Auckland
6400002NZL, Wellington
6400003NZL, Christchurch
6400004NZL, Dunedin
6400005NZL, Hamilton
6400006NZL, Bay of Plenty
6400007NZL, Canterbury
6400008NZL, Central
6400009NZL, Northland
6400010NZL, Otago & Southland
6400011NZL, Waikato
6400012NZL, National
6400013NZL, Other
6400014NZL, Auckland Central
6400015NZL, Auckland North
6400016NZL, Auckland South
6400017NZL, Auckland West
6400018NZL, Auckland East
PostAddr
#167 Postal Address of store
externalid
#168 External Id number. Used as reference to other systems code for this location
region
#169 Region code
branch
#170 Branch code
fax
#171
shortcode
#176, Datatype: String 20 bytes
A short abbreviated code to describe this location. Must be uniqe
installpassword
#177, Datatype: String 40 bytes
A simple password that must be entered in order to install new software into this store.
installallow
#178, Datatype: Integer
An encoded bitmask that controls what sort of software can be installed into this location

Possible Values

ValueDescription
1PosGreen allowed
2Direct to server permitted
256StoreServer allowed
receiptmessage
#179, Datatype: String 240 bytes
Store specific message to place on customer receipts. The receipt format must specifically include this symbol
StockLoc
#183, Datatype: Integer
Location number stock is taken from
commission
#184, Datatype: Double
Commission payable by store. This value is not directly used by Fieldpine but is available for site specific reports
StoreControllerType
#185, Datatype: Integer
Where this stores controller is located

Possible Values

ValueDescription
0Undefined
2Self Hosted
4External
8Child
256Fieldpine Online/Sandbox
257Fieldpine Online/ArmsLength
StoreControllerLoc
#186, Datatype: Integer
Location id of owning store controller
altnames
#190 Alternative List. Other names this store can be known by. Often used when matching supplier store names. Names must be unique, no single value can match multiple locations.
operatingprofile
#200, Datatype: String 40 bytes
Key to profile this store uses
rve
#253, Datatype: Double
physkey
#254 Internal identifying key. This value can always be used to refer to a specific location
RmSystem
#255, Datatype: String 44 bytes
backupflags
#261, Datatype: Integer
Internal flags controlling how backups are performed
permdesc
#262, Datatype: String 40 bytes
labelcontrol
#263 JSON data to override label formats when printing
timezone
#264, Datatype: String 80 bytes
PublicName
#300 Friendly version of store name for general public use
CostCentre
#301 Financial cost centre used in accounting system
phone_quick
#302 Quick dial phone number for internal PABX
fax_quick
#303 Quick dial fax number for internal PABX
Mobile
#304 Mobile phone number of store
EmailMgr
#305 Managers email
IPRangeInStore
#306
IPNetmaskInStore
#307
StoreServer1Addr
#308, Datatype: String 150 bytes
Store Server 1
StoreServer2Addr
#309, Datatype: String 150 bytes
Store Server 2
StoreDNS
#310, Datatype: String 150 bytes
Store DNS
RouterProfile
#311 Router Profile
PriceBand
#312 Price Band
AgencyLogin
#313, Datatype: String 64 bytes
login name (email) used for an agency to access fieldpine.com
AgencyLoginPass
#314, Datatype: String 64 bytes
login password used for an agency to access fieldpine.com
CountryState
#315, Datatype: String 16 bytes
Regional state of this location
deployedsscode
#316, Datatype: Integer
Approved server code that can be run in this location

Possible Values

ValueDescription
0Not specified
GeoAllow
#318, Datatype: String 120 bytes
InstalledProfile
#319, Datatype: String 180 bytes
ScLaneAuthKey
#320, Datatype: String 60 bytes
An auth key that can be used to authorise lanes onto remote RmSystem
ScOwnerAuthKey
#321, Datatype: String 60 bytes
An auth key that we can use to access the remote RmSystem
ScRmSystem
#322, Datatype: String 40 bytes
The RmSystem allocated to the remote RmSystem
CountryCode
#323, Datatype: Integer
Country code number for where this store is located
GeoArea
#324, Datatype: Integer
Geographic area of this store
pickingemail
#325, Datatype: String 96 bytes
Email address to receive internal notices about web sales
purchaseordertemplate
#326, Datatype: String 196 bytes
Url of default purchase order template for this store
heartbeatdt
#1600
zone
#1601
zid
#1602
station
#1603, Datatype: Integer
Holds an alternative number for location, used by some retailers that need to hold additional values. This value is not used directly by Fieldpine
poweraccountno
#6000 Power Supplier Account Number
nzl_mtano
#1064000 MTA Membership number
nzl_powericp
#1064001 Power supplier ICP reference number
nzl_powerretailer
#1064002 Power supplier (retailer)
avic
#1064003, Datatype: String 6 bytes
NZTAApproval
#1064004, Datatype: Integer
NZTAAssesor
#1064005, Datatype: String
nzl_datacom_payroll_id
#2064000, Datatype: String 6 bytes
InStoreRadio
#3000000, Datatype: Boolean (stored in integer field)