Search This Blog
April 30, 2023
Azure Key vault parameter setup in D365FO
February 25, 2023
Best way to do Postman setup with D365FO
7. Name this request as 'Authorization', you can name it as per your use like getting public entities details or get a specific entity data or metadata. In the post request paste below as is.
Now you see we parameterize most of things and you don't need to create multiple request for different environments, you can simple change the environment from right top corner.
December 26, 2022
More about Bicep
November 11, 2022
Useful links for OData and Rest API integration
July 30, 2020
Say Hello to Microsoft DataFlex
Hi Folks,
Here we go again with a rebranding around CDS which is now known as DataFlex, yes that's right, CDS is now DataFlex - a new low-code data platform for Microsoft Teams. You can create and run thousands of applications, flows, and intelligent agents with a smart, secure, and now with scalable low-code data platform.
What it offers:
DataFlex Pro: Its more or less the same as what we have in CDS today including same licensing i.e.
$10/app/User/Month
$40/User/Month – unlimited apps
DataFlex: Its basically a new simplified version or I would say lightweight of CDS for building an app on top of Microsoft Team. It includes as free with your Microsoft office 365 license which includes Teams.
Below is a screenshot for reference, hoe easily you can create different tables and columns to meet business requirements.
Why DataFlex?
i. Provides the ability to use model-driven power apps in Teams with no extra licensing cost.
ii. Currently, to build an app for Team you need to use the SharePoint list to save some licensing cost that too not a relation Database and without scalability. While DataFlex provides a free relation DB to build a team-based PowerApps
iii. A low code tool that enables you to build Team apps and bots similar to native apps.
iv. Streamline identity protection and secure guest access with identity management and multifactor authentication.
v. Reduce data management stress and let Dataflex Pro determine your storage needs for relational data, file and blob storage, logs, and search indexing.
vi. Quickly develop applications your way—either with custom code or using no or low-code—across Azure, Office 365, and Dynamics 365, and with more than 300 connectors.
vii. Create a solid data foundation using automatic duplication detection and more than 300 data transformations that clean and reshape data
Further references:
> Introducing Project Oakdale, a new low-code data platform for Microsoft Teams
> CDS Doc
September 02, 2019
Set up MT940 format for bank reconciliation #MSD365FO
In D365FO advance bank reconciliation is a feature to import bank statement file and automatically reconcile with related bank accounts. There are many formats which a bank commonly used i.e. ISO20022, MT940, BAI2.
In this post, we will see how to set up the MT940 format which is commonly used in most banks nowadays.
Let's get started.
Step 1: Get sample entity template and transformation files
To transform the source file into FnO format, you need few files and these are available under 'Resources' node of AOT. Files names are as below
Step 2: Create an import project
Under Data management workspace, create an import project with name MT940. Add a new file with below details
I. Entity Name: Bank statements
II. Upload file name: SampleBankCompositeEntity (which you got from Resource node)
Once file successfully uploaded, click on view map. On next screen select BankStatementDocumentEntity from the list and click on 'View Map' and go to 'Transformation' tab. Click new and click upload file, select different XLST file one by one, in the sequence shown in below image.
Step 3: Setup Bank statement format
Navigate to Cash and Bank management > Setup > Advance bank reconciliation setup > Bank statement format.
Here create a new record as below
Step 4: Configure the bank account to use Advance reconciliation option
Navigate to Cash and Bank management > Bank accounts. Select a Bank account to view details. Under Reconciliation tab,
I. Set the 'Advance bank reconciliation' option to 'yes'. This is a one-time setup, the system doesn't allow to undo/change once set to yes.
II. Set Statement format field to format which we have created in step 3 i.e. MT940
Step 5: Testing
Navigate to Cash and Bank management > Bank accounts. On Reconcile tab click on Bank statements.
On the next screen click on Import statement. A dialog will pop up. Fill/select details as below
I. Import statement for multiple bank account in all entities. : Set this as Yes if your file contains more than one bank accounts.
II. Bank Account: If the source file contains a single bank, select that bank here.
III. Statement Format: Select your statement format here it must be MT940.
IV: Bank statement file import: Select source file and click upload.
V: Click ok and it must import transaction in the current form.
Note: After every DB refresh you need to redo import project. DB will break the links and you need to remove the entity from your import project and add upload the transformation files accordingly.
-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta
PS: This post referred to MS documentation.
May 13, 2019
How to add 'Actions' on Odata entities
[Updated on Jan 27, 2020
I got one feedback about Odata action on entity extension are not supported. It worked for me for one odd case, I yet to test myself if its not working now. I would request all readers to take necessary caution while trying below code. Thanks to Ashish for sharing his feedback.]
public static void <methodName>(<parameters(optional)>)
{
}
October 02, 2013
Connecting to Databases through X++ PART -IV
Connecting to Databases through X++ PART -IV
{
}
static void dbConnectionClass()
{
ResultSet rs;
SqlStatementExecutePermission perm;
;
perm.assert();
{
info(rs.getString(1));
}
CodeAccessPermission::revertAssert();
}
private static ResultSet statementExeQuery(str _sql, Connection _con = null)
{
ResultSet resultSet;
Statement statement;
;
{
if(!_con)
{
_con = new Connection();
}
// BP deviation documented
resultSet = statement.executeQuery(_sql);
}
catch (Exception::Error)
{
throw error("@SYS99562");
}
}
{
;
}
Related posts,
-Harry
September 16, 2013
Connecting to Databases through X++ PART -III
Connecting to Databases through X++ PART -III
OLEDB Connection:
{
System.Exception e;
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand cmdSelect;
System.Data.OleDb.OleDbDataReader reader;
InteropPermission perm;
str connectStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;"+
"Persist Security Info=False;Initial Catalog=AX2009;Data Source= theAxapta ";
str exceptionStr;
;
{
perm = new InteropPermission(InteropKind::ClrInterop);
if (perm == null)
{
throw error("Error with file permissions");
}
perm.assert();
objConn.Open();
cmdSelect.set_CommandText("SELECT * FROM CustTable where DATAAREAID = ‘CEU’");
reader = cmdSelect.ExecuteReader();
{
info(reader.GetString(0));
}
}
catch(Exception::CLRError)
{
CodeAccessPermission::revertAssert();
if (perm == null)
{
return;
}
perm.assert();
{
exceptionStr += e.get_Message();
e = e.get_InnerException();
}
info(exceptionStr);
}
catch
{
error("An Exception has occurred");
}
objConn.Close();
}
Other related posts:
September 11, 2013
Connecting to Databases through X++ PART -II
Connecting to Databases through X++ PART -II
Class Name | Description |
Helps in establishing a connection to the target database. | |
Helps in executing a command (a Text type or a Stored procedure) | |
Stores the data | |
A collection of all fields in CCADORecordSet | |
A single field from the collection of fields | |
A class that helps in passing parameters that a command needs or demands |
{
CCADOConnection connection = new CCADOConnection();
CCADOCommand ccADOCommand;
CCADORecordSet record;
str connectStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;"+
"Persist Security Info=False;Initial Catalog=AXDEVDB;Data Source= theAxapta";
;
try
{
connection.open(connectStr);
ccADOCommand = new CCADOCommand();
ccADOCommand.commandText("Select * from CustTable where DataAreaId = ‘CEU’");
ccADOCommand.activeConnection(connection);
record = ccADOCommand.execute();
recordSet = record.recordSet();
while (!record.EOF())
{
info(any2str(record.fields().itemIdx(0).value()));
recordSet.moveNext();
}
}
catch
{
error("An Exception has occurred");
}
}
-Harry
September 09, 2013
Connecting to Databases through X++ PART -I
{
LoginProperty loginProp;
ODBCConnection conn;
Resultset resultSet, resultSetCount;
Statement statement1, statement2;
;
loginProp.setDatabase(‘AXDEVDB′);
resultSet = statement1.executeQuery("SELECT * from CustTable where DATAAREAID = ‘CEU’");
{
info(resultSet.getString(1));
}
}
Other related posts:
-Harry
May 22, 2013
How to Read/Write an Excel file through X++ code
How to write an Excel file through X++ code
In This Post you will found two code sample1.Write data in excel through X++ code.
2. Read from an Excel through X++ code
1.Write data in excel through X++ code.
static void thaAxapta_Write2Excel(Args _args)
{
InventTable inventTable;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');
cell = cells.item(1,1);
cell.value("Item");
cell = cells.item(1,2);
cell.value("Name");
row = 1;
while select inventTable
{
row++;
cell = cells.item(row, 1);
cell.value(inventTable.ItemId);
cell = cells.item(row, 2);
cell.value(inventTable.ItemName);
}
application.visible(true);
}
2. Read from an Excel through X++ code
static void theAxapta_ReadExcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
ItemId itemid;
Name name;
FileName filename;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "C:\\item.xls";
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
itemId = cells.item(row, 1).value().bStr();
name = cells.item(row, 2).value().bStr();
info(strfmt('%1 - %2', itemId, name));
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
March 14, 2013
Exporting data to Excel from axapta x++
Exporting data to Excel from axapta x++
Hi All!
Sometimes we need to export data from Microsoft Dynamics AX to Excel using axapta x++ code and we don't know how to do this...
Exists some differents ways to do this, but I think the best way is using the SysExcel class of Dynamics AX and its related.
The only problem I found using this class... is that it can not be used in a batch process.
Sample code:
static void TheaxaptaCreateExcel(Args _args)
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheetCollection;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
CustTable custTable;
int row = 1;
str fileName;
;
//Filename
fileName = "C:\\Test.xlsx";
//Initialize Excel instance
xlsApplication = SysExcelApplication::construct();
//Open Excel document
//xlsApplication.visible(true);
//Create Excel WorkBook and WorkSheet
xlsWorkBookCollection = xlsApplication.workbooks();
xlsWorkBook = xlsWorkBookCollection.add();
xlsWorkSheetCollection = xlsWorkBook.worksheets();
xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1);
//Excel columns captions
xlsWorkSheet.cells().item(row,1).value("Account Num");
xlsWorkSheet.cells().item(row,2).value("Name");
row++;
//Fill Excel with CustTable AccountNum and Name fields (only 20 records)
while select custTable
{
if(row == 20)
break;
xlsWorkSheet.cells().item(row,1).value(custTable.AccountNum);
xlsWorkSheet.cells().item(row,2).value(custTable.Name);
row++;
}
//Check whether the document already exists
if(WinApi::fileExists(fileName))
WinApi::deleteFile(fileName);
//Save Excel document
xlsWorkbook.saveAs(fileName);
//Open Excel document
xlsApplication.visible(true);
//Close Excel
//xlsApplication.quit();
//xlsApplication.finalize();
}
-Harry
March 06, 2013
Integrating Axapta with Microsoft Outlook
Integrating Axapta with Microsoft Outlook
In Microsoft Dynamics AX 4.0, you can set up the Microsoft Office Outlook Integration to integrate contacts, tasks and appointments between Microsoft Dynamics AX and Microsoft Office Outlook.To set up the Microsoft Office Outlook Integration to integrate contacts, tasks and appointments between Microsoft Dynamics AX and Microsoft Office Outlook, follow these steps:
1. View the e-mail account setting in Control Panel, follow these steps:
a. Click Start, click Control Panel, and then click Mail.
b. In the Mail Setup – Outlook dialog box, click E-mail Accounts.
Note If the profile is not set for the desired user, click Show profiles, change to use the correct profile, click Properties, and then click E-mail Accounts.
c. Click to select the View or change existing e-mail accounts option, and then click Next.
d. Click Change.
e. In the E-mail Accounts dialog box, note the user name in the User Name field, and then note the value in the E-mail Address field.
2. Verify that all information on the Microsoft Office Outlook tab is correct in the Employee dialog box. To do this, follow these steps:
a. Click Administration, and then click Users.
b. In the User dialog box, click a user, and then click User relations.
c. On the General tab, select the employee record that you try to integrate with the current user in Microsoft Dynamics AX in the Employee field.
d. Click Human Resources, click Employee, and then click the record that you selected in step 2c.
e. In the Employee dialog box, click the Contact information tab, and then specify the correct e-mail account information in the E-mail field. This information should match the value in the E-mail Address field in step 1e.
f. Click the Microsoft Office Outlook tab, and then view the information. You may notice that not all information is specified.
Note In this step, you must verify that the "Microsoft Office Outlook user Identification" setting matches the user name in the User Name field in step 1e. This will make sure that you can synchronize Microsoft Dynamics AX and the Outlook client on the computer.
3. Run the Use current Microsoft Office Outlook profile function to set the program to use the current Outlook profile. To do this, click Function, and then click Use current Microsoft Office Outlook profile.
4. Follow these steps to synchronize contacts, tasks and appointments between Microsoft Dynamics AX and Outlook:
a. Run the Pick contact Microsoft Office Outlook folder function.
b. Run the Pick task Microsoft Office Outlook folder function.
c. Run the Pick appointment Outlook folder function.
5. Click Save.
if you want to save send copy in your outlook then check the box
-Harry
January 09, 2013
Sending mail from AX using .NET Framework
Sending mail from AX using .NET Framework
Sometimes happen that SysMailer class (using CDO) is not the right solution for sending mails with attachments. There is a little sample of X++ Job that is using System.Net.Mail namespace to achieve same.static void JobNETSendMail(Args _args)
{
System.Net.Mail.MailMessage mailMessage;
System.Net.Mail.Attachment attachment;
System.Net.Mail.AttachmentCollection attachementCollection;
System.Net.Mail.SmtpClient smtpClient;
System.Net.Mail.MailAddress mailAddressFrom;
System.Net.Mail.MailAddress mailAddressTo;
str strBody;
str strSMTPServer;
str strFileName;
FileIOPermission perm;
;
// preparing parameters
mailAddressFrom = new System.Net.Mail.MailAddres"test@localmail.com", "");
mailAddressTo = new System.Net.Mail.MailAddress("admin@localmail.com","");
strBody = "There is a email body";
strSMTPServer = "MailServerName";
// preparing mail with body, subject, from, to.
mailMessage = new System.Net.Mail.MailMessage(mailAddressFrom, mailAddressTo);
mailmessage.set_Subject("There is a email subject");
mailmessage.set_Body(strBody);
attachementCollection = mailMessage.get_Attachments();
strFileName = "C:\\path\\filename";
// assert permision
perm = new FileIOPermission(strFileName,'w');
perm.assert();
// attaching file to that email.
attachment = new System.Net.Mail.Attachment(strFileName);
attachementCollection.Add(attachment);
smtpClient = new System.Net.Mail.SmtpClient(strSMTPServer);
smtpClient.Send(mailmessage);
// release permision
CodeAccessPermission::revertAssert();
}
-Harry