Determine table ID in SQL
Create this function to easily get the table number within a SQL statement.-- =============================================-- Create date: 2010.11.01-- Description: Gets the AX table ID for the table name-- =============================================ALTER FUNCTION [dbo].[fnAXTableID] ( -- Add the parameters for the function here @tableName nvarchar(40))RETURNS intASBEGIN -- Declare the return variable here DECLARE @tableNum [...]
Change default layers when comparing code
During the integration of system patches or third-party layers I found it annoying to continually select a certain layer when using the compare tool. Multiply the time it takes by 400 or more nodes and you get an unhappy developer.Fixing this problem is very easy. Just add the following code [...]
Convert Axapta Time in SQL
AX 4.0 stores time in the database using the seconds since midnight. So in order to view the time (military format) we must divide. Here is an example:SELECT TOP 20 StartTime AS [Seconds since midnight], CAST(StartTime/60/60 AS VARCHAR(2)) + ':' + RIGHT('0' + CAST(FLOOR((StartTime/60.0/60.0 %1)*60) AS VARCHAR(2)), 2) AS [Start [...]
Disable users who are not active in Active Directory
Occasionally when auditors come by I like to disable all user accounts in AX which have been disabled in Active Directory. Even though AD will not let them login auditors have a hard time understanding it, so I disable the users. Many times we do not get notification that someone [...]
Line ###-Offset voucher does not exist in account ______.
If a vendor transaction is reversed but an AP check has been printed (and the payment journal not posted) you may get this error. Essentially the record which was marked for settlement by the check has been deleted so it does not exist (VendTransOpen was deleted when the invoice was [...]
Get the next unique file name
Sometimes you are saving a temporary file so you don't want to delete or overwrite anything that already exists in a directory...there is a nice function to find the next unique file name.fileNameTemp = Global::fileNameNext(fileOriginalsPath + fileName);
Show the call stack in the infolog
info(con2str(xSession:: xppCallStack(), 'rn'));
Rename items quickly
Here is a little script to rename items quickly. Just provide a CSV file with old and new item number. I found that making the CSV file was faster than making a job to intelligently rename items because I could use Excel functions and review the new item numbers faster.static [...]
Modifying tab order
In AX to change tab order normally you change the order of the controls in the form. You can set the "Skip" property on a control to Yes and when the user tabs it will skip that control. Certain forms where there are many groups or process flow should be [...]
Nagios: Automatically restart IIS after a CRITICAL alert
You can execute code when a service or host changes state. These are called event handlers.Go to the Windows server and create a local userCreate the following script called iisreset.sh in your Nagios plugins directory replacing the username and password in the three locations below#!/bin/sh## Event handler script for restarting IIS [...]
Deleting all transactions in a company
Sometimes when creating a new playground for testing you need to delete all transactions in a company. There is a nice utility included in AX for this purpose. The SysDatabaseTransDelete class will delete all tables by checking the TableGroup property on the table. All WorksheetHeader, WorksheetLine, and Transaction tables will be [...]
How to modify the permissions of the Admin group
After creating a new security key you will need to modify the permissions of the Admin group, but the User group permissions form does not allow it. This small AX job will grant the Admin group permissions to the new security key.static void grantSafetyAccess(Args _args){ #Admin SecurityKeySet securitySet; ; setPrefix(funcName()); [...]
Reports being used
I was asked recently which reports were being run by users so that some of the menus could be simplified. This SQL will list users and the AX report name for every report that has been run by anyone. It is a good start, but it cannot get us the [...]
Inventory Closing and Adjustment
After writing a report which shows inventory transaction cost as of a specific date we had a small issue where very occasionally certain transactions were showing double cost. After some poking around I found this post:http://fedotenko.info/?page_id=31The developer goes into depth about the procedure conceptually, technically, and differences between AX 4.0 [...]
Physical remaining quantity and total quantity must have the same sign
When posting a production report as finished journal you may receive this error,Physical remaining quantity and total quantity must have the same sign.It is because the remaining quantity on the production order is incorrect.The quantity should never be negative. I think it happens after running estimation incorrectly. Run this job and it [...]
You can’t have unallocated cost on a planning item
When using the functionality provided by Process Manufacturing by ToIncrease occasionally we get the following error when running inventory close and adjustments.You can't have unallocated cost on a planning itemThe error always occurs when it is trying to pass an adjustment through a production order ("Batch order"). Every other month we encounter [...]