Opportunity

Dynamics 365 BC Table ReferenceTablesCompany TablesOOpportunity

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 NameField CaptionClassTypeLengthSQL Column NameSQL DataType
1No. SearchNo.NormalCode20No_nvarchar(40)
2Description Primary Key SearchDescriptionNormalText100Descriptionnvarchar(200)
3Salesperson Code SearchSalesperson CodeNormalCode20Salesperson Codenvarchar(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]
4Campaign No. SearchCampaign No.NormalCode20Campaign 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_]
5Contact No. SearchContact No.NormalCode20Contact 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_]
6Contact Company No. SearchContact Company No.NormalCode20Contact Company No_nvarchar(40)
7Sales Cycle Code SearchSales Cycle CodeNormalCode10Sales Cycle Codenvarchar(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]
8Sales Document No. SearchSales Document No.NormalCode20Sales Document No_nvarchar(40)
9Creation Date SearchCreation DateNormalDate4Creation Datedatetime
10Status SearchStatusNormalOption4Statusint
Available options are:
  • “Not Started”
  • “In Progress”
  • “Won”
  • “Lost”
11Priority SearchPriorityNormalOption4Priorityint
Available options are:
  • “Low”
  • “Normal”
  • “High”
12Closed SearchClosedNormalBoolean4Closedtinyint
13Date Closed SearchDate ClosedNormalDate4Date Closeddatetime
15No. Series SearchNo. SeriesNormalCode20No_ Seriesnvarchar(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]
16Comment SearchCommentFlowFieldBoolean4
17Current Sales Cycle Stage SearchCurrent Sales Cycle StageFlowFieldInteger4
18Estimated Value (LCY) SearchEstimated Value ($)FlowFieldDecimal12
19Probability % SearchProbability %FlowFieldDecimal12
20Calcd. Current Value (LCY) SearchCalcd. Current Value ($)FlowFieldDecimal12
21Chances of Success % SearchChances of Success %FlowFieldDecimal12
22Completed % SearchCompleted %FlowFieldDecimal12
23Contact Name SearchContact NameFlowFieldText100
24Contact Company Name SearchContact Company NameFlowFieldText100
25Salesperson Name SearchSalesperson NameFlowFieldText50
26Campaign Description SearchCampaign DescriptionFlowFieldText100
27Segment No. SearchSegment No.NormalCode20Segment 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_]
28Estimated Closing Date SearchEstimated Closing DateFlowFieldDate4
29Sales Document Type SearchSales Document TypeNormalOption4Sales Document Typeint
Available options are:
  • ” “
  • “Quote”
  • “Order”
  • “Posted Invoice”
30No. of Interactions SearchNo. of InteractionsFlowFieldInteger4
720Coupled to CRM SearchCoupled to Dynamics 365 SalesNormalBoolean4Coupled to CRMtinyint
721Coupled to Dataverse SearchCoupled to Dynamics 365 SalesFlowFieldBoolean4
9501Wizard Step SearchWizard StepNormalOption4Wizard Stepint
Available options are:
  • ” “
  • “1”
  • “2”
  • “3”
  • “4”
  • “5”
  • “6”
9502Activate First Stage SearchActivate First StageNormalBoolean4Activate First Stagetinyint
9503Segment Description SearchSegment DescriptionNormalText100Segment Descriptionnvarchar(200)
9504Wizard Estimated Value (LCY) SearchWizard Estimated Value ($)NormalDecimal12Wizard Estimated Value (LCY)decimal(38,38)
9505Wizard Chances of Success % SearchWizard Chances of Success %NormalDecimal12Wizard Chances of Success _decimal(38,38)
9506Wizard Estimated Closing Date SearchWizard Estimated Closing DateNormalDate4Wizard Estimated Closing Datedatetime
9507Wizard Contact Name SearchWizard Contact NameNormalText100Wizard Contact Namenvarchar(200)
9508Wizard Campaign Description SearchWizard Campaign DescriptionNormalText100Wizard Campaign Descriptionnvarchar(200)
2000000000$systemId SearchSystem IDNormalGUID16$systemIduniqueidentifier
2000000001SystemCreatedAt SearchCreated AtNormalDateTime8$systemCreatedAtdatetime
2000000002SystemCreatedBy SearchCreated ByNormalGUID16$systemCreatedByuniqueidentifier
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]
2000000003SystemModifiedAt SearchModified AtNormalDateTime8$systemModifiedAtdatetime
2000000004SystemModifiedBy SearchModified ByNormalGUID16$systemModifiedByuniqueidentifier

Show/hide tables related from Opportunity table

Salesperson/PurchaserSalesperson/Purchaser
Dynamics 365 BC Table ReferenceTablesCompany TablesOOpportunity

Leave a Reply

Your email address will not be published. Required fields are marked *