PurchLine validation

If you need to add constraints on PurchLine s, have a look at :

 

As an example, there is one default behavior for return items:

Check PurchLineType_ReturnItem

    -> validateWrite()

    if (purchLine.PurchQty > 0
    &&  purchLine.SkipUpdate == InterCompanySkipUpdate::No)
        ok = checkFailed("@SYS53512");

the label saying : "Quantity of returned items orders must be negative."

SSRS report - division issue

I've just found out an interesting thing about divisions in an SSRS report. Nothing fancy but you have to pay attention:

My expression is something like this:

  =IIF(Parameters!ReportType.Value="UsedMerchandiseInStore",
     (1.0 * Fields!inventTransCostValue_RO.Value)\(1.0 * Fields!Qty.Value),
     Fields!CostPrice.Value)

I admit: i was desperate and added those * 1.0 but that didn't solved it.

Instead, i was a lucky wanderer and read Paul Miner's answer on this post:

http://arstechnica.com/civis/viewtopic.php?t=168308

This is so nice, how a few words can help over the years.

 So i reiterate his suggestion, to change the "\" to "/". It seems that the former is integer division.

That did it.

Display formatted date if exists in SSRS report, or nothing

    First, of all, i set Allow Blank true and Nullable true for my parameters. In case the user chooses to avoid providing them, just display all the lines, without ranges. 

I am not going to post all the code from my report, but at some point in the processReport phase i set the dates range as :

 
     if (fromDate && toDate)
    {
        qbr = qbdsCT.addRange(fieldNum(CustTrans, TransDate));
        qbr.value(queryRange(fromDate, toDate));
    }

where qbdsCT is my QueryBuildDataSource object.



And finally in the SSRS i use this :

 =IIF(Parameters!CustTrans_DS_FromDate.Value is nothing, nothing,         FormatDateTime(Parameters!CustTrans_DS_FromDate.Value, 2))

Joining with EcoResProductTranslation

Pay attention when joining with this table. It will multiply your rows because it keeps Description and Name for your products in as many languages as you have defined in your system.

In my case, i had to join InventTable with it, in order to get the name of the product. This is the join relation:

And i have restricted the language to only the current one:

 

Set the range on the EcoResProductTranslation table and choose the value of LanguageId as follows:

 

Check if customer is a vendor also

I've encountered this request, to find out if one of our vendors is a client also.

How to check that? Well, there is a field on CustTable named VendAccount. This is the starting point.

 To check all of the existing pairs I've tried this:

T-SQL:

SELECT ct.ACCOUNTNUM
    , ct.VENDACCOUNT
FROM CUSTTABLE AS ct
WHERE EXISTS (SELECT 1
                FROM VENDTABLE
                    WHERE ACCOUNTNUM = ct.VENDACCOUNT)

And in X++:

static void getCustVendAccount(Args _args)
{
    CustTable   custTable;
    VendTable   vendTable;

    while select AccountNum, VendAccount from custTable
        exists join vendTable
        where custTable.VendAccount == vendTable.AccountNum
    {
        info(strFmt("Custmer Id: %1 - Vendor Id: %2", custTable.AccountNum, custTable.VendAccount));
    }
}

For both of them i had to compute the balance. 

Using the above X++ code i wrote:

TmpCustVendTrans tmpCustVendTrans;
;

tmpCustVendTrans = TmpCustVendTrans::custTransBalanceCurrency(custTable.AccountNum);

and for vendor:

tmpCustVendTrans = TmpCustVendTrans::custTransBalanceCurrency(custTable.VendAccount);

then just read the tmpCustVendTrans.AmountCur.