computed column in Dataentity

synatx:-  

public static server str methodname()

  {
      str purchid=SysComputedColumn::returnField(dataentityviewstr(Dataentityname),dataEntityDataSourceStr(Dataentityname,Datasourcename),fieldStr(Datasourcename,fieldname));

      return strFmt(@'SELECT TOP 1 from tablename where tablename.fieldname=%1',purchid);

Note:-For lengthy sql stmts dont write direclty here it will show huge errors so pls write first huge sql stmts in SSMS then confirm data correctly then paste in above return statement
--------------------------------------------------------
     To declare a string use SysComputedColumn::returnLiteral('string value');

Get enumvalues through sql

 Generally in AOT sometimes we can not see tthe basenum values in properties so here is the query which helps 

SELECT
    EIT.NAME AS EnumName,
    EVT.ENUMVALUE,
    EVT.NAME AS EnumElement
FROM ENUMIDTABLE EIT
INNER JOIN ENUMVALUETABLE EVT
    ON EVT.ENUMID = EIT.ID
WHERE EIT.NAME = 'YourBaseenum'
ORDER BY EVT.ENUMVALUE;

Get workflow firstApprover and firstapproval datetime through code

  WorkflowTrackingStatusTable wfTrackingTable;

 WorkflowTrackingTable       wfTracking;

 select firstonly wfTrackingTable

     order by wfTrackingTable.CreatedDateTime desc

             where wfTrackingTable.ContextTableId == tablenum(tablename)

             && wfTrackingTable.ContextRecId   == _purchTable.RecId;

 select firstonly   wfTracking

         order by wfTracking.CreatedDateTime asc

         where wfTracking.WorkflowTrackingStatusTable == wfTrackingTable.RecId

            && wfTracking.TrackingType == WorkflowTrackingType::Approval

             && wfTracking.TrackingContext==WorkflowTrackingContext::WorkItem;

 Date POApprovalDate = DateTimeUtil::date(wfTracking.CreatedDateTime);

 str POApprovedBy   = HcmWorker::find(HcmWorker::userId2Worker(wfTracking.user)).name();

Get base enum lables insted enum ids in SSRS report

Generally in ssrs report to get baseenum labels instead basenum element id , we can do that in two ways

Way1) Take a  string field in Tmp table and make enum2str and assign.

Way2) In expressions generally we get for basenums =Fields!BaseenumName.Value now change it to 

Fields!BaseenumLabel.Value 

==> build it and check it.

Get workflow LastApprover and Lastapproval datetime through code

  WorkflowTrackingStatusTable wfTrackingTable;

 WorkflowTrackingTable       wfTracking;

 select firstonly wfTrackingTable

     order by wfTrackingTable.CreatedDateTime desc

             where wfTrackingTable.ContextTableId == tablenum(tablename)

             && wfTrackingTable.ContextRecId   == _purchTable.RecId;

 select firstonly   wfTracking

         order by wfTracking.CreatedDateTime desc

         where wfTracking.WorkflowTrackingStatusTable == wfTrackingTable.RecId

            && wfTracking.TrackingType == WorkflowTrackingType::Approval

             && wfTracking.TrackingContext==WorkflowTrackingContext::WorkItem;

 Date POApprovalDate = DateTimeUtil::date(wfTracking.CreatedDateTime);

 str POApprovedBy   = HcmWorker::find(HcmWorker::userId2Worker(wfTracking.user)).name();

SSRS report

 To make values empty while opening contract and making last given values empty

code:-

In controller class :-

 protected void prePromptModifyContract()

 {

     super();

     MyContract contract = this.parmReportContract().parmRdpContract() as MyContract;

     if (contract)

     {

         contract.parmFromDate(dateNull());

         contract.parmToDate(dateNull());

         contract.parmPRNumber(null);

     }

 }

Maintenance mode in d365fo

 Scenario:- Generally For activating some configurations  maintenance mode is required .

This can be done in two ways

1) From LCS

2) SQL procedure

We discuss here 2 step 

 SQL procedure

Step1) First of all  , run this script 

select * from SQLSYSTEMVARIABLES where PARM = 'CONFIGURATIONMODE'

result :- value field 0=normal and 1= maintenance mode

Step2) To turn into maintenance mode run the below script

update SQLSYSTEMVARIABLES SET VALUE = 0 where PARM = 'CONFIGURATIONMODE'

Step3) DO iisreset in commandprompt

Step4) do db sync

