Brief overview of inFlow On-Premise’s database schema

This article is highly technical and intended for programmers, not the typical inFlow On-Premise user.

Behind the scenes, inFlow uses a Microsoft SQL Server database to store its data. This may be either SQL Server 2005, SQL Server 2008 R2 or SQL Server 2014, depending on options chosen during the installation process.

If you have the necessary programming skills available, it’s possible to get access to this database directly. You can use it to show information on your website, more sophisticated integration with other programs, etc.

Caution: This is not something we technically support. Future versions of inFlow may change the way data is stored. Therefore, we recommend that accessing the database should only be used to pull information out of inFlow, not push information into the database directly.

Here are some of the major database tables that you may be interested in looking at:
-We use a convention where the table name starts with a loosely defined module. Most of the basic table names start with BASE_, Sales Order related tables start with SO_, Purchase Orders with PO_, and other inventory information in INV_ tables.

-For product information (name, description, etc.), look at the BASE_Product table. The primary key is ProdId, and this is used in many other tables to join to BASE_Product.

-For inventory, look in VIEW_Inventory and VIEW_InventoryQuantityTotal. VIEW_Inventory shows the quantity on hand, broken down by location, sublocation, and serial number. You may want to join with BASE_Location to convert LocationId into a human-readable form. Sublocations are stored directly as strings wherever they are used. Use VIEW_InventoryQuantityTotal if you just want the total quantity owned, cost value (CuHInventoryValue) or the amounts that you’re expecting to receive from vendors (QuantityOnOrder) or send to customers (QuantityReserved). If nothing is set in these tables, assume that the inventory is 0.

-BASE_Customer and BASE_Vendor respectively store the customer and vendor information.

– PO_PurchaseOrder is the main table that contains the order number, vendor information, and summary information like the amounts of tax and the total. inFlow can have different values in the Purchase tab, the Receive tab, Return tab, etc. The SubTotal displayed on the screen isn’t stored directly in the database, but can be calculated is OrderTotal – ReturnTotal. Taxes and Freight (OrderExtra, ReturnExtra) are done similarly.

-PO_PurchaseOrder_Line should be joined with PO_Order on PurchaseOrderId. This contains the information for each line item of your order.

-SO_SalesOrder and PO_SalesOrder_Line store the sales order information. These are organized similar to Purchase Orders.
Good luck!

inFlow Inventory
Archon Systems
w,
+1.866.923.4974
260 Carlaw Ave #397
Toronto, ON, M4M 3L1
Canada
August 21, 2017

Rate this article

Not enough ratings yet
1 star2 stars3 stars4 stars5 stars
Loading...
  • Did this article help? If not, please let us know so we can improve it.

  • This field is for validation purposes and should be left unchanged.