Solved

Redirect to report from processing screen (T240 for example)

  • 15 June 2022
  • 2 replies
  • 100 views

Userlevel 3
Badge +1

I have a complex report (lots of tables/joins) based on a sales order. 

Here is the schema tables/relations defined in the report:

 

 

I need to redirect to this at the end of a process.  My process loop is working, just need to add this redirection.

 

So far, I have a PXReportResultset defined and I have a SelectFrom that matches the schema of the report (had to create derived DAC classes to join to same tables more than once):

            PXReportResultset salesOrders = new PXReportResultset(typeof(SOOrder),
typeof(Branch),
typeof(BAccount),
typeof(CustomerAcc),
typeof(BillingAddress),
typeof(CompanyAddress),
typeof(SOLine),
typeof(ShippingAddress),
typeof(SOOrderType),
typeof(Note),
typeof(LineNote),
typeof(Terms),
typeof(CompanyContact),
typeof(Carrier),
typeof(FOBPoint),
typeof(ShippingContact),
typeof(BillingContact),
typeof(ShipTerms),
typeof(CommonSetup),
typeof(PX.SM.PreferencesGeneral),
typeof(Customer),
typeof(EmContact),
typeof(SOOrderTypeOperation));
SelectFrom<SOOrder>
.InnerJoin<Branch>.On<SOOrder.branchID.IsEqual<Branch.branchID>>
.InnerJoin<BAccount>.On<Branch.bAccountID.IsEqual<BAccount.bAccountID>>
.InnerJoin<CustomerAcc>.On<SOOrder.customerID.IsEqual<CustomerAcc.bAccountID>>
.LeftJoin<BillingAddress>.On<SOOrder.billAddressID.IsEqual<BillingAddress.addressID>>
.LeftJoin<CompanyAddress>.On<BAccount.defAddressID.IsEqual<CompanyAddress.addressID>.And<BAccount.bAccountID.IsEqual<CompanyAddress.bAccountID>>>
.LeftJoin<SOLine>.On<SOOrder.orderType.IsEqual<SOLine.orderType>.And<SOOrder.orderNbr.IsEqual<SOLine.orderNbr>>>
.LeftJoin<ShippingAddress>.On<SOOrder.shipAddressID.IsEqual<ShippingAddress.addressID>>
.InnerJoin<SOOrderType>.On<SOOrder.orderType.IsEqual<SOOrderType.orderType>>
.LeftJoin<Note>.On<SOOrder.noteID.IsEqual<Note.noteID>>
.LeftJoin<LineNote>.On<SOLine.noteID.IsEqual<LineNote.noteID>>
.LeftJoin<Terms>.On<SOOrder.termsID.IsEqual<Terms.termsID>>
.LeftJoin<CompanyContact>.On<BAccount.defContactID.IsEqual<CompanyContact.contactID>>
.LeftJoin<Carrier>.On<SOOrder.shipVia.IsEqual<Carrier.carrierID>>
.LeftJoin<FOBPoint>.On<SOOrder.fOBPoint.IsEqual<FOBPoint.fOBPointID>>
.LeftJoin<ShippingContact>.On<SOOrder.shipContactID.IsEqual<ShippingContact.contactID>>
.LeftJoin<BillingContact>.On<SOOrder.billContactID.IsEqual<BillingContact.contactID>>
.LeftJoin<ShipTerms>.On<SOOrder.shipTermsID.IsEqual<ShipTerms.shipTermsID>>
.CrossJoin<CommonSetup>
.CrossJoin<PX.SM.PreferencesGeneral>
.LeftJoin<Customer>.On<SOOrder.customerID.IsEqual<Customer.bAccountID>>
.LeftJoin<EmContact>.On<SOOrder.ownerID.IsEqual<EmContact.contactID>>
.LeftJoin<SOOrderTypeOperation>.On<SOOrderType.orderType.IsEqual<SOOrderTypeOperation.orderType>.And<SOOrderType.defaultOperation.IsEqual<SOOrderTypeOperation.operation>>>
.View.Select(graph).Where(sp => sp.Record.OrderNbr == order.OrderNbr && sp.Record.OrderType == order.OrderType)

What I’m struggling with is how to cast the SelectFrom to a PXResultSet (due to many SOLine, LineNotes, etc. I cannot use FirstOrDefault otherwise I only get 1 row for these)

Then, how to get this into PXReportResultset?  If I add FirstOrDefault() I can get a PXResult, and add to my PXReportResultset and the report runs, but I only get 1 SOLine per order.

