Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/6504
Table Type: Company
Table Name: Serial No. Information
Database Table Name: CRONUS UK Ltd_$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972
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 Serial No. Information can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[CRONUS UK Ltd_$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972]Show/hide an example select of all columns by name
SELECT
[Item No_]
,[Variant Code]
,[Serial No_]
,[Description]
,[Blocked]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972]Show/hide an example select of all columns including those from all joined tables
SELECT
*
FROM
[CRONUS UK Ltd_$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Serial No. Information]
LEFT JOIN
[CRONUS UK Ltd_$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Variant]
ON
[Serial No. Information].[Variant Code] = [Item Variant].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Serial No. Information].[Location Filter] = [Location].[Code]
LEFT JOIN
[User] AS [User]
ON
[Serial No. Information].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Serial No. Information].[SystemModifiedBy] = [User].[User Security ID]Show/hide columns in Serial No. Information table
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|---|---|---|---|---|---|---|
| 1 | Item No.   | Item No. | Normal | Code | 20 | Item No_ | nvarchar(40) |
Key to join to the Item table.Show/hide example querySELECT
[Serial No. Information].[Item No_]
,[Item].[No_]
FROM
[CRONUS UK Ltd_$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Serial No. Information]
LEFT JOIN
[CRONUS UK Ltd_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Item]
ON
[Serial No. Information].[Item No_] = [Item].[No_] | |||||||
| 2 | Variant Code | Variant Code | Normal | Code | 10 | Variant Code | nvarchar(20) |
Key to join to the Item Variant table.Show/hide example querySELECT
[Serial No. Information].[Variant Code]
,[Item Variant].[Code]
FROM
[CRONUS UK Ltd_$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Serial No. Information]
LEFT JOIN
[CRONUS UK Ltd_$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Variant]
ON
[Serial No. Information].[Variant Code] = [Item Variant].[Code] | |||||||
| 3 | Serial No. | Serial No. | Normal | Code | 50 | Serial No_ | nvarchar(100) |
| 10 | Description | Description | Normal | Text | 100 | Description | nvarchar(200) |
| 13 | Blocked | Blocked | Normal | Boolean | 4 | Blocked | tinyint |
| 14 | Comment | Comment | FlowField | Boolean | 4 | ||
| 20 | Inventory | Quantity on Hand | FlowField | Decimal | 12 | ||
| 21 | Date Filter | Date Filter | FlowFilter | Date | 4 | ||
| 22 | Location Filter | Location Filter | FlowFilter | Code | 10 | ||
| 23 | Bin Filter | Bin Filter | FlowFilter | Code | 20 | ||
| 24 | Expired Inventory | Expired Inventory | FlowField | Decimal | 12 | ||
| 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 querySELECT
[Serial No. Information].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Serial No. Information]
LEFT JOIN
[User] AS [User]
ON
[Serial No. Information].[$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 querySELECT
[Serial No. Information].[$systemModifiedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Serial No. Information]
LEFT JOIN
[User] AS [User]
ON
[Serial No. Information].[$systemModifiedBy] = [User].[User Security ID] | |||||||