error: Cannot create record. The Record already exists.

Problem :
Our client recently facing this error during creating any journal lines (LedgerJournalTrans) .

we did restart aos, full CIL, and any standard technical troubleshoot hoping it was a cache error or something like that. And we tried drop the table and inject it with the copied data. After doing some checking on table SystemSequences with table id 212 (ledgerJournalTrans), its nextVal RecId value record is already exist on that table.

reference for RecId on AX :http://www.axaptapedia.com/RecId

solution :
1. Stop the AOS
2. get the max recid from the table on SQL with:

select max(recId) from ledgerjournaltrans;

3. update the systemSequences table on SQL with:

update SYSTEMSEQUENCES set NEXTVAL = 'MAXRECID from table' where tabid= 'tableID'

4. restart AOS and run AX

Conclusion :
they did not doing some sort of AX Version upgrade that will impact whole data
and the main problem why the AX stored procedure on SQL returning next RecId value for the next record that already exist on the table remains a mystery.

Reference :
1. https://community.dynamics.com/ax/f/33/t/109379
2. https://waytoax.wordpress.com/2014/06/01/cannot-create-a-record-in-table-the-record-already-exists-2/

Step by step to deploy newly added WebControl to Enterprise Portal AX 2012

Deploying newly added webcontrol is much complicated than deploying SSRS.
I hope microsoft will simplify it on the next version of AX.

note :
this guide below is only for newly addded webcontrols.
if you already done the steps below, and want to update your webpage by using webcontrol, just right click on web controls AOT, and select deploy. It will replace the old web page with the new web page.

here is the steps for deploying webcontrols :

1. deploy the webcontrol on aot (Web -> Web Files -> Web Controls) , right click and choose “Deploy”
Capture

2. then deploy Web Content Managed on aot (Web -> Web Content -> Managed) , and choose the module
Capture

3. after you deploy web content managed , you will get two new object on AOT,
Web Urls (Web -> Web Menu Items -> URLs), and Web Page Definition (Web -> Web Files -> Page Definitions) , Right click on web page definitions , select “Deploy Elements”, right clicks on web urls, and select “Import Pages”

Web page definitions
Capture

Web Ulrs
Capture

4. Restart di IIS with IISRESET on your sharepoint server , see the reference post below
https://hellodax.com/2015/05/22/restart-iis-command-prompt/

5. Clear cache for making sure the enterprise portal is displaying the newly added webparts. (on some cases you must generate Incremental CIL too)
Capture

6. To check the newly added custom webcontrol, just copy paste your Default Enterprise portal URLS (System Administration -> Setup -> Enterprise portal -> Websites) and webcontrol urls (from Web Urls Properties) to web browser.

default enterprise portal urls :
Capture

web control urls :
Capture

full urls on web browser :
Capture

another guide to add to quick launch sidebar :
https://hellodax.com/2015/05/22/add-new-custom-webparts-data-grid-to-enterprise-portal-quick-launch-sidebar-tutorial/

Search and get table name with parameter table name and have data inside. SQL

this query is useful to check table name with data inside after running the class SysDatabaseTransDelete.

i put another parameter table name, for only search only customize table on AX with Developer identifier.

preview :
Capture

code below

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'TID%'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF 
	(@table_name LIKE @search_string)
	BEGIN
		SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ')'+ 'PRINT ''' + @table_name +''''
		EXECUTE(@sql_string)
	END
    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

Useful powershell command for export and install AXModel

Export axmodel layer example :
Export-AXModel -Model CUS Model -config MicrosoftDynamicsAXUAT -file CUS.axmodel
Capture2

Import axmodel with replace and create parent example :
Install-AXModel -File CUS.axmodel -Conflict Overwrite -Config MicrosoftDynamicsAXUAT -createparents
Capture

reference link :
http://axbloggerblog.blogspot.co.id/p/powershell-commands.html

do full compile, full sync db, full CIL and incremental. Re-deploy website EP if you are customize the EP