![]() |
#1 |
Участник
|
Unable to retrieve TypeId from DocuRef table for SSRS report
Good day.
My client needs a new report and for the report I have to check customer and vendor attachments. When I test the table selection in a job, the DocuRef.TypeId has a value, but when I print the report the field is empty. The report has no special design, it is just a simple table. I need the TypeId for the if statement, but it looks as it never fetches the value. I created a table "testTable" for testing to see what values are fetched and after I execute the report I see that TypeId is empty in all rows but the table DocuRef itself has no row with an empty TypeId field. I am doing something wrong but I just can't see the error. I hope you could help me. Code below. X++: public void processReport() { CustTable custTable; VendTable vendTable; DocuRef docuRef; LogisticsPostalAddress postalAddress; DirPartyLocation partyLocation; LogisticsAddressCountryRegion addressCountryRegion; CustVendAC custVendAcc; utcDateTime startDateTime, endDateTime; TestTable_t testTable; #define.SPP('URL')//TODO:Testing startDateTime = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(), DateTimeUtil::getCompanyTimeZone()); endDateTime = DateTimeUtil::addYears(startDateTime, -1); while select createdBy, createdDateTime, TypeId from docuRef where docuRef.RefTableId == tableNum(CustTable) && docuRef.RefCompanyId == curext() && (docuRef.createdDateTime >= startDateTime && docuRef.createdDateTime <= endDateTime && docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP join AccountNum, Party from custTable where custTable.RecId == docuRef.RefRecId && custTable.Blocked == CustVendorBlocked::No exists join partyLocation where partyLocation.IsPrimary == true && partyLocation.Party == custTable.Party exists join postalAddress where postalAddress.Location == partyLocation.Location exists join addressCountryRegion where addressCountryRegion.CountryRegionId == postalAddress.CountryRegionId && addressCountryRegion.SanctionedCountry_mb == NoYes::Yes { tmpCustVendListTable.clear(); testTable.clear(); if (custVendAcc != custTable.AccountNum) { custVendAcc = custTable.AccountNum; } else { if (docuRef.TypeId != #SPP) continue; } tmpCustVendListTable.BPAccountNum = custVendAcc; tmpCustVendListTable.BPName = custTable.name(); testTable.BPAccountNum = custVendAcc; testTable.BPName = custTable.name(); testTable.TypeId = docuRef.TypeId; if (docuRef.TypeId == #SPP) { tmpCustVendListTable.ResponsibleUser = XUserInfo::find(false, docuRef.createdBy).name; tmpCustVendListTable.ScreeningDateTime = docuRef.createdDateTime; testTable.ResponsibleUser = XUserInfo::find(false, docuRef.createdBy).name; testTable.ScreeningDateTime = docuRef.createdDateTime; } tmpCustVendListTable.insert(); testTable.insert(); } } |
|
![]() |
#2 |
Участник
|
It seems that your startDateTime is greater than endDateTime.
try to find out what is inside: X++: info(strFmt("%1 %2", startDateTime, endDateTime)); X++: where docuRef.RefTableId == tableNum(CustTable) && docuRef.RefCompanyId == curext() && (docuRef.createdDateTime >= startDateTime && docuRef.createdDateTime <= endDateTime && docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP
__________________
// no comments |
|
![]() |
#3 |
Участник
|
Thank You for the reply.
The date variables are correct. NAME: endDateTime VALUE:17.10.2019 20:18:39 NAME: startDateTime VALUE: 17.10.2018 20:18:39 I use them to fetch DocuRef records which are a year old from current day and have TypeId == URL. The "or" statement is used without the date period. The complete requirement for data selection is as follows: Report should show all active BP (CustTable.Blocked=“no“ and VendTable. Blocked =“no“) related records in DocuRef table where DocuRef.Type= “URL” if primary address (DirPartyPostalAddressView IsPrimary=”YES”) country has mark “YES” “Sanctioned country” and last screening date is older than one year from today and all active debtors and vendors (CustTable.Blocked=“no“ and VendTable. Blocked =“no“) records with primary address (DirPartyPostalAddressView IsPrimary=”YES”) country has mark “YES” “Sanctioned country” and without related records in DocuRef table where DocuRef.Type= “URL”. I think the while select statement looks correct. I am just testing on CustTable. |
|
![]() |
#4 |
Участник
|
I see in your code if startDateTime contains current 2019 year then endDateTime should contain the date of 2018 year:
X++: endDateTime = DateTimeUtil::addYears(startDateTime, -1); No, it doesn't look correct. Due to your current conditions in a case when docuRef.TypeId != #SPP you should pull the records for any company (RefCompanyId) and any table (RefTableId). I'm sure you pulled the records with empty TypeId field right for this case. Enclose following code in additional braces and you will see the difference. X++: (docuRef.createdDateTime >= startDateTime && docuRef.createdDateTime <= endDateTime && docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP X++: docuRef.createdDateTime >= startDateTime
__________________
// no comments Последний раз редактировалось dech; 18.10.2019 в 10:50. |
|
![]() |
#5 |
Участник
|
Цитата:
I see in your code if startDateTime contains current 2019 year then endDateTime should contain the date of 2018 year:
Цитата:
Here you substract the value of 1 year from startDateTime and assign result to endDateTime. Such conditions do not allow you to get something else than nothing.
Цитата:
No, it doesn't look correct. Due to your current conditions in a case when docuRef.TypeId != #SPP you should pull the records for any company (RefCompanyId) and any table (RefTableId). I'm sure you pulled the records with empty TypeId field right for this case. Enclose following code in additional braces and you will see the difference.
X++: while select createdBy, createdDateTime, TypeId from docuRef where docuRef.RefTableId == tableNum(CustTable) && ((docuRef.createdDateTime >= endDateTime && docuRef.createdDateTime <= startDateTime && docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP) join AccountNum, Party from custTable where custTable.RecId == docuRef.RefRecId && custTable.dataAreaId == docuRef.RefCompanyId && custTable.Blocked == CustVendorBlocked::No exists join partyLocation where partyLocation.IsPrimary == true && partyLocation.Party == custTable.Party exists join postalAddress where postalAddress.Location == partyLocation.Location exists join addressCountryRegion where addressCountryRegion.CountryRegionId == postalAddress.CountryRegionId && addressCountryRegion.SanctionedCountry_mb == NoYes::Yes { tmpCustVendListTable.clear(); if (custVendAcc != custTable.AccountNum) { custVendAcc = custTable.AccountNum; } else { if (docuRef.TypeId != #SPP) continue; } tmpCustVendListTable.BPAccountNum = custVendAcc; tmpCustVendListTable.BPName = custTable.name(); if (docuRef.TypeId == #SPP) { tmpCustVendListTable.ResponsibleUser = XUserInfo::find(false, docuRef.createdBy).name; tmpCustVendListTable.ScreeningDateTime = docuRef.createdDateTime; } tmpCustVendListTable.insert(); } Thank you dech. |
|
Теги |
axapta 2012, docuref, missing value, ssrs, typeid |
|
|