Skip to main content

table & field ID conflicts

Some months ago, I posted an article about SQLDictionary ID conflicts. It was a x++ job that scans and solves at once. Last month I have to do this in a use live environment. The customer preferred detailed information about the ID conflicts and solving it was the next step. The keyword in this process is: Don’t lose customer data on the Database!


Let’s look at next example





Tablename
AOT ID
SQL ID
A
50003
40001
B
40025
40013
C
40026
40025






So I created a script that checks:


  • Difference in Table ID

  • Difference in Field ID

  • Difference in String size

  • Changed field properties



Check if documents in document management are attached to tables with an ID conflict. (or amy other table).


From the results I created small jobs that solved the ID conflicts on a structural way.


  • Solve any difference in the field Properties In your AOT (string size, Created date, etc)

  • Solve the Table ID conflicts Process the tables in an order that the change to a non existing ID in the sqldicttable. So we have to solve first table C next table B. We will not fix the Field ID issues yet.





static void repairSQLDictTableConflicts(Args _args)
{
SQLDIctionary dictionary;
void doTable(int aotTable, int sqlTable)
{
ttsbegin;
while select forupdate dictionary where dictionary.tabId == sqlTable
{
dictionary.tabId = aotTable;
dictionary.update();
}
ttscommit;
}
;
ttsbegin;
doTable( tablenum(a), 40001 ) ;
doTable( tablenum(c), 40031 ) ;
doTable( tablenum(b), 40025 ) ;
ttscommit;
}




  • Solve the Field ID conflicts. I used the next job for solving the Field ID conflicts





static void repairSQLDictFieldConflicts(Args _args)
{
SQLDIctionary dictionary;
void doField(int aotTtable, int aotField, int sqlField)
{
ttsbegin;
while select forupdate dictionary where dictionary.tabId == aotTtable && dictionary.fieldId == sqlField
{
dictionary.fieldId = aotField;
dictionary.update();
}
ttscommit;
}
;
ttsbegin;
dofield(tablenum(A),fieldnum( A,field),4001);
dofield(tablenum(B),fieldnum( B,otherfield),4002);
ttscommit;
}




For analyzing the ID conflicts I used this script, It can also be used for checking if the ID conflicts are solved.


