d365 F&O How to filter records in a form by code using extensions in x++

  In this case filtering vendor invoice journals by creating an extension class in the form datasource - init() method

I'm trying to filter vendor invoices with USD currency code

[ExtensionOf(formDataSourceStr(vendinvoicejournal, VendInvoiceJour))]

final class VendInvoiceJournal_FormDS_Extension

{

    public void init()

    {

        next init();

        

        str formName =  this.formRun().name();

        str callerName =  this.formRun().args().callerName();

        if(formName == 'VendInvoiceJournal' && callerName == 'VendInvoiceJournal')

        {

                          this.query().dataSourceName(this.name()).addRange(fieldnum(VendInvoiceJour, Currency)).value(SysQuery::valueLike("USD"));

            }

          

        }

    }

D365 FO Computed column in View ( Returning Field in View )

The technique to add a computed column begins with you adding a static method to the view. The method must return a string. The system automatically concatenates the returned string with other strings that the system generates to form an entire T-SQL create view statement.

The method that you add to the view typically has at least one call to the DictView.computedColumnString method. The parameters into the computedColumnString method include the name of a data source on the view, and one field name from that data source. The computedColumnString method returns the name of the field after qualifying the name with the alias of the associated table. For example, if the computedColumnString method is given the field name of AccountNum, it returns a string such as A.AccountNum or B.AccountNum. 

1 - Create Your View 

2- Add a Static Method to the View 

  The code below 

3 - Add Computed column 

Here is an example which illustrates how can we add computed columns to return Year , Month and substring from field in view 

private static server str ProjectID()

    {

        #define.CompView(SG_BudgetLineView)

        #define.CompDS(DimensionAttributeValueCombination)

        #define.DisplayValue(DisplayValue)

        str LedgerAccount;

        LedgerAccount =  return SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #DisplayValue));

        return "SUBSTRING(" + LedgerAccount + ", 10 , 5)";

    }

 private static server str Year()

    {

        str yearId;

        yearId = SysComputedColumn::returnField(identifierStr("BudgetLineView"),identifierStr("BudgetTransactionLine"),identifierStr("Date"));

        return "YEAR(" + yearId + ")";

    }

   private static server str Month()

    {

        str MonthId;

        MonthId = SysComputedColumn::returnField(identifierStr("BudgetLineView"),identifierStr("BudgetTransactionLine"),identifierStr("Date"));

        return "Month(" + MonthId + ")";

        // return "mthOfYr(" + MonthId + ")";

    }

    private static server str MainAccountID()

    {

        str LedgerAccount;

        LedgerAccount = SysComputedColumn::returnField(identifierStr("SG_BudgetLineView"),identifierStr("DimensionAttributeValueCombination"),identifierStr("DisplayValue"));

        return "SUBSTRING(" + LedgerAccount + ", 0 , 9)";

    }

  Computed column; Multiplying two coulmns and returning resultant from View

