Bipost Sync Settings

IMPORTANT NOTICE: Many configuration settings including Service No, Activation No and Specific Bucket are linked exclusively to your account. Treat these as sensitive information.

From this point on you need your Service No. and Activation No. available on your Factor BI Console.

Screenshot

Click Configuration and set:

  • Service No.: 36 digit hex number, it may look like this: a1bcd23e-4fa5-67b8-cd9e-f0123abc4567
  • Activation No.: 24 digit hex number, it may look like this: 5990ab12c3de45f6a78bc90d
  • Engine: Select Firebird or SQL (Microsoft SQL Server).
  • System: Select Custom...

    If you use Microsip ERP or Aspel SAE then select the System according.


Firebird Connection

  • Database: Location of your .FDB file.
  • Password: Set your Firebird password, sometimes masterkey

Use the following only when Bipost Sync is not located on the same server as the Firebird server.

  • Remote Connection: Enable when biPost.exe is on a remote location on your LAN.
  • Server: IP or name of the server on your LAN network.

SQL Connection

Screenshot

  • Server: IP or name of the server on your LAN network.

  • User: Login for your SQL server. It only needs read permissions.

  • Password: Password for the Login provided.

  • Database: Name of your database.


General Settings

Screenshot

  • Specific Bucket: Enable to use your own AWS Account.

    Enter your Bucket Name that we provided over email.

    Remove arn:aws:s3::: and just leave bipostdata-xx_my_number_xx, example:

    bipostdata-123456789012
    
  • Download Data: Enable to download data from AWS Aurora-MySQL to your on-premises.

Recursive Sync

When enabled it optimizes upload by extracting and uploading one day at a time for the given date range.

Very useful to upload historic data or big data sets.

It is always used in combination with customData.json so you can configure the date field to use for each table.

When turned off it automatically sets today as start and end date, using your system clock.


customData.json

This file allows you to specify the tables, fields and filter criteria to apply on the select statement that extracts data sets from your on-prem DB.

Example 1, using recursiveDateField:

[
  {
    "active": "true",
    "table": "Venta",
    "fields": "Venta.ID, Venta.Empresa, Venta.Mov, Venta.MovID, Venta.FechaEmision, Venta.Concepto, Venta.Moneda, Venta.TipoCambio, Venta.Estatus, Venta.Cliente, Venta.Almacen, Venta.Agente, Venta.Condicion, Venta.Vencimiento, Venta.DescuentoLineal, Venta.Sucursal, Venta.SubModulo, Venta.Importe, Venta.Impuestos, Venta.CostoTotal, Venta.FechaRegistro, Venta.DescuentoGlobal, Venta.Proyecto",
    "join": "MovTipo WITH (NOLOCK) ON Venta.Mov = MovTipo.Mov AND MovTipo.Modulo = 'VTAS'",
    "filter": "Venta.Estatus NOT IN ('SINAFECTAR','BORRADOR') AND MovTipo.Clave IN ('VTAS.F','VTAS.D','VTAS.N','VTAS.FM','VTAS.FC')",
    "recursiveDateField": "Venta.FechaEmision"
  },
  {
    "active": "true",
    "table": "VentaD",
    "fields": "VentaD.ID, VentaD.Renglon, VentaD.RenglonSub, VentaD.Articulo, VentaD.Cantidad, VentaD.Almacen, VentaD.Precio, VentaD.DescuentoLinea, VentaD.DescuentoImporte, VentaD.Impuesto1, VentaD.Costo, VentaD.ContUso, VentaD.Unidad, VentaD.Factor, VentaD.Agente",
    "join": "Venta WITH (NOLOCK) ON Venta.ID = VentaD.ID JOIN MovTipo WITH (NOLOCK) ON Venta.Mov = MovTipo.Mov AND MovTipo.Modulo = 'VTAS'",
    "filter": "Venta.Estatus NOT IN ('SINAFECTAR','BORRADOR') AND MovTipo.Clave IN ('VTAS.F','VTAS.D','VTAS.N','VTAS.FM','VTAS.FC')",
    "recursiveDateField": "Venta.FechaEmision"
  }
]

