Power BI with your accounting data

BIPurchaseOrders - Sage 50 Accounts Purchase Orders table

Posted by Hugh Johnson on 19-Apr-2018 11:18:34
Hugh Johnson
Find me on:

Sage 50 Accounts OData feed for Power BI

This article describes the BIPurchaseOrders table in the Suntico Sage 50 Accounts OData feed for tools like Microsoft Power BI.  This table is derived from the underlying Sage 50 Accounts PURCHASE_ORDER ODBC table. 

This table is essential if you want to analyse your Sage 50 Purchase Orders.

BIPurchaseOrders table

 

Column Type Description
AmountPrepaid Decimal Number Prepaid amount on the PO
CurrencyUsedCode Text Currency used code
DateID Text Internal Date ID to link to the BIDates table
ForeignRate Decimal number Exchange rate over base currency
OrderDate Date/Time Date of the purchase order
ItemsNet Decimal Number Net amount of the line items
OrderNumber Text Purchase Order number
PostedCode Text Whether a Purchase Invoice has been posted for this PO. 
Status 0 = no invoice,
Status 1 = partly invoiced,
Status 2 = fully invoiced
PurchaseOrderID Whole Number Link to Purchase Order lines
Status Text Status of the PO. "On Order" indicates that the PO has been placed on the supplier and not all items have been received yet.
TakenBy Text "TakenBy" field from Sage. By default this is the Sage user who created the PO
SupplierID Text Link to BISuppliers table

BIPurchaseOrders relationships

The BIPurchaseOrders table in the OData feed is related to the BISuppliers table via the [SupplierID} and the BIPurchaseOrderLines table via the [PurchaseOrderID].  The Dates table shown in the diagram below is not part of the OData feed, but I would recommend that you create one and link it to the BIPurchaseOrders table.  

 BIPurchaseOrders relationships Sage 50 Accounts

To try our this OData feed for your Sage 50 Accounts data, why not create your own Suntico BI trial account?

Use free for 30 days 

 

Topics: Power BI Desktop, ODBC, OData, purchase orders