MS-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 NameField CaptionClassTypeLengthSQL Column NameSQL DataType
1Id Primary Key SearchIdNormalText250Idnvarchar(500)
2SyncToken SearchSyncTokenNormalText250SyncTokennvarchar(500)
3MetaData SearchMetaDataNormalBLOB8MetaDataimage
4MetaData CreateTime SearchMetaData CreateTimeNormalDateTime8MetaData CreateTimedatetime
5MetaData LastUpdatedTime SearchMetaData LastUpdatedTimeNormalDateTime8MetaData LastUpdatedTimedatetime
6Name SearchNameNormalText100Namenvarchar(200)
7Sku SearchSkuNormalText100Skunvarchar(200)
8Description SearchDescriptionNormalBLOB8Descriptionimage
9Active SearchActiveNormalBoolean4Activetinyint
10SubItem SearchSubItemNormalBoolean4SubItemtinyint
11ParentRef SearchParentRefNormalBLOB8ParentRefimage
12Level SearchLevelNormalInteger4Levelint
13FullyQualifiedName SearchFullyQualifiedNameNormalText250FullyQualifiedNamenvarchar(500)
14Taxable SearchTaxableNormalBoolean4Taxabletinyint
15SalesTaxIncluded SearchSalesTaxIncludedNormalBoolean4SalesTaxIncludedtinyint
16UnitPrice SearchUnitPriceNormalDecimal12UnitPricedecimal(38,38)
17Type SearchTypeNormalText250Typenvarchar(500)
18IncomeAccountRef SearchIncomeAccountRefNormalBLOB8IncomeAccountRefimage
19ExpenseAccountRef SearchExpenseAccountRefNormalBLOB8ExpenseAccountRefimage
20PurchaseDesc SearchPurchaseDescNormalText30PurchaseDescnvarchar(60)
21PurchaseTaxIncluded SearchPurchaseTaxIncludedNormalBoolean4PurchaseTaxIncludedtinyint
22PurchaseCost SearchPurchaseCostNormalDecimal12PurchaseCostdecimal(38,38)
23AssetAccountRef SearchAssetAccountRefNormalBLOB8AssetAccountRefimage
24TrackQtyOnHand SearchTrackQtyOnHandNormalBoolean4TrackQtyOnHandtinyint
25InvStartDate SearchInvStartDateNormalDate4InvStartDatedatetime
26QtyOnHand SearchQtyOnHandNormalDecimal12QtyOnHanddecimal(38,38)
27SalesTaxCodeRef SearchSalesTaxCodeRefNormalBLOB8SalesTaxCodeRefimage
28PurchaseTaxCodeRef SearchPurchaseTaxCodeRefNormalBLOB8PurchaseTaxCodeRefimage
29All SearchAllNormalBLOB8Allimage
2000000000$systemId SearchSystem IDNormalGUID16$systemIduniqueidentifier
2000000001SystemCreatedAt SearchCreated AtNormalDateTime8$systemCreatedAtdatetime
2000000002SystemCreatedBy SearchCreated ByNormalGUID16$systemCreatedByuniqueidentifier
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]
2000000003SystemModifiedAt SearchModified AtNormalDateTime8$systemModifiedAtdatetime
2000000004SystemModifiedBy SearchModified ByNormalGUID16$systemModifiedByuniqueidentifier
Key to join to the User table.

Show/hide example query

SELECT
       [MS-QBO Item].[$systemModifiedBy]
       ,[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].[$systemModifiedBy] = [User].[User Security ID]

Leave a Reply

Your email address will not be published. Required fields are marked *