I am struggling to add a PXDBScalar field to a CRQuote extension. In my customization, I have added a NonPersistedField to CR.CRQuote:
[PXDecimal]
[PXUIField(DisplayName="Lead Days (Rev)")]
[PXDBScalar(typeof(Search<
PBSA.UsrQTRevision.leadDays, Where<
PBSA.UsrQTRevision.opportunityID, Equal<CRQuote.opportunityID>, And<
PBSA.UsrQTRevision.quoteNbr, Equal<CRQuote.quoteNbr>, And<
PBSA.UsrQTRevision.revNbr, Equal<CRQuoteExt.usrRevNbr>
>
>
>
>))]
PBSA.UsrQTRevision is a custom table and DAC. I have another table and DAC like it for Sales Orders called UsrSORevision. For troubleshooting purposes I have added a similar PXDBScalar field to a SOOrder DAC extension:
[PXDecimal]
[PXUIField(DisplayName="Lead Days (Rev)")]
[PXDBScalar(typeof(Search<
PBSA.UsrSORevision.leadDays, Where<
PBSA.UsrSORevision.orderType, Equal<SOOrder.orderType>, And<
PBSA.UsrSORevision.orderNbr, Equal<SOOrder.orderNbr>, And<
PBSA.UsrSORevision.revNbr, Equal<SOOrderExt.usrRevNbr>
>
>
>
>))]
The SOOrder one works. The CRQuote one always gives me a null value in the DAC Browser Source Data preview, and when i examine the query in the trace, CRQuote is not trying to fetch the value:
SELECT
TOP (23) [CRQuote_CRQuote].[QuoteID] AS [CRQuote_QuoteID],
[CRQuote_CROpportunityRevision].[OpportunityID] AS [CRQuote_OpportunityID],
[CRQuote_CRQuote].[QuoteNbr] AS [CRQuote_QuoteNbr],
...
NULL AS [CRQuote_UsrLeadDaysRev]
FROM
(
[CRQuote] [CRQuote_CRQuote]
INNER JOIN [CROpportunityRevision] [CRQuote_CROpportunityRevision] ON ([CRQuote_CROpportunityRevision].[CompanyID] = 2)
AND (
[CRQuote_CROpportunityRevision].[BranchID] IS NULL
OR (
[CRQuote_CROpportunityRevision].[BranchID] IN (24, 25, 26)
)
)
AND [CRQuote_CROpportunityRevision].[NoteID] = [CRQuote_CRQuote].[QuoteID]
LEFT JOIN [CROpportunity] [CRQuote_CROpportunity] ON ([CRQuote_CROpportunity].[CompanyID] = 2)
AND [CRQuote_CROpportunity].[OpportunityID] = [CRQuote_CROpportunityRevision].[OpportunityID]
)
WHERE
([CRQuote_CRQuote].[CompanyID] = 2)
ORDER BY
[CRQuote_CROpportunityRevision].[OpportunityID],
[CRQuote_CRQuote].[QuoteNbr] OPTION (OPTIMIZE FOR UNKNOWN)
While the SOOrder in the same preview is creating a subquery for the field:
SELECT
TOP (22) [SOOrder].[BranchID] AS [SOOrder_BranchID],
[SOOrder].[RiskLineCntr] AS [SOOrder_RiskLineCntr],
[SOOrder].[OrderType] AS [SOOrder_OrderType],
[SOOrder].[Behavior] AS [SOOrder_Behavior],
[SOOrder].[OrderNbr] AS [SOOrder_OrderNbr],
...
(
SELECT
TOP (1) [UsrSORevision].[LeadDays]
FROM
[UsrSORevision] [UsrSORevision]
WHERE
([UsrSORevision].[CompanyID] = 2)
AND (
[UsrSORevision].[OrderType] = [SOOrder].[OrderType]
AND [UsrSORevision].[OrderNbr] = [SOOrder].[OrderNbr]
AND [UsrSORevision].[RevNbr] = [SOOrder].[UsrRevNbr]
)
ORDER BY
[UsrSORevision].[LeadDays]
) AS [SOOrder_UsrLeadDaysRev],
...
FROM
[SOOrder] [SOOrder]
WHERE
([SOOrder].[CompanyID] = 2)
AND ([SOOrder].[DatabaseRecordStatus] = 0)
AND (
[SOOrder].[BranchID] IS NULL
OR ([SOOrder].[BranchID] IN (24, 25, 26))
)
ORDER BY
[SOOrder].[OrderType],
[SOOrder].[OrderNbr] OPTION (OPTIMIZE FOR UNKNOWN)
I suspect there is something different about CRQuote because it is a projection, but this post suggests it is possible to use PXDBScalar in projections.
If I sort or filter on the CRQuote scalar column, a subquery is created:
SELECT
TOP (23) [CRQuote_CRQuote].[QuoteID] AS [CRQuote_QuoteID],
[CRQuote_CROpportunityRevision].[OpportunityID] AS [CRQuote_OpportunityID],
[CRQuote_CRQuote].[QuoteNbr] AS [CRQuote_QuoteNbr],
...
NULL AS [CRQuote_UsrLeadDaysRev]
FROM
(
[CRQuote] [CRQuote_CRQuote]
INNER JOIN [CROpportunityRevision] [CRQuote_CROpportunityRevision] ON ([CRQuote_CROpportunityRevision].[CompanyID] = 2)
AND (
[CRQuote_CROpportunityRevision].[BranchID] IS NULL
OR (
[CRQuote_CROpportunityRevision].[BranchID] IN (24, 25, 26)
)
)
AND [CRQuote_CROpportunityRevision].[NoteID] = [CRQuote_CRQuote].[QuoteID]
LEFT JOIN [CROpportunity] [CRQuote_CROpportunity] ON ([CRQuote_CROpportunity].[CompanyID] = 2)
AND [CRQuote_CROpportunity].[OpportunityID] = [CRQuote_CROpportunityRevision].[OpportunityID]
)
WHERE
(
(
SELECT
TOP (1) [UsrQTRevision].[LeadDays]
FROM
[UsrQTRevision] [UsrQTRevision]
WHERE
([UsrQTRevision].[CompanyID] = 2)
AND (
[UsrQTRevision].[OpportunityID] = [CRQuote_CROpportunityRevision].[OpportunityID]
AND [UsrQTRevision].[QuoteNbr] = [CRQuote_CRQuote].[QuoteNbr]
AND [UsrQTRevision].[RevNbr] = [CRQuote_CRQuote].[UsrRevNbr]
)
ORDER BY
[UsrQTRevision].[LeadDays]
) >.0
)
AND ([CRQuote_CRQuote].[CompanyID] = 2)
ORDER BY
[CRQuote_CROpportunityRevision].[OpportunityID],
[CRQuote_CRQuote].[QuoteNbr] OPTION (OPTIMIZE FOR UNKNOWN)
But the field select still doesn’t get the subquery. If I take the query into SSMS, it does find data:

What am I doing wrong? Is this not possible on CRQuote? Do I need to do something extra with the Standalone?

Thank you for reading.