AOT query date ranges

In LogisticsPostalAddress table i had to add a range on ValidFrom and ValidTo fields in order to get only the active addresses.

Having an AOT query, i did the following:

Got the addresses for a customer with a query like this:


SELECT ct.ACCOUNTNUM
, dpt.NAME
, dpl.ISPOSTALADDRESS
, dpl.ISPRIMARY
, ll.DESCRIPTION
, ll.RECID
, lpa.ADDRESS
, lpa.VALIDFROM
, lpa.VALIDTO
FROM CUSTTABLE AS ct

JOIN DIRPARTYTABLE AS dpt
ON ct.PARTY = dpt.RECID

JOIN DIRPARTYLOCATION AS dpl
ON dpl.PARTY = dpt.RECID

JOIN LOGISTICSLOCATION AS ll
ON dpl.LOCATION = ll.RECID

JOIN LOGISTICSPOSTALADDRESS AS lpa
ON lpa.LOCATION = ll.RECID

WHERE ct.ACCOUNTNUM = 'C00000010'

I've just created this address which shows up in my AX view. So, .. today() acts like less then or equal.

Get SalesLines with Invoice but without associated PackingSlip

First of all, I would like to display all the sales lines with a relation in CustInvoiceTrans via InventTransId field. So .. fully or partially invoiced, doesn't matter.
 - Further more, I want to check only those invoices which were not posted from a PackingSlip and for this, i am using CustInvoicePackingSlipQuantityMatch table.

The relation is simple:

•   CustInvoiceTrans (SourceDocumentLine) 
•   CustInvoicePackingSlipQuantityMatch (InvoiceSourceDocumentLine)

and

•  CustPackingSlipTrans(SourceDocumentLine) 
•  CustInvoicePackingSlipQuantityMatch (PackingSlipSourceDocumentLine)

I have noticed one thing: if a packing slip is created first, a line is added in the CustInvoicePackingSlipQuantityMatch with a reference to that packing slip. If you continue and create the invoice it will be added in the table too. 

But if you only create the invoice, without a packing slip, no record will exist in the table regarding your invoice. 

Here is a query for this:

 SELECT sl.SALESID

, CONVERT(INT, sl.QTYORDERED) AS 'SalesQty'

, sl.INVENTTRANSID

, (SELECT cit1.INVOICEID

FROM CUSTINVOICETRANS AS cit1

WHERE cit1.INVENTTRANSID = sl.INVENTTRANSID

AND cit1.PARTITION = sl.PARTITION

AND cit1.DATAAREAID = sl.DATAAREAID) AS 'InvoiceId'

, cpst.PACKINGSLIPID

, CONVERT(INT, cpst.QTY)  AS 'PackingSlipQty'

, cipsqm.PACKINGSLIPSOURCEDOCUMENTLINE

, cipsqm.INVOICESOURCEDOCUMENTLINE

, cit.INVOICEID

FROM SALESTABLE AS st

JOIN SALESLINE AS sl

ON st.SALESID = sl.SALESID

AND st.PARTITION = sl.PARTITION

AND st.DATAAREAID = sl.DATAAREAID

LEFT JOIN CUSTPACKINGSLIPTRANS AS cpst

ON sl.INVENTTRANSID = cpst.INVENTTRANSID

AND sl.PARTITION = cpst.PARTITION

AND sl.DATAAREAID = cpst.DATAAREAID

LEFT JOIN CustInvoicePackingSlipQuantityMatch AS cipsqm

ON cpst.SOURCEDOCUMENTLINE = cipsqm.PACKINGSLIPSOURCEDOCUMENTLINE

LEFT JOIN CUSTINVOICETRANS AS cit

ON cipsqm.INVOICESOURCEDOCUMENTLINE = cit.SOURCEDOCUMENTLINE

WHERE st.CREATEDDATETIME > '2017-01-10'

And the results:

As you can see, the second sales order is there, its invoice is there but no info was added to CustInvoicePackingSlipQuantityMatch.

Let's get to X++ now :

