To select the nth row in a SQL database table?

Query:

SELECT * FROM

    (

        SELECT ROW_NUMBER () OVER (ORDER BY RecId) AS RowNum, *   FROM SALESTABLE

    ) sub

WHERE RowNum = 9

Database import failure from SANDBOX to DEV VM in D365FO

 Error: " Changes to connection setting default values were incorporated in a recent release. Error importing database: Could not import package."

Command:

SqlPackage.exe /a:import /sf:J:\MSSQL_BACKUP\AXDBSIT_19122022_backup.bacpac /tsn:localhost /tdn:AxDB_SIT_19122022 /p:CommandTimeout=1200

Cause: Connection Security Improvements in SqlPackage 161 - Microsoft Community Hub 

Fix (Solution):

SqlPackage.exe /a:import /sf:J:\MSSQL_BACKUP\AXDBSIT_19122022_backup.bacpac /tsn:localhost /tdn:AxDB_SIT_19122022 /p:CommandTimeout=1200 /TargetEncryptConnection:False

Compare buffer value (source and orig) in D365FO X++

 static server boolean CompareBuffers(Common Buffer1,Common Buffer2,boolean IncludeSystemFields = true)

    {

        Boolean retBool = false;

        DictTable tbl;

        DictField fld;

        int fldCnt;

        int fldIdx;

        int TblId;

        int fldId;

        /*

        if(!Buffer1 || ! Buffer2)

        {

            info("Atleast 1 buffer is null");

            return retBool;

        }

        */

        if(!(Buffer1.TableId > 0) || (Buffer1.TableId != Buffer2.TableId))

        {

            info("The Tale buffers are not Valid");

        }

       tblId = buffer1.TableId;

       tbl = new DictTable(tblId);

       fldCnt = tbl.fieldCnt();

        for(fldIdx = 1; fldIdx <= fldCnt; fldIdx++)

        {

            fldId = tbl.fieldCnt2Id(fldIdx);

           fld = new DictField(TblId,fldId);

           if(!fld.isSystem() || InCludeSystemFields)

           {

                if(buffer1.(fldId) != buffer2.(fldId))

                {

                    info(strfmt("The Field %1 is different. buffer1.%1 = %2 , Buffer2.%1 = %3",FieldId2Name(tbl.id(),fldId),buffer1.(fldId),buffer2.(fldId)));

                    return retBool;

                }

           }

        }

        retbool = true;

        return retbool;

    }

Delete Recurring Batch Job in D365FO X++

 public static void deleteRecurringBatchJob(ClassName _className, str _errorMessage)

    {

        Batch batch;

        BatchHistory batchHistory;

        BatchJob batchJob;

        BatchJobHistory batchJobHistory;

        select firstonly forupdate  from batchJob

            join forupdate from batch

                where batchJob.RecId == batch.BatchJobId

                    && batch.ClassNumber == className2Id(_className);

        if (batch.statusClass().canDelete()

            && batchJob.Status != BatchStatus::Executing

            && batchJob.Status != BatchStatus::Scheduled

            && batchJob.Status != BatchStatus::Cancelling)

        {

            ttsbegin;

            delete_from batchHistory

                where batchHistory.BatchJobId == batchJob.RecId;

            delete_from batchJobHistory

                where batchJobHistory.BatchJobId == batchJob.RecId;

            batch.delete();

            batchJob.delete();

            ttscommit;

        }

        else

        {

            throw error(_errorMessage);

        }

    }

Get resource related file name in D365FO

  public static str getResourceFilename(str _resourceName)

    {

        resourceNode resource = new resourceNode(_resourceName);

        return _resourceName && resource ? resource.filename() : null;

    }

Loop all tables and get details table hasNaturalKey or not in D365FO X++

 public static boolean hasNaturalKey(TableId _tableId)

    {

        SysDictTable    dictTable;

        DictIndex       dictIndex;

        boolean         hasNaturalKey;

        TableId         currentTableId;

        currentTableId = _tableId;

        do

        {

            dictTable = new SysDictTable(currentTableId);

            dictIndex = dictTable.indexObject(dictTable.replacementKey());

            hasNaturalKey = (dictIndex != null) && (dictIndex.numberOfFields() > 0);

            currentTableId = dictTable.supportInheritance() ? dictTable.extends() : 0;

        }

        while (!hasNaturalKey && currentTableId != 0);

        return hasNaturalKey;

    }

Mask value in D365FO X++

 public str mask(str _element)

    {

        str result;

int maskLength = 8;

int elementLength = strLen(_element);

            int elementLenghtWillBeMasked = elementLength - maskLength;

            str elementValueKept = strDel(_element, 0, elementLenghtWillBeMasked);

            str elementValueMasked;

            while (elementLenghtWillBeMasked > 0)

            {

                elementValueMasked = elementValueMasked + 'X';

                elementLenghtWillBeMasked--;

            }

            result = elementValueMasked + elementValueKept;

        return result;

    }

Unzip files from shared URL and get inside files stream in D365FO X++

 /// <summary>

    /// Extract files from zip file and upload.

    public static List getUnzippedFiles(SharedServiceUnitURL _uploadFileURL, container _fileSuffixes)

    {

        #File

        System.IO.Compression.ZipArchive zipArchive =

            new System.IO.Compression.ZipArchive(File::UseFileFromURL(_uploadFileURL), System.IO.Compression.ZipArchiveMode::Read);

        CLRObject archiveEntries =  zipArchive.get_Entries();

        int length = archiveEntries.get_Count();

        List uploadUrlList = new List(types::Container);

        container consUnzippedFile;

        int i;

        for (i = 0; i < length; i++)

        {

            System.IO.Compression.ZipArchiveEntry item = archiveEntries.get_Item(i);

            List fileNameComponents = strSplit(item.Name, #FileExtensionDelimiter);

            ListIterator iterator = new ListIterator(fileNameComponents);

            str fileSuffix;

            while (iterator.more())

            {

                fileSuffix = iterator.value();

                iterator.next();

            }

            if (conLen(_fileSuffixes) == 0 || conFind(_fileSuffixes, fileSuffix) > 0)

            {

                using(System.IO.MemoryStream stream = new System.IO.MemoryStream())

                {

                    item.Open().CopyTo(stream);

                    str fileID = BankStatementImportUtils::sendFileToTempStore(stream, item.Name).getFileId();

                    consUnzippedFile = [fileID, item.Name];

                    uploadUrlList.addEnd(consUnzippedFile);

                }

            }

        }

        return uploadUrlList;

    }

Method reference to add entity dynamically post deployment process in D365FO X++

 public static void addEntityToDefinitionGroup(

        DMFDefinitionGroupName _definitionGroupName,

        DMFEntityName _entityName,

        FieldGroupName _fieldList = "@SYS6047",

        DMFSourceName _exportTypeSourceName = DefaultExportTypeSourceName,

        boolean _enableChangeTracking = false)

    {

        DMFRefreshType refreshType;

        if (_enableChangeTracking && DataManagementIntegrationUtils::canEntityUseIncrementalPush(_entityName))

        {

            refreshType = DMFRefreshType::IncrementalPush;

        }

        else

        {

            refreshType = DMFRefreshType::FullPush;

        }

        if (DMFDefinitionGroup::find(_definitionGroupName))

        {

            if (!DMFDefinitionGroupEntity::find(_definitionGroupName, _entityName))

            {

                PersonnelIntegrationUtils::getEventSource().addEntityStart(PersonnelIntegrationUtils::getEnvironmentNamespace(), DataManagementIntegrationUtils::sanitizeDefinitionGroup(_definitionGroupName), _entityName);

                SysInfologLevel infologLevel = infolog.infologLevel();

                try

                {

                    if (refreshType == DMFRefreshType::IncrementalPush)

                    {

                        // Ensure entity is set for delta processing

                        DataManagementIntegrationUtils::enableChangeTrackingOnEntity(_entityName);

                    }

                    try

                    {

                        // The DMF framework logs a lot of infolog messages, to suppress these for now.

                        infolog.setInfoLogLevel(SysInfologLevel::None);

                        DMFEntityBase::addEntityForProcessingV3(_definitionGroupName,

                            _definitionGroupName,

                            _entityName,

                            _exportTypeSourceName,

                            '',

                            _fieldList,

                            true,

                            true,

                            '',

                            false,

                            refreshType,

                            false);

                    }

                    finally

                    {

                        infolog.setInfoLogLevel(infologLevel);

                    }

                }

                catch

                {

                    PersonnelIntegrationUtils::getEventSource().addEntityException(PersonnelIntegrationUtils::getEnvironmentNamespace(),

                                                                                   DataManagementIntegrationUtils::sanitizeDefinitionGroup(_definitionGroupName),

                                                                                   _entityName,

                                                                                   '');

                    exceptionTextFallThrough();

                }

                PersonnelIntegrationUtils::getEventSource().addEntityEnd(PersonnelIntegrationUtils::getEnvironmentNamespace(), DataManagementIntegrationUtils::sanitizeDefinitionGroup(_definitionGroupName), _entityName);

            }

        }

        else

        {

            error(strFmt("@SYS62688", _definitionGroupName));

        }

    }

Get current date EOD in D365FO X++

//It can also changed to COMPANY TIMEZONES 

public static utcDateTime endOfDay(utcDateTime _dateTime)

    {

        Timezone userTimeZone = DateTimeUtil::getUserPreferredTimeZone();

        utcDateTime dateInTimeZone = DateTimeUtil::applyTimeZoneOffset(_dateTime, userTimeZone);

        return DateTimeUtil::newDateTime(DateTimeUtil::date(dateInTimeZone), timeMax(), userTimeZone);

    }