Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/30112
Table Type: Company
Table Name: Shpfy Shop Inventory
Database Table Name: CRONUS UK Ltd_$Shpfy Shop Inventory$ec255f57-31d0-4ca2-b751-f2fa7c745abb
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 Shpfy Shop Inventory can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[CRONUS UK Ltd_$Shpfy Shop Inventory$ec255f57-31d0-4ca2-b751-f2fa7c745abb]Show/hide an example select of all columns by name
SELECT
[Shop Code]
,[Product Id]
,[Variant Id]
,[Location Id]
,[Inventory Item Id]
,[Shopify Stock]
,[Stock]
,[Last Synced On]
,[Last Calculated On]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$Shpfy Shop Inventory$ec255f57-31d0-4ca2-b751-f2fa7c745abb]Show/hide an example select of all columns including those from all joined tables
SELECT
*
FROM
[CRONUS UK Ltd_$Shpfy Shop Inventory$ec255f57-31d0-4ca2-b751-f2fa7c745abb] AS [Shpfy Shop Inventory]
LEFT JOIN
[CRONUS UK Ltd_$Shpfy Shop$ec255f57-31d0-4ca2-b751-f2fa7c745abb] AS [Shpfy Shop]
ON
[Shpfy Shop Inventory].[Shop Code] = [Shpfy Shop].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Shpfy Shop Inventory].[Location Id] = [Location].[$systemId]
LEFT JOIN
[User] AS [User]
ON
[Shpfy Shop Inventory].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Shpfy Shop Inventory].[SystemModifiedBy] = [User].[User Security ID]Show/hide columns in Shpfy Shop Inventory table
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|---|---|---|---|---|---|---|
| 1 | Shop Code   | Shop Code | Normal | Code | 20 | Shop Code | nvarchar(40) |
Key to join to the Shpfy Shop table.Show/hide example querySELECT
[Shpfy Shop Inventory].[Shop Code]
,[Shpfy Shop].[Code]
FROM
[CRONUS UK Ltd_$Shpfy Shop Inventory$ec255f57-31d0-4ca2-b751-f2fa7c745abb] AS [Shpfy Shop Inventory]
LEFT JOIN
[CRONUS UK Ltd_$Shpfy Shop$ec255f57-31d0-4ca2-b751-f2fa7c745abb] AS [Shpfy Shop]
ON
[Shpfy Shop Inventory].[Shop Code] = [Shpfy Shop].[Code] | |||||||
| 2 | Product Id | Product Id | Normal | BigInteger | 8 | Product Id | bigint |
| 3 | Variant Id | Variant Id | Normal | BigInteger | 8 | Variant Id | bigint |
| 4 | Location Id | Location Id | Normal | BigInteger | 8 | Location Id | bigint |
Key to join to the Location table.Show/hide example querySELECT
[Shpfy Shop Inventory].[Location Id]
,[Location].[$systemId]
FROM
[CRONUS UK Ltd_$Shpfy Shop Inventory$ec255f57-31d0-4ca2-b751-f2fa7c745abb] AS [Shpfy Shop Inventory]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Shpfy Shop Inventory].[Location Id] = [Location].[$systemId] | |||||||
| 5 | Inventory Item Id | Inventory Item Id | Normal | BigInteger | 8 | Inventory Item Id | bigint |
| 6 | Shopify Stock | Shopify Stock | Normal | Integer | 4 | Shopify Stock | int |
| 7 | Stock | Last Calculated Stock | Normal | Integer | 4 | Stock | int |
| 8 | Last Synced On | Last Synced On | Normal | DateTime | 8 | Last Synced On | datetime |
| 9 | Last Calculated On | Last Calculated On | Normal | DateTime | 8 | Last Calculated On | datetime |
| 10 | Location Name | Location Name | FlowField | Text | 250 | ||
| 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
[Shpfy Shop Inventory].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Shpfy Shop Inventory$ec255f57-31d0-4ca2-b751-f2fa7c745abb] AS [Shpfy Shop Inventory]
LEFT JOIN
[User] AS [User]
ON
[Shpfy Shop Inventory].[$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
[Shpfy Shop Inventory].[$systemModifiedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Shpfy Shop Inventory$ec255f57-31d0-4ca2-b751-f2fa7c745abb] AS [Shpfy Shop Inventory]
LEFT JOIN
[User] AS [User]
ON
[Shpfy Shop Inventory].[$systemModifiedBy] = [User].[User Security ID] | |||||||