Skip to main content
Answer

CRQuote PXDBScalar Always Null - No Subquery

  • March 13, 2025
  • 3 replies
  • 76 views

Forum|alt.badge.img

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.

Best answer by darylbowman

You wouldn’t need the PXDBScalar attribute on the Standalone field. But you would need to add the BqlField property in the projection field pointed back to the Standalone field.

3 replies

Forum|alt.badge.img+8
  • Captain II
  • March 14, 2025

Hi ​@PBSA 

 

CRQuote is a projection, you need to add the field to Standalone.CRQuote too.

 

Hope this helps

Aleks


Forum|alt.badge.img
  • Author
  • Jr Varsity II
  • March 14, 2025

@aiwan Thank you for your suggestion. When I initially tried this, I had this field on the Standalone.CRQuote:

I tried adding the PXDBScalar definition to that, but Standalone.CRQuote doesn’t have the fields to support the BQL. Can you confirm if the screenshot is in line with what you were suggesting, or if I need to find a way to get the needed fields into Standalone.CRQuote to define the PXDBScalar again?


darylbowman
Captain II
Forum|alt.badge.img+15
  • Answer
  • March 17, 2025

You wouldn’t need the PXDBScalar attribute on the Standalone field. But you would need to add the BqlField property in the projection field pointed back to the Standalone field.