Unable to retrieve TypeId from DocuRef table for SSRS report

[Ventainais]

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.

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();
    }
}

[dech]

It seems that your startDateTime is greater than endDateTime.
try to find out what is inside:

info(strFmt("%1 %2", startDateTime, endDateTime));

Next step is to define conditions correctly. I guess you missed braces, because AND priority is higher than OR.

        where docuRef.RefTableId    == tableNum(CustTable)  &&
              docuRef.RefCompanyId  == curext()             &&
             (docuRef.createdDateTime >= startDateTime   &&
              docuRef.createdDateTime <= endDateTime     &&
              docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP

[Ventainais]

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.

[dech]

I see in your code if startDateTime contains current 2019 year then endDateTime should contain the date of 2018 year:

endDateTime     = DateTimeUtil::addYears(startDateTime, -1);

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.

Цитата:

Сообщение от Ventainais

I think the while select statement looks correct.

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.

             (docuRef.createdDateTime >= startDateTime   &&
              docuRef.createdDateTime <= endDateTime     &&
              docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP

P.S. if endDateTime is current date and time you can omit this condition and wrinte only

docuRef.createdDateTime >= startDateTime

[Ventainais]

Цитата:

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.

Not really sure what You mean by this. The two dateTime variables are used to fetch DocuRef records by field CreatedDateTime on one year period from current date.

Цитата:

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.

This got me thinking and I rewrote the while select statement as follows:

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();
    }

Now when I execute the report I get the results I need.
Thank you dech.