Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/5882
Table Type: Company
Table Name: Pstd. Phys. Invt. Record Line
Database Table Name: CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972
Table Number: 5882Due 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 Pstd. Phys. Invt. Record Line can be queried.
SELECT
*
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972]
SELECT
[Order No_]
,[Recording No_]
,[Line No_]
,[Order Line No_]
,[Recorded without Order]
,[Item No_]
,[Variant Code]
,[Location Code]
,[Bin Code]
,[Description]
,[Description 2]
,[Unit of Measure]
,[Unit of Measure Code]
,[Quantity]
,[Quantity (Base)]
,[Qty_ per Unit of Measure]
,[Recorded]
,[Use Item Tracking]
,[Shelf No_]
,[Date Recorded]
,[Time Recorded]
,[Person Recorded]
,[Serial No_]
,[Lot No_]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972]
SELECT
*
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Pstd. Phys. Invt. Record Line]
LEFT JOIN
[CRONUS UK Ltd_$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Variant]
ON
[Pstd. Phys. Invt. Record Line].[Variant Code] = [Item Variant].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Pstd. Phys. Invt. Record Line].[Location Code] = [Location].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Bin$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Bin]
ON
[Pstd. Phys. Invt. Record Line].[Bin Code] = [Bin].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Unit of Measure$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Unit of Measure]
ON
[Pstd. Phys. Invt. Record Line].[Unit of Measure] = [Unit of Measure].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Employee$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Employee]
ON
[Pstd. Phys. Invt. Record Line].[Person Recorded] = [Employee].[No_]
LEFT JOIN
[User] AS [User]
ON
[Pstd. Phys. Invt. Record Line].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Pstd. Phys. Invt. Record Line].[SystemModifiedBy] = [User].[User Security ID]
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|
| 1 | Order No.  | Order No. | Normal | Code | 20 | Order No_ | nvarchar(40) |
| 2 | Recording No.  | Recording No. | Normal | Integer | 4 | Recording No_ | int |
| 3 | Line No. | Line No. | Normal | Integer | 4 | Line No_ | int |
| 16 | Order Line No. | Order Line No. | Normal | Integer | 4 | Order Line No_ | int |
| 17 | Recorded without Order | Recorded without Order | Normal | Boolean | 4 | Recorded without Order | tinyint |
| 20 | Item No. | Item No. | Normal | Code | 20 | Item No_ | nvarchar(40) |
| Key to join to the Item table.SELECT
[Pstd. Phys. Invt. Record Line].[Item No_]
,[Item].[No_]
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Pstd. Phys. Invt. Record Line]
LEFT JOIN
[CRONUS UK Ltd_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Item]
ON
[Pstd. Phys. Invt. Record Line].[Item No_] = [Item].[No_] |
| 21 | Variant Code | Variant Code | Normal | Code | 10 | Variant Code | nvarchar(20) |
| Key to join to the Item Variant table.SELECT
[Pstd. Phys. Invt. Record Line].[Variant Code]
,[Item Variant].[Code]
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Pstd. Phys. Invt. Record Line]
LEFT JOIN
[CRONUS UK Ltd_$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Variant]
ON
[Pstd. Phys. Invt. Record Line].[Variant Code] = [Item Variant].[Code] |
| 22 | Location Code | Location Code | Normal | Code | 10 | Location Code | nvarchar(20) |
| Key to join to the Location table.SELECT
[Pstd. Phys. Invt. Record Line].[Location Code]
,[Location].[Code]
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Pstd. Phys. Invt. Record Line]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Pstd. Phys. Invt. Record Line].[Location Code] = [Location].[Code] |
| 23 | Bin Code | Bin Code | Normal | Code | 20 | Bin Code | nvarchar(40) |
| Key to join to the Bin table.SELECT
[Pstd. Phys. Invt. Record Line].[Bin Code]
,[Bin].[Code]
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Pstd. Phys. Invt. Record Line]
LEFT JOIN
[CRONUS UK Ltd_$Bin$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Bin]
ON
[Pstd. Phys. Invt. Record Line].[Bin Code] = [Bin].[Code] |
| 30 | Description | Description | Normal | Text | 100 | Description | nvarchar(200) |
| 31 | Description 2 | Description 2 | Normal | Text | 50 | Description 2 | nvarchar(100) |
| 32 | Unit of Measure | Unit of Measure | Normal | Text | 50 | Unit of Measure | nvarchar(100) |
| Key to join to the Unit of Measure table.SELECT
[Pstd. Phys. Invt. Record Line].[Unit of Measure]
,[Unit of Measure].[Code]
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Pstd. Phys. Invt. Record Line]
LEFT JOIN
[CRONUS UK Ltd_$Unit of Measure$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Unit of Measure]
ON
[Pstd. Phys. Invt. Record Line].[Unit of Measure] = [Unit of Measure].[Code] |
| 40 | Unit of Measure Code | Unit of Measure Code | Normal | Code | 10 | Unit of Measure Code | nvarchar(20) |
| 41 | Quantity | Quantity | Normal | Decimal | 12 | Quantity | decimal(38,38) |
| 42 | Quantity (Base) | Quantity (Base) | Normal | Decimal | 12 | Quantity (Base) | decimal(38,38) |
| 43 | Qty. per Unit of Measure | Qty. per Unit of Measure | Normal | Decimal | 12 | Qty_ per Unit of Measure | decimal(38,38) |
| 45 | Recorded | Recorded | Normal | Boolean | 4 | Recorded | tinyint |
| 53 | Use Item Tracking | Use Item Tracking | Normal | Boolean | 4 | Use Item Tracking | tinyint |
| 99 | Shelf No. | Shelf No. | Normal | Code | 10 | Shelf No_ | nvarchar(20) |
| 100 | Date Recorded | Date Recorded | Normal | Date | 4 | Date Recorded | datetime |
| 101 | Time Recorded | Time Recorded | Normal | Time | 4 | Time Recorded | datetime |
| 102 | Person Recorded | Person Recorded | Normal | Code | 20 | Person Recorded | nvarchar(40) |
| Key to join to the Employee table.SELECT
[Pstd. Phys. Invt. Record Line].[Person Recorded]
,[Employee].[No_]
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Pstd. Phys. Invt. Record Line]
LEFT JOIN
[CRONUS UK Ltd_$Employee$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Employee]
ON
[Pstd. Phys. Invt. Record Line].[Person Recorded] = [Employee].[No_] |
| 130 | Serial No. | Serial No. | Normal | Code | 50 | Serial No_ | nvarchar(100) |
| 131 | Lot No. | Lot No. | Normal | Code | 50 | Lot No_ | nvarchar(100) |
| 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.SELECT
[Pstd. Phys. Invt. Record Line].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Pstd_ Phys_ Invt_ Record Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Pstd. Phys. Invt. Record Line]
LEFT JOIN
[User] AS [User]
ON
[Pstd. Phys. Invt. Record Line].[$systemCreatedBy] = [User].[User Security ID] |
| 2000000003 | SystemModifiedAt | Modified At | Normal | DateTime | 8 | $systemModifiedAt | datetime |
| 2000000004 | SystemModifiedBy | Modified By | Normal | GUID | 16 | $systemModifiedBy | uniqueidentifier |
