References and routes

Reference tables provide a device with products, customers and related data. The accounting system prepares a separate XML file for each table in outbox. See XML tables for the general format.

Common reference fields

ref_products and ref_customers have these required fields:

parent must refer to a group in the same reference. Do not use 0 in place of an empty parent code: it refers to a group whose code is 0.

Products — ref_products

In addition to the common fields, include packaging and at least one price field with the price_ prefix.

This example contains group G-001 and product P-001 within it:

<?xml version="1.0" encoding="utf-8"?>
<table name="ref_products">
  <schema>
    <field name="code" type="TEXT KEY"/>
    <field name="name" type="TEXT"/>
    <field name="parent" type="TEXT KEY"/>
    <field name="group" type="INTEGER"/>
    <field name="packaging" type="REAL"/>
    <field name="price_Retail" type="REAL"/>
    <field name="rest" type="REAL"/>
    <field name="measure_unit" type="TEXT"/>
  </schema>
  <data>
    <record>
      <f>G-001</f>
      <f>Beverages</f>
      <f/>
      <f>1</f>
      <f>1</f>
      <f>0</f>
      <f>0</f>
      <f/>
    </record>
    <record>
      <f>P-001</f>
      <f>Water 1 L</f>
      <f>G-001</f>
      <f>0</f>
      <f>6</f>
      <f>42.5</f>
      <f>120</f>
      <f>pcs.</f>
    </record>
  </data>
</table>

Use value="Retail" in the price categories list for this example. Keep category codes consistent in vl_cat_prices, the price_… fields and customer settings.

Customers — ref_customers

You can add the following to the common fields:

<?xml version="1.0" encoding="utf-8"?>
<table name="ref_customers">
  <schema>
    <field name="code" type="TEXT KEY"/>
    <field name="name" type="TEXT"/>
    <field name="parent" type="TEXT KEY"/>
    <field name="group" type="INTEGER"/>
    <field name="cat_price" type="TEXT"/>
    <field name="debt" type="REAL"/>
  </schema>
  <data>
    <record>
      <f>C-001</f>
      <f>Store LLC</f>
      <f/>
      <f>0</f>
      <f>Retail</f>
      <f>850</f>
    </record>
  </data>
</table>

You can add addresses, phone numbers and other information as separate fields. Their titles and display can be configured on the device in column settings or through tables_columns.xml.

Individual prices — customer_product_data

This table defines individual product prices for customers. Each record links one customer to one product. The customer_code and product_code pair must be unique.

Include optional fields only when needed. If the individual price is empty or 0, the application uses a nonzero price_multiplier; if none is available, it uses the category price. Setting these fields to 0 does not make a product free.

<?xml version="1.0" encoding="utf-8"?>
<table name="customer_product_data">
  <schema>
    <field name="customer_code" type="TEXT KEY"/>
    <field name="product_code" type="TEXT KEY"/>
    <field name="price" type="REAL"/>
    <field name="order_count" type="REAL"/>
  </schema>
  <data>
    <record>
      <f>C-001</f>
      <f>P-001</f>
      <f>40</f>
      <f>12</f>
    </record>
  </data>
</table>

Routes — routes

Each record defines a customer visit. The table must contain customer_code and at least one scheduling field: date or week_day.

If the schema contains date, the application uses it to select the route; week_day is then ignored. Do not include date for a weekly schedule.

<?xml version="1.0" encoding="utf-8"?>
<table name="routes">
  <schema>
    <field name="customer_code" type="TEXT KEY"/>
    <field name="week_day" type="INTEGER"/>
    <field name="trade_point_value" type="TEXT"/>
  </schema>
  <data>
    <record>
      <f>C-001</f>
      <f>1</f>
      <f>TP-001</f>
    </record>
    <record>
      <f>C-001</f>
      <f>4</f>
      <f>TP-002</f>
    </record>
  </data>
</table>

The example schedules visits for Monday and Thursday. The customer and trade points must be downloaded to the same device.

Check relationships

After an exchange, check the log on the device, then check the data:

• groups open, and products and customers appear at the correct levels;
• the customer's price category matches an existing price_… field;
• an individual price applies only to the intended customer and product pair;
• the route shows customers for the selected date and their trade points.

All these tables are already listed in the Завантажити дані (Download data) profile in the standard exchange.xml.