Example 2, for catalogs:

[
  {
    "active": "true",
    "table": "MovTipo",
    "fields": "Modulo, Mov, Clave",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  },
  {
    "active": "true",
    "table": "Cta",
    "fields": "Cuenta, Rama, Descripcion, Tipo, EsAcreedora, EsAcumulativa, CentrosCostos, Estatus, ClaveSAT",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  },
  {
    "active": "true",
    "table": "CentroCostos",
    "fields": "CentroCostos, Rama, Descripcion, EsAcumulativo, Grupo, SubGrupo, SubSubGrupo, Estatus",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  },
  {
    "active": "",
    "table": "",
    "fields": "",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  }
]

The above JSON will send all data for the specified tables, as join and filter are not in use.

Note that you can leave "active": "",

Example 3, using special filter when no datetime is available:

[
  {
    "active": "true",
    "table": "bibliaAlmacen",
    "fields": "id, empresa, articulo, almacen, y, m, venta, devolucion, compra, devolucionCompra, trasladoRecepcion, trasladoSalida, otraEntrada, otraSalida, invInicial, inventario, valor, valorUSD, costo, costoUSD, costoVenta, costoVentaUSD, total1, peso, acum, abc, idym",
    "join": "",
    "filter": "y = datepart(yy,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))) AND m = datepart(m,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))",
    "recursiveDateField": ""
  }
]

join and filter can use any syntax supported on SQL Server/Firebird SQL.

Example 4 with Microsip ERP:

[
  {
    "active": "true",
    "table": "ATRIBUTOS",
    "fields": "*",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  },
  {
    "active": "true",
    "table": "IMPUESTOS_ARTICULOS",
    "fields": "*",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  },
  {
    "active": "true",
    "table": "IMPUESTOS_DOCTOS_CM",
    "fields": "*",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  },
  {
    "active": "true",
    "table": "IMPORTES_DOCTOS_CC_IMPTOS",
    "fields": "*",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  },
  {
    "active": "true",
    "table": "IMPORTES_DOCTOS_CP_IMPTOS",
    "fields": "*",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  },
  {
    "active": "true",
    "table": "DOCTOS_ENTRE_SIS",
    "fields": "*",
    "join": "",
    "filter": "",
    "recursiveDateField": ""
  }
]

In the above example Bipost Sync is using System: Microsip and on every sync it sends all the factory embedded tables plus the tables set in customData.json

Tables & Primary Keys

Take note of the following:

  • Every table specified in customData.json must have a Primary Key and these must be listed on "fields"
  • You can only setup customData.json with base tables.
  • Use * on fields to retrieve all table columns.
  • "fields" parameter must only include fields that exist on "table":

recursiveDateField

This parameter is used when Recursive Sync check box is enabled.

It only supports date fields without hour.

On SQL Server using datetime data type, it only supports dates ending in 00:00:00.000

Handle multiple customData.json settings

It is very common to make changes to customData.json to upload different sets of data.

Use Cases:

  1. Some tables may be uploaded once since that data is rarely changed, e.g. config & company tables.

  2. Historic data may be uploaded once, e.g. transactions from previous years.

  3. Recently changed data may be uploaded monthly or daily, e.g. invoices, quotes, purchase orders, etc.

  4. Recently created and updated catalogs may be uploaded monthly or daily, e.g. customers, items, vendors, etc.

For all these reasons it may be very useful to make copies of Bipost Sync folder and just change customData.json. Moreover you may want to have different sync schedules, which are explained next.


Schedule

Screenshot

If you want automated execution of Bipost Sync, then set the Hour desired and click Schedule.

This will create a Windows Task that will run daily. If you want a different schedule, then open Windows Task Scheduler as follows.

Control Panel \ Administrative Tools:

Screenshot

Screenshot

If you manually create a task to run biPost then use argument: post

Screenshot


Check for Updates

New versions of Bipost Sync can be checked using Help \ Check for Updates.

Screenshot

Screenshot


Sync multiple databases

If you are going to synchronize two or more databases, create a separate Bipost Sync folder on Windows for each one. Then customize each database with the desired data set as explained here.