Skip to main content

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.

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!


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