Hi All,
I am trying add two fields(OpportunityId ,PaymtermId) to Open Transaction report. I am using standard Tmp table(custTransOpenPerDateTmp) to get the invoice of current record and I am unable to get the record.
we have a custom field (OpportunitiId) in salesTable.
I wrote COC for protected void populateCustTransOpenPerDateTmp()
I wrote my code after next Keyword, base code will be executed first I thought I can use already fetched values in Tmp table which is used in base code. Below is the code I wrote please guide me.
[ExtensionOf(classStr(CustTransOpenPerDateDP))]
internal final class TCustTransOpenPerDateDP_Extension
{
protected void populateCustTransOpenPerDateTmp()
{
next populateCustTransOpenPerDateTmp();
SalesTable salesTable;
PaymTerm paymTerm;
CustInvoiceJour custInvoiceJour;
CustTransOpenPerDateTmp custTransOpenPerDateTmp;
CustTrans custTrans;
//select * from custInvoiceJour
// where custInvoiceJour.InvoiceId == custTrans.Invoice
// join salesTable
// where salesTable.SalesId == custInvoiceJour.SalesId
// join paymTerm
// where paymTerm.PaymTermId == salesTable.Payment;
while select custTrans
where custTrans.Invoice == custTransOpenPerDateTmp.Invoice
{
select firstonly custInvoiceJour
where custInvoiceJour.InvoiceId == custTrans.Invoice;
select firstonly salesTable
where salesTable.SalesId == custInvoiceJour.SalesId;
select firstonly paymTerm
where paymTerm.PaymTermId == salesTable.Payment;
custTransOpenPerDateTmp.OppurtunityId = salesTable.OpportunityId;
custTransOpenPerDateTmp.PaymentTerm = salesTable.Payment;
custTransOpenPerDateTmp.insert();
}
}
}
we added a custome field OppurtunityId in SalesTable
An obvious problem is that you’re using a new, empty buffer of CustTransOpenPerDateTmp table that you declared in your method. You never insert any records there, therefore you’ll never find anything in it.
You need to use the same buffer as the standard code, i.e. the the class-level variable. Try this code to access the data:
protected void populateCustTransOpenPerDateTmp()
{
next populateCustTransOpenPerDateTmp();
CustTransOpenPerDateTmp data = this.custTransOpenPerDateTmp;
while select data
{
...
}
}
Hi Martin,
I am able to get the records from Tmp table and also when I debugging they are inserted in Tmp table but they are not populating in the report.
I can think of two two options:
- The records are inserted correctly but the report doesn’t show them, e.g. because of a filter on a field that you didn’t populate.
- Or your debugging isn’t correct and the records aren’t actually inserted (to the right temporary buffer).
By the way, it’s not clear to me why you’re inserting anything at all. The requirement sounds to me like you want to add an extra field to existing records, i.e. updating them instead of leaving existing data the untouched and adding some additional records.
The correct relation between CustTrans and CustInvoiceJour is defined by CustInvoiceJour relation. It has four fields, not just one.
Your logic for fetching the SalesTable record isn’t correct either. It doesn’t taking into account that a single invoice may include several sales orders.
I don’t know if the whole idea (finding a sales order for a CustTrans) makes a good sense.
Hi Martin,
I have changed my code according to your guidance(your suggestions really helped me). Now I am able to get values in the report but am I doing it in a right way. Below is my code
[ExtensionOf(classStr(CustTransOpenPerDateDP))]
internal final class TCustTransOpenPerDateDP_Extension
{
protected void populateCustTransOpenPerDateTmp()
{
next populateCustTransOpenPerDateTmp();
SalesTable salesTable;
PaymTerm paymTerm;
CustInvoiceJour custInvoiceJour;
CustTransOpenPerDateTmp custTransOpenPerDateTmp;
CustTrans custTrans;
//select * from custInvoiceJour
// where custInvoiceJour.InvoiceId == custTrans.Invoice
// join salesTable
// where salesTable.SalesId == custInvoiceJour.SalesId
// join paymTerm
// where paymTerm.PaymTermId == salesTable.Payment;
CustTransOpenPerDateTmp data = this.custTransOpenPerDateTmp;
while select custTrans
where custTrans.Invoice == data.Invoice
join salesTable
where salesTable.CustAccount==custTrans.OrderAccount
{
select firstonly custInvoiceJour
where custInvoiceJour.InvoiceId == custTrans.Invoice;
select firstonly salesTable
where salesTable.SalesId == custInvoiceJour.SalesId;
select firstonly paymTerm
where paymTerm.PaymTermId == salesTable.Payment;
if(salesTable.OpportunityId!= '')
{
data.OppurtunityId = salesTable.OpportunityId;
data.update();
}
if(paymTerm.PaymTermId!='')
{
data.PaymentTerm = salesTable.Payment;
data.update();
}
}
}
}
Apart from that the whole thing makes no sense to me…
All the relations (with one exceptions: PaymTerm) are wrong; I already mentioned some of them (and you didn’t fix any). But the most meaningless is the first join, where you join all orders of a customer to each CustTrans. Not just most of them are completely unrelated to the transaction, but it’ll return a the same transaction once for each order. If the customer has five thousand sales orders, you’ll process the same transaction five thousand times. And there may be many transactions…
It’s minor in comparison to the wrong business logic and the disastrous performance bug, but running all the separate select statements inside the while loop is a performance problem too. You should limit the number of DB requests by using joins.
Calling update() twice is bad too.