Brief overview of inFlow’s database schema

Note: We will sunset inFlow v2 on July 31, 2024. This means inFlow v2 will no longer be offered for download and we will no longer be hosting its Knowledge Base and support articles.

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

Behind the scenes, inFlow uses a Microsoft SQL Server 2005 database to store its data. If you have the necessary programming skills available, it’s possible to get access to this database and use it to show information on your website, more sophisticated integration with other programs, etc.

Please keep in mind that future versions of inFlow may change the way the data is stored. Also, we do not recommend attempting to make changes to the database directly (the inFlow client does a lot of sophisticated handling). Instead, you should be importing the data into inFlow.

That said, 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 BASE_Inventory and BASE_InventoryQuantityTotal. BASE_Inventory stores the quantity on hand, broken down by location and sublocation. You may want to join with BASE_Location to convert LocationId into a human-readable form. Sublocations are stored directly as strings in the BASE_Inventory table and other places where they are used. Use BASE_InventoryQuantityTotal if you just want the total quantity on hand or the amounts that you’re expecting to receive from vendors (QuantityOnOrder) or send to customers (QuantitySold). If nothing is set in these tables, assume that the inventory is 0.

-Use VIEW_InventoryCost.Cost to look up the unit cost of an item.

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

-For Sales Order information, all the tables whose names start with SO_ may be relevant. SO_SalesOrder is the main table that contains the order number, customer information, and summary information like the amounts of tax and the total. inFlow can have different values in the Order tab, the Pick tab, the Invoice tab, etc. In most cases, it’s easiest to get information from the Order tab; the summary fields are OrderSubtotal, OrderTax1, OrderExtra (freight) OrderTotal, etc.

-SO_SalesOrder_Line should be joined with SO_Order on SalesOrderId. This contains the information for each order of your order.

-PO_PurchaseOrder and PO_PurchaseOrder_Line store the purchase order information. These are organized similar to Sales Orders.

Let us know what you plan to work on; while we can’t help you with the programming, and don’t offer an API, we may be able to provide suggestions as you work out the specifics. Good luck!

Yun Ling
Archon Systems
260 Carlaw Ave #397
Toronto, ON, M4M 3L1
April 11, 2012