Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/40905
Table Type: Company
Table Name: Hist. Payables Document
Database Table Name:
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 Hist. Payables Document can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[]
Show/hide an example select of all columns by name
SELECT
FROM
[]
Show/hide an example select of all columns including those from all joined tables
SELECT
*
FROM
[] AS [Hist. Payables Document]
LEFT JOIN
[CRONUS UK Ltd_$Sales Header$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Sales Header]
ON
[Hist. Payables Document].[Document Type] = [Sales Header].[Document Type]
AND
[Hist. Payables Document].[Document No_] = [Sales Header].[No_]
LEFT JOIN
[CRONUS UK Ltd_$Sales Header Archive$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Sales Header Archive]
ON
[Hist. Payables Document].[Document Type] = [Sales Header Archive].[Document Type]
AND
[Hist. Payables Document].[Document No_] = [Sales Header Archive].[No_]
LEFT JOIN
[CRONUS UK Ltd_$Currency$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Currency]
ON
[Hist. Payables Document].[Currency Code] = [Currency].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Payment Terms$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Payment Terms]
ON
[Hist. Payables Document].[Payment Terms Id] = [Payment Terms].[$systemId]
LEFT JOIN
[User] AS [User]
ON
[Hist. Payables Document].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Hist. Payables Document].[SystemModifiedBy] = [User].[User Security ID]
Show/hide columns in Hist. Payables Document table
Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
---|---|---|---|---|---|---|---|
1 | Primary Key | Primary Key | Normal | Integer | 4 | ||
2 | Voucher No. | Voucher No. | Normal | Code | 35 | ||
3 | Vendor No. | Vendor No. | Normal | Code | 35 | ||
Key to join to the Vendor table.Show/hide example querySELECT [Hist. Payables Document].[] ,[Vendor].[No_] FROM [] AS [Hist. Payables Document] LEFT JOIN [CRONUS UK Ltd_$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Vendor] ON [Hist. Payables Document].[] = [Vendor].[No_] | |||||||
4 | Vendor Name | Vendor Name | Normal | Text | 100 | ||
5 | Document Type | Document Type | Normal | Option | 4 | ||
6 | Document No. | Document No. | Normal | Code | 35 | ||
7 | Document Date | Document Date | Normal | Date | 4 | ||
8 | Document Amount | Document Amount | Normal | Decimal | 12 | ||
9 | Currency Code | Currency | Normal | Code | 10 | ||
Key to join to the Currency table.Show/hide example querySELECT [Hist. Payables Document].[] ,[Currency].[Code] FROM [] AS [Hist. Payables Document] LEFT JOIN [CRONUS UK Ltd_$Currency$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Currency] ON [Hist. Payables Document].[] = [Currency].[Code] | |||||||
10 | Current Trx. Amount | Current Trx. Amount | Normal | Decimal | 12 | ||
11 | Disc. Taken Amount | Disc. Taken Amount | Normal | Decimal | 12 | ||
12 | Batch No. | Batch No. | Normal | Code | 35 | ||
13 | Batch Source | Batch Source | Normal | Text | 50 | ||
14 | Due Date | Due Date | Normal | Date | 4 | ||
15 | Purchase No. | Purchase No. | Normal | Code | 35 | ||
16 | Audit Code | Audit Code | Normal | Code | 35 | ||
17 | Trx. Description | Trx. Description | Normal | Text | 50 | ||
18 | Post Date | Post Date | Normal | Date | 4 | ||
19 | User | User | Normal | Text | 50 | ||
20 | Misc. Amount | Misc. Amount | Normal | Decimal | 12 | ||
21 | Freight Amount | Freight Amount | Normal | Decimal | 12 | ||
22 | Tax Amount | Tax Amount | Normal | Decimal | 12 | ||
23 | Total Payments | Total Payments | Normal | Decimal | 12 | ||
24 | Voided | Voided | Normal | Boolean | 4 | ||
25 | Invoice Paid Off Date | Invoice Paid Off Date | Normal | Date | 4 | ||
26 | Ship Method | Ship Method | Normal | Text | 50 | ||
27 | 1099 Amount | 1099 Amount | Normal | Decimal | 12 | ||
28 | Write Off Amount | Write Off Amount | Normal | Decimal | 12 | ||
29 | Trade Discount Amount | Trade Discount Amount | Normal | Decimal | 12 | ||
30 | Payment Terms ID | Payment Terms ID | Normal | Text | 50 | ||
Key to join to the Payment Terms table.Show/hide example querySELECT [Hist. Payables Document].[] ,[Payment Terms].[$systemId] FROM [] AS [Hist. Payables Document] LEFT JOIN [CRONUS UK Ltd_$Payment Terms$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Payment Terms] ON [Hist. Payables Document].[] = [Payment Terms].[$systemId] | |||||||
31 | 1099 Type | 1099 Type | Normal | Text | 50 | ||
32 | 1099 Box Number | 1099 Box Number | Normal | Text | 50 | ||
33 | PO Number | PO Number | Normal | Code | 35 | ||
2000000000 | $systemId | System ID | Normal | GUID | 16 | ||
2000000001 | SystemCreatedAt | Created At | Normal | DateTime | 8 | ||
2000000002 | SystemCreatedBy | Created By | Normal | GUID | 16 | ||
Key to join to the User table.Show/hide example querySELECT [Hist. Payables Document].[] ,[User].[User Security ID] FROM [] AS [Hist. Payables Document] LEFT JOIN [User] AS [User] ON [Hist. Payables Document].[] = [User].[User Security ID] | |||||||
2000000003 | SystemModifiedAt | Modified At | Normal | DateTime | 8 | ||
2000000004 | SystemModifiedBy | Modified By | Normal | GUID | 16 |