static void checkSQLDictionary(Args _args)
{
SQLDictionary dictionary,dictionaryMissing;
str tableName;
int AOTTableID, AOTFieldID;
TreeNode treeNode;
boolean doTable;
boolean skip;
sysdictField sdf;
SysDictTable sdt;
str propertyName;
int sdtFields,sdtFieldsTotal;
;
while select dictionary order by tabid,fieldId where dictionary.fieldId <= 61450&& dictionary.tabId < 65505
{
if(dictionary.fieldId == 0)
{
print dictionary.name;
skip =dictionary.flags;
if (skip) continue;
treeNode = TreeNode::findNode('\\data dictionary\\tables\\'+dictionary.name);
if (treeNode)
{
tableName = dictionary.name;
AOTTableID = treeNode.applObjectId();
if(AOTTableID != dictionary.tabId)
{
skip = true;
error(strfmt('conflict at Table %1 AOT ID %2 SQL ID %3', dictionary.name,AOTTableID,dictionary.tabId));
}
}
else
{
skip = true;
sdt = new sysDictTable(dictionary.TabId);
if(sdt && sdt.name() != dictionary.name)
{
error(strfmt('conflict at AOT Table %1ID %2 name SQL Name %3 ID %4', sdt.name(),dictionary.tabId,dictionary.name,dictionary.tabId));
}
else
{
error(strfmt('Table does not exist in AOT %1', dictionary.name));
}
}
}
else
{
if (skip) continue;
if( dictionary.fieldId >= 61440)
{
treeNode = TreeNode::findNode('\\data dictionary\\tables\\' + tableName);
switch (dictionary.fieldId)
{
case 61440:
propertyName = "ModifiedDate";
break;
case 61441:
propertyName = "ModifiedTime";
break;
case 61442:
propertyName = "ModifiedBy";
break;
case 61443:
propertyName = "ModifiedTransactionId";
break;
case 61444:
propertyName = "CreatedDate";
break;
case 61445:
propertyName = "CreatedTime";
break;
case 61446:
propertyName = "CreatedBy";
break;
case 61447:
propertyName = "CreatedTransactionId";
break;
case 61448:
propertyName = "SaveDatePerCompany";
break;
case 61450:
propertyName = "OCC";
break;
default:
error(strfmt('UnknownField Table %1 Field %2',tableName, dictionary.name));
break;
}
if(findProperty(treeNode.AOTgetProperties(), propertyName) == 'No')
{
error(strfmt('conflict Table %1 Propery %2 is turned of',tableName, propertyName));
}
}
else
{
treeNode = TreeNode::findNode('\\data dictionary\\tables\\' + tableName + '\\fields\\'+dictionary.name);
if (treeNode)
{
AOTFieldID = treeNode.applObjectId();
if((AOTTableID != dictionary.tabId) || (AOTFieldID != dictionary.fieldId ))
{
error(strfmt('conflict at Table %1 Field %2 AOT %3, SQL %4',tableName, dictionary.name,AOTFieldID,dictionary.fieldId));
}
else
{
if(dictionary.fieldType == types::String)
{
sdf = new SysDictField(AOTTableID,AOTFieldID);
if(sdf && sdf.stringLen() != dictionary.strSize)
{
error(strfmt('conflict Fieldsize at Table %1 Field %2',tableName, dictionary.name));
}
}
}
}
else
{
error(strfmt('CRITICAT Table %1 Field %2 does not exist in AOT',tableName,dictionary.name));
}
}
}
}
}

Popular posts from this blog

What does this mean: "The form datasource query object does not support changing its AllowCrossCompany property after the form has executed the query."?

I have made a form with datasources vendtable and vendtrans. Inside vendtable_ds.executequery() looks like this: QueryBuildDataSource queryBuildDatasource ,queryBDS_VendTrans_Invoice; ; queryBuildDatasource = this.query().dataSourceTable(tablenum(vendtable)); queryBDS_VendTrans_Invoice = this.query().dataSourceTable(tablenum(vendtrans)); if (curext() == "MASTERCOMP") { this.query().allowCrossCompany(true); } else { this.query().allowCrossCompany(false); } //FilterVendorName = stringedit control on form if (FilterVendorName.text()) { queryBuildDatasource.addRange(fieldNum(VendTable,Name)).value(strfmt("*%1*", FilterVendorName.text())); } else { queryBuildDatasource.clearRange(fieldNum(VendTable,Name)); } //FilterInvoiceNumber = stringedit control on form if (FilterInvoiceNumber.valueStr() == "") { queryBDS_VendTrans_Invoice.enabled(false); } else { queryBDS_VendTrans_Invoice.enabled(true); queryBDS_VendTrans_In...

Credit Note [Dynamics AX] using X++

This post will help to create credit note for a sales order based on the invent lot id. All the invoices raised for a particular sales line – Lot Id will be raised back as a credit note. Information on Credit Note: A credit note or credit memorandum (memo) is a commercial document issued by a seller to a buyer. The seller usually issues a Credit Memo for the same or lower amount than the invoice, and then repays the money to the buyer or sets it off against a balance due from other transactions Below Code will help to create credit note for all the invoices raised against the sales line -lot id. Please note: This code can be customized as per your requirements. This is just a template to help creating credit note using X++ code. Please test the code before use. static void SR_CreateCreditNote_Sales(Args _args) { // Coded by Sreenath Reddy CustInvoiceTrans custInvoiceTrans; Dialog dialog = new Dialog(“Create credit note – for sales.”); DialogField dfInv...