static void salesPackingSlipsInvoicesTest(Args _args)

{

    Query localQuery;

    QueryBuildDataSource qbdsLocalSalesTable, qbdsLocalSalesLine, qbdsLocalCustInvoiceTrans, qbdsLocalCustInvoicePackingSlipQuantityMatch;

    QueryBuildRange localQbr;

    QueryRun localQueryRun;

    SalesTable salesTable;

    CustInvoiceTrans custInvoiceTrans;

    TransDate fromDate, toDate;

    utcDateTime dateToBeginValue, dateToEndValue;

    DimensionProvider dimensionProvider = new DimensionProvider();

    localQuery = new query();

    qbdsLocalSalesTable = localQuery.addDataSource(tableNum(SalesTable));

    qbdsLocalSalesLine = qbdsLocalSalesTable.addDataSource(tableNum(SalesLine));

    qbdsLocalSalesLine.joinMode(JoinMode::InnerJoin);

    qbdsLocalSalesLine.relations(true);

    //qbdsLocalSalesLine.fetchMode(QueryFetchMode::One2One);

    qbdsLocalCustInvoiceTrans = qbdsLocalSalesLine.addDataSource(tableNum(CustInvoiceTrans));

    qbdsLocalCustInvoiceTrans.joinMode(JoinMode::InnerJoin);

    qbdsLocalCustInvoiceTrans.relations(false);

    qbdsLocalCustInvoiceTrans.addLink(fieldNum(SalesLine, InventTransId),

        fieldNum(CustInvoiceTrans, InventTransId));

    //qbdsLocalCustInvoiceTrans.fetchMode(QueryFetchMode::One2One);

    //Use the junction table to go from packing lines to invoice lines

    qbdsLocalCustInvoicePackingSlipQuantityMatch = qbdsLocalCustInvoiceTrans.addDataSource(tableNum(CustInvoicePackingSlipQuantityMatch));

    qbdsLocalCustInvoicePackingSlipQuantityMatch.joinMode(JoinMode::NoExistsJoin);

    qbdsLocalCustInvoicePackingSlipQuantityMatch.relations(false);

    qbdsLocalCustInvoicePackingSlipQuantityMatch.addLink(fieldNum(CustInvoiceTrans, SourceDocumentLine),

        fieldNum(CustInvoicePackingSlipQuantityMatch, InvoiceSourceDocumentLine));

    //qbdsLocalCustInvoicePackingSlipQuantityMatch.fetchMode(QueryFetchMode::One2One);

    

    dimensionProvider.addAttributeRangeToQuery(localQuery

                  , qbdsLocalSalesLine.name()

                  , fieldStr(SalesLine, DefaultDimension)

                  , DimensionComponent::DimensionAttribute

                  , 'b2b'

                  , "CostCenter"

                  , false);

    fromDate = mkDate(9, 1, 2017);

    toDate = mkDate(10, 1, 2017);

    if (fromDate || toDate)

    {

        dateToBeginValue = datetobeginUtcDateTime(fromDate, 0);

        dateToEndValue   = datetoendUtcDateTime(toDate,  0);

        qbdsLocalSalesTable.addRange(fieldnum(SalesTable, CreatedDateTime)).value(queryRange(dateToBeginValue, dateToEndValue));

    }

    localQueryRun = new QueryRun(localQuery);

    while (localQueryRun.next())

    {

        salesTable = localQueryRun.get(tableNum(SalesTable));

        custInvoiceTrans = localQueryRun.get(tableNum(CustInvoiceTrans));

        info(strFmt("Salesid %1 - InvoiceId %2", salesTable.SalesId, custInvoiceTrans.InvoiceId));

    }

}

 After running this query i obtain the following :

So .. what is this ?

you will get an explanation here.

Magical FetchMode property

Just uncomment the red lines and voila !

The desired line is right here. As i have shown above, this is the one with invoice and without packing slip associated.

 

