how to get the dates which are having blank records using x++

(0) Share

Report

Posted on by Dineshkarlekar

1,479

i am trying to get dates which are having blank records using x++ can anyone suggest how can i get this done.

All responses (30)

Answers (2)

Dineshkarlekar 1,479 on at

Like (0)

Report
RE: how to get the dates which are having blank records using x++

hi

Girishs

FINALLY i am getting the desired output, the code is below its giving me the sum of previous amount which i want , if i use accounting date== from date i it will give me difference amount in next date but using Accounting date <= fromdate i is giving me the sum of previus date and difference amount .

select sum(TransactionCurrencyAmount) from genJourAcEntry join genJourEntry where genJourEntry.RecId == genJourAcEntry.GeneralJournalEntry && genJourEntry.AccountingDate <= fromDate i && genJourAcEntry.MainAccount == _recId && genJourEntry.SubledgerVoucherDataAreaId == _dataAreaId;
Verified answer

GirishS 27,821 Super User 2024 Season 1 on at

Like (0)

Report

RE: how to get the dates which are having blank records using x++

Is the issue resolved or you still need support?

Thanks,

Girish S.
Dineshkarlekar 1,479 on at

Like (0)

Report
RE: how to get the dates which are having blank records using x++

hi

Girishs

I have check with the code , now the value is getting zero actuallly i had to pass dataareaid there

select sum(TransactionCurrencyAmount) from genJourAcEntry join genJourEntry where genJourEntry.RecId == genJourAcEntry.GeneralJournalEntry && genJourEntry.AccountingDate == fromDate i && genJourAcEntry.MainAccount == _recId && genJourEntry.SubledgerVoucherDataAreaId == _dataAreaId;

thanks

regards,

Dinesh
GirishS 27,821 Super User 2024 Season 1 on at

Like (0)

Report

RE: how to get the dates which are having blank records using x++

Then you need to check the select statement you have written. Try to run the SQL query against each date - Get the transaction amount and then try the same query in your DP class.

Thanks,

Girish S.
Dineshkarlekar 1,479 on at

Like (0)

Report

RE: how to get the dates which are having blank records using x++

thanks

I am getting the transaction date 2,3 are zero and other date which do not have transaction are zero as well but first date is still taking the value it should not be there as there is no transaction on date 1 below are the images
GirishS 27,821 Super User 2024 Season 1 on at

Like (0)

Report
RE: how to get the dates which are having blank records using x++

No, you need to pass i values also and increment From date. Refer to the below code.

select sum(TransactionCurrencyAmount) from genJourAcEntry join genJourEntry where genJourEntry.RecId == genJourAcEntry.GeneralJournalEntry && genJourEntry.AccountingDate == fromDate i && genJourAcEntry.MainAccount == _recId;

Thanks,

Girish S.
Dineshkarlekar 1,479 on at

Like (0)

Report
RE: how to get the dates which are having blank records using x++

I have assign Accounting Date== FromDate instead of AccountingDate <= fromDate i but it passing same values in all recordes , actually in previous logic the code was skipping the dates which have zero transactions and in this code i am getting all the dates but it is taking sum of all transaction like in date 1 ,2, 3 there is no transaction so it should show zero there but that 74448 value get inserted i am not getting where it is coming from .

select sum(TransactionCurrencyAmount) from genJourAcEntry join genJourEntry where genJourEntry.RecId == genJourAcEntry.GeneralJournalEntry && genJourEntry.AccountingDate == fromDate && genJourAcEntry.MainAccount == _recId;
GirishS 27,821 Super User 2024 Season 1 on at

Like (0)

Report

RE: how to get the dates which are having blank records using x++

Code you have added will get the transaction amount for the date lesser than the from date + i and it will loop for date difference between from and to date. So, it will sum up all the amount where the accounting date is lesser then from date for each loop and it will show as the transaction amount.

I am still not able to understand the date range functionality you needed.

May be in your scenario you need to add Accounting Date== FromDate instead of AccountingDate <= fromDate + i;

Thanks,

Girish S.
Dineshkarlekar 1,479 on at

Like (0)

Report
RE: how to get the dates which are having blank records using x++

hi

Girishs

I have edited the code and replaced the qbdsquery with simple while query now i am getting all the dates as required but i am having issue with amount the report is fetching amount for the date which dont have any transaction . my code is below

public void populateTableDetails(DataAreaId _dataAreaId) { if(_dataAreaId) { changecompany(_dataAreaId) { ListIterator listDep = new ListIterator(Account); str DepValue; while(listDep.more()) { DepValue = listDep.value(); if(DepValue) { this.getRecord(DepValue,_dataAreaId); } listDep.next(); } } } } public Void getRecord(str _DepValue,str _dataAreaId) { MainAccount mainAccount; GeneralJournalEntry genJourEntry; GeneralJournalAccountEntry genJourAcEntry; int diff = toDate - fromDate; for(int i = 0; i<= diff;i ) { while select mainAccount where mainAccount.MainAccountId == _DepValue { _recId = mainAccount.RecId; select sum(TransactionCurrencyAmount) from genJourAcEntry join genJourEntry where genJourEntry.RecId == genJourAcEntry.GeneralJournalEntry && genJourEntry.AccountingDate <= fromDate i && genJourAcEntry.MainAccount == _recId; cashBalTmp.clear(); cashBalTmp.DTDataAreaID = _dataAreaId; cashBalTmp.MainAccountId = mainAccount.MainAccountId; cashBalTmp.Name = mainAccount.Name; cashBalTmp.TransactionCurrencyAmount = genJourAcEntry.TransactionCurrencyAmount; cashBalTmp.insert(); } } }

thanks

regards ,

dinesh
GirishS 27,821 Super User 2024 Season 1 on at

Like (0)

Report

RE: how to get the dates which are having blank records using x++

Can you try with normal while select statement with where condition and let us know the output with code?

Thanks,

Girish S