Axapta - Usable functions for queries

Thanks for that great page:

https://thwidmer.wordpress.com/2013/07/31/function-to-use-in-queries-filters/

AX 2012 - Add computed column to view.

When we add a computed column to AX view what we do actually is adding extra keywords to SQL Server view without any errors.

First we add a table method to AX view:

public static server str SNBisBuggedReverse()
{
return strFmt("select top 1 sign(sum(accountingcurrencyamount)) from GENERALJOURNALACCOUNTENTRY g "+
"where isnull(%1,'') <> '' and exists (select * from TRANSACTIONREVERSALTRANS t where t.TRACENUM = %1 and t.REFRECID = g.RECID and t.REFTABLEID = %2) "+
"group by g.LEDGERACCOUNT "+
"order by 1 desc",SysComputedColumn::comparisonField(identifierstr(PAXMizanView), identifierstr(TransactionReversalTrans), identifierstr(TraceNum)),
 tableNum(GeneralJournalAccountEntry));
}

Seem at up we just use SQL Server query keywords, not AX. Our method will return a string like that:

select top 1 sign(sum(accountingcurrencyamount)) from GENERALJOURNALACCOUNTENTRY g where isnull(T4.TRACENUM,'') <> '' and exists (select * from TRANSACTIONREVERSALTRANS t where t.TRACENUM = T4.TRACENUM and t.REFRECID = g.RECID and t.REFTABLEID = 3119) group by g.LEDGERACCOUNT order by 1 desc

We want to add an Enum field as type NoYes. Than:




After that we select our table methods name from ViewMethod property:




My computed column was unfortunately made my view so slow, we should faster things as possible as we could for create a fast view.

AX 2012 - Source Document Framework errors

In AX 2012 we get some errors -without knowing reasons- about  Source Document Framework. I did some research at forums; found different opinions, some adviced full CIL, some adviced a job for clean orphaned records (1), another job for fix Source Document records (2).


Error which I got at newFromSourceDocumentLineImplementation method of SourceDocumentLineItem class, original error message "SourceDocumentLineItem object not initialised":

I just debugged and saw record deleted from SourceDocumentLine table related to PurchLine.SourceDocumentLine meanwhile record related with VendInvoiceInfoLine.SourceDocumentLine still exists. Jobs I mentioned at upper was useless in that case. I just reviewed a correct Source Document record tied another picking list with bounded another  PurchLine record and wrote that job below. After run that job post was succeeded.

 PurchLine            purchLine;
    SourceDocumentLine   line;
    SourceDocumentHeader header;


    select firstonly forUpdate purchLine
        where purchLine.RecId == 5637329927;

    ttsBegin;
    header.AccountingStatus = SourceDocumentAccountingStatus::InProcess;
    header.SourceRelationType = 345;
    header.TypeEnumName = "SourceDocument_ProductOrder";
    header.TypeEnumValue = 1;
    header.insert();

    Line.SourceDocumentHeader = header.RecId;
    line.AccountingStatus = SourceDocumentLineAccountingStatus::Completed;
    Line.SourceRelationType = 340;
    Line.TypeEnumName = "SourceDocumentLine_ProductOrder";
    Line.TermQuantity = 0;
    Line.TypeEnumValue = 1;
    Line.insert();
    purchLine.SourceDocumentLine = Line.RecId;
    purchLine.doUpdate();
    ttsCommit;

Field values used at upper job like TypeEnumName, SourceRelationType etc... change about type of document. You can review a correct record like me adopt that job in your case. Of course you have to do this first test environment and you have to note RecId values of records you created at live.

(1) That's job advised for clean orphan records:

  SourceDocumentLine sline;

    SysDictTable table;

    PurchTable header;

    PurchLine purchline;

    PurchId purchId = "SA025965";

    boolean fix;

    Common rec;

    int fieldId, found, notfound;

    if (purchId)

    {

        while select purchLine where purchLine.PurchId == purchId

        {

            while select forUpdate sline where sline.ParentSourceDocumentLine == purchLine.SourceDocumentLine

            {

                table = new SysDictTable(sline.SourceRelationType);

                rec = table.makeRecord();

                fieldId = fieldName2id(sline.SourceRelationType, "SourceDocumentLine");

                select rec where rec.(fieldId) == sline.RecId;

                if (rec.RecId)

                {

                    info(strFmt("Record Match Found %1 %2 %3", table.name(),rec.caption(),sline.RecId));

                    found++;

                }

                else

                {

                    ttsBegin;

                    sline.doDelete();

                    ttsCommit;

                    info(strFmt("Orphan Found %1", table.name()));

                    notfound++;

                }

            }

            info(strFmt("Found %1", found));

            info(strFmt("Orphans found and deleted %1",notfound));

            found = 0;

            notfound = 0;

        }

    }

(2) That's another job we found at forums for fix Source Document records didn't worked in my case:

   RecID VendInvoiceRecID =  5637357894;

    VendInvoiceInfoTable    vendInvoiceInfoTable;

    VendInvoiceInfoLine     vendInvoiceInfoLine;

    SourceDocumentLine SourceDocumentLine;

    SourceDocumentHeader SourceDocumentHeader;

    PurchParmUpdate   PurchParmUpdate;

    RandomGenerate          rg = RandomGenerate::construct();

    vendInvoiceInfoTable = VendInvoiceInfoTable::findRecId(VendInvoiceRecID,true);

    if(vendInvoiceInfoTable)

    {

        ttsBegin;

        while select forupdate SourceDocumentHeader

             where SourceDocumentHeader.SourceRelationType == 1425

                && SourceDocumentHeader.RecId == vendInvoiceInfoTable.SourceDocumentHeader

        {

            SourceDocumentHeader.delete();

        }

        while select forupdate VendInvoiceInfoLine

             where VendInvoiceInfoLine.TableRefId  == vendInvoiceInfoTable.TableRefId

               && VendInvoiceInfoLine.ParmId       == vendInvoiceInfoTable.ParmId

        {

           delete_from SourceDocumentLine

             where SourceDocumentLine.SourceRelationType == 1430

               && SourceDocumentLine.RecId == VendInvoiceInfoLine.SourceDocumentLine  ;

//            VendInvoiceInfoLine.SourceDocumentLine = 0;

                        VendInvoiceInfoLine.SourceDocumentLine = rg.randomInt(1000,10000000);

            VendInvoiceInfoLine.doUpdate();

        }

        vendInvoiceInfoTable.selectForUpdate(true);

        vendInvoiceInfoTable.SourceDocumentHeader = 0;

        vendInvoiceInfoTable.SourceDocumentLine = 0;

        vendInvoiceInfoTable.doUpdate();

        ttsCommit;

        ttsBegin;

SourceDocumentProcessorFacade::submitSourceDocumentImplementation(vendInvoiceInfoTable,1);

        SourceDocumentProcessorFacade::submitSourceDocumentLineImplementation(vendInvoiceInfoTable,1);

        while select forupdate VendInvoiceInfoLine

             where VendInvoiceInfoLine.TableRefId  == vendInvoiceInfoTable.TableRefId

               && VendInvoiceInfoLine.ParmId  == vendInvoiceInfoTable.ParmId

        {

SourceDocumentProcessorFacade::submitSourceDocumentLineImplementation(VendInvoiceInfoLine,1);

        }

        ttsCommit;

        info("ok");

    }

AX 2012 & Power BI

I'll mention about eaiest way of learn Power BI, some troubles and important points about using AX data with Power BI at this page:

-Best source which I found about Power BI is a course at  EDX named  Analyzing and Visualizing Data with Power BI. This a video & subtitle combined course with an excellent way. When video is streaming subtitle is moving and you can click any part of subtitle and video jumps that part. There are some tests and nothing skips; Year-to-Day, gateway etc...

-You need Date table for carry forward and Waterfall report. I used a script which I found from Power BI İstanbul or you can use SQLBI team's solution.

let fnTarihTablosu = (BaşlangıçTarihi as date, BitişTarihi as date, MaliYılBaşlamaAyı as number) as table =>

  let

    DayCount = Duration.Days(Duration.From(BitişTarihi - BaşlangıçTarihi)) + 1,

    Source = List.Dates(BaşlangıçTarihi,DayCount,#duration(1,0,0,0)),

    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Tarih"}}),

    YılEkle = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Tarih]),type text),

    CeyrekEkle = Table.AddColumn(YılEkle, "Quarter No", each Date.QuarterOfYear([Tarih])),

    AyEkle = Table.AddColumn(CeyrekEkle, "Month No", each Date.Month([Tarih]), type text),

    GünEkle = Table.AddColumn(AyEkle, "Day of Month", each Date.Day([Tarih])),

    AyEkleIsim = Table.AddColumn(GünEkle, "Month", each Date.ToText([Tarih], "MMMM"), type text),

    AyEkleKısaIsim = Table.AddColumn(AyEkleIsim, "Month Short", each Date.ToText([Tarih], "MMM"), type text),

    HaftanınGünüEkle = Table.AddColumn(AyEkleKısaIsim, "Day of Week No", each Date.DayOfWeek([Tarih])+1),

    GünIsmiEkle = Table.AddColumn(HaftanınGünüEkle, "Day Of Week", each Date.ToText([Tarih], "dddd"), type text),

    HaftaNoEkle= Table.AddColumn(GünIsmiEkle, "Week No", each Date.WeekOfYear([Tarih])),

    ChangedType1 = Table.TransformColumnTypes(HaftaNoEkle,{{"Week No", Int64.Type},{"Year", type text},{"Day Of Month", Int64.Type}, {"Month No", Int64.Type}, {"Quarter No", Int64.Type}, {"Week of Day No", Int64.Type}}),

    KısaYılEkle = Table.AddColumn(ChangedType1, "Year Short", each Text.End(Text.From([Yıl]), 2), type text),

    MaliYıl = Table.AddColumn(KısaYılEkle, "Fiscal Year", each "MY"&(if [Ay No]>=MaliYılBaşlamaAyı then Text.From(Number.From([Yıl Kısa])+1) else [Yıl Kısa]))

in

    MaliYıl

in

    fnTarihTablosu

-There is a common problem with Turkish about "i" and "ı" letter. I faced with  DataAreaId. DataAreadId at Company table was "DEIM" and at others was "deim" and Power BI cannot match these with Turkish collate. Also primary/secondary address and expired address issues would be trouble about duplicate key issue. There are some tweaks need about these:

select c.accountnum,d.name,c.custgroup,l.state,l.COUNTRYREGIONID,lc.SHORTNAME,  iif(c.dataareaID = 'deim','DEIM',c.dataareaid) as DATAAREAID

from custtable c left join DIRPARTYTABLE d on c.PARTY = d.RECID

left join DIRPARTYLOCATION dl on d.RECID = dl.PARTY

 left join LOGISTICSPOSTALADDRESS l on  dl.LOCATION = l.LOCATION

left join LOGISTICSADDRESSCOUNTRYREGIONTRANSLATION lc on lc.COUNTRYREGIONID = l.COUNTRYREGIONID

where

 

lc.LANGUAGEID = 'EN-AU'

and Dl.ISPRIMARY = 1 and L.VALIDTO IN (select top 1 VALIDTO from LOGISTICSPOSTALADDRESS a 

where a.LOCATION = dl.LOCATION order by VALIDTO desc)

First highlighted command part at upper about Turkish collate problem, second for avoid duplicated records about different languages, third about get valid primary address.

-For periodic data transfer to cloud you have to install a gateway ( preferably at SQL server. There is a lesson about gateway at EDX.

-Don't forget customize your reports with Phone View button at Power BI desktop. This's easy but if you missed up this step your reports won't be very impressive at phone.

-Power BI can create a relation with one-by-one field. You can fix this issue by use merged column feature of Power BI. There is a lesson about merged columns at that EDX course.

-A lot of primary key value duplicates by different DataArea's at AX. This'll a trouble about  1:N relations. You can handle this too with method at upper. Sample for CustTable use AccountNum & DataAreaId and for normalized invoice table use CustAccount & DataAreaId...

AX - 2012 Send Purchase order report as email

Send Purchase order report (PurchPurchaseOrder) as mail:

    SrsReportRunController          controller = new SrsReportRunController();
    PurchPurchaseOrderContract      Contract = new PurchPurchaseOrderContract();
    SRSPrintDestinationSettings     printSettings;
    VendPurchOrderJour              orderJour;
    SrsReportEMailDataContract      emailContract = new SrsReportEMailDataContract();

   select firstonly orderJour
            order by PurchOrderDate Desc,CreatedDateTime Desc
            where orderJour.PurchId == "ST000181";

    emailContract.parmAttachmentFileFormat(SRSReportFileFormat::PDF);
    emailContract.parmSubject("Purchase Order");
    emailContract.parmTo("test@hotmail.com");
    
    
    controller.parmReportName(ssrsReportStr(PurchPurchaseOrder, Report));
    controller.parmShowDialog(false);
    Contract.parmRecordId(orderJour.RecId);
    controller.parmReportContract().parmRdpContract(Contract);

    printSettings = controller.parmReportContract().parmPrintSettings();
    printSettings.printMediumType(SRSPrintMediumType::Email);
    printSettings.fileFormat(SRSReportFileFormat::PDF);
    printSettings.parmEMailContract(emailContract);
    printSettings.overwriteFile(true);

    controller.runReport();

There is a disadvantage of method at upper; AX will use Outlook for send email. Alternate is below has another disadvantage; it will generate a .PDF file at disk.:

  PurchTable          purchTable;

    str                 body;

    PurchLine           purchLine;

    boolean             found;

    VendPurchOrderJour  orderJour;

    Filename            filename;

    SysMailer           mailer;

    SysEmailParameters  parameters;

      SrsReportRunController  controller = new SrsReportRunController();

    PurchPurchaseOrderContract  contract = new PurchPurchaseOrderContract();

SRSPrintDestinationSettings printSettings;    

    

    controller.parmReportName(ssrsReportStr(PurchPurchaseOrder,Report));

    controller.parmExecutionMode(SysOperationExecutionMode::Synchronous);

printSettings = controller.parmReportContract().parmPrintSettings();
printSettings.fileFormat(SRSReportFileFormat::PDF);
printSettings.printMediumType(SRSPrintMediumType::File);
printSettings.overwriteFile(true);


printSettings.fileName(@filename);

    filename = strFmt(@"%1%2.PDF",WinAPI::getTempPath(),"PO00001");

     select firstonly orderJour

            order by PurchOrderDate Desc,CreatedDateTime Desc

            where orderJour.PurchId == "PO00001"

    contract.parmRecordId(orderJour.RecId);

    controller.parmReportContract().parmRdpContract(contract);

    

    controller.parmShowDialog(false);

    controller.runReport();

    infolog.clear();

    

    new InteropPermission(InteropKind::ComInterop).assert();

    mailer = new SysMailer();

    parameters = SysEmailParameters::find();

    if (parameters.SMTPRelayServerName)

    {

        mailer.SMTPRelayServer(parameters.SMTPRelayServerName,

                            parameters.SMTPPortNumber,

                            parameters.SMTPUserName,

                            SysEmailParameters::password(),

                            parameters.NTLM);

    }

    else

    {

        mailer.SMTPRelayServer(parameters.SMTPServerIPAddress,

                            parameters.SMTPPortNumber,

                            parameters.SMTPUserName,

                            SysEmailParameters::password(),

                            parameters.NTLM);

    }

       

    body = strFmt(@"<br><caption>%1</caption><br><br>","Purchase order form is appended.");      

    

    mailer.fromAddress("sender@hotmail.com");

    mailer.tos().appendAddress("test@hotmail.com");

    mailer.htmlBody(body);

    mailer.subject(title);

    mailer.attachments().add(filename);

   

    mailer.sendMail();

AX 2012 - Exchange rate edit, calculate, convert currency amount to default amount

Find exchange rate:

ExchangeRateCurrencyPair    pair;

//TCMB : Exchange rate type (ExchangeRateType) tablle Name key field
select firstOnly pair
                    where pair.ExchangeRateType == ExchangeRateType::findByName("TCMB").RecId &&
                        pair.FromCurrencyCode == "USD" &&
                        pair.ToCurrencyCode == Ledger::accountingCurrency(CompanyInfo::current());


this.ExchangeRate = ExchangeRate::findByDate(pair.RecId,systemDateGet()).ExchangeRate;

Edit method for exchange rate entry ( There're a lot of samples at default forms like SalesTable ) :

public edit CurrencyExchangeRate editExchRate(boolean set, CurrencyExchangeRate _exchRate)
{
    ExchangeRateHelper exchangeRateHelper = ExchangeRateHelper::newCurrency(Ledger::primaryLedger(CompanyInfo::findDataArea(curext()).RecId), this.Currency);

    if (set)
    {
        this.ExchangeRate = exchangeRateHelper.prepareExchangeRateForStorage(_exchRate);
    }
    else
    {
        _exchRate = exchangeRateHelper.displayStoredExchangeRate(this.ExchangeRate);
    }

    return _exchRate;
}

Calculate default currency amount from exchange rate:
ExchangeRateHelper exchangeRateHelper = ExchangeRateHelper::newCurrency(Ledger::primaryLedger(CompanyInfo::findDataArea(curext()).RecId), Ledger::accountingCurrency(CompanyInfo::current()));
 CurrencyExchangeHelper  cur = CurrencyExchangeHelper::construct();
   
cur.parmLedgerRecId(Ledger::primaryLedger(CompanyInfo::current()));
cur.parmExchangeDate(today());
//If you want to find daily currency rate from system, delete next two lines 
 cur.parmExchangeRate1(exchangeRateHelper.prepareExchangeRateForStorage(1));
cur.parmExchangeRate2(this.ExchangeRate);
    this.BudgetAmountMST = cur.calculateTransactionToAccounting(this.Currency,this.BudgetAmount,true);

or short type of upper:

info(strFmt("%1", Currency::curAmount(100,"usd",today(),UnknownNoYes::Yes,500,100)));

    info(strFmt("%1", Currency::curAmount2CurAmount(100,"usd","try",today())));

AX 2012 - Change backcolor for Test/Dev/per company

Yo can write something like this to Run method of SysSetupFormRun class:

 this.design().colorScheme(FormColorScheme::RGB);
 this.design().backgroundColor(WinAPI::RGB2int(71,216,86));


or you can write a switch/case with curext() function.