private static server str compTotalCostPrice()
{
   #define.CompView(SWProjForecastCost)
   #define.CompDS(ProjForecastCost)
   #define.QtyCol(Qty)
   #define.PriceCol(CostPrice)


   return SysComputedColumn::multiply(
           SysComputedColumn::returnField(
               tableStr(#CompView),
               identifierStr(#CompDS),
               fieldStr(#CompDS, #PriceCol)
           ),
           SysComputedColumn::returnField(
               tableStr(#CompView),
               identifierStr(#CompDS),
               fieldStr(#CompDS, #QtyCol)
           )
         );
}

Computed column; Returning Enum Value in View
public static server str compGeneralTransType()
{
   return SysComputedColumn::returnLiteral(Transaction::ProjectInvoice);
}

Computed column; Returning Field in View
public static server str compAmount()
{
   #define.CompView(SWProjForecastCost)
   #define.CompDS(ProjForecastCost)
   #define.CostPrice(CostPrice)


   return SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #CostPrice));
} 


Computed column; Case Statement in View
public static server str TransType()
{
   #define.CompDS(ProjForecastCost)
   #define.CompView(SWProjForecastCost)
   str ret;

   str ModelId = SysComputedColumn::returnField(identifierStr(SWProjForecastCost), identifierStr(ProjForecastCost), identifierStr(ModelId));
   ret = "case " + modelId +
         " when 'Sales' then 'Forecast Sales' " +
         " when 'Orders' then 'Forecast Orders' " +
         " when 'Latest' then 'Forecast Latest' " +
         " end";
   return ret;


}
Case Statement for this view looks like in SQL server as below;

   CASE T2.MODELID
      WHEN 'Sales' THEN 'Forecast Sales'
      WHEN 'Orders' THEN 'Forecast Orders'
      WHEN 'Latest' THEN 'Forecast Latest' END



Refrence1
Refrence2

D365 FO Sending Email with SSRS reports as attachment using X++

 Public class EmailCustAccountStmnt

{

public void run(CustTable _custTable)

{

SysOperationQueryDataContractInfo sysOperationQueryDataContractInfo;

SrsReportRunController reportRunController;

CustTransListContract custTransListContract;

SRSReportExecutionInfo reportExecutionInfo;

SRSPrintDestinationSettings printDestinationSettings;

SRSReportRunService srsReportRunService;

SRSProxy srsProxy;

QueryBuildRange qbrCustAccount;

QueryBuildDataSource queryBuildDataSource;

Object dataContractInfoObject;

Map reportParametersMap;

Map mapCustAccount;

MapEnumerator mapEnumerator;

Array arrayFiles;

System.Byte[] reportBytes;

Filename fileName;

Args args;

System.IO.MemoryStream memoryStream;

System.IO.MemoryStream fileStream;

CustParameters custParameters;

Email toEmail;

 

    Map                                 templateTokens;

    str                                 emailSenderName;

    str                                 emailSenderAddr;

    str                                 emailSubject;

    str                                 emailBody;

 

    Microsoft.Dynamics.AX.Framework.Reporting.Shared.ReportingService.ParameterValue[]  parameterValueArray;

 

    #define.Subject("Subject")

    #define.CustAccount("CustAccount")

    #define.EmailDate("Date");

 

    custParameters          = CustParameters::find();

 

    reportRunController     = new SrsReportRunController();

    custTransListContract   = new CustTransListContract();

    reportExecutionInfo     = new SRSReportExecutionInfo();

    srsReportRunService     = new SrsReportRunService();

    reportBytes             = new System.Byte[0]();

    args                    = new Args();

    templateTokens          = new Map(Types::String, Types::String);

    var messageBuilder      = new SysMailerMessageBuilder();

 

    custTransListContract.parmNewPage(NoYes::Yes);

 

    fileName    = strFmt("CustomerAccountStatement_%1.pdf", _custTable.AccountNum);

 

    reportRunController.parmArgs(args);

    reportRunController.parmReportName(ssrsReportStr(CustTransList, Report));

    reportRunController.parmShowDialog(false);

    reportRunController.parmLoadFromSysLastValue(false);

    reportRunController.parmReportContract().parmRdpContract(custTransListContract);

 

    // Modify query

    mapCustAccount = reportRunController.getDataContractInfoObjects();

    mapEnumerator = mapCustAccount.getEnumerator();

 

    while (mapEnumerator.moveNext())

    {

        dataContractInfoObject = mapEnumerator.currentValue();

 

        if (dataContractInfoObject is SysOperationQueryDataContractInfo)

        {

            sysOperationQueryDataContractInfo = dataContractInfoObject;

 

            queryBuildDataSource    = SysQuery::findOrCreateDataSource(sysOperationQueryDataContractInfo.parmQuery()

                                                                    , tableNum(CustTable));

            qbrCustAccount          = SysQuery::findOrCreateRange(queryBuildDataSource, fieldNum(CustTable, AccountNum));

            qbrCustAccount.value(_custTable.AccountNum);

        }

    }

 

    printDestinationSettings = reportRunController.parmReportContract().parmPrintSettings();

    printDestinationSettings.printMediumType(SRSPrintMediumType::File);

    printDestinationSettings.fileName(fileName);

    printDestinationSettings.fileFormat(SRSReportFileFormat::PDF);

 

    reportRunController.parmReportContract().parmReportServerConfig(SRSConfiguration::getDefaultServerConfiguration());

    reportRunController.parmReportContract().parmReportExecutionInfo(reportExecutionInfo);

 

    srsReportRunService.getReportDataContract(reportRunController.parmreportcontract().parmReportName());

    srsReportRunService.preRunReport(reportRunController.parmreportcontract());

 

    reportParametersMap = srsReportRunService.createParamMapFromContract(reportRunController.parmReportContract());

    parameterValueArray = SrsReportRunUtil::getParameterValueArray(reportParametersMap);

 

    srsProxy        = SRSProxy::constructWithConfiguration(reportRunController.parmReportContract().parmReportServerConfig());

    reportBytes     = srsproxy.renderReportToByteArray(reportRunController.parmreportcontract().parmreportpath()

                                                    , parameterValueArray

                                                    , printDestinationSettings.fileFormat()

                                                    , printDestinationSettings.deviceinfo());

 

    memoryStream    = new System.IO.MemoryStream(reportBytes);

    memoryStream.Position = 0;

 

    fileStream      = memoryStream;

    toEmail         = this.getCustEmail(_custTable.AccountNum);

 

    if (custParameters.EmailId && toEmail)

    {

 

        templateTokens.insert(#CustAccount, _custTable.name());

        templateTokens.insert(#EmailDate, date2StrXpp(systemDateGet()));

 

        [emailSubject, emailBody, emailSenderAddr, emailSenderName] =

            EmailCustAccountStmnt::getEmailTemplate(custParameters.EmailId, _custTable.languageId());

 

 

        messageBuilder.addTo(this.getCustEmail(_custTable.AccountNum))

                        .setSubject(strFmt("Customer account statement for %1", _custTable.AccountNum))

                        .setBody(SysEmailMessage::stringExpand(emailBody, SysEmailTable::htmlEncodeParameters(templateTokens)))

                        .addCC("");

 

        messageBuilder.setFrom(emailSenderAddr, emailSenderName);

        messageBuilder.addAttachment(fileStream, fileName);

 

        SysMailerFactory::sendNonInteractive(messageBuilder.getMessage());

 

        info(strFmt("Email sent successfully to the customer account %1", _custTable.AccountNum));

    }

    else

    {

        info(strFmt("There is no email id mappiing for this customer %1 or check the Email template setup.", _custTable.AccountNum));

    }

}

 

protected static container getEmailTemplate(SysEmailId _emailId, LanguageId _languageId)

{

    var messageTable = SysEmailMessageTable::find(_emailId, _languageId);

    var emailTable = SysEmailTable::find(_emailId);

 

    if (!messageTable && emailTable)

    {

        // Try to find the email message using the default language from the email parameters

        messageTable = SysEmailMessageTable::find(_emailId, emailTable.DefaultLanguage);

    }

 

    if (messageTable)

    {

        return [messageTable.Subject, messageTable.Mail, emailTable.SenderAddr, emailTable.SenderName];

    }

    else

    {

        warning("@SYS135886"); // Let the user know we didn't find a template

        return ['', '', emailTable.SenderAddr, emailTable.SenderName];

    }

}

 

public Email getCustEmail(CustAccount _custAccount)

{

    CustTable                   custTable;

    DirPartyLocation            dirPartyLocation;

    LogisticsLocation           logisticsLocation;

    LogisticsElectronicAddress  logisticsElectronicAddress;

 

    custTable = CustTable::find(_custAccount);

 

    select firstonly Location, Party from dirPartyLocation

        where dirPartyLocation.Party                        == custTable.Party

            join RecId from logisticsLocation

                where logisticsLocation.RecId               == dirPartyLocation.Location

            join Locator from logisticsElectronicAddress

                where logisticsElectronicAddress.Location   == logisticsLocation.RecId

                    && logisticsElectronicAddress.Type      == LogisticsElectronicAddressMethodType::Email

                    && logisticsElectronicAddress.IsPrimary == NoYes::Yes;

 

    return logisticsElectronicAddress.Locator;

}

D365 Table event methods

 

Here are quick examples of the most important event methods on a table,

	class Dpk_VendTableEventHandlerClass
	{
	/// <summary>
	///
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	[DataEventHandler(tableStr(VendTable), DataEventType::InitializedRecord)]
	public static void VendTable_onInitializedRecord(Common sender, DataEventArgs e)
	{
	VendTable vendTable = sender as VendTable;
	vendTable.VendGroup = "VG001"; //Change this value
	//Add business logic
	}
	/// <summary>
	///
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	[DataEventHandler(tableStr(VendTable), DataEventType::Inserted)]
	public static void VendTable_onInserted(Common sender, DataEventArgs e)
	{
	VendTable vendTable = sender as VendTable;
	if(!vendTable.YourAccountNum)
	{
	//Add business logic
	vendTable.YourAccountNum = "This is event handler example";
	}
	}
	/// <summary>
	///
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	[DataEventHandler(tableStr(VendTable), DataEventType::Deleted)]
	public static void VendTable_onDeleted(Common sender, DataEventArgs e)
	{
	VendTable vendTable = sender as VendTable;
	//Add business logic
	if (box::yesNo(strFmt('The selected vendor with Rating %1 will be deleted, are you sure ?', vendTable.CreditRating), dialogButton::Yes, 'Confirmation') == dialogButton::Yes)
	{
	Info("Operation successfull");
	}
	}
	/// <summary>
	///
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	[DataEventHandler(tableStr(VendTable), DataEventType::ValidatingWrite)]
	public static void VendTable_onValidatingWrite(Common sender, DataEventArgs e)
	{
	VendTable vendTable = sender as VendTable;
	//Add business logic
	if((vendTable.CreditMax && !vendTable.CreditRating)
	|| (!vendTable.CreditMax && vendTable.CreditRating))
	{
	warning("Credit rating must filled for credit limit");
	}
	}
	/// <summary>
	///
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	[DataEventHandler(tableStr(VendTable), DataEventType::ModifiedField)]
	public static void VendTable_onModifiedField(Common sender, DataEventArgs e)
	{
	ModifyFieldEventArgs event = e as ModifyFieldEventArgs;
	VendTable vendTable = sender as VendTable;
	FieldId fieldId = event.parmFieldId();
	switch(fieldId)
	{
	//Add business logic
	case fieldNum(VendTable, PaymMode):
	Info("Business logic for PaymMode");
	break;
	default:
	break;
	}
	}
	/// <summary>
	///
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	[DataEventHandler(tableStr(VendTable), DataEventType::ValidatingField)]
	public static void VendTable_onValidatingField(Common sender, DataEventArgs e)
	{
	VendTable vendTable = sender as VendTable;
	ValidateFieldEventArgs validateFieldEventArgs = e as ValidateFieldEventArgs ;
	boolean ret = validateFieldEventArgs.parmValidateResult();
	if(ret)
	{
	switch(validateFieldEventArgs.parmFieldId())
	{
	//Add business logic
	case fieldNum(VendTable, CreditMax):
	if(vendTable.CreditRating == "Amber" && vendTable.CreditMax > 10000)
	{
	vendTable.CreditMax = 0;
	Error("Credit can not exceed more than 10K for Amber rating");
	ret = false;
	}
	break;
	}
	}
	validateFieldEventArgs.parmValidateResult(ret);
	}
	}

Reference