visualstudio>extensions>syncorinzedb

Step 5) Now after db sync env will be in maintence mode

Note:- after activating and put maintenance mode back to normal by following same procedure but with 0

Navigate to a specific legal entity through code

 code:-

appl.setDefaultCompany(Legalentitycode, false);

Get product receipt details from posted invoice

 code:-

sql:-

select DISTINCT vendPackingSlipTrans.PACKINGSLIPID from vendInvoiceTrans

inner join vendInvoiceJour

  on vendInvoiceTrans.PurchID = vendInvoiceJour.PurchId and

      vendInvoiceTrans.InvoiceId = vendInvoiceJour.InvoiceId and

      vendInvoiceTrans.InvoiceDate = vendInvoiceJour.InvoiceDate and

      vendInvoiceTrans.NumberSequenceGroup = vendInvoiceJour.NumberSequenceGroup and 

      vendInvoiceTrans.InternalInvoiceId = vendInvoiceJour.InternalInvoiceId

  inner join vendInvoicePackingSlipQuantityMatch

      on vendInvoicePackingSlipQuantityMatch.InvoiceSourceDocumentLIne = vendInvoiceTrans.SourceDocumentLine

  inner join vendPackingSlipTrans

      on vendPackingSlipTrans.SourceDocumentLine = vendInvoicePackingSlipQuantityMatch.PackingSlipSourceDocumentLine

  and vendInvoiceJour.LEDGERVOUCHER='pass your journal vocuher'

and also if you want reverse like if you have grn get invoices then code

sql:-

select vendPackingSlipTrans.DATAAREAID,vendPackingSlipTrans.ORIGPURCHID,vendPackingSlipTrans.PACKINGSLIPID,vendInvoiceTrans.INVOICEID from vendPackingSlipTrans
inner join vendInvoicePackingSlipQuantityMatch
on vendPackingSlipTrans.SourceDocumentLine = vendInvoicePackingSlipQuantityMatch.PackingSlipSourceDocumentLine
inner join vendInvoiceTrans
  on vendInvoicePackingSlipQuantityMatch.InvoiceSourceDocumentLIne = vendInvoiceTrans.SourceDocumentLine
  and vendPackingSlipTrans.ORIGPURCHID='passpurchid'and  vendPackingSlipTrans.DATAAREAID='pass dataareaid'

 

Assign roles through ssms in d365fo

 Step1;- Here my Role name I want to assign is System admin

SELECT  RecId,* FROM SecurityRole WHERE Name = 'System administrator';

step 2:- User name here is sai  and pass recid from above to below query

insert into SECURITYUSERROLE (USER_, SECURITYROLE, ASSIGNMENTSTATUS, ASSIGNMENTMODE)
VALUES ('sai', 171,1,1);

Thats it!! refresh and check

For deleting of roles through SSMS

Step1:-SELECT  RecId,* FROM SecurityRole WHERE Name = 'System administrator';

pass recid from above to below query

Step 2:-DELETE FROM SECURITYUSERROLE WHERE USER_='sai'and SECURITYROLE=171;

Thank you!!

Get all Dimensions values with - separated with provided dimension recid

 To get all dimension with - separated with provided dimension recid

Code:-

public static str Getdimensionset(RecId recid)
{
     str                                 descriptionLedgerLoc = '';
     boolean                             first = true;
     DimensionAttributeLevelValueAllView view,viewloc;
     DimensionAttributeValue             dimValue;
     DimensionAttributeValueGroup        dimensionAttributeValueGroup;
     container                           con;

     select firstonly viewloc
         where viewloc.ValueCombinationRecId==recid;

 

     while select view order by view.ValueOrdinal asc
      where view.ValueCombinationRecId == recid
&& view.DimensionAttributeValueGroup==viewloc.DimensionAttributeValueGroup
     {

         if (!first)
         {
             descriptionLedgerLoc += " - ";
         }
         descriptionLedgerLoc +=view.DisplayValue ;
         first = false;


     }
     return descriptionLedgerLoc;
}

Note:-dimensions will get with - separated

Thank you!!

Get dimensions values with provided dimension Recid

 To get Dimensions value as displayed in Control level 

Code:-

LedgerDimensionFacade::getDisplayValueForLedgerDimension(DimensionRecid);

Note:- dimensions will get with ~ separated

Thank you!!

Run a class through front end in d365fo

 link to run a class from front end through sysclassrunner

dynamics url/?mi=SysClassRunner&cls=classname

Get amount with required exchange rate of currency

Scenario:- When you need to get amount with required exchange rate.

Case :- It also depends on whether we are going with Account currency exchange rate or Budget exchange rate type as shown in image below 

Case 1:- If it is Budget exchange

Code:-

CurrencyCode            currencyCode = SystemParameters::find().SystemCurrencyCode;
            str fromcurrency='USD';
            str tocurrency='CAD';
            real amount=120;
            CurrencyExchangeHelper  currencyExchangeHelper;
            currencyExchangeHelper = currencyExchangeHelper::construct();
            currencyExchangeHelper.parmLedgerRecId(Ledger::current());
            currencyExchangeHelper.parmExchangeRateTypeRecId(Ledger::budgetExchangeRateType());
            Info(strFmt("%1",currencyExchangeHelper.calculateCurrencyToCurrency(fromcurrency, tocurrency,amount, true)));

Case 2:- If it is Account currency exchange rate

Code:-

CurrencyCode            currencyCode = SystemParameters::find().SystemCurrencyCode;
            str fromcurrency='USD';
            str tocurrency='CAD';
            real amount=120;
            CurrencyExchangeHelper  currencyExchangeHelper;
            currencyExchangeHelper = currencyExchangeHelper::construct();
            currencyExchangeHelper.parmLedgerRecId(Ledger::current());
            currencyExchangeHelper.parmExchangeRateTypeRecId(Ledger::defaultExchangeRateType());
            Info(strFmt("%1",currencyExchangeHelper.calculateCurrencyToCurrency(fromcurrency, tocurrency,amount, true)));

Stop sending notifications from dynamics through SMTP when you have subject #FAIL#

Scenario :- I got a requirement where dynamics sends notification and these notifications are to be restricted so these the logic where you can control the notifications.

Code:-

ExtensionOf(classstr(SysMailerSMTP))]

 final class NotificationsstopSysMailerSMTP_Extension

{

    protected boolean sendMessage(System.Net.Mail.MailMessage _message, boolean _interactive, guid _correlationId)

    {

        str val="#FAIL#";

        if(strKeep(val,_message.Subject)==val)

        {

            System.Net.Mail.MailAddress buf;

            System.Net.Mail.MailAddressCollection buf2;

            System.Net.Mail.MailMessage _messageloc;

            _message=_messageloc;

            _message.Subject='';

            _message.Sender=buf;

        }

        boolean ret;

       

        ret =  next sendMessage( _message,  _interactive,  _correlationId);

        return ret;

       

    }

}

NOTE:- This is a framework class customizations .So make sure we go with procedure  until and unless you are confident to implement the change

Thank you !! 

How to make a workflow global or legal entity specific

 scenario :- Generally in Dynamics there are some workflows with globally and some are Legal entity specific . With association type  as shown in below

Step 1:- To make a workflow global or Legal entity specific. Go to visual studio >

find the workflow type and click on properties and in  Association type choose the type you need as shown in below

Note :- Don't change this for standard workflows. And can use this for Custom mostly.

DB restore from tier 2 to tier 1

 Step 1:- Get Backpac file to your dev machine

Step2:- move this file to location as shown in below

                                     

Step 2 :- Navigate to Download and Install SqlPackage - SQL Server | Microsoft Learn 

and scroll down for windows package as shown in below

download the file and extract the zip file

Step 3:- open the file and copy location as shown in below

Step 4:-Open commnd promt with run of administrator and run cd paste the location copied from above as shown in below

Step5:-run this command SqlPackage.exe /a:import /sf:J:\MSSQL_BACKUP\uatbackup.bacpac /tsn:localhost /tdn:AXDB_New /TargetTrustServerCertificate:True  /p:CommandTimeout=1500

Step 6:-Wait for 6 hours until you see DB restored sucessfully.

Step 7:-Go to SSMS >Database >AXDB>properites>makes multiuser to single user 

then Rename to AXDB_OLD 

Step 8:-Rename AXDB_New (new db restored) to AXDB 

Make AXDB_OLD  properties to single user

Step 9:-Restart all services. Now Dev machines runs with Tier 2 database 

Note:- This process is when you have been given with bacpac file (usually when we export tier 2 it will be bacpac only.most of the time we get bacpac file only).

If we get bac file we can directly restore with in few steps and lesstime in ssms itself without cmd promt.

Thank you!!

Generate entity diagram in d365fo

 Today in this blog we learn about how to see relations between tables in pictorial representation.

Step1:- Navigate to Github https://github.com/noakesey/d365fo-entity-schema and click on releases as shown in below

Step2:- Down the first DLL file as shown in below

Step3:-Once you have downloaded the file it will shown in warning file click on three dots >Keep>Keep anyway as shown in below

Step4:-Click on the properties of the file and click on unblock and apply as shown in below

Step5:-Copy the file and place in folder

Navigation:-

C:\Program Files\Microsoft Visual Studio\2022\Professional\Common7\IDE\Extensions\jalba1yr.dr0\AddinExtensions

If jalbalyr.dr0 folder not available then paste folder  in C:\Program Files\Microsoft Visual Studio\2022\Professional\Common7\IDE\Extensions

Step6:-Go to any table>rightclik>Addins>Generate entity relation schema as shown in below

Step7:-Click on outward and inward >DBML as shown in below

Step8:-Copy the whole code and click on the link as shown in below

Step9:-then click on create diagram as shown in below

Step10:-Clear existing code in left pane and paste the code copied in VS as shown in below

Thank you!!

Perform actions to workflow with code in d365fo x++

 Scenario:- We can do some actions in workflow through code 

1) Submit:-

Code:-

WorkflowTypeName   workflowTypeName  =  workflowTypeStr(CustomWorkflowType);

Workflow::activateFromWorkflowType(workflowTypeName,  Table.RecId, workflowComment, NoYes::No);

2) Approve:-

Code:-

WorkflowWorkItemTable workflowWorkItemTable;

WorkflowTypeName  workflowTypeName  =  workflowTypeStr(CustomWorkflowType);
select firstonly workflowWorkItemTable
where  workflowWorkItemTable.Type   == WorkflowWorkItemType::WorkItem
&&  workflowWorkItemTable.Status    == WorkflowWorkItemStatus::Pending
&&  workflowWorkItemTable.RefTableId== tableNum(EmployeeTable)
&&  workflowWorkItemTable.RefRecId  ==  5637144576;

 

if(workflowWorkItemTable)
{
     WorkflowWorkItemActionManager::dispatchWorkItemAction(workflowWorkItemTable,"Auto Approve by system",curUserId(), WorkflowWorkItemActionType::Complete,menuitemDisplayStr(Employee));       

}

3) cancel or recall  :-

Code:-

         WorkflowWorkItemTable workflowWorkItemTable;

WorkflowTypeName  workflowTypeName  =  workflowTypeStr(CustomWorkflowType);
  select firstonly workflowWorkItemTable
where  workflowWorkItemTable.Type   == WorkflowWorkItemType::WorkItem
&&  workflowWorkItemTable.Status    == WorkflowWorkItemStatus::Pending
&&  workflowWorkItemTable.RefTableId== tableNum(EmployeeTable)
&&  workflowWorkItemTable.RefRecId  ==  5637146826;

 

if(workflowWorkItemTable)
{

  Workflow::cancelWorkflow(workflowWorkItemTable.CorrelationId, "cancel by code");
  }

4) Resume:-

Code:-

         Workflowtrackingstatustable workflowTrackingStatusTable;

    select * from workflowTrackingStatusTable where workflowTrackingStatusTable.CONTEXTRECID == 5637147576
&& workflowTrackingStatusTable.CONTEXTTABLEID == tableNum(EmployeeTable);

    if(workflowTrackingStatusTable)
   {

     Workflow::resumeWorkflow(workflowTrackingStatusTable.CorrelationId, "resume by code");

  }

Thankyou!!

Run some logic in workflow at particular step using WorkflowAutomatictask

 Scenario:- If you want to run some logic in workflow at particular we can use WorkflowAutomatictask

Step1:- create a class 

 Code:-

       class CustomWorkflowAutomatedEvnentHandler implements WorkflowElementExecutionEventHandler

{
    public void execute(WorkflowElementEventArgs _workflowElementEventArgs)
    {
        WorkflowContext    workflowContext = _workflowElementEventArgs.parmWorkflowContext();

 

        RecId recid = workflowContext.parmRecId();

 

        EmployeeTable emptable;

 

        select * from emptable where emptable.RecId == recid;

 

        info(strFmt("%1",emptable.EmployeeId));
    }

 

}

Step2:- Add Workflowautomatictask and give properties as shown in below

Step3:-In the supported elements add this automatic task and give type as automatictask.

Step4:-Open frontend and drag this automatictask to run some logic as shown in below

Thankyou!!