Ali Raza Zaidi

A practitioner’s musings on Dynamics 365 Finance and Operations

Dynamics Ax 7

How to assign workflow instance to other then document originator Dynamics 365 for finance and operations

by

In this webcast, I explain how to use Custom workflow participant provider. I got requirement that In HR Portal self service provider. HR can submit request on the behalf of some worker. But when HR submit the document in Workflow for approval. The first user will be the behalf-ed user, so he can review and confirm the requirement. And certainly that User / worker will be different then document originator. I achieved that with the help of Custom Workflow Participant Provider.

 

Build Dynamics Query in Dynamics 365 for Finance and Operations – Video Webcast MsDyn365FO

by

 

Hi every one, here is another video.  Perfection is enemy of best and Best is enemy of good.  I spend 3 less sleep nights but unable to attain perfection in terms of presentation, I decided to complete it in good condition. It is beginner level video for developers. Certainly It based my 6 years technical work in Dynamics Ax 2012 and Dynamics 365 for Finance and Operations. Imagine how much I explored while making this video.   I used many reference to complete the code snippet form Internet.  Ahhoo

  • Build Query in AOT

  You can build a query in the AOT using Visual Studio. An advantage of creating the query in the AOT is that it can be reused in various places, saving lines of identical code, and making wide-reaching query adjustment easier.

 

 

 

  • Build Query in X++

Queries can also be built dynamically with X++ code. Both approaches are used in the standard application. One advantage of making the query dynamic is that it is not public in the AOT and is protected against unintentional AOT changes.

 

 

  • X++ Query Component
    • QueryRun
    • Query
    • QueryBuildDataSource
    • QueryBuildFieldList
    • QueryBuildRange
    • QueryFilter
    • QueryBuildDynaLink
    • QueryBuildLink

 

 

 

  • QueryRun

Use the queryRun object to execute the query and fetch data.

 QueryRun queryRun;

    queryRun = new QueryRun(query);

    if (queryRun.prompt())

    {

        while (queryRun.next())

        {

            inventTrans = queryRun.get(tableNum(InventTrans));

        }

}

  • Query

 The query object is the definition master. It has its own properties and has one or more related data sources.

        Query query;

       query = new Query();

        query.addDataSource(tableNum(InventTrans));

  • QueryBuildDataSource

Using QueryBuildDataSources you add all the tables you want joined (just one in this example). This is also where you define how the resultset is to be sorted. The orderMode() method lets you define:

  • OrderBy
  • GroupBy

QueryBuildDataSource queryBuildDataSourceTrans, queryBuildDataSourceDim;

queryBuildDataSourceTrans = query.addDataSource(tableNum(InventTrans));

queryBuildDataSourceDim = queryBuildDataSourceTrans.addDataSource(tableNum(InventDim));

 queryBuildDataSourceDim.addGroupByField(fieldNum(InventDim, InventBatchId));

 queryBuildDataSourceDim.relations(true);

 

 

  • QueryBuildFieldList

 The queryBuildFieldList object defines which fields to fetch from the database. The default is a dynamic field list that is equal to a “select * from …”. Each data source has only one queryBuildFieldList object which contains information about all selected fields. You can also specify aggregate functions like sum, count, and avg with the field list object.

 QueryBuildFieldList  qbfl       = qbds.fields();

 qbfl.addField(fieldNum(CustTable,CreditMax),SelectionField::Sum);

   qbfl.addField(fieldnum(CustTable,RecId),SelectionField::Count);

 

  • QueryBuildRange

  QueryBuildRange – The queryBuildRange object contains a limitation of the query on a single field.

QueryBuildDataSource    custTableQBDS, custTransQBDS;

QueryBuildRange         qbr1, qbr2;

custTableQBDS = query.addDataSource(tablenum(custTable));

qbr1 = query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, AccountNum));

    qbr2 = query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, InvoiceAccount));

    qbr1.value(SysQuery::value(‘4011’));

    qbr2.value(SysQuery::value(‘4010’));

  • QueryFilter

QueryFilter – The queryFilter object is used to filter the result set of an outer join. It filters the data at a later stage than the queryBuildRange object and filters the parent table based on the child table results.

QueryBuildDataSource    custTableQBDS, custTransQBDS;

 QueryFilter qFilter1, qFilter2;

