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.