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

(0) Share

Report

Posted on by Dineshkarlekar

1,836

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

I have the same question (0)

All responses (30)

Answers (2)

Suggested answer

Anton Venter 20,345 Super User 2025 Season 2 on at

Like (0)

Report

If you mean where the date field is "empty", then use dateNull() or utcDateTimeNull() in X++.

Was this reply helpful? Yes No
Suggested answer

GirishS 27,827 Moderator on at

Like (0)

Report

Hi Dinesh,

Refer to the below thread.

https://www.dynamicsuser.net/t/field-type-date-is-empty/30163/3

Thanks,

Girish S.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report

thanks for reply,

anton

i have date filter in report , when i select date like 1/3/2023 to 10/3/2023 , in my report output the date which have transaction are displayed but which are not having transaction are not displaying like 2/3/2023 and 7/3/2023 is getting skipped so how to get data even if this date dont have transaction or if these dates have blank transaction i want them to appear in report ,can you please guide me on this .

thanks

regards

dinesh

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Try adding date range as one range and date null as other range.

Thanks,

Girish S.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
thanks ,

girishs

Ihave added date range from date to todate can you please gide me how can i modify this code,

qbdsGeneralJourEntry.addRange(fieldNum(GeneralJournalEntry, AccountingDate)).value(queryRange(fromDate,toDate));

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report
You can use strfmt to add the query range. Please fix if there is any syntax error on below code. I roughly added the code.

qbdsGeneralJourEntry.addRange(fieldNum(GeneralJournalEntry, AccountingDate)) .value(strFmt('((%1.%2 == %3 || (%1.%2== %4)', querybuildDatasource.name(), fieldStr(GeneralJournalEntry, AccountingDate), queryrange(fromDate,todate) datenull()))));

Thanks,

Girish S.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
thanks ,

girishs

i tried to make changes but its giving me error for round bracket, its asking for ")" near date null and qr is also showing invalid

qbdsGenalAcEntry = qbdsMainAccount.addDataSource(tableNum(GeneralJournalAccountEntry)); qbdsGenalAcEntry.joinMode(JoinMode::InnerJoin); qbdsGenalAcEntry.addLink(fieldNum(MainAccount, RecId), fieldNum(GeneralJournalAccountEntry, MainAccount)); qbdsGeneralJourEntry = qbdsGenalAcEntry.addDataSource(tableNum(GeneralJournalEntry)); qbdsGeneralJourEntry.joinMode(JoinMode::InnerJoin); qbdsGeneralJourEntry.addLink(fieldNum(GeneralJournalAccountEntry, GeneralJournalEntry),fieldNum(GeneralJournalEntry, RecId)); //qbdsGeneralJourEntry.addRange(fieldNum(GeneralJournalEntry, AccountingDate)).value(queryRange(fromDate,toDate)); qbdsGeneralJourEntry.addRange(fieldNum(GeneralJournalEntry, AccountingDate)) .value(strFmt('((%1.%2 == %3 || (%1.%2== %4)', querybuildDatasource.name(), fieldStr(GeneralJournalEntry, AccountingDate), queryrange(fromDate,todate) datenull())))); QueryRun qr = new QueryRun(query); while (qr.next()) { MainAccount mainAccount = qr.get(tableNum(MainAccount)); GeneralJournalAccountEntry genJourAcEntry = qr.get(tableNum(GeneralJournalAccountEntry)); GeneralJournalEntry genJourEntry = qr.get(tableNum(GeneralJournalEntry));

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Refer to the below blog for how to add a STRFMT to query range.

http://msdaxsolutions.blogspot.com/2017/11/how-to-add-range-using-expressions-in.html

Using the above link try to modify the code.

Thanks,

Girish S.

Was this reply helpful? Yes No
Mohit Rampal 12,565 Moderator on at

Like (0)

Report

I think there is an extra closing brace here datenull())))). Try removing one closing brace ')'

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
hi ,

i made changes in code but report is not geting executed not even in excel i am not getting why its happpening,

qbdsGenalAcEntry = qbdsMainAccount.addDataSource(tableNum(GeneralJournalAccountEntry)); qbdsGenalAcEntry.joinMode(JoinMode::InnerJoin); qbdsGenalAcEntry.addLink(fieldNum(MainAccount, RecId), fieldNum(GeneralJournalAccountEntry, MainAccount)); qbdsGeneralJourEntry = qbdsGenalAcEntry.addDataSource(tableNum(GeneralJournalEntry)); qbdsGeneralJourEntry.joinMode(JoinMode::InnerJoin); qbdsGeneralJourEntry.addLink(fieldNum(GeneralJournalAccountEntry, GeneralJournalEntry),fieldNum(GeneralJournalEntry, RecId)); qbdsGeneralJourEntry.addRange(fieldNum(GeneralJournalEntry, AccountingDate)) .value(strFmt('((%1.%2 == %3 || (%1.%2== %4)', fieldStr(GeneralJournalEntry, AccountingDate), queryrange(fromDate,todate) ,dateNull())); QueryRun qr = new QueryRun(query); while (qr.next()) { MainAccount mainAccount = qr.get(tableNum(MainAccount)); GeneralJournalAccountEntry genJourAcEntry = qr.get(tableNum(GeneralJournalAccountEntry)); GeneralJournalEntry genJourEntry = qr.get(tableNum(GeneralJournalEntry)); cashBalTmp.clear(); cashBalTmp.DTDataAreaID = _dataAreaId; cashBalTmp.MainAccountId = mainAccount.MainAccountId; cashBalTmp.Name = mainAccount.Name; cashBalTmp.AccountingDate = genJourEntry.AccountingDate; cashBalTmp.TransactionCurrencyAmount = genJourAcEntry.TransactionCurrencyAmount; cashBalTmp.insert(); }

Was this reply helpful? Yes No