Or, is there a better way to do this vs. using PXReportRequiredException?  Can I just pass the OrderNbr to a parameter, run/execute each report but have it all combine into one report/preview window?  This way, the report can just do the query itself, right? 

Thanks for any suggestions.

icon

Best answer by rjean09 15 June 2022, 22:23

View original

2 replies

Userlevel 3
Badge +1

This isn’t what you’re looking for specifically but I’m curious to know if a database view and a DAC to wrap around that would help simplify all of this for you?

Thanks.  This is inside of a static process so not inside an instance.  Could probably do this but I got it working by looping through my var results and just adding to my PXReportResultset (no need for PXResultset to be declared explicitly)

                    var results =
SelectFrom<SOOrder>
.InnerJoin<Branch>.On<SOOrder.branchID.IsEqual<Branch.branchID>>
.InnerJoin<BAccount>.On<Branch.bAccountID.IsEqual<BAccount.bAccountID>>
.InnerJoin<CustomerAcc>.On<SOOrder.customerID.IsEqual<CustomerAcc.bAccountID>>
.LeftJoin<BillingAddress>.On<SOOrder.billAddressID.IsEqual<BillingAddress.addressID>>
.LeftJoin<CompanyAddress>.On<BAccount.defAddressID.IsEqual<CompanyAddress.addressID>.And<BAccount.bAccountID.IsEqual<CompanyAddress.bAccountID>>>
.LeftJoin<SOLine>.On<SOOrder.orderType.IsEqual<SOLine.orderType>.And<SOOrder.orderNbr.IsEqual<SOLine.orderNbr>>>
.LeftJoin<ShippingAddress>.On<SOOrder.shipAddressID.IsEqual<ShippingAddress.addressID>>
.InnerJoin<SOOrderType>.On<SOOrder.orderType.IsEqual<SOOrderType.orderType>>
.LeftJoin<Note>.On<SOOrder.noteID.IsEqual<Note.noteID>>
.LeftJoin<LineNote>.On<SOLine.noteID.IsEqual<LineNote.noteID>>
.LeftJoin<Terms>.On<SOOrder.termsID.IsEqual<Terms.termsID>>
.LeftJoin<CompanyContact>.On<BAccount.defContactID.IsEqual<CompanyContact.contactID>>
.LeftJoin<Carrier>.On<SOOrder.shipVia.IsEqual<Carrier.carrierID>>
.LeftJoin<FOBPoint>.On<SOOrder.fOBPoint.IsEqual<FOBPoint.fOBPointID>>
.LeftJoin<ShippingContact>.On<SOOrder.shipContactID.IsEqual<ShippingContact.contactID>>
.LeftJoin<BillingContact>.On<SOOrder.billContactID.IsEqual<BillingContact.contactID>>
.LeftJoin<ShipTerms>.On<SOOrder.shipTermsID.IsEqual<ShipTerms.shipTermsID>>
.CrossJoin<CommonSetup>
.CrossJoin<PX.SM.PreferencesGeneral>
.LeftJoin<Customer>.On<SOOrder.customerID.IsEqual<Customer.bAccountID>>
.LeftJoin<EmContact>.On<SOOrder.ownerID.IsEqual<EmContact.contactID>>
.LeftJoin<SOOrderTypeOperation>.On<SOOrderType.orderType.IsEqual<SOOrderTypeOperation.orderType>.And<SOOrderType.defaultOperation.IsEqual<SOOrderTypeOperation.operation>>>
.View.Select(graph).Where(sp => sp.Record.OrderNbr == order.OrderNbr && sp.Record.OrderType == order.OrderType);


foreach (PXResult < SOOrder,
Branch,
BAccount,
CustomerAcc,
BillingAddress,
CompanyAddress,
SOLine,
ShippingAddress,
SOOrderType,
Note,
LineNote,
Terms,
CompanyContact,
Carrier,
FOBPoint,
ShippingContact,
BillingContact,
ShipTerms,
CommonSetup,
PX.SM.PreferencesGeneral,
Customer,
EmContact,
SOOrderTypeOperation> result in results)
{
salesOrders.Add(result);
}

Seems to be working fine now!

Userlevel 7
Badge +5

This isn’t what you’re looking for specifically but I’m curious to know if a database view and a DAC to wrap around that would help simplify all of this for you?

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 — 2024  Acumatica, Inc. All rights reserved