MS-QBO Item – Dynamics 365 BC Table Reference

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

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

Table Type: Company

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

Table Number: 7821

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 Item can be queried.

Show/hide an example select of all columns

SELECT

   *

FROM

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

Show/hide an example select of all columns by name

SELECT

   [Id]
   ,[SyncToken]
   ,[MetaData]
   ,[MetaData CreateTime]
   ,[MetaData LastUpdatedTime]
   ,[Name]
   ,[Sku]
   ,[Description]
   ,[Active]
   ,[SubItem]
   ,[ParentRef]
   ,[Level]
   ,[FullyQualifiedName]
   ,[Taxable]
   ,[SalesTaxIncluded]
   ,[UnitPrice]
   ,[Type]
   ,[IncomeAccountRef]
   ,[ExpenseAccountRef]
   ,[PurchaseDesc]
   ,[PurchaseTaxIncluded]
   ,[PurchaseCost]
   ,[AssetAccountRef]
   ,[TrackQtyOnHand]
   ,[InvStartDate]
   ,[QtyOnHand]
   ,[SalesTaxCodeRef]
   ,[PurchaseTaxCodeRef]
   ,[All]
   ,[$systemId]
   ,[$systemCreatedAt]
   ,[$systemCreatedBy]
   ,[$systemModifiedAt]
   ,[$systemModifiedBy]

FROM

   [CRONUS UK Ltd_$MS-QBO Item$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 Item$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [MS-QBO Item]
LEFT JOIN
   [User] AS [User]
      ON
         [MS-QBO Item].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
   [User] AS [User]
      ON
         [MS-QBO Item].[SystemModifiedBy] = [User].[User Security ID]

Show/hide columns in MS-QBO Item 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	Name	Name	Normal	Text	100	Name	nvarchar(200)
7	Sku	Sku	Normal	Text	100	Sku	nvarchar(200)
8	Description	Description	Normal	BLOB	8	Description	image
9	Active	Active	Normal	Boolean	4	Active	tinyint
10	SubItem	SubItem	Normal	Boolean	4	SubItem	tinyint
11	ParentRef	ParentRef	Normal	BLOB	8	ParentRef	image
12	Level	Level	Normal	Integer	4	Level	int
13	FullyQualifiedName	FullyQualifiedName	Normal	Text	250	FullyQualifiedName	nvarchar(500)
14	Taxable	Taxable	Normal	Boolean	4	Taxable	tinyint
15	SalesTaxIncluded	SalesTaxIncluded	Normal	Boolean	4	SalesTaxIncluded	tinyint
16	UnitPrice	UnitPrice	Normal	Decimal	12	UnitPrice	decimal(38,38)
17	Type	Type	Normal	Text	250	Type	nvarchar(500)
18	IncomeAccountRef	IncomeAccountRef	Normal	BLOB	8	IncomeAccountRef	image
19	ExpenseAccountRef	ExpenseAccountRef	Normal	BLOB	8	ExpenseAccountRef	image
20	PurchaseDesc	PurchaseDesc	Normal	Text	30	PurchaseDesc	nvarchar(60)
21	PurchaseTaxIncluded	PurchaseTaxIncluded	Normal	Boolean	4	PurchaseTaxIncluded	tinyint
22	PurchaseCost	PurchaseCost	Normal	Decimal	12	PurchaseCost	decimal(38,38)
23	AssetAccountRef	AssetAccountRef	Normal	BLOB	8	AssetAccountRef	image
24	TrackQtyOnHand	TrackQtyOnHand	Normal	Boolean	4	TrackQtyOnHand	tinyint
25	InvStartDate	InvStartDate	Normal	Date	4	InvStartDate	datetime
26	QtyOnHand	QtyOnHand	Normal	Decimal	12	QtyOnHand	decimal(38,38)
27	SalesTaxCodeRef	SalesTaxCodeRef	Normal	BLOB	8	SalesTaxCodeRef	image
28	PurchaseTaxCodeRef	PurchaseTaxCodeRef	Normal	BLOB	8	PurchaseTaxCodeRef	image
29	All	All	Normal	BLOB	8	All	image
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 Item].[$systemCreatedBy] ,[User].[User Security ID] FROM [CRONUS UK Ltd_$MS-QBO Item$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [MS-QBO Item] LEFT JOIN [User] AS [User] ON [MS-QBO Item].[$systemCreatedBy] = [User].[User Security ID]
2000000003	SystemModifiedAt	Modified At	Normal	DateTime	8	$systemModifiedAt	datetime
2000000004	SystemModifiedBy	Modified By	Normal	GUID	16	$systemModifiedBy	uniqueidentifier

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

Show/hide an example select of all columns

Show/hide an example select of all columns by name

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

Show/hide columns in MS-QBO Item table

Show/hide example query

Leave a Reply Cancel reply