Get time according to current time zone:
 -  in my case: Timezone::GMTPLUS0200ATHENS_BUCHAREST_ISTANBUL


     utcDateTime dateTime;


    dateTime = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(),
                Timezone::GMTPLUS0200ATHENS_BUCHAREST_ISTANBUL);
   
    info(strFmt("Time %1", time2StrHMS(DateTimeUtil::time(dateTime))));

Response: Time 16:52:06

SSRS line and borders highlighting

Changing colors on grouped lines:  

Group lines according to Account value and change the color for the entire group.

=IIF(RunningValue(Fields!Account.Value, CountDistinct, Nothing) MOD 2 = 1, "WhiteSmoke", "White")


 Border style on groupings:

Top: =IIF(Fields!Account.Value = Previous(Fields!Account.Value), "None", "Solid")

-            So, when Account value changes, top line becomes gets a solid border style.

Bottom: =IIF(RowNumber("AccountLines_DS") = CountRows("AccountLines_DS"), "Solid", "None")

-            When we reach the last line, draw a solid border.


No grouping at all:


= IIF(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")



And something ..more like a personal note.

=FORMAT(Fields!TransDate.Value, "dd.MM.yyyy")

Get customer's party type

Starting from a LedgerJournalTrans line:

if (ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)
        {
            if (CustTable::find(DimensionStorage::ledgerDimension2AccountNum(
ledgerJournalTrans.LedgerDimension)).partyType() == DirPartyType::Person)
              {
               ........
               }
        }

So, the partyType() method from CustTable will do the job.

Now here is how i did it in Sql:

--DirPerson 2975, DirOrganization 2978, CompanyInfo 41, OMOperatingUnit 2377, OMTeam 5329
SELECT CASE (SELECT dpt.INSTANCERELATIONTYPE
        FROM DIRPARTYTABLE AS dpt
        WHERE dpt.RECID = ct.PARTY)
            WHEN 2975 THEN 'Person'
            WHEN 2978 THEN 'Organization'
        END AS 'Party'
    , ct.AccountNum
FROM CUSTTABLE AS ct

and in order to get only the persons, just add a cte over it:


WITH parties
AS
(
    SELECT CASE (SELECT dpt.INSTANCERELATIONTYPE
            FROM DIRPARTYTABLE AS dpt
            WHERE dpt.RECID = ct.PARTY)
                WHEN 2975 THEN 'Person'
                WHEN 2978 THEN 'Organization'
            END AS 'Party'
        , ct.AccountNum
    FROM CUSTTABLE AS ct
)
SELECT *
FROM parties p
WHERE p.Party = 'Person'

SSRS - Hide tablix column when all rows are empty

First of all, don't use Hidden property but right click on tablix header, choose Column Visibility

and below "Show or hide based on an expression" write down your expression.

As an example:

 =IIF(Max(Fields!OffsetDimension.Value, "LedgerTransStatementDS")= "", true, false)

So, if maximum of all field values is equal to an empty string, it means we have absolutely no values in this column.

If you would add this expression to the Hidden property then you will end up with a gap in your table.

Check if string enum element is part of real enum.

When importing lines from a .csv file, at some point, i have to check if the account type is correctly provided.

Here is the method for this and a call example :

//Example: enumElementIsValid("Vendor", "LedgerJournalACType");
private boolean enumElementIsValid(str _inputEnumElement, str _inputEnumType)
{
    boolean isValid = false;

    DictEnum enum = new DictEnum(enumName2Id(_inputEnumType));

    int i;
    for (i = 0; i < enum.values(); i++)
    {
        //check if input account equals one of the labels.
        if (_inputEnumElement == SysLabel::labelId2String2(enum.index2LabelId(i), 'en-za') ||
                _inputEnumElement == SysLabel::labelId2String2(enum.index2LabelId(i), 'en-us'))
        {
            isValid = true;
        }
    }
    return isValid;
}


So, i am sending the enum name for this task, and its element as it was read from the .csv file.

I've chosen to compare the string value from the file with the label of the enum element because that's what the users see and not the actual name. And I've done this in two languages.