MS-QBO Invoice – Dynamics 365 BC Table Reference

Dynamics 365 BC Table Reference Tables Company Tables MMS-QBO Invoice

Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/7822

Table Type: Company

Table Name: MS-QBO Invoice
Database Table Name: CRONUS UK Ltd_$MS-QBO Invoice$437dbf0e-84ff-417a-965d-ed2bb9650972

Table Number: 7822

Due to how Dynamics BC tables and columns are named, square brackets need to be wrapped around the table name and column names. Below are example queries showing how MS-QBO Invoice can be queried.

Show/hide an example select of all columns

SELECT

   *

FROM

   [CRONUS UK Ltd_$MS-QBO Invoice$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns by name

SELECT

   [Id]
   ,[SyncToken]
   ,[MetaData]
   ,[MetaData CreateTime]
   ,[MetaData LastUpdatedTime]
   ,[CustomField]
   ,[DocNumber]
   ,[TxnDate]
   ,[DepartmentRef]
   ,[CurrencyRef]
   ,[ExchangeRate]
   ,[PrivateNote]
   ,[LinkedTxn]
   ,[Line]
   ,[TxnTaxDetail]
   ,[CustomerRef]
   ,[CustomerMemo]
   ,[BillAddr]
   ,[ShipAddr]
   ,[ClassRef]
   ,[SalesTermRef]
   ,[DueDate]
   ,[GlobalTaxCalculation]
   ,[ShipMethodRef]
   ,[ShipDate]
   ,[TrackingNum]
   ,[TotalAmt]
   ,[HomeTotalAmt]
   ,[ApplyTaxAfterDiscount]
   ,[PrintStatus]
   ,[EmailStatus]
   ,[BillEmail]
   ,[DeliveryInfo]
   ,[Balance]
   ,[HomeBalance]
   ,[TxnSource]
   ,[Deposit]
   ,[$systemId]
   ,[$systemCreatedAt]
   ,[$systemCreatedBy]
   ,[$systemModifiedAt]
   ,[$systemModifiedBy]

FROM

   [CRONUS UK Ltd_$MS-QBO Invoice$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns including those from all joined tables

SELECT
   *
FROM
   [CRONUS UK Ltd_$MS-QBO Invoice$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [MS-QBO Invoice]
LEFT JOIN
   [User] AS [User]
      ON
         [MS-QBO Invoice].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
   [User] AS [User]
      ON
         [MS-QBO Invoice].[SystemModifiedBy] = [User].[User Security ID]

Show/hide columns in MS-QBO Invoice table

Field No.	Field Name	Field Caption	Class	Type	Length	SQL Column Name	SQL DataType
1	Id	Id	Normal	Text	250	Id	nvarchar(500)
2	SyncToken	SyncToken	Normal	Text	250	SyncToken	nvarchar(500)
3	MetaData	MetaData	Normal	BLOB	8	MetaData	image
4	MetaData CreateTime	MetaData CreateTime	Normal	DateTime	8	MetaData CreateTime	datetime
5	MetaData LastUpdatedTime	MetaData LastUpdatedTime	Normal	DateTime	8	MetaData LastUpdatedTime	datetime
6	CustomField	CustomField	Normal	BLOB	8	CustomField	image
7	DocNumber	DocNumber	Normal	Text	21	DocNumber	nvarchar(42)
8	TxnDate	TxnDate	Normal	Date	4	TxnDate	datetime
9	DepartmentRef	DepartmentRef	Normal	BLOB	8	DepartmentRef	image
10	CurrencyRef	CurrencyRef	Normal	BLOB	8	CurrencyRef	image
11	ExchangeRate	ExchangeRate	Normal	Decimal	12	ExchangeRate	decimal(38,38)
12	PrivateNote	PrivateNote	Normal	BLOB	8	PrivateNote	image
13	LinkedTxn	LinkedTxn	Normal	BLOB	8	LinkedTxn	image
14	Line	Line	Normal	BLOB	8	Line	image
15	TxnTaxDetail	TxnTaxDetail	Normal	BLOB	8	TxnTaxDetail	image
16	CustomerRef	CustomerRef	Normal	BLOB	8	CustomerRef	image
17	CustomerMemo	CustomerMemo	Normal	BLOB	8	CustomerMemo	image
18	BillAddr	BillAddr	Normal	BLOB	8	BillAddr	image
19	ShipAddr	ShipAddr	Normal	BLOB	8	ShipAddr	image
20	ClassRef	ClassRef	Normal	BLOB	8	ClassRef	image
21	SalesTermRef	SalesTermRef	Normal	BLOB	8	SalesTermRef	image
22	DueDate	DueDate	Normal	Date	4	DueDate	datetime
23	GlobalTaxCalculation	GlobalTaxCalculation	Normal	BLOB	8	GlobalTaxCalculation	image
24	ShipMethodRef	ShipMethodRef	Normal	BLOB	8	ShipMethodRef	image
25	ShipDate	ShipDate	Normal	Date	4	ShipDate	datetime
26	TrackingNum	TrackingNum	Normal	Text	250	TrackingNum	nvarchar(500)
27	TotalAmt	TotalAmt	Normal	Decimal	12	TotalAmt	decimal(38,38)
28	HomeTotalAmt	HomeTotalAmt	Normal	Decimal	12	HomeTotalAmt	decimal(38,38)
29	ApplyTaxAfterDiscount	ApplyTaxAfterDiscount	Normal	Boolean	4	ApplyTaxAfterDiscount	tinyint
30	PrintStatus	PrintStatus	Normal	Text	13	PrintStatus	nvarchar(26)
31	EmailStatus	EmailStatus	Normal	Text	10	EmailStatus	nvarchar(20)
32	BillEmail	BillEmail	Normal	BLOB	8	BillEmail	image
33	DeliveryInfo	DeliveryInfo	Normal	Text	250	DeliveryInfo	nvarchar(500)
34	Balance	Balance	Normal	Decimal	12	Balance	decimal(38,38)
35	HomeBalance	HomeBalance	Normal	Decimal	12	HomeBalance	decimal(38,38)
36	TxnSource	TxnSource	Normal	Text	250	TxnSource	nvarchar(500)
37	Deposit	Deposit	Normal	Text	30	Deposit	nvarchar(60)
2000000000	$systemId	System ID	Normal	GUID	16	$systemId	uniqueidentifier
2000000001	SystemCreatedAt	Created At	Normal	DateTime	8	$systemCreatedAt	datetime
2000000002	SystemCreatedBy	Created By	Normal	GUID	16	$systemCreatedBy	uniqueidentifier
	Key to join to the User table. Show/hide example query SELECT [MS-QBO Invoice].[$systemCreatedBy] ,[User].[User Security ID] FROM [CRONUS UK Ltd_$MS-QBO Invoice$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [MS-QBO Invoice] LEFT JOIN [User] AS [User] ON [MS-QBO Invoice].[$systemCreatedBy] = [User].[User Security ID]
2000000003	SystemModifiedAt	Modified At	Normal	DateTime	8	$systemModifiedAt	datetime
2000000004	SystemModifiedBy	Modified By	Normal	GUID	16	$systemModifiedBy	uniqueidentifier
	Key to join to the User table. Show/hide example query SELECT [MS-QBO Invoice].[$systemModifiedBy] ,[User].[User Security ID] FROM [CRONUS UK Ltd_$MS-QBO Invoice$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [MS-QBO Invoice] LEFT JOIN [User] AS [User] ON [MS-QBO Invoice].[$systemModifiedBy] = [User].[User Security ID]