As of several versions of Microsoft Dynamics AX there is a feature to setup and use temporary vouchers for journal entry. Also, this feature still exists in Microsoft Dynamics 365 for Finance and Operations, Enterprise edition. In this blog post I will tell you about the temporary voucher number and namely how you can benefit from this feature.
There are countries/regions where it is mandatory to use continuous voucher series. While saying continuous, it should be really continuous. When you do setup a number sequence to be continuous it is still possible to get gaps in posted vouchers. A lot of accountants did complain about the gaps. A possible cause for these gaps can arise in the next situation.
Assume user A and B are entering general journals. Voucher GJV0050 was the last posted voucher number. User A creates lines in a journal using the next voucher number: GJV0051, GJV0052, GJV0053 and GJV0054.
User B also creates a journal with voucher numbers: GJV0055, GJV0056 and GJV0057.
Now User B did check his journal and posted it. User A decides to delete the rows as he discovered the transactions were already posted before. Now we do have a gap in posted voucher numbers.
For some countries/regions it isn’t a problem as it would be allowed to reuse the ‘gaps’. Some countries do have regulations that per period the numbers should not have any gaps, so using numbers GJV0051, GJV0052, GJV0053 and GJV0054 in a period later than the already posted numbers are not allowed.
To overcome this issue, you can work with temporary vouchers.
When using temporary vouchers, the journal lines to get a voucher from the temporary voucher series. In the example above, the users would create the next lines:
User A: TMP0078, TMP0079, TMP0080 and TMP0081.
User B: TMP0082, TMP0083 and TMP0084.
During posting the temporary voucher numbers are being replaced with the correct voucher series for the journal. So, when user B will post his journal, the vouchers will be correctly replaced with GJV0051, GJV0052 and GJV0053. Per temporary voucher number a new voucher from the series will be retrieved. As you will notice now, there are no gaps anymore in the real voucher series.
There are two settings required to be able to work with temporary voucher numbers. The first setting is the Temporary voucher number sequence on the General Ledger parameters form.
The second setting is the Number allocation at posting on the Journal names. Per journal you can decide to enable the use of temporary vouchers or not.
When you do create journal lines, initially it will use the temporary voucher number as you can see in the next screenshot.
During posting of the journal, it will replace the temporary numbers with the actual voucher numbers.
When you have to work a lot with importing journal lines, it would be possible to use e.g. the Data Import Export Framework. Custom import scripts can also be used. In both ways, you have to take care of the correct voucher numbers. For these scenarios, also the temporary voucher is a great aid.
You can provide manual assigned numbers in your source file which could be in fact also against the setup of the temporary voucher number sequence. Per journal you can start over with voucher number 1 if you want to. Whatever you provide, during posting each unique provided voucher number will be replaced with the final voucher number retrieved from the journal setting.
If you imported lines manually and try to delete them as they were incorrect, there is a check if the temporary voucher number meets the format of the temporary voucher sequence. If it doesn’t match, an error will be raised.
This error sounds odd as you only want to delete not posted journal lines. The reason for this is that if you did setup the temporary voucher number to be continuous, it should put the number in the pending number sequence list. That is not possible if the format is different.
For this reason, probably avoid using formats for the temporary voucher series, so that the numbers will be natural numbers like 1, 2, 3, etc.
There is one disadvantage to mention. If there is an error during posting, it will give you the error with the replaced voucher number. The error causes the journal lines to get the old values, so with the temporary voucher numbers. For this reason, it is hard to find the row(s) which contains the error in larger journals.
That’s all for now. Till next time!
Microsoft Dynamics CommunitySubscribe to this blogger RSS FeedMy book on Merging global address book records in AX 2012
The post What is the temporary voucher functionality in Dynamics 365? appeared first on Kaya Consulting.
This post will inform you about the Entity execution parameters which can be found on the Data import/export framework parameters form. It will give you an option for gaining performance when importing large number of records using the data management features.
The entity execution parameters do have an option to provide information how to divide the workload when performing data import using the batch framework. In Microsoft Dynamics AX 2012 we were used to specify the number of tasks when creating the processing group. In the current product it can be defined in upfront as a parameter.
Use the next path to open the Entity execution parameters: System administration > Workspaces > Data management; Then click the Framework Parameters tile. The Data management workspace can also be started from your default dashboard.
On the Framework parameters form you can select the tab page Entity settings and the button Configure entity execution parameters to open the next form.
As you can see in the picture above, you can specify a threshold per entity when the tasks need to be split and how many tasks will be created then. You can also make variations for the same entity. So, you can use 4 tasks for smaller and 8 tasks for larger files.
When specifying these parameters with a task count, it will create multiple threads where the workload will be divided and run in parallel. From my experience, this can make a huge difference in performance during data import.
To be able to really use these settings, you have to run the import using the batch framework. The Import button on the Import project form, will start a single thread on the server. You can use the Import in batch button on the Import options menu.
Then complete the parameters on the batch slider to have the batch scheduled. When the batch job is executing, you can actually monitor how many tasks are created on the View tasks form. From the Execution details, you have a link to the batch job. On this form, you can browse deeper into the tasks for this job.
Some data entities like Fixed Assets, are not created to support the use of multiple tasks. When trying to setup these entities, you get an error like shown in the screenshot below.
The post Speed up data import with (data) entity execution parameters appeared first on Kaya Consulting.
It’s been a while since I posted a blog post. I have been extremely busy and had barely time to sit down and finish some posts. During my work I collected some experiences and made some drafts, so I will be publishing some more blog posts, for sure. One of the things that took some time from me was testing platform update 11 before it could be released. This platform update was released October 6, 2017 and mainly has some technical improvements. The majority is related to support more options for extending the application to prevent overlayering. One new item is a functional improvement: a Document count indicator.
In the earlier days of Microsoft Dynamics AX, there was a small indication if a record did contain attachments (notes, files, etcetera). The document button was highlighted or was a bit sunken. This pattern didn’t fit in the user experience of Dynamics 365 and was not implemented. Now in the latest platform update 11 there is a new document count indicator which will show you the presence of documents.
When you now open e.g. the vendor list page, per record, you can see the number of documents attached to it.
The count of document is a good addition, in my opinion. Now you can see if there are attachments, but also the number. Somehow, this experience makes me think of a web shop basket :). When adding a new attachment, this count will increase accordingly, showing then a new count:
When there are over 9 documents, the count will show only’ 9+’ as e.g. ‘325’ would consume too much space. Of course it makes sense, but hovering the button will still show ‘9+’ where there is space enough to display the real count. Personally, I do think the real count would give more value to this feature. For now, I’m confident this feature will satisfy the need from many customers.
When you want to learn more about platform update 11, you can visit the page What’s new or changed in Dynamics 365 for Finance and Operations, Enterprise edition platform update 11 (October 2017).
Microsoft Dynamics AX CommunitySubscribe to this blogger RSS FeedMy book on Merging global address book records in AX 2012
The post New document count indicator in platform update 11 appeared first on Kaya Consulting.
When using Microsoft Dynamics 365 for Operations you can create new security roles or even customize existing ones. When you do so, you have to be careful if everything is working like intended. This blog post will tell you about the enhanced worker information and how to grant access to this.
When working in the application, some fields contains a name of the worker. To be able to select the correct worker, there is a neat lookup form, showing some additional information. Also, when you hover over the name field, a preview form with a picture and contact details will be shown. These forms are illustrated in the next screenshots.
As you can see, there is no standard lookup with just a number and name, but it almost looks like the preview of Skype for Business. When configured correctly, also the presence indicator will show you if the person is available for chats.
When you create a custom role, the lookup and preview form might not work due to not sufficient permissions. I will show an example what might go wrong. For this example, I created a new security role with only two duties. The Purchasing assistant would be allowed to maintain purchase orders and view information about vendors.
This role is assigned to a user and will behave in the next (wrong) way.
Initially the lookup is throwing an error. A second attempt will again raise the error and shows an empty lookup form. The tooltip is not showing the enhanced form, but only repeats the worker name. So now I’m disappointed and would like to have a solution for this as I’m aware of the nice preview and lookup capabilities.
I did explore the application and Visual Studio how this information has been secured. It appears to be part of the Employee security role out of the box. If you don’t want to assign the complete role, you can also add the duty View basic information about workers in the system into your security role. Also, other roles like Account payable clerk, Manager, Human resource manager do have this duty included. I assume you don’t want to add for example the Recruiter role to have the enhanced information available.
This fixes the lookup in all cases. However, you should keep in mind that the neat preview isn’t always working. It is only working when the user also has access to the Worker detail form. There is some weird behavior on the tooltip functionality in combination with clickable fields which will open the details. So it seems like it first checks the access on the Worker details form and forgets about the tooltip preview. In my test I did add the menu item in a separate privilege. In this way there is no access on the standard worker list pages.
It would be possible to create a privilege with the menu item HCMworker and grant read access only. In that scenario a user would have access to the worker details with the next information. This user has no access to private information, so private addresses and contact details are not visible. The details form is then filtered on one worker only.
There might be reasons why you don’t want to grant access to the worker details. In that case, currently the preview tooltip will not work. Considerations are:
The post Don’t forget the enhanced worker information appeared first on Kaya Consulting.
Recently we had a challenge to read uncommitted data from the Dynamics 365 for Operations database. Usually this is not required as you may retrieve dirty data. When using a business application, you should rely on correct and committed data as otherwise reporting might provide you incorrect data. However, if you find a very good reason to change the isolation level temporary, this post will guide you how to achieve this.
First of all, I’m not a technical database specialist. Thanks to several troubleshooting on performance and finding gains in Microsoft Dynamics AX (and in my previous life, also NAV), I learned something about locking and data isolation. A recommended read would be the documentation from Microsoft: Isolation Levels in the Database Engine and Understanding Isolation Levels.
It is measured that the Microsoft Dynamics AX database performs the best using the option READ_COMMITTED_SNAPSHOT. You can read more about this on the next page: Configuring the Microsoft Dynamics AX business database.
When you want to run a report while other users are attempting to insert and update records, you must ensure that data will be consistent. E.g. when updating a packing slip, first the sales order lines are being updated; then the sales order header. In opposite when you run a report, the header is retrieved first; then the lines. If you first print the header as still being an open order, you don’t want to have the lines already updated with the status and remaining quantities. For this reason, data which will be updated (header and lines) will be isolated until the transaction is complete.
Sometimes, you want to read data which has not been committed yet. A reason would be having a look at intermediate data when performing larger insert or update sets. As mentioned before, the downside is that there are risks of reading dirty data.
When we had an on-premise SQL server, it is possible to use statements to set connection properties in X++ or C# coding. As we have to deal with an Azure SQL server database, we cannot use this. Also, I understood that select statements containing the keyword WITH NOLOCK is not supported on Azure SQL databases. The only option to read uncommitted data seems to be using a statement like:
set transaction isolation level READ UNCOMMITTED
select * from myTable
But then the 64-million-dollar question: How can we do this using X++ statements? And moreover: How to translate an advanced query object containing multiple data sources and ranges with wildcards?
Continue reading for your answer…
To execute a simple SQL statement, you can use the next coding for reading uncommitted data:
str sqlStatement = 'set transaction isolation level READ UNCOMMITTED\n'
+ 'SELECT * FROM MYTABLE';
UserConnection connection = new UserConnection();
Statement statement = connection.createStatement();
resSet = statement.executeQuery(sqlStatement);
The result set (resSet) contains the data retrieved from the database. You have to extend the coding to read the data from the result set. An example is provided below. Note that you are executing the command directly. When you need to retrieve data from a single company, you have to specify the filter with a WHERE clause yourself.
Suppose you have a (packed) query object which contains multiple data sources and some more advanced queries. For example, a query with customers from several regions and a date range between January 1 and April 30. On forehand, you don’t know the number of data sources and query ranges. Good to know, there is one x++ statement getSQLStatement() which will do the correct translation for you. This method has been introduced in Microsoft Dynamics AX 2012 R2. This method is used by the Data Import Export Framework which also needs to get the statement and alter it for some reasons.
If you use this statement, it will convert a query to an SQL statement, initially using parameters for the range values such as the DataAreaId. For this reason, also the parameters needs to be converted to literal range values. Now, given some complexity, use the next X++ statements to perform the SQL statement with reading uncommitted data:
str sqlUncommitted = 'set transaction isolation level READ UNCOMMITTED';
UserConnection connection = new UserConnection();
Statement statement = connection.createStatement();
// Assume the _queryRun variable was received as method parameter
// First set the query to send literal values instead of parameters
sqlQuery = _queryRun.query().getSQLStatement();
sqlStatement = strFmt('%1\n%2', sqlUncommitted, sqlQuery);
resSet = statement.executeQuery(sqlStatement);
// Loop and read the result set
// If the indexes in the resSet variable match the column indexes, use the next statements
RecId recId = resSet.getInt64(1);
GroupId groupId = resultSet.getString(2);
Description description = resultSet.getString(3);
This is one example how to read the data. It is also possible to make this more flexible by using the ResultSetMetaData class. Using this class, you can find out the number of columns and find column names to convert the values in your own variables.
I like to conclude my post with some warnings and notes for consideration.
The post How to: Read uncommitted records using Dynamics 365 for Operations appeared first on Kaya Consulting.