custTableQBDS = query.addDataSource(tablenum(custTable));

qFilter1 = query.addQueryFilter(custTableQBDS,”AccountNum”);

qFilter1.value(“4011″);

qFilter2 = query.addQueryFilter(custTableQBDS,” InvoiceAccount”);

qFilter1.value(“4010″);

 

 

  • QueryBuildDynalink

QueryBuildDynalink – Contains information regarding a relation (limitation) to an external record. When the query is run, this information is converted to additional entries in the WHERE clause of the query SQL statement. Can only exist on the parent data source of a query. The function is used by forms, when two data sources are synchronized. Then the child data source will contain a dynalink or dynalinks to the parent data source. The function is used even if the two data sources are placed in two different forms but are still synchronized.

  • QueryBuildLink

QueryBuildLink  –  Specifies the relation between the two data sources in the join. Can only exist on a child data source.

QueryBuildLink          qbl;

QueryBuildDataSource    custTableQBDS, custTransQBDS;

custTableQBDS = query.addDataSource(tablenum(custTable));

custTransQBDS = custTableQBDS.addDataSource(tableNum(CustTrans));

qbl = custTransQBDS.addLink(fieldNum(CustTable, AccountNum), fieldNum(CustTrans, AccountNum));

 

Reference : 

http://www.axaptapedia.com/Query_class

https://docs.microsoft.com/en-us/dynamicsax-2012/developer/query-object-model

Required table did not appear in relation drop-down D365 for Finance and Operations.

by

Hi All, I was doing my Pilot project in D365 for Finance and operations. I want to add HCMWorker relation to my custom table. But did not found in drop down.

 

 

 

 

It means model reference(Package) was not included in Current Model. For this

 

For this first check Required model on table and add its reference On AOT

 

 

 

For adding this   click on Dynamics 365 Menu on top. Model Management => Update Model Parameters

 

 

Select your current Model, for example, My Pilot Model is OpnAxPayroll I selected that.

 

 

Select required Models and

 

 

Now I found the HCMWorker in relation dropdown.

Updating Date Effective Tables in AX 2012, Dynamics 365 for finance and operations

by

I was struggling to update Date Effective Table in Dynamics Ax 2012 R3. At least one hour of struggle, finally I update the required record.

 

Only good post which helps is by Agnela GoldBrg.

 

http://ibisinc.com/blog/updating-date-effective-tables-in-ax-2012/

 

One line tip from this post is on Date Effective table, The active record reference can be get with  of

DateTimeUtil::utcNow()

 

Its really small tip, yes learning and solving problem at hand waste hours.

 

Lets take a real world example We have to update Job detail with X++.

 

For HCMJobDetail table following way works for me.

 

hcmJobDetail=    HcmJobDetail::findByJob(hcmJob.RecId,DateTimeUtil::utcNow(),DateTimeUtil::utcNow(),true);

 

We can query on required table  with following  way

 

_validFrom = DateTimeUtil::utcNow();

_validTo =DateTimeUtil::utcNow();

 

select ValidTimeState(_validFrom, _validTo) hcmJobDetail

where hcmJobDetail.Job == _job;

 

 

The complete code snippet which works for me is here.

 

 

HcmJobDetail hcmJobDetail;

HcmJob hcmJob;

HCMJOBID  _id=’XyZ’;

date dateToday,

 

hcmJob = HcmJob::findByJob(_id);

 

_validFrom = DateTimeUtil::utcNow();

_validTo =DateTimeUtil::utcNow();

 

select ValidTimeState(_validFrom, _validTo) hcmJobDetail

where hcmJobDetail.Job == _job;

 

 

hcmJobDetail=    HcmJobDetail::findByJob(hcmJob.RecId,DateTimeUtil::utcNow(),DateTimeUtil::utcNow(),true);

if (hcmJobDetail!=null)

{

try

{

ttsBegin;

 

hcmJobDetail.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);

hcmJobDetail.SL_TempEndDate = today();

hcmJobDetail.SL_TempStartDate = today();

hcmJobDetail.Note = ” test and test”;

 

hcmJobDetail.update();

 

ttsCommit;

}

catch

{

ttsAbort;

HcmJob::CheckFailed(‘ Unable to update job’);

//    status  = SL_EntityStatus::Error;

}

}

}

 

 

}