Musing's of ERP Consultant
Table Id can be found SysTableIdView. in Dev environments, you can do direct query on sql server.
select * from SysTableIdView
If you did not access sql server then you can open it on table browser.
http://usnconeboxax1aos.cloud.onebox.dynamics.com/?cmp=usmf&mi=SysTableBrowser&TableName=SysTableIdView
Here collection of good articles / blog posts for Extension framework.
I just completed the customization using extension. Initially, it’s really tough for me. But with passage of time and my library of code snippet increased, I felt the power of extensions over old classic overlay concept. Even I used extension in custom forms instead write old age methods in controls and data sources.
Here are some links which are very helpful for me and worth to share.
Event handling:
https://devserra.wordpress.com/2016/04/19/ax7-using-table-event-handler/
https://axility.net/en/access-stuff-in-the-new-event-subscriptions/
Workflow:
http://d365technext.blogspot.com/2018/07/avoid-over-layering-in-development-of.html
https://devmusings.blog/2018/03/18/custom-workflows-in-microsoft-dynamics-365-for-operations/
https://workflowax.wordpress.com/2009/10/30/participant-provider-example/
Model:
https://stoneridgesoftware.com/what-is-the-modelutil-exe-good-for/
https://community.dynamics.com/365/financeandoperations/f/765/t/223547
https://www.cloudfronts.com/model-importexport-in-dynamics-365-operations/
Email templates
https://www.sikich.com/insight/how-to-email-purchase-orders-in-dynamics-365-for-operations/
Vendor Collaboration:
Reports through extension:
Custom form:
I just wrote couple of custom workflow. But on Submit workflow, form did not refresh and available for edit until, you manually refresh the form. That behavior is certainly not acceptable by end user.
usually “_args.caller().updateWorkflowControls();” will do this. Possible I missed something. But for its solution I add following code snippet for data set refresh that works for me.
dssRfpTable tableBuffer = _args.record();
FormDataSource custInvoiceTableDataSource;
custInvoiceTableDataSource = FormDataUtil::getFormDataSource(tableBuffer);
if (custInvoiceTableDataSource)
{
custInvoiceTableDataSource.research(true);
custInvoiceTableDataSource.refresh();
}
Buddy, Today I need to disabled the best practice check, It take too much time.
Usually rapid development, we did not care about best practice check. Otherwise compile take too much time.
For disabled is You have to unchecked the check box in Debug => options and then expand d365 node and then build
Hi all following code snippet for custom lookup. Copy the onlookup event handler and copy in extension class and the following code snippet
Field based
[FormControlEventHandler(formControlStr(Txxxx, Txxxx_ChildName), FormControlEventType::Lookup)]
public static void xxxxx_DssChildName_OnLookup(FormControl sender, FormControlEventArgs e)
{
Query query = new Query();
QueryBuildDataSource qbds;
QueryBuildDataSource qbdsJoin;
SysTableLookup sysTableLookup = sysTableLookup::newParameters(tableNum(DirPartyTable), sender);qbds= query.addDataSource(tableNum(DirPartyTable));
qbdsJoin= qbds.addDataSource(tableNum(DirPartyRelationship));
//qbdsJoin.relations( false);
//qbdsJoin.fields().dynamic(NoYes::Yes);
// hcmWorkerCaller = HcmWorker::find(currentWorker());
// qbds.addRange(fieldNum(DirPartyTable, RecId)).value(int642Str(HcmWorker::find(currentWorker()).Person));
qbdsJoin.addRange(fieldNum(DirPartyRelationship, parentparty)).value(int642Str(HcmWorker::find(currentWorker()).Person));
qbdsJoin.addLink(fieldNum(DirPartyTable, RecId), fieldNum(DirPartyRelationship, childparty));
qbdsJoin.joinMode(JoinMode::InnerJoin);sysTableLookup.parmQuery(query);
sysTableLookup.addLookupfield(fieldNum(DirPartyTable, Name), true);
sysTableLookup.addLookupfield(fieldNum(DirPartyTable, RecId));
sysTableLookup.performFormLookup();FormControlCancelableSuperEventArgs event = e as FormControlCancelableSuperEventArgs;
event.CancelSuperCall();}
Reference based is as follow
[FormControlEventHandler(formControlStr(xxxxx, Txeee_dddd), FormControlEventType::Lookup)]
public static void ddddee_DSSPerson_OnLookup(FormControl sender, FormControlEventArgs e)
{Query query = new Query();
QueryBuildDataSource qbds;
QueryBuildDataSource qbdsJoin;
SysReferenceTableLookup _sysTableLookup = SysReferenceTableLookup::newParameters(tableNum(DirPartyTable), sender);qbds= query.addDataSource(tableNum(DirPartyTable));
qbdsJoin= qbds.addDataSource(tableNum(DirPartyRelationship));
//qbdsJoin.relations( false);
//qbdsJoin.fields().dynamic(NoYes::Yes);
// hcmWorkerCaller = HcmWorker::find(currentWorker());
// qbds.addRange(fieldNum(DirPartyTable, RecId)).value(int642Str(HcmWorker::find(currentWorker()).Person));
qbdsJoin.addRange(fieldNum(DirPartyRelationship, parentparty)).value(int642Str(HcmWorker::find(currentWorker()).Person));
qbdsJoin.addLink(fieldNum(DirPartyTable, RecId), fieldNum(DirPartyRelationship, childparty));
qbdsJoin.joinMode(JoinMode::InnerJoin);_sysTableLookup.parmQuery(query);
_sysTableLookup.addLookupfield(fieldNum(DirPartyTable, Recid));
_sysTableLookup.addLookupfield(fieldNum(DirPartyTable, Name));
//, true);
// _sysTableLookup.addLookupfield(fieldNum(DirPartyTable, recid));
_sysTableLookup.performFormLookup();FormControlCancelableSuperEventArgs event = e as FormControlCancelableSuperEventArgs;
event.CancelSuperCall();
}
Hi all, here is small tip, I learned today, so document it
I need a custom method in table extension. And then this method need to call in field mapping inside Grid. Interesting it is amazing easy.
So like other extensions. You have to create a class add a attribute and make in static class show it will extension of certain table. And now create a method Suppose I need extension for PurchRFQScoring table
[ExtensionOf(tableStr(PurchRFQScoring))]
public static class PurchRFQScoring_Extension
{
[SysClientCacheDataMethodAttribute(true)] //This attribute will cache your display method.
public static display Name ScoringName(PurchRFQScoring _this)
{
return PurchRFQScoringRFQCaseCriteria::find(_this.ScoringRFQCaseCriteria).name;
}
}
Now compile solution. And open the grid and from field mapping, set table In source. And in field you have to add method with class reference like
PurchRFQScoring_Extension::ScoringName
During writing custom workflow. I found interesting things. Worth to share. In workflow condition screen. I did not found a lot of options. First thing I found that these Fields in conditions comes from field group we used in Approval creation wizard. But where to come child level fields. ??
Interestingly, these child level fields comes from Workflow Document Query. Required child fields comes from child or tables in main data source of query.
Following screenshots will helps you to more understanding
This is second part of webcast. Here I demonstrate to setup number sequence in Dynamics 365 for finance and operations
First Part of number sequence framework webcast.
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
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.
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.
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));
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:
QueryBuildDataSource queryBuildDataSourceTrans, queryBuildDataSourceDim;
queryBuildDataSourceTrans = query.addDataSource(tableNum(InventTrans));
queryBuildDataSourceDim = queryBuildDataSourceTrans.addDataSource(tableNum(InventDim));
queryBuildDataSourceDim.addGroupByField(fieldNum(InventDim, InventBatchId));
queryBuildDataSourceDim.relations(true);
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 – 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 – 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 – 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 – 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
In Dynamics 365 for Finance and Operations cleanup routines are available across various modules within the product. It is important to note that these cleanup routines should be only executed after detailed analysis and confirmation from the business this data is no longer needed. Also always test each routine first in test environment prior executing it in production. This article provides an overview on what is available today.
read complete article from this link https://blogs.msdn.microsoft.com/axsa/2018/09/05/cleanup-routines-in-dynamics-365-for-finance-and-operations/
Small tip, follow these steps,
Create a run able class in Visual studio.
in its main method write following line of code
Browser br = new Browser();
br.navigate(“https://www.tech.alirazazaidi.com, true);
Create a action menu.
Set its object type to class and set object with runnable class name.
Add this menu item to required menu extension. Save compile
On clicking menu item from navigation. A popup windows open, Allow popup option allow.
Very interesting scenario we faced in one customization. Requirement was very simple and developer used very code to send Email.
Usually testing will be done on dev box which has both AOS and client installed on same machine. No error occurred and code shipped to production. But when we deploy the code on production server and test the email sent functionality we got following error.
Method ‘send’ in COM object of class ‘CDO.Message’ returned error code 0x8004020F (<unknown>) which means: <unknown>.
After trouble shoot we found that when we run the AX client from another machine pointing to AOS this error occurred. There is some network security issue which did not access rights to SMTP server while running code from Client.
So what is solution. Then we explore how Ax send email. What is out of the box solution.
We found that in out of the box functionality, AX did not send email immediately, Instead, they stored required information in some tables and then Batch sent emails on Server.
So We changed our design and new code just inject information into out of the box tables and emails goes with out of box batch job. Only attachment part was little bit tricky.
Recommendation and lesson learned that, instead calling Smtp server directly on code, we have to use out of the box functionality and just integrate it.
Hi all, here is another youtube video. I used to explore things and them document. Here I documented line level charges on sales order applied based on customer charges group and Item Charges group. English is not my native language so bear with me.
We can disable the export to Excel option in list pages, There is option on grid properties “Export to Excel”. Its by default true. By changing it to false disable export to excel on specific form.
Today I got issue on my Dev vm hosted in local environment. Some certificate was expired.
open windows menu
Certificates – Local Computer > Personal > Certificates.
And find expired certificate
Open these certificate and copy thumbprints in Notepad ++
Open PowerShell with run as administrator and generate new clone certificates
Set-Location -Path “cert:\LocalMachine\My”
$OldCert = (Get-ChildItem -Path F79C64B7CD90911328818A2B10DF8BDA2736CC8E)
New-SelfSignedCertificate -CloneCert $OldCert -NotAfter (Get-Date).AddMonths(999)
My notepad++ files look like similar after copy paste new thumbprints
Then open following three files form C:\AOSService\webroot:
Search old thumbprints with new one.
Save and restart the machine.
After reboot, Successfully login
More detail article which helps me by
This is third video in Sale order Processing series using D365 for finance and operations. We use trade agreements for Price listing for Customer or group of customers.
Hi all I faced this error on Production server. Error occur when User Post Purchase Invoice.
[Microsoft][SQL Server Native Client 11.0][SQL Server]BudgetControlProcessProc ( in
SysRaiseErrorProc): Trying:exec BudgetControlCheckBudgetProc :BudgetControlCheckBudgetProc:
Trying:Initial insert #BSTDStage – BudgetSourceTrackingDetail:String or binary data would be
truncated.(8152) at line 369 at line 67
Solution: I found out of box length of Purchase Invoice number is 20. Some one extend the length and it is used notes like functionality. But extension of length did not reflect on BudgetControlCheckBudgetProc.
When we enter the Purchase Invoice number value as 20. Invoice is posted without error.
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.
I found a array based field in TSTimesheetLineWeek table, Field TSWeeklyHours
Now challenge is to enabled field based on Array. For example need to disabled on day 6.
Following code snippet help me to enabled disabled fields generated on the base of array extended Type
TSTimesheetLineWeek_ds.object(fieldId2Ext(fieldNum(TSTimesheetLineWeek, Hours), 6)).allowEdit(false);
Reference :
https://msdn.microsoft.com/en-us/library/aa675074.aspx
https://docs.microsoft.com/en-us/previous-versions/dynamicsax-2009/developer/aa592700(v=ax.50)