Update to my old Blog post Functions to use in Queries, Filters

There are many new functions that can be used in Queries. In the code they are marked with [QueryRangeFunction()] or [QueryRangeFunctionAttribute()]

see https://calafell.me/the-utility-of-query-range-functions-dynamics-365-for-finance-operations-supply-chain-management/ for a comprehensive list and a script to compile a new list your current list.

Use by providing the full class and method e.g. UserExternalQueryRangeFunction::currentUserExternalParty()

Current list as of 10.0.22

• str SysQueryRangeUtilDMF::greaterThanLastActivityStartedDateTime()
• str SysQueryRangeUtilDMF::greaterThanLastActivityCompletedDateTime()
• str SysQueryRangeUtilDMF::greaterThanLastSuccessfulExecutionDateTime()
• str SysQueryRangeUtilDMF::greaterThanLastExecutionDateTime()
• str SysQueryRangeUtilDMF::notExportableUser()
• RecId UserExternalQueryRangeFunction::currentUserExternalParty()
• date SysQueryRangeUtil::currentDate()
• str SysQueryRangeUtil::currentLanguageId()
• str SysQueryRangeUtil::currentSessionDate()
• str SysQueryRangeUtil::currentSessionDateTime()
• str SysQueryRangeUtil::currentUserId()
• str SysQueryRangeUtil::anyUserIdNotCurrent()
• str SysQueryRangeUtil::currentUserLanguage()
• str SysQueryRangeUtil::dateRange(date _startDate, date _endDate)
• str SysQueryRangeUtil::day(int relativeDays = 0)
• str SysQueryRangeUtil::dayRange(int relativeDaysFrom = 0, int relativeDaysTo = 0)
• str SysQueryRangeUtil::greaterThanDate(int relativeDays = 0)
• str SysQueryRangeUtil::greaterThanUtcDate(int relativeDays = 0)
• str SysQueryRangeUtil::greaterThanUtcNow()
• str SysQueryRangeUtil::lessThanDate(int relativeDays = 0)
• str SysQueryRangeUtil::lessThanUtcDate(int relativeDays = 0)
• str SysQueryRangeUtil::lessThanUtcNow()
• str SysQueryRangeUtil::monthRange(int relativeMonthsFrom = 0, int relativeMonthsTo = 0)
• str SysQueryRangeUtil::yearRange(int relativeYearsFrom = 0, int relativeYearsTo = 0)
• str SysQueryRangeUtil::value(anytype a)
• str SysQueryRangeUtil::valueEmptyString()
• str SysQueryRangeUtil::valueUnlimited()
• SelectableDataArea SysQueryRangeUtil::currentCompany()
• utcdatetime SysQueryRangeUtil::getCurrentSessionDateTime()
• str SysQueryRangeUtil::literalFilterValue(str nonLiteralFilterValue)
• utcdatetime SysQueryRangeUtil::dateTimeMinValue()
• str AgreementClassificationEntityHelper::salesAgreementClassificationRangeConfiguration()
• str AgreementClassificationEntityHelper::purchAgreementClassificationRangeConfiguration()
• str AgreementClassificationEntityHelper::agreementClassificationRangeConfiguration()
• str AgreementConfirmationLineEntityHelper::assignLinesToHeadersByDateEffectiveness_ SalesAgreementConfirmation()
• str AgreementConfirmationLineEntityHelper::assignLinesToHeadersByDateEffectiveness_ PurchaseAgreementConfirmation()
• RefRecId CompanyInfoHelper::currentLegalEntity()
• str CustCustomerExternalCodeClassEntityHelper::queryRangeExpressionCustTableTableId()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeConfiguration()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeSize()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeColor()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeStyle()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeVersion()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeSite()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeWarehouse()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeLocation()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeStatus()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeLicensePlate()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeBatch()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeSerial()
• str EcoResDimensionGroupEntityHelper::productDimensionRangeInventoryOwner()
• str EcoResDimensionGroupEntityHelper_RU::productDimensionRangeInventoryProfile()
• str EcoResDimensionGroupEntityHelper_RU::productDimensionRangeGTD()
• str EcoResProductDimensionQueryRangeHelper::productDimensionRangeColor(DataSourceName _dataSource1Name, DataSourceName _d
• ataSource2Name, boolean _allowEmptyDimension = true)
• str EcoResProductDimensionQueryRangeHelper::productDimensionRangeSize(DataSourceName _dataSource1Name, DataSourceName _da
• taSource2Name, boolean _allowEmptyDimension = true)
• str EcoResProductDimensionQueryRangeHelper::productDimensionRangeStyle(DataSourceName _dataSource1Name, DataSourceName _d
• ataSource2Name, boolean _allowEmptyDimension = true)
• str EcoResProductDimensionQueryRangeHelper::productDimensionRangeConfiguration(DataSourceName _dataSource1Name, DataSourc
• eName _dataSource2Name, boolean _allowEmptyDimension = true)
• str EcoResProductDimensionQueryRangeHelper::productDimensionRangeVersion(DataSourceName _dataSource1Name, DataSourceName
• _dataSource2Name, boolean _allowEmptyDimension = true)
• str EcoResProductEntityHelper::queryRangeExpressionNotVariant()
• str EcoResProductMasterDimensionEntityHelper::productDimensionColorTableId()
• str EcoResProductMasterDimensionEntityHelper::productDimensionConfigurationTableId()
• str EcoResProductMasterDimensionEntityHelper::productDimensionSizeTableId()
• str EcoResProductMasterDimensionEntityHelper::productDimensionStyleTableId()
• str EcoResProductMasterDimensionEntityHelper::productDimensionVersionTableId()
• str EcoResReleasedProductExternalCodeClassEntityHelper::queryRangeExpressionInventTableTableId()
• str EcoResReleasedProductVariantExternalCodeClassEntityHelper::queryRangeExpressionInventDimCombinationTableId()
• str FormLetterSortingParametersEntityHelper::procurementTableRangeValues()
• str FormLetterSortingParametersEntityHelper::salesTableRangeValues()
• str IntrastatEntityHelper::intrastatCommodityCodeEntityRangeDataAreaId()
• str InventInventoryDimensionsParametersEntityHelper::tableNumInventDim()
• str InventInventoryDimensionsParametersEntityHelper::includedFieldIds()
• str InventLedgerPostingDefinitionEntityHelper::inventAccountTypeInventFilter()
• str InventLedgerPostingDefinitionEntityHelper::inventAccountTypeProdFilter()
• str InventLedgerPostingDefinitionEntityHelper::inventAccountTypePurchFilter()
• str InventLedgerPostingDefinitionEntityHelper::inventAccountTypeSalesFilter()
• str InventLedgerPostingDefinitionEntityHelper::inventAccountTypeStdCostVarianceFilter()
• FieldId InventSysQueryRangeUtil::inventBatchIdFieldNum()
• FieldId InventSysQueryRangeUtil::wMSLocationIdFieldNum()
• str PmfSysQueryRun::pmfInventTableIsBOMFormula()
• str ProdStatusSysQueryRangeUtil::prodStatusBetweenScheduledAndStartedUp()
• str ProdStatusSysQueryRangeUtil::prodStatusBetweenScheduledAndReportedFinished()
• str ProdStatusSysQueryRangeUtil::prodStatusBetweenCostEstimatedAndStartedUp()
• str ProdStatusSysQueryRangeUtil::prodStatusBetweenStartedUpAndReportedFinished()
• str ProdStatusSysQueryRangeUtil::prodStatusBetweenCostEstimatedAndReportedFinished()
• str ProdStatusSysQueryRangeUtil::prodStatusBetweenCreatedAndStartedUp()
• str ProdStatusSysQueryRangeUtil::prodStatusBetweenCreatedAndReportedFinished()
• str PurchaseRequisitionEntityHelper::getPurchaseRequsitionHeaders()
• str PurchPurchaseOrderUpdateLinesParametersEntityHelper::tableNumPurchTable()
• str PurchPurchaseOrderUpdateLinesParametersEntityHelper::includedFieldIds()
• str PurchPurchasingPolicyEntityHelper::purchasingPolicyType()
• str PurchPurchasingPolicyEntityHelper::purchReqLineFilterQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchaseRequisitionProcurementCatalogPolicyRuleQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchaseRequisitionConsumptionCategoryAccessPolicyRuleQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchaseCategoryPolicyRuleQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchaseRequistionControlRFQPolicyRuleQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchaseRequistionConsumptionControlPolicyRuleQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchaseRequistionPurposePolicyRuleQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchaseRequistionReplenishmentCategoryAccessPolicyRuleQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchaseRequistionReplenishmentControlPolicyRuleQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchaseOrderCreationAndDemandConsolidationPolicyRuleQueryRangeValue()
• str PurchPurchasingPolicyEntityHelper::purchReapprovalPolicyRuleQueryRangeValue()
• str ReqDemPlanHistoricalExternalDemandEntityHelper::queryRangeVersionForecastedExternally()
• str ReqDemPlanHistoricalExternalDemandEntityHelper::queryRangeDataSourceOriginForecastedExternally()
• str ReqRoutePlannedOrderRouteOperationEntityHelper::wrkCtrDateFromEffectiveness()
• str ReqRoutePlannedOrderRouteOperationEntityHelper::wrkCtrDateToEffectiveness()
• str RetailPeriodicDiscountQueriesHelper::getDiscountOfferTypes()
• str RetailPeriodicDiscountQueriesHelper::getPriceOfferTypes()
• str RetailProductWarehouseInventoryQueryHelper::getReservationHierarchyFieldIds()
• str SalesOrderUpdateLinesParametersEntityHelper::tableNumSalesTable()
• str SalesOrderUpdateLinesParametersEntityHelper::includedFieldIds()
• str SalesQueryRangeUtil::salesStatusCanBeInvoiced()
• str SalesQueryRangeUtil::salesTypeCustomerDemand()
• str SysQueryRangeUtilProject::projActiveAll(ProjActiveAll _projActiveAll)
• str SysQueryRangeUtilProject::projLevels(ProjLevels _projLevels)
• str SysQueryRangeUtil_Extension::validToMaxValueRange()
• str SysQueryRangeUtil_Extension::validToMaxValueRangeDate()
• str TaxCodeExternalCodeClassEntityHelper::queryRangeExpressionTaxTableTableId()
• str TMSTransportationRouteScheduleEntityHelper::getRoutePlan()
• str TrvExpenseTransHelper::currentLegalEntity()
• str TSQueryRangeUtil::validTimesheetFrequency()
• TableId TSTimesheetHelper::getTSTimesheetTableId()
• str VendVendorExternalCodeClassEntityHelper::queryRangeExpressionVendTableTableId()
• str WHSCustomerGroupProductFilterEntityHelper::getCustomerGroups()
• str WHSCustomerProductFilterEntityHelper::getCustomers()
• str WHSGenerallyAvailableProductFilterEntityHelper::getGenerallyAvailableProducts()
• str WHSProductFilterGroupDetailEntityHelper::getProductFilterGroupsQueryRange()
• str WHSQueryHelper_BR::getRefTableIdsFromWhsFiscalDocument()
• str WHSReleaseLoadToWarehouseSysQueryRangeUtil::loadStatus()
• str WHSSysQueryRangeUtil::workStatusOpenOrInProcess()
• str WHSSysQueryRangeUtil::no()
• str WHSVendorGroupProductFilterEntityHelper::getVendorGroups()
• str WHSVendorProductFilterEntityHelper::getVendors()
• str WMSQueryRangeUtil::shipmentsInProgress()
• TableId FinancialReportingGeneralJournalReversals::tableName2Id(str _tableName)
• str AssetWorkspaceQueryHelper::currentFiscalYearRange()
• str BudgetPlanningWorkspaceQueryRangeHelper::budgetPlanningProcess()
• str BudgetPlanningWorkspaceQueryRangeHelper::currentDate()
• str BudgetPlanningWorkspaceQueryRangeHelper::emptyValue()
• str BudgetTrackingWorkspaceQueryRangeHelper::overThreshold()
• str BudgetTrackingWorkspaceQueryRangeHelper::underThreshold()
• str BudgetTrackingWorkspaceQueryRangeHelper::budgetPlanningProcess()
• str BudgetTrackingWorkspaceQueryRangeHelper::currentLedger()
• str DirRelationshipTypeHelper::getUserDefinedRelationshipTypeRange(DataSourceName _dataSourceName = ”, FieldName _system
• TypeFieldName = fieldStr(DirRelationshipTypeTable, SystemType)
• boolean DirUtility::currentRoleHasAccessToPrivate(boolean _useCache = true)
• DirPartyRecId DirUtility::currentParty()
• boolean DirPartyLocation::currentRoleHasAccessToPrivate(boolean _useCache = true)
• DirPartyRecId DirPartyTable::currentParty()
• str DocQueryRangeUtil::isRunningInBatch()
• str DocQueryRangeUtil::isNotRunningInBatch()
• str DOMConfigurationManager::notUtcNull()
• str DOMConfigurationManager::domBatchTimeGreaterThanBatchTime()
• str DOMConfigurationManager::getDOMClassId()
• str DOMQueryHelper::getQueryNotValueForExcludedModesOfDelivery()
• str EngChgTools::productDimensionRangeVersion()
• str EntAssetSysQueryRangeUtil::greaterThanUtcDateCompanyTimezone(int relativeDays = 0)
• RecId HcmWorkerLookup::currentPerson()
• HcmWorkerRecId HcmWorkerLookup::currentWorker()
• boolean EssPersonIdentificationNumberRangeUtil::skipSelfServiceReadPolicy()
• boolean EssPersonIdentificationNumberRangeUtil::skipSelfServiceInsertUpdateDeletePolicy()
• RefRecId HcmCompanyInfoHelper::currentLegalEntity()
• boolean HcmPersonIdentificationNumberRangeUtil::skipSelfServiceReadPolicy()
• boolean HcmPersonIdentificationNumberRangeUtil::skipSelfServiceInsertUpdateDeletePolicy()
• str HcmQueryRangeUtil::getAotNameHcmHumanResourceManager()
• str HcmQueryRangeUtil::getAotNameHcmManager()
• HcmPositionHierarchyTypeRecId HcmQueryRangeUtil::findPositionHierarchyLineType()
• str HcmQueryRangeUtil::lessThanCompanyDate(int _relativeDays = 0)
• str HcmQueryRangeUtil::greaterThanCompanyDate(int _relativeDays = 0)
• str HcmQueryRangeUtil::dateTimeRangeTodayToEndOfYearInCompanyTimezone()
• str HcmQueryRangeUtil::currentWorkerPositionRange()
• str HcmQueryRangeUtil::greaterThanOrEqualToUtcNow()
• str HcmQueryRangeUtil::lessThanOrEqualToUtcNow()
• boolean HcmWorkerActionHireRangeUtil::skipSelfServiceReadPolicy()
• boolean HcmWorkerActionHireRangeUtil::skipSelfServiceInsertUpdateDeletePolicy()
• boolean HcmWorkerBankAccountRangeUtil::skipSelfServiceReadPolicy()
• boolean HcmWorkerBankAccountRangeUtil::skipSelfServiceInsertUpdateDeletePolicy()
• str MssExitingWorkerQueryRangeUtil::dateTimeRangeExiting()
• str MssExitingWorkerQueryRangeUtil::numberOfHierarchyResultLevelRange()
• str HcmBenefitEligibilityPolicyEntity::hcmBenefitEligibilityPolicyType()
• str SysPolicyEntityHelper::sysPolicyRuleTypeTableId()

$rootDirectory = "C:\AOSService\PackagesLocalDirectory"

cd $rootDirectory
ls "*.xml" -Recurse | % {
    $matches = (gc $_.FullName -ReadCount 0 | Select-String '\[QueryRangeFunction(?:Attribute)?\(?\)?\]\s+?public static ([^\r\n ]+) ([^\r\n)]+)' -AllMatches).Matches

    if ($matches.Count -ne 0)
    {
        $file = $_.Name.Substring(0, $_.Name.Length -4)
        #$fullName = $_.FullName

        foreach ($m in $matches) {
            Write-Host "$($m.Groups[1]) " -NoNewline
            Write-Host "$($file)." -NoNewline -ForegroundColor Gray
            Write-Host "$($m.Groups[2]))"
        }
    }
}

Electronic Reporting seems to be an integral part of the future of Dynamics 365 that can be used for importing and exporting data of all kinds above and beyond the capabilities of the static data entities, so it is worth finding out how this works.
In the following we will do this by example.

Online ressources

As a starting point there is standard microsoft documentation and this great blog with detaillied instructions:

• https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/general-electronic-reporting
• https://dynamicsmanuals.com/2019/09/12/introduction-to-electronic-reporting/

High level overview for creation of new Bank file

1. Extend a preexisting bank format
2. Define new export format – Constants
3. Define new export format – Data fields
4. Add transformations for correct formatting
5. Add new calculated fields
6. Calculate total fields
7. Add user input parameters
8. Add validations for readable errors
9. Activate

BACS: 1. Extend a preexisting bank format

It is always easier to start based on preexisting work instead of starting from scratch, so to create a new bank format, try to find a similar one to extend.

There is already a BACS format provided by Microsoft, however this does not conform to the current specification 18, but this will be out starting point.

BACS: 2. Define new export format – Constants

Define new file:

• Define End of Line character
• Define Constants

BACS: 3. Define new export format – Data fields

Define new file:

• Define data fields –> map to data source

BACS: 4. Add transformations

If the format to be printed does not match the data source transformations are required. Examples:

• Amounts as cents i.e. decimal number format without decimal separator: 123.45 transforms to 12345
• Date as Year combined with day in year: YYddd

BACS: 5. Add new calculated fields

Define new calculated fields:

• isCredit

…click Edit formula…

Note: if fields require new data sources, then the data mapping must be extended.

BACS: 6. Calculate total fields

Define new totals:

• Count of credit line / count of debit lines
• Sum of credit amount / sum of debit amount

…click Edit group by…

BACS: 7. User input parameters

Add User input parameters to allow the user to choose options in the file generation dialog.

The parameters can be used in formulas:

BACS: 8. Add validations

Validation help to give proper error messages that tell the user what is wrong.

Note: the condition must be true so the error does not occur.

Other option: make fields mandatory, but the error message is not understandable.

BACS: 9. Final Step – Activate (Complete)

Don’t forget to “Complete” your Draft – only then the changes are available.

Files

• Specification BACS 18
• Demo File
• ER Definition XML

Rob showed how to make Date Based Alerts using Microsoft Flow. So this is a great start into the future of the still missing AX alerts. But of course we want change based alerts for example to be informed about a sales order that has been processed. It does not take to long to find out that this is still not possible in the way it has been in the past. However you can get a message once the sales order reaches a certain status:

You see that the sales order is hard coded and once the sales order status has changed, you will receive an email every day. So why not use your Android Flow App to disable or delete the Flow?

Get Microsoft Dynamics Office Add-in for Excel 2016

Microsoft Dynamics Office Add-in

Open Number sequences in Excel

In D365fO go to Organization administration > Number sequences > Number sequences

Press Office Button > Open in Excel

Add format field to Excel

In Excel add the Format field. (Note: add the Name Field as well! Otherwise it will be reset.)

Edit data

Change the format as desired (e.g. remove the dash using excel search & replace)

Publish

Press Publish