I have a string which stores the bufferTable name, now i want to use this string in a select statement. I could use this sample code
The Set class is used for the storage and retrieval of data from a collection in which the values of the elements contained are unique and serve as the key values according to which the data is automatically ordered.You can create a set of primitive data types or complex data types such as a Class,…
I had a scenario where I had to call a new method in one of the fields from Vendor Payment Journal Line.The new method exists in Extension class of Vendor Payment journal.(which is a form method). This has to be called in modified method of the field ‘Ledger Dimension’.The steps are as follows How do…
You can use following code/job to get a bank account name of a customer either from custtransopen or custtable.
Dynamics 365 for Finance and Operations on a local development environment may all of the sudden start reporting problems with the server due to expired certificates. Here you can find the solution to the issue : https://ax.docentric.com/expired-certificates-renewal-in-d365fo/?toolbarclose=yes
1) Create contract class for both outer and inner node. 2) Use FormJsonSerializer::deserializeObject method Step 1 : Create contract class 1 to hold all the values of data node. Note : DataMemberAttribute name should be matched with JSON key name i.e. data, ID Nation Step 2 : Create contract class for outer node. In your case if…
Step 1: From the home screen of D365, go to the system administration workspace. Not from the modules. See below screenshot: Step 2: From the summary, click on “Data management IT”. See below screenshot for guide: Step 3: Go to the recurring data jobs and find the required data job, in the enabled field, select…
You can use FormDataSource.displayOption method This method is executed one time for each record before the record is displayed in a form. This is an example on how to change the backcolor of a row grid at Form DataSource level using Extensions
I have a table and I need to retrieve the ID of the Second row. How to achieve that ? By Top 2 I select the two first rows, but I need only the second row Solution : you can use OFFSET and FETCH NEXT NOTE: OFFSET can only be used with ORDER BY clause. It cannot be used on its own.…
Good post that describes how to override Lookup method using Event Handler : https://dynamics365musings.com/override-an-existing-lookup-method-event-handler/
Just something simple to try – you may have tried it already. Right click the Solution in solution explorer, click “clean solution”, this deletes all the compiled and temporary files associated with a solution.
Given the following table in SQL Server What is the best way to write the query that yields the following result (i.e. one that yields the final column – a column containing the minium values out of Col1, Col2, and Col 3 for each row)? Using CROSS APPLY! like shown in below code
This post describes how to renew an SSL certificate on a DEV environment
If you want to share a Form record within AX with others for example within an e-mail you can do it by using a D365FO API. This API let’s you create URL links that point to certain forms and records Below an example method, used in a runnable job, which generates a deep link for…
You can use DateTimeUtil::time(MyDate); Retrieves the number of seconds that have elapsed since midnight as a timeOfDay value from the specified utcdatetime value.
This post shows how to add n working days to a date by using D365FO calendars : https://dotnetxperience.blogspot.com/2016/01/ax-2012-x-add-n-working-days-to-date.html
To remove a substring from another string you can use this custom method
Searches a text string for an occurrence of another string. int strScan(str _text1, str _text2, int _position, int _number) The comparisons aren’t case-sensitive. Values for the _position parameter that are less than 1 are treated as 1. The direction of the scan is controlled by the sign that is specified in the _number parameter. A positive sign indicates that each successive comparison…
This post describes how to iterate all controls of a Form : https://microsoft-dynamics-ax-erp.blogspot.com/2012/07/iterate-through-all-controls-of-form.html
This post describes how to Display the Form With Different Colors For a Particular Control Value or Rows grid cell : https://ashokax.blogspot.com/2017/09/how-to-display-form-with-different.html
Have you ever come across requirements where business often wants easier ways of identifying data records in certain status with specific color coding of the records? For example you might want to see all Approved Sales or Purchase orders marked in “Green Color”, and all Rejected/In Review sales orders marked in Orange/Red color. This was just an example to…
I have a form with 3 datasources – CustPackingSlipJour, PurchLine, WHSLoadTable. I have placed a display method on CustPackingSlipJour form DataSource, but I need access to one field on PurchLine. All I can access is the CustPackingSlipJour which is passed as a parameter _custPackingSlipJour, as standard. How can I retreve value from the second DataSource…
You can use extension to enable or disable a Form Control. In this example I will show how to disable a Form Control button (LineStripNew button) in an extension class od SalesTable Form
I want to get a lookup for all available table names in the system. I know to achieve this on a dialog but now I want a lookup on a stringedit/combobox on a form. The only way I know is to create a table with a field bounded to RefTableId/TableName EDT and override the lookup…
To set a Combobox default value just set the DefaultSelectedItems property like shown below : DefaultSelectedItems = [“Your default value”]
In PowerApps every data source (SharePoint, Dataverse, OneDrive) is under limitation of 500 items by default but you can change this and retrieve up to 2000 which is a maximum value you can set which means you can’t get more that 2000 items To do that just open your app > Settings > General >…
The Default property of the gallery control is the item or record from the data source to be selected in the gallery when the app starts up. In my example I have a button in a gallery which is selected by default whenever I start my APP, but I want to disable this behaviour. To do…
Here is an example of how to use a multi select lookup in a SysOperation class 1. Data contract class: (just showing relevant parameters, there can be other parameters too). Just to note, container type parameter can also be used in place of List. 2. UI Builder Class: This is most important to build a multi…
I have two tables tha are identical (Different names but same fields). The second table is the exact copy of the first When a record in the first table is modified I want the record in the second table to also be modified. To know if the record has been changed I should check every…
You can delete multiple records from a database table by using a delete_from statement. This can be more efficient and faster than deleting one record at a time by using the xRecord .delete method in a loop. If you have overridden the delete method, the system interprets the delete_from statement into code that calls the delete method…
There are several different ways to create records using X++. Insert Types Single Insert Pattern This is an example of a single insert pattern in a loop; open tran, set values, call insert(), close tran, loop Multiple Insert Pattern This is an example of a multi insert pattern with a loop; open tran, set values,…
Most applications today use restful web services to communicate with each other. Sometimes, however, we are required to work with SOAP web services. This post shows hot to use it : https://www.sikich.com/insight/how-to-use-soap-pass-through-with-logic-apps-custom-connector/
Substring Function String Comparison String Deletion Find characters in string String format Length of string Convert string in lower case Convert string in upper case Repetition of string Convert date into string in x++
This is a simple example of how to call external web services from D365FO without using any C# DLL files. Just plain X++ code. As sample External Web Service, I have selected Microsoft Flow. Azure Logic Apps will be similar. Create a test Microsoft Flow Let’s create a simple Microsoft Flow that will be triggered…
It’s easy, you must use this.formRun().design().controlName() syntax like shown below
Sequence of Methods calls while opening the FormForm — init ()Form — Datasource — init ()Form — run ()Form — Datasource — execute Query ()Form — Datasource — active () Sequence of Methods calls while closing the FormForm — canClose ()Form — close () Sequence of Methods calls while creating the record in the FormForm…
Occasionally we will need to change the accessibility of a certain controls/fields on a form in Dynamics AX. This can either be to hide/disable or show/enable the field from the user based on the current record they are on.
If you need to clean up your production orders and their related journals as well as related info, then you’ll want to look at the clean up functionality for production orders. This will delete ended production orders (ProdTable) ended on or before the date you select with business logic and alerts disabled. Next, it will delete…
Dynamics AX provides a framework, which can be used to update production orders to a specific status. In the example, a production order is ended/finished. If you want to update several production orders at the same time, you must invoke the ProdMultiHistoricalCost.insert() method per production order and pass the respective record of ProdTable.
The performance of queries that are used to calculate on-hand inventory is affected by the number of records in the tables that are involved. One way to help improve the performance is to reduce the number of records that the database must consider. This article describes the on-hand entries cleanup job, which deletes unneeded records…
If you have enabled database logs for some time you will have noticed that the amount of data grows very quickly. Maybe the time has come to delete some data, perhaps those older than a certain date. You can do this by running the “Database log cleanup” batch in D365FO under “System Administation”>>”Periodic Tasks”>>”Database” Here…
Use this batch job to maintain the InventDim table by deleting unused inventory dimension combination records that are no longer referenced by any transaction (regardless of whether the transaction is open or closed) or by any master data. You cannot delete an inventory dimension combination record that is still referenced by a transaction, because when…
Reports are useful to organize data into summaries and grouping to quickly visualize the data. Sometimes having additional visual cues can be helpful to zoom on key sections of the report. In this tip, we will look at how to add conditional formatting to a SQL Server Reporting Services SSRS report to make reports even…
There seems to be an issue with the str2num function when what you want is a real value. This matters because it can be used to convert from a string to an int or a real. In order to successfully convert from a string representation of a decimal value to an actual real type on X++, you…
While calling a Stored procedure on a linked Server I face this error : “Server ‘xxx’ is not configured for RPC” Open the linked server properties and go to the Server Options tab, there is an option for RPC and RPC Out. RPC Out needs to be set to True in order to execute a…
First Example The macro will select records from a table ‘CustTable’ and print a field from the same as the table to be fetched, and the fields (%1, %2, %3) to be printed are specified in the parameters for the first Macro call.In the second Macro call, the table has not been specified so text…
When the packing slip ID is generated from the load or the shipment or the packing station, the table WHSLoadTableCustPackingSlipJour stores the link between the load ID and the packing slip ID. The below example method gets Load from a given packing slip
There a clever query utility which let’s you create dynamic ranges. The different options are (today’s date is 25-02-2014): To see the results in a job or to play around with the different date options, you can copy and use the job below:
I have a form with a string field. In this field I want to display a lookup with the users of my organization (userid and display name). When I select a user I want to save the user’s display name in the form field. Override the form field’s lookup() method with this code
After creating a data entity you may get the error Validation started 10/28/2023 10:21:26.Validation step: Database synchronization started. Time: 10/28/2023 10:21:26Log level – Error | Infolog diagnostic message: Log level – Error | Infolog diagnostic message: ‘Cannot create a record in Entity (DMFEntity). Entity: Sales orders, OTS_SalesTableStaging.The record already exists.’ on category ‘Error’. these advise…
In a dialog you cannot use primitive type dialog fields. You must necessarily use extended data typesSo how do you use simple string or integer data? Simple, use an extended data type that extends a primitive data type.
In D365FO you can rename the primary key of a table record with the Rename function. This allows you to rename the primary key of any record and ensures data consistency It can be accessed from the Record information form (shown in the following screenshot), which can be opened by selecting Record info from the right-click menu on any record You can…
In simple dialogs we want the form to validate the newly inserted fields when the OK button is pressed and not close if one of these contains an incorrect value. In simple dialogs we cannot avoid closing the form unless we override the canClose() method Follow this example to reach the goal.
First configure SMTP parameters Then use this sample code
Use the following values to specify the positions of the day, month, and year in the _sequence parameter: For example, if the sequence in the string is month, year, then day, the _sequence parameter must be 231. A zero date is returned if the input parameters specify an invalid date. The following two examples specify…
By default, the page size is 8.5 x 11 inches but you can change this size by using the Report Properties pane, Page Setup dialog box or by changing the PageHeight and PageWidth properties in the Properties pane. The page size does not grow or shrink to accommodate the contents of the report body.
The FORMAT() Function The FORMAT() function returns a value formatted in the specified format and optional culture. You can use it to return the month name from a date. Here’s an example: Result: In this case we provided a format string of MMMM which is a custom date and time format string for returning the month name. The good thing about the FORMAT() function is…
The above query gives a datetime value for midnight at the beginning of December 31. This is about 24 hours short of the last moment of the year. If you want to include time that might occur on December 31, then you should compare to the first of the next year, with a < comparison.…
I will show you how you can fix missing labels on horizontal Axis on Columns charts of SSRS report
The “SRSAnalysisEnums” table contains the base enumeration and their translations that is necessary for the generation and update of the SQL Server Analysis Services projects. This table used by the Microsoft for internal purpose, but you can use it to get the enum translations in your language This table can also be regenerated by executing…
To retrieve part of a string in X++ you can use substr() function. This is the function definition str subStr(str _text, int _position, int _number) Parameters Parameter Description _text The original string. _position The position in the original string where the part to retrieve begins. _number A signed integer that indicates the direction and number…
In this example I will be showing you how to get a sales order delivery address using below code.
I need to remove a group by statement from a QueybuildDataSource object. and didn’t find any method to do this (The opposite of addGroupByField). Unfortunately, x++ does not have any method to remove one field from group by but by some tricks we can do that. I have written a method for removing group by…
I have a form and I want to get Form Control objects only from Control name. Thi s example shows hot to do that
SQL Server Reporting Services starts up slowly after a period of inactivity when I try to access the reports page. Sometimes it’s slow accessing it just half an hour later, sometimes hours later, or next day, but I can’t figure out why or the pattern behind it? SSRS has a XML configuration file with a…
To check how many CPU’s SQL Database Server is able to use, run the following SQL Query: select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers the CPUs used are those with status = Visible online
In AX X++, there are two keywords that are often confused between one another but do very different things: Break and Continue. Both have their own purpose independent of each other but often people forget. Break – Breaks out of the loop completely. If there are 10 records to loop over, it will break skip all…
Accidentally left the caps lock on and typed something, but can’t be bothered to start again and retype it all? Simply enter your text in this site and choose the case you want to convert it to
If you want to display dates according to client’s datetime format then you have to specify in Language property of Report.Set Language property under localization tab of Report properties (see below)
Comma separator tool automatically converts a column to a comma list or list to CSV (comma-separated values). Copy your column of data and watch the conversion happen. You can do the reverse and convert a delimited list to a column. You can use our comma separator tool to convert column lists to comma-separated value (CSV)…
Below is the example for how to use notexists join in x++.
DateNull for the date can be checked by two ways :
If you need to copy data from one table to another table which has similar structure (Eg. to history or logging table) the .data() and buf2Buf() cannot be used. But we can make some modification to the buf2Buf() method to copy based on field name instead of field Id. Just create a new static method…
Do you need to remove the spaces in a string like that ? Eg: str txt = ” abc def ghi”; I need to remove the spaces in the string which are between ‘abc’ and ‘def’ and ‘ghi’ in the string txt.
To populate a temporary table with data from a persisted table, use the setTmp() and data() method. The following code example copies all invent items in Toronto to the temporary table.
How about running custom X++ scripts without any downtime? This video explains how : https://www.linkedin.com/feed/update/urn:li:activity:6894355177550209025
If you’re gonna write code like A = B /C, you better make sure that C holds a value.Of course you can do a check with a simple if-statement (if C then A = B/C), but there is an easier way. You can use minOne, a method that exists in the Global class.
Example below explains how to create a QueryBuildRange on default field CreatedDateTime.
I need to get the second occurrence of the space for below text. It should be the space after the 56 select charindex(‘ ‘, ‘Posted/edited: 56 days ago’, 2) The solution would be: Select charindex(‘ ‘,’Posted/edited: 56 days ago’,(charindex(‘ ‘, ‘Posted/edited: 56 days ago’) +1))
When we open the child form from another form the datasource of the child form is sync with the previous form this is due to the dynalink between these two forms and all the data is shown on the child form. you can use the below code on the init method of the child form…
Use this command.Select * from SysTableIdView Where SystableIdView.Name = ‘PurchTable’You will get Table id
Reserve: Remove reservation:
To check if a UTCDateTime is null you can use Global::utcDateTimeNull()
Below shows how to get the serial ids or the current qty that is reserved for a sales order, sales line or sales parm line (picking, packing, etc.. form aka SalesEditLines (form))
Do you want to filter a form date field showing only today’s records? You can use AddRange Method and “T” value like shown below
you can easily find the latest Posted sales order confirmation with X++. To do this you need to extend the CustConfirmJour Table class and create a new method. Here is an example