Skip to main content
Question

CRQuote PXDBScalar Always Null - No Subquery


Forum|alt.badge.img
  • Jr Varsity II
  • 31 replies

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.

3 replies

Forum|alt.badge.img+8
  • Captain II
  • 364 replies
  • 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
  • 31 replies
  • 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+13

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.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings