Get a table ID in SQL - D365

Hi

select ID from SysTableIdView where SysTableIdView.Name = 'CustTable'

Regards,

Get enum value of a Enum in SQL- D365

 Hi,

SELECT

    et.Name AS enumIdName, 
    ev.EnumValue AS enumValue, 
    ev.Name AS enumValueName
FROM EnumIdTable et
INNER JOIN EnumValueTable ev 
    ON et.Id = ev.EnumId
WHERE et.Name = 'LedgerPostingType'
  AND ev.Name = 'PurchPckSlpPurchaseOffsetAccount';

Regards

SQL code to upate one Legal entity banner to all the legal entity in D365

 Hi,

update companyimage
set 

companyimage.Image  = companyimageA.Image 

from

 ( select Image from companyimage where dataAreaid = 'USFM' and imagetype = 1) as companyimageA 
where

 dataAreaid != 'USFM' and imagetype = 1

 

Regards

SQL _ Update statement using selection of same table or different table

 Hi,

update TableA

set TableA.Image  = TableB.Image from ( select Image from TableA where dataAreaid = 'USMF' and imagetype = 1) as TableB
where dataAreaid = 'USF1' and imagetype = 1

Regards,

 

X++ code to submit the pending vendor invoices

 Hi,

VendInvoiceHeaderWorkflow::submitToWorkflow(VendInvoiceInfoTable::find(541879755462212), "Submitted by Integration");

Regards,

Group By and Max(Date) in SQL / disticnt (GROUP BY with MAX(DATE) [duplicate] )

 Hi,

Train    Dest      Time

1        HK        10:00

1        SH        12:00

1        SZ        14:00

2        HK        13:00

2        SH        09:00

2        SZ        07:00

Expected output

Train    Dest     Time

1            SZ      14:00 

2           HK      13:00

Distinct ( max(AccountingDate) and group by (SubLedgerVoucherDataAreaID) from GeneralJournalEntry )

 distinct (GROUP BY with MAX(DATE) [duplicate] )

/**************************/

SELECT GJE.SubLedgerVoucherDataAreaID, MAX(GJE.AccountingDate) as MaxDate

FROM GENERALJOURNALENTRY GJE

GROUP BY GJE.SubLedgerVoucherDataAreaID

Thanks,

Iterate the container in D365 / AX 2012

 Hi,

static void HandlingContainerJob(Args _args)
{
    container contain;
    int i;
    str containstr;
    ;
    contain = ["DataAt1stPosition","DataAt2ndPosition"]; //Data will be inserted at position 1 and 2
    contain = Conins(contain,3,"DataAt3rdtPosition","DataAt4thPosition"); // from 3rd position onward
    contain = conins(contain,5,"DataAt5thPosition"); // now the data inserted in 5th position
    //or use this technique
 
    info (strFmt("Length of the container: %1", conLen(contain)));
    for (i=1; i <= conLen(contain); i++)
    {
        info (strFmt("container value %1 is %2", i, conPeek(contain, i)));
    }

}

How to invoke and iterate List as Contract methods in AX 2012/ D365

Hi,

Public void performContractIterate(ClassContainsListAsContract    _ListCarryClass)
{
List contractFieldList = new List(Types::Class);
ResResourceCategoryDataContract    resourceCategoryDataContract =  new ResResourceCategoryDataContract();
contractFieldList = _ListCarryClass.parmCustomFields();
/*
// List Iterator are not recorded for mutiTier data flow example integrations with other Apps like PowerApps or APIs
ListIterator        literator = new ListIterator(contractFieldList );
while (literator.more())
{
resourceCategoryDataContract= literator.value();
if(resourceCategoryDataContract.parmId() == 1 )
{
resourceCategoryId = resourceCategoryDataContract.parmValue();
}
literator.next();
}
*/
//Traverse the same _ListCarryClass using an enumerator
ListEnumerator   enumerator = contractFieldList .getEnumerator();
while(enumerator.moveNext())
{
resourceCategoryDataContract= enumerator.current();
switch (resourceCategoryDataContract.parmId())
{
case 1:
resourceCategoryId = resourceCategoryDataContract.parmValue();
break;
}
}
//<Perform your action based on the iterator value fetched>
}

_________________________________________________________________________________

Similar example.

DimensionValueService service = new DimensionValueService();
DimensionContract dimensionContract = new DimensionContract();
DimensionValueContract dimensionValueContract;
DimensionValue dimensionValue;
List dimensionValueContractList;
ListIterator contractorIterator;
 
;
  
dimensionContract.parmDimensionName(#DimName);
dimensionValueContractList = service.getDimensionValues(dimensionContract);
contractorIterator = new ListIterator(dimensionValueContractList);
ListEnumerator contractorEnumerator = dimensionValueContractList.getEnumerator();
    
    while(contractorEnumerator.moveNext())
    {
        dimensionValueContract = contractorEnumerator.current();
        dimensionValue = dimensionValueContract.parmValue();
        {
            info(strFmt("%1", dimensionValue));
        }
    }

Regards,

How find size of recordsortedlist in D365/AX 2012

Hi,

This is the continuity of the previous article where we are now getting the size of recordsortedlist .

if(recordsortedlist.len() >1)
{
    print("Given recordsortedlist is bigger than 1");
}

Thanks,

How find size of List in D365/AX 2012

Hi,

public int listLengthCounter(List list)
{
Enumerator enumerator;
int length = 0;
;
enumerator = list.getEnumerator();
while(enumerator.moveNext())
{
length++;
}
return length;
}

But List has such a direct funtion

 if (list.elements() > 1) 

    { 

        print "It has has more than 1 elements"; 

    } 

Thanks

FileNameSplit() to slip the Directory, file name and extension in D365 FnO

 Hi,

    /// <summary>
    /// Validate the Fileformat
    /// </summary>
    /// <param name = "filepath">FilePath</param>
    /// <returns>Boolean</returns>
    public boolean validateFileExtension(str filepath)
    {
        str         actualFileName, fileExtension, actualFilePath;
        boolean     ok = true;
 
        [actualFilePath,actualFileName, fileExtension] = fileNameSplit(filePath);
 
        ok = strScan(actualFileName,allowedFileNameFormat,1,strLen(actualFileName));
        if(!ok)
        {
            Error("@SUNR:FileDoesntExisit");
        }
 
        return ok;
    }

Regards,

Dimension Lookup for both standard and Custom dimension in D365

Hi,

 /// <summary>

    /// Provides the look-up interaction for the individual dimension value lookup controls.

    /// </summary>

    /// <param name="_dimensionValueControl">

    /// The <c>FormStringControl</c> enumeration value that triggers this event.

    /// </param>

    /// <param name="_localizedName">

    /// The localized dimension attribute name

    /// </param>

    /// <param name="_promptErrorMessage">

    /// Whether prompt the error message if the dimension type is empty.

    /// </param>

    public static void dimensionValueLookup(

        FormStringControl   _dimensionValueControl,

        Name                _localizedName,

        boolean             _promptErrorMessage = false)

    {

        Query                   query = new Query();

        SysTableLookup          sysTableLookup;

        QueryBuildDataSource    qbds;

        DimensionAttribute      dimensionAttribute;

        RecId                   dimensionAttributeId;

        DataAreaId              dataAreaId = curext();

        if (_localizedName)

        {

            dimensionAttribute      = DimensionAttribute::findByLocalizedName(_localizedName, false, currentUserLanguage());

            dimensionAttributeId    = dimensionAttribute.RecId;

        }

        if (dimensionAttributeId)

        {

            sysTableLookup = SysTableLookup::newParameters(DimensionCache::instance().dimensionAttributeBackingTable(dimensionAttributeId), _dimensionValueControl);

            sysTableLookup.addLookupfield(dimensionAttribute.ValueAttribute);

            LedgerDimensionTranslationLookupHelper::addLookupTranslation(sysTableLookup, dimensionAttributeId);

            sysTableLookup.addSelectionField(dimensionAttribute.NameAttribute);

            changeCompany(dataAreaId)

            {

                qbds = query.addDataSource(DimensionCache::instance().dimensionAttributeBackingTable(dimensionAttributeId));

                qbds.addOrderByField(DimensionCache::instance().dimensionAttributeValueField(dimensionAttributeId));

                DimensionAttribute::restrictQueryToCategorizedValues(qbds, dimensionAttributeId);

            }

            sysTableLookup.parmQuery(query);

            sysTableLookup.performFormLookup();

        }

        else if (_promptErrorMessage)

        {

            //Please choose a value for "Dimension type" first!

            checkFailed("@GLS100015");

        }

    }

Regards

How to get enumValue from enumID in D365 (using SQl)

 Hi,

SELECT ENUMVALUETABLE.ENUMID, ENUMVALUETABLE.ENUMVALUE, ENUMVALUETABLE.NAME 

FROM ENUMVALUETABLE

INNER JOIN ENUMIDTABLE ON ENUMIDTABLE.ID = ENUMVALUETABLE.ENUMID

where ENUMIDTABLE.NAME = 'ProjOrigin'

Regards,

How to add 9 years to Transdate in AX 2012 / D365

 Hi,

dateMthFwd( _projTable.StartDate, 12*9) ;

Thanks,

Auto submit of any process to Workflow in D365 / X++

 Hi,

        try
        {
            ttsbegin;
                        
            Workflow::activateFromWorkflowType( workFlowTypeStr( "workFlowTypeName"),  TableName.RecId, "Auto submitted by action Alfa", NoYes::No);
            
            ttscommit;
        }
        catch(exception::Error)
        {
            throw Error("Not submitted");
        }

///             _workflowCorrelationId = Workflow::activateFromWorkflowType( _workflowTypeName, _recId, "Comments", NoYes::No);

             

             IssueStateChangeManager::submit(TableBuffer);

Regards,

Auto Submit the pending vendor Invoice to workflow in D365

 /// <summary>
    /// Post open inventory journals
    /// </summary>
    public void run()
    {
        TradeLineRefId       headerReference;
        VendInvoiceInfoTable vendInvoiceInfoTable;
     
        //vendInvoiceInfoTable = VendInvoiceInfoTable::findTableRefId(headerReference);
 
        while (gQueryRun.next())
        {
            vendInvoiceInfoTable    = gQueryRun.get(tableNum(VendInvoiceInfoTable));
            try
            {
                if(this.validationWorkflowSubmit(vendInvoiceInfoTable))
                {
                    this.SubmitVendorInvoice(vendInvoiceInfoTable);
                }
            }
            
            catch (Exception::CLRError)
            {
                System.Exception interopException = CLRInterop::getLastException();
                error(strFmt("%1", interopException));
            }
            catch (Exception::Error)
            {
                error(strFmt("An error occured during the update."));
            }
        }
    }

    /// <summary>
    /// Validates if the workflow can be started.
    /// </summary>
    /// <returns>true if workflow can start</returns>
    private boolean validationWorkflowSubmit(vendInvoiceInfoTable   _vendInvoiceInfoTable)
    {
        // Don't submit if a posted invoice with the same invoice number already exists.
        if (!this.validateDuplicateInvoice(_vendInvoiceInfoTable))
        {
            throw Exception::error;
        }
                
        if (PublicSectorUtils::isBudgetReservationEnabled())
        {
            VendInvoiceInfoTable::checkBudgetReservationBalance_PSN(_vendInvoiceInfoTable.TableRefId, _vendInvoiceInfoTable.SourceDocumentHeader);
        }
        if (isConfigurationkeyEnabled(configurationKeyNum(Project)) && ProjFundingLimitTrackingManager::hasLimitErrorSourceDocument(_vendInvoiceInfoTable.SourceDocumentHeader))
        {
            throw error("@SYS4110061");
        }
        boolean areAccountingDistributionsValid;
        VendParameters vendParameters = VendParameters::find();
        if((Dynamics.AX.Application.FeatureStateProvider::isFeatureEnabled( AccountingDistributionWorkflowSubmissionFeature::instance()) && vendParameters.BypassValidationOfAccountingDistributions == NoYes::Yes)
            || SourceDocumentProvider::areSourceDocAccDistAccountValid( SourceDocumentHeader::find( _vendInvoiceInfoTable.SourceDocumentHeader), true, true))
        {
            areAccountingDistributionsValid = true;
        }
        if (!areAccountingDistributionsValid)
        {
            return false;
        }
        return true;
    }

    /// <summary>
    /// Determines if a posted invoice with the same invoice number already exists.
    /// </summary>
    /// <returns>
    /// True if a posted invoice with the same invoice number does not exist. False if already exists.
    /// </returns>
    private boolean validateDuplicateInvoice(VendInvoiceInfoTable  _vendInvoiceInfoTable)
    {
        boolean isInvoiceNumberFreeForUse = true;
        if (Dynamics.AX.Application.FeatureStateProvider::isFeatureEnabled( VendInvoiceRejectDuplicateNumOnWorkflowSubmitFeature::instance()))
        {
            VendTable localVendTable = VendTable::find(_vendInvoiceInfoTable.InvoiceAccount);
            if (!localVendTable.checkInvoice(_vendInvoiceInfoTable.Num, _vendInvoiceInfoTable.TransDate))
            {
                isInvoiceNumberFreeForUse = false;
            }
        }
        return isInvoiceNumberFreeForUse;
    }
    /// <summary>
    /// Creates the vendor invoice.
    /// </summary>
    public void SubmitVendorInvoice(VendInvoiceInfoTable  _vendInvoiceInfoTable)
    {

        workflowComment        workflowComment = "Auto submission to workflow.";
        //changecompany (custInvoice.ProjIntercompany)
        //{
        if (_vendInvoiceInfoTable.RecId
                && _vendInvoiceInfoTable.RequestStatus == VendInvoiceRequestStatus::Draft)
               // && custTable.interCompanyTradingPartner().interCompanyTradingRelation().Active
                //&& this.validationWorkflowSubmit())
            {
                VendInvoiceHeaderWorkflow::submitToWorkflow( _vendInvoiceInfoTable, workflowComment);
            }
        //}
    }

CancelSuperCall in D365

 Hi,

Usually, based on the FormControlEventArgs e, the cancellation can be implemented using the following code snippets:

FormControlCancelableSuperEventArgs     eventArgs = e as FormControlCancelableSuperEventArgs;

eventArgs.CancelSuperCall();

In cases where there are no FormControlEventArgs, you are required to throw an error to halt the super process.

Thanks,

BP Rule: [BPUpgradeCodeToday]:Method today has been deprecated

 Hi,

DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone())

Regards,

Resource to worker relation in D365

 Hi,

ResourceView::find(this.Resource).Worker

Reagrds

Code to call the caller form refresh in D365

 Hi,

can use the below code snippets in Controller or any other class called from

        #Task

        FormRun formRun;

        formRun = element.args().caller(); //  args.caller();

        if(formRun)

               formRun.task(#taskF5);

   

Regards,