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");

    }