Opportunity – Dynamics 365 BC Table Reference

Dynamics 365 BC Table Reference Tables Company Tables OOpportunity

Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/5092

Table Type: Company

Table Name: Opportunity
Database Table Name: CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972

Table Number: 5092

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 Opportunity can be queried.

Show/hide an example select of all columns

SELECT

   *

FROM

   [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns by name

SELECT

   [No_]
   ,[Description]
   ,[Salesperson Code]
   ,[Campaign No_]
   ,[Contact No_]
   ,[Contact Company No_]
   ,[Sales Cycle Code]
   ,[Sales Document No_]
   ,[Creation Date]
   ,[Status]
   ,[Priority]
   ,[Closed]
   ,[Date Closed]
   ,[No_ Series]
   ,[Segment No_]
   ,[Sales Document Type]
   ,[Coupled to CRM]
   ,[Wizard Step]
   ,[Activate First Stage]
   ,[Segment Description]
   ,[Wizard Estimated Value (LCY)]
   ,[Wizard Chances of Success _]
   ,[Wizard Estimated Closing Date]
   ,[Wizard Contact Name]
   ,[Wizard Campaign Description]
   ,[$systemId]
   ,[$systemCreatedAt]
   ,[$systemCreatedBy]
   ,[$systemModifiedAt]
   ,[$systemModifiedBy]

FROM

   [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns including those from all joined tables

SELECT
   *
FROM
   [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Opportunity]
LEFT JOIN
   [CRONUS UK Ltd_$Salesperson_Purchaser$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Salesperson/Purchaser]
      ON
         [Opportunity].[Salesperson Code] = [Salesperson/Purchaser].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Sales Cycle$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Sales Cycle]
      ON
         [Opportunity].[Sales Cycle Code] = [Sales Cycle].[Code]
LEFT JOIN
   [User] AS [User]
      ON
         [Opportunity].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
   [User] AS [User]
      ON
         [Opportunity].[SystemModifiedBy] = [User].[User Security ID]

Show/hide columns in Opportunity table

Field No.	Field Name	Field Caption	Class	Type	Length	SQL Column Name	SQL DataType
1	No.	No.	Normal	Code	20	No_	nvarchar(40)
2	Description	Description	Normal	Text	100	Description	nvarchar(200)
3	Salesperson Code	Salesperson Code	Normal	Code	20	Salesperson Code	nvarchar(40)
	Key to join to the Salesperson/Purchaser table. Show/hide example query SELECT [Opportunity].[Salesperson Code] ,[Salesperson/Purchaser].[Code] FROM [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Opportunity] LEFT JOIN [CRONUS UK Ltd_$Salesperson_Purchaser$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Salesperson/Purchaser] ON [Opportunity].[Salesperson Code] = [Salesperson/Purchaser].[Code]
4	Campaign No.	Campaign No.	Normal	Code	20	Campaign No_	nvarchar(40)
	Key to join to the Campaign table. Show/hide example query SELECT [Opportunity].[Campaign No_] ,[Campaign].[No_] FROM [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Opportunity] LEFT JOIN [CRONUS UK Ltd_$Campaign$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Campaign] ON [Opportunity].[Campaign No_] = [Campaign].[No_]
5	Contact No.	Contact No.	Normal	Code	20	Contact No_	nvarchar(40)
	Key to join to the Contact table. Show/hide example query SELECT [Opportunity].[Contact No_] ,[Contact].[No_] FROM [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Opportunity] LEFT JOIN [CRONUS UK Ltd_$Contact$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Contact] ON [Opportunity].[Contact No_] = [Contact].[No_]
6	Contact Company No.	Contact Company No.	Normal	Code	20	Contact Company No_	nvarchar(40)
7	Sales Cycle Code	Sales Cycle Code	Normal	Code	10	Sales Cycle Code	nvarchar(20)
	Key to join to the Sales Cycle table. Show/hide example query SELECT [Opportunity].[Sales Cycle Code] ,[Sales Cycle].[Code] FROM [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Opportunity] LEFT JOIN [CRONUS UK Ltd_$Sales Cycle$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Sales Cycle] ON [Opportunity].[Sales Cycle Code] = [Sales Cycle].[Code]
8	Sales Document No.	Sales Document No.	Normal	Code	20	Sales Document No_	nvarchar(40)
9	Creation Date	Creation Date	Normal	Date	4	Creation Date	datetime
10	Status	Status	Normal	Option	4	Status	int
	Available options are: “Not Started” “In Progress” “Won” “Lost”
11	Priority	Priority	Normal	Option	4	Priority	int
	Available options are: “Low” “Normal” “High”
12	Closed	Closed	Normal	Boolean	4	Closed	tinyint
13	Date Closed	Date Closed	Normal	Date	4	Date Closed	datetime
15	No. Series	No. Series	Normal	Code	20	No_ Series	nvarchar(40)
	Key to join to the No. Series table. Show/hide example query SELECT [Opportunity].[No_ Series] ,[No. Series].[Code] FROM [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Opportunity] LEFT JOIN [CRONUS UK Ltd_$No_ Series$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [No. Series] ON [Opportunity].[No_ Series] = [No. Series].[Code]
16	Comment	Comment	FlowField	Boolean	4
17	Current Sales Cycle Stage	Current Sales Cycle Stage	FlowField	Integer	4
18	Estimated Value (LCY)	Estimated Value ($)	FlowField	Decimal	12
19	Probability %	Probability %	FlowField	Decimal	12
20	Calcd. Current Value (LCY)	Calcd. Current Value ($)	FlowField	Decimal	12
21	Chances of Success %	Chances of Success %	FlowField	Decimal	12
22	Completed %	Completed %	FlowField	Decimal	12
23	Contact Name	Contact Name	FlowField	Text	100
24	Contact Company Name	Contact Company Name	FlowField	Text	100
25	Salesperson Name	Salesperson Name	FlowField	Text	50
26	Campaign Description	Campaign Description	FlowField	Text	100
27	Segment No.	Segment No.	Normal	Code	20	Segment No_	nvarchar(40)
	Key to join to the Segment Header table. Show/hide example query SELECT [Opportunity].[Segment No_] ,[Segment Header].[No_] FROM [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Opportunity] LEFT JOIN [CRONUS UK Ltd_$Segment Header$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Segment Header] ON [Opportunity].[Segment No_] = [Segment Header].[No_]
28	Estimated Closing Date	Estimated Closing Date	FlowField	Date	4
29	Sales Document Type	Sales Document Type	Normal	Option	4	Sales Document Type	int
	Available options are: ” “ “Quote” “Order” “Posted Invoice”
30	No. of Interactions	No. of Interactions	FlowField	Integer	4
720	Coupled to CRM	Coupled to Dynamics 365 Sales	Normal	Boolean	4	Coupled to CRM	tinyint
721	Coupled to Dataverse	Coupled to Dynamics 365 Sales	FlowField	Boolean	4
9501	Wizard Step	Wizard Step	Normal	Option	4	Wizard Step	int
	Available options are: ” “ “1” “2” “3” “4” “5” “6”
9502	Activate First Stage	Activate First Stage	Normal	Boolean	4	Activate First Stage	tinyint
9503	Segment Description	Segment Description	Normal	Text	100	Segment Description	nvarchar(200)
9504	Wizard Estimated Value (LCY)	Wizard Estimated Value ($)	Normal	Decimal	12	Wizard Estimated Value (LCY)	decimal(38,38)
9505	Wizard Chances of Success %	Wizard Chances of Success %	Normal	Decimal	12	Wizard Chances of Success _	decimal(38,38)
9506	Wizard Estimated Closing Date	Wizard Estimated Closing Date	Normal	Date	4	Wizard Estimated Closing Date	datetime
9507	Wizard Contact Name	Wizard Contact Name	Normal	Text	100	Wizard Contact Name	nvarchar(200)
9508	Wizard Campaign Description	Wizard Campaign Description	Normal	Text	100	Wizard Campaign Description	nvarchar(200)
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 query SELECT [Opportunity].[$systemCreatedBy] ,[User].[User Security ID] FROM [CRONUS UK Ltd_$Opportunity$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Opportunity] LEFT JOIN [User] AS [User] ON [Opportunity].[$systemCreatedBy] = [User].[User Security ID]
2000000003	SystemModifiedAt	Modified At	Normal	DateTime	8	$systemModifiedAt	datetime
2000000004	SystemModifiedBy	Modified By	Normal	GUID	16	$systemModifiedBy	uniqueidentifier

Show/hide tables related from Opportunity table

Dynamics 365 BC Table Reference Tables Company Tables OOpportunity

Leave a Reply Cancel reply