Solved

Need help formatting a PXSelect statement

  • 29 October 2022
  • 3 replies
  • 46 views

Userlevel 5
Badge

Please help me add one more “And<>” to my statement.

This statement is all squiqqle free and works as expected.

PXSelectBase<AuditHistory> recordsToProcess = new PXSelectJoin<AuditHistory,
    LeftJoin<QTCAuditHistoryReport, 
        On<QTCAuditHistoryReport.screenID, Equal<AuditHistory.screenID>,
            And<QTCAuditHistoryReport.batchID, Equal<AuditHistory.batchID>,
            And<QTCAuditHistoryReport.changeID, Equal<AuditHistory.changeID>>>>>,
    Where<AuditHistory.changeDate, GreaterEqual<Required<AuditHistory.changeDate>>,
        And<AuditHistory.changeDate, Less<Required<AuditHistory.changeDate>>,
        And<QTCAuditHistoryReport.screenID, IsNull>>>>(this);
    
return recordsToProcess.Select(filter.StartDate, filter.EndDate.Value.AddDays(1).AddTicks(-1));

This is the “colorized” version:

 

My record set returns rows from the AuditHistory table that have null screen id’s and I want to exclude them.  

I am trying to add on more And<> and I cannot get it to work (won’t compile...lots of red squiggles)

This is what I think should work, but I can’t seem to get it formatted correctly. 

I am adding in the line in BOLD

PXSelectBase<AuditHistory> recordsToProcess = new PXSelectJoin<AuditHistory,
    LeftJoin<QTCAuditHistoryReport, 
        On<QTCAuditHistoryReport.screenID, Equal<AuditHistory.screenID>,
            And<QTCAuditHistoryReport.batchID, Equal<AuditHistory.batchID>,
            And<QTCAuditHistoryReport.changeID, Equal<AuditHistory.changeID>>>>>,
    Where<AuditHistory.changeDate, GreaterEqual<Required<AuditHistory.changeDate>>,
        And<AuditHistory.changeDate, Less<Required<AuditHistory.changeDate>>,
        And<AuditHistory.screenID, IsNotNull>,
        And<QTCAuditHistoryReport.screenID, IsNull>>>> (this);

return recordsToProcess.Select(filter.StartDate, filter.EndDate.Value.AddDays(1).AddTicks(-1));
 

This is what it looks like “colorized”

All my “<” and “>” match up where they should.    

Any ideas?

Thanks...

icon

Best answer by Leonardo Justiniano 29 October 2022, 02:44

View original

3 replies

Userlevel 5
Badge

Hi @joe21 

It’s late Friday 🤓

PXSelectBase<AuditHistory> recordsToProcess = new PXSelectJoin<AuditHistory,
LeftJoin<QTCAuditHistoryReport,
On<QTCAuditHistoryReport.screenID, Equal<AuditHistory.screenID>,
And<QTCAuditHistoryReport.batchID, Equal<AuditHistory.batchID>,
And<QTCAuditHistoryReport.changeID, Equal<AuditHistory.changeID>>>>>,
Where<AuditHistory.changeDate, GreaterEqual<Required<AuditHistory.changeDate>>,
And<AuditHistory.changeDate, Less<Required<AuditHistory.changeDate>>,
And<AuditHistory.screenID, IsNotNull, // Remove >
And<QTCAuditHistoryReport.screenID, IsNull>>>>> (this); // Add >

 

Userlevel 5
Badge

@Leonardo Justiniano ...LOL  

WORKS GREAT NOW! 

Thank you.  Glad I’m not the only one working on a Friday night.

The “<” and “>” drive me nuts sometimes.  

I just tried doing it with BQLFluent and it works this way too, but I’m going to stick with your fix.

var recordsToProcess = SelectFrom<AuditHistory>.LeftJoin<QTCAuditHistoryReport>
    .On<QTCAuditHistoryReport.batchID.IsEqual<AuditHistory.batchID>
        .And<QTCAuditHistoryReport.changeID.IsEqual<AuditHistory.changeID>>>
    .Where<AuditHistory.changeDate.IsGreaterEqual<@P.AsDateTime>
        .And<AuditHistory.changeDate.IsLess<@P.AsDateTime>>
        .And<AuditHistory.screenID.IsNotNull>
        .And<QTCAuditHistoryReport.batchID.IsNull>>.View.Select(this, filter.StartDate, filter.EndDate.Value.AddDays(1).AddTicks(-1));

return recordsToProcess;
 

 

Userlevel 5
Badge

@joe21 

Try Fluent-BQL. It is easier to read and fix. It’s built over BQL.

Have a great weekend.

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2022  Acumatica, Inc. All rights reserved