How to identify the user that was used to change an object from AOT in AX2012

1. Get the object name for which we need to track these (user and date&time) information's.
2. Login to SQL Server Management Studio and connect to corresponding AX Model database.
3. Run below give query (replace element name with the object name).
4. From the results, track the user and date&time of modifications.

SELECT ModelElement.ElementType, ModelElement.ElementHandle, ModelElement.Name, ModelElement.Origin,

 ModelElementData.CREATEDDATETIME, ModelElementData.CREATEDBY, ModelElementData.MODIFIEDDATETIME,

 ModelElementData.MODIFIEDBY, parent.ElementType AS [Parent type], Parent.Name AS [Parent name]

 FROM ModelElement

 INNER JOIN ModelElementData

 ON ModelElementData.ElementHandle = ModelElement.ElementHandle

 LEFT OUTER JOIN ModelElementData AS ParentData

 ON ParentData.ElementHandle = ModelElement.ParentHandle

 INNER JOIN ModelElement AS Parent

 ON Parent.ElementHandle = ParentData.ElementHandle

 WHERE --ModelElement.ElementType = 42 -- UtilElementType == Tables

  ModelElement.Name LIKE '%EcoResProductCategory%' --Replace object name

 AND ModelElementData.LayerId > 5 -- Customizations only. Values are in Layer table

Passing multiple parameter to store proc

strFmt('exec CNLAS_InventValueReportItemnew \'%1\',\'%2\',\'%3\',\'%4\'',curUserId(), curext(),ItemId,datestring) ;

Advance Structure in AX 2012

In table DimensionAttributeValueGroupCombination , Ordinal field value 1,2,3,4 is version of advance account structure and account structure. First time it will be 1 and  then it will be 2,3....


Select MA.MAINACCOUNTID,DAVGC.ORDINAL,
DALV.DIMENSIONATTRIBUTEVALUEGROUP ,DALV.DISPLAYVALUE, DALV.ORDINAL,
ROW_NUMBER()
Over (Partition
By
DALV.DimensionAttributevaluegroup
Order
by
DALV.Ordinal
asc) as
'For Sorting'
from
DimensionAttributeValueCombination
DAVC
inner
join
DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION
DAVGC
on
DAVC.RecID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION
inner
join
DIMENSIONATTRIBUTEVALUEGROUP
DAVG
on
DAVG.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION
inner
join
DIMENSIONATTRIBUTELEVELVALUE
DALV
on
DAVG.RECID = DALV.DIMENSIONATTRIBUTEVALUEGROUP
join MAINACCOUNT MA
on MA.RECID = DAVC.MAINACCOUNT

--where DAVG.DIMENSIONHIERARCHY = 22565421207
where DAVGC.ORDINAL not in (1)
Group
by
DALV.DIMENSIONATTRIBUTEVALUEGROUP, DALV.DISPLAYVALUE, DALV.ORDINAL,MA.MAINACCOUNTID,DAVGC.ORDINAL

SQL script for Duplicate recid issues

USE ERPLITE_SIT
DECLARE @MaxRecID BIGINT
DECLARE @NextVal BIGINT

SELECT @MaxRecID = MAX(RECID)
FROM PURCHTABLE

SELECT @NextVal = NEXTVAL
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = 'PURCHTABLE'
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID

IF (@NextVal > @MaxRecID)
BEGIN
PRINT 'PURCHTABLE did not need to be updated.'
END
ELSE
BEGIN
PRINT 'Updated PURCHTABLE from ' + CONVERT(VARCHAR(MAX), @NextVal) + '' + 'to' + '' + CONVERT(VARCHAR(MAX), @MaxRecID + 1)

UPDATE SYSTEMSEQUENCES
SET NEXTVAL = @MaxRecID + 1
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = 'PURCHTABLE'
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID
END 

Reportitems not showing data with saved as PDF

don't set the group property to hide. set individual text (Field) to hide

Date format SSRS

Microsoft Dynamics AX - [Fix] Improve DocuRef Delete Performance

The problem occurred while deleting a Sales Header. But in reality we have the problem every time we remove DocuRef containing a lot of records.

https://community.dynamics.com/ax/b/dynamicsaxexperience/archive/2016/01/23/slow-docuref-delete-performance

How to get sales/purchase tax/GST/VAT info for report?

For posted Sales Invoice report

1 2	select taxTrans where taxTrans.InventTransId == _custInvoiceTrans.InventTransId;     salesInvoiceTmp.TaxValue = taxTrans.TaxValue;

For posted Free Text Invoice report

1 2 3 4

select taxTrans     where taxTrans.Source == TaxModuleType::FreeTxtInvoice         && taxTrans.SourceTableId == tableNum(CustInvoiceTrans)         && taxTrans.SourceRecId == _custInvoiceTrans.RecId;

Execute SQL in x++

server static void main(Args _args)
{
   Connection      connection;
    Statement       statement;
    str             query;
    Resultset       resultSet;
    Str1260         test = "delay";
    ;

    // create connection object
    connection = new Connection();

    // create statement
    statement = connection.createStatement();

    // Set the SQL statement
    query = "select recid from LANGUAGETXT where LANGUAGETXT.txt = '" + test +"'";

    // assert SQL statement execute permission
    new SqlStatementExecutePermission(query).assert();

    // when the query returns result,
    // loop all results for processing
    //BP Deviation documented
    resultSet = statement.executeQuery(query);

    while(resultSet.next())
    {
        // do something with the result
        info(strFmt("%1",resultSet.getInt64(1)));
    }

    // limit the scope of the assert call
    CodeAccessPermission::revertAssert();
}

Clear Master and release variant which has no transaction and barcode

Clear Release variant first and them master variant.

static void ClearReleaseVarient(Args _args)

{

    EcoResDistinctProductVariant                EcoResDistinctProductVariant, variant;

    EcoResProductVariantDimensionValue          EcoResProductVariantDimensionValue;

    EcoResProductTranslation                    ecoResProductTranslation_Variant;

    EcoResProductVariantColor                   ecoResProductVariantColor;

    EcoResProductVariantStyle                   ecoResProductVariantStyle;

    EcoResProductVariantSize                    ecoResProductVariantSize;

    EcoResProductVariantConfiguration           ecoResProductVariantConfiguration;

   

    InventDimCombination                        InventDimCombination,InventDimCombination1,InventDimCombination2;

    InventItemBarcode                           inventBarcode;

    InventTrans                                 InventTrans;

    InventDimCombination                        InventDimCombinationCheckother;

    DataArea                                    dataArea;

    boolean                                     checkdelete;

   /* EcoResSize                                  size;

    EcoResColor                                 color;

    EcoResStyle                                 style;

    EcoResProductMaster                         productmaster;

    , dimFromCombination;

    EcoResConfiguration                         config;

    InventTable                                 inventTable;

    ,ecoResProducttest;

    container                                   con;

    int                                         conin;*/

    InventDim                                   inventDim,inventDimcheck;

    EcoResProduct                               ecoResProduct;

    FromTime    startTime;

    int seconds = 1,counts;

    CommaIo file;

    container line;

    #define.ExampleFile(FileNameSave)

    FileIoPermission perm;

    CompanyInfo CompanyInfo;

    #File

    str   elapsed;

    ;

    startTime = timeNow();

 while select crossCompany InventDimCombination order by ItemId asc

                            where InventDimCombination.ItemId =="A01112Y07521"

                    notexists join inventBarcode

                           where inventBarcode.RetailVariantId == InventDimCombination.RetailVariantId

                    notexists join InventTrans

                            where InventTrans.ItemId == InventDimCombination.ItemId

                                && InventTrans.inventDimId == InventDimCombination.InventDimId

            {

                counts++;

                select ecoResProduct

                        where ecoResProduct.DisplayProductNumber == InventDimCombination.ItemId

                            && (ecoResProduct.Division == ProductTypeSetup::findByDivision("20").RecId

                            || ecoResProduct.Division == ProductTypeSetup::findByDivision("29").RecId)

                            && EcoResProduct.ProductCreationType == ProductCreationType::GlobalProduct;

                if(ecoResProduct)

                {

                    if(InventDimCombination.validateDelete())

                    {

                         select forUpdate * from InventDimCombination1 where InventDimCombination1.RecId == InventDimCombination.RecId;

                        InventDimCombination1.delete();

                    }

                }

                else

                    {

                        inventDim       = inventDim::find(InventDimCombination.InventDimId);

                        info(strFmt("Item %1, Size %2, Color %3, Config %4, Season %5 has transactions", InventDimCombination.ItemId, inventDim.InventSizeId, inventDim.InventColorId, inventDim.configId, inventDim.InventStyleId));

                    }

    }

        elapsed = timeConsumed(startTime, timeNow());

         info(strFmt("Time elapsed: %1 and number of records deleted %2", elapsed,counts));

}

-------------------------------------------------------------------------------------------------------------------

static void ClearProductMastervarient(Args _args)

{

     EcoResDistinctProductVariant                EcoResDistinctProductVariant, variant;

    EcoResProductVariantDimensionValue          EcoResProductVariantDimensionValue;

    EcoResProductTranslation                    ecoResProductTranslation_Variant;

    EcoResProductVariantColor                   ecoResProductVariantColor;

    EcoResProductVariantStyle                   ecoResProductVariantStyle;

    EcoResProductVariantSize                    ecoResProductVariantSize;

    EcoResProductVariantConfiguration           ecoResProductVariantConfiguration;

     DimensionAttributeValues                 DimensionAttributeValues;

    InventDimCombination                        InventDimCombination,InventDimCombination1,InventDimCombination2;

    InventItemBarcode                           inventBarcode;

    InventTrans                                 InventTrans;

    InventDimCombination                        InventDimCombinationCheckother;

    DataArea                                    dataArea;

    boolean                                     checkdelete;

   /* EcoResSize                                  size;

    EcoResColor                                 color;

    EcoResStyle                                 style;

    EcoResProductMaster                         productmaster;

    , dimFromCombination;

    EcoResConfiguration                         config;

    InventTable                                 inventTable;

    ,ecoResProducttest;

    container                                   con;

    int                                         conin;*/

    InventDim                                   inventDim,inventDimcheck;

    EcoResProduct                               ecoResProduct;

    FromTime    startTime;

    int seconds = 1,counts;

    CommaIo file;

    container line;

    #define.ExampleFile(FileNameSave)

    FileIoPermission perm;

    CompanyInfo CompanyInfo;

    #File

    str   elapsed;

    ;

    startTime = timeNow();

      while   select  ecoResProduct

                        where

                         ecoResProduct.DisplayProductNumber == "A01112Y07521"

                            && (ecoResProduct.Division == ProductTypeSetup::findByDivision("20").RecId

                            || ecoResProduct.Division == ProductTypeSetup::findByDivision("29").RecId)

                            && EcoResProduct.ProductCreationType == ProductCreationType::GlobalProduct

    {

     while select EcoResDistinctProductVariant order by RecId asc

     where EcoResDistinctProductVariant.DisplayProductNumber like ecoResProduct.DisplayProductNumber +"*"

        {

        select crossCompany InventDimCombination

        where InventDimCombination.DistinctProductVariant == EcoResDistinctProductVariant.RecId

        && InventDimCombination.ItemId == ecoResProduct.DisplayProductNumber;

                if(!InventDimCombination)

                {

                    ttsBegin;

                   // select forUpdate * from ecoResProductTranslation_Variant where ecoResProductTranslation_Variant.Product ==ecoResProduct.RecId;

                   // ecoResProductTranslation_Variant.delete();

                        select forUpdate * from EcoResProductVariantColor where EcoResProductVariantColor.DistinctProductVariant == EcoResDistinctProductVariant.RecId;

                    EcoResProductVariantColor.delete();

                        select forUpdate * from EcoResProductVariantStyle where EcoResProductVariantStyle.DistinctProductVariant == EcoResDistinctProductVariant.RecId;

                    EcoResProductVariantStyle.delete();

                        select forUpdate * from EcoResProductVariantSize where EcoResProductVariantSize.DistinctProductVariant == EcoResDistinctProductVariant.RecId;

                    EcoResProductVariantSize.delete();

                        select forUpdate * from EcoResProductVariantConfiguration where EcoResProductVariantConfiguration.DistinctProductVariant == EcoResDistinctProductVariant.RecId;

                    EcoResProductVariantConfiguration.delete();

                        select forUpdate * from DimensionAttributeValues where DimensionAttributeValues.EcoResDistinctProductVariant == EcoResDistinctProductVariant.RecId;

                     DimensionAttributeValues.delete();

                    InventDimCombination1=null;

                    select forUpdate * from variant where variant.RecId == EcoResDistinctProductVariant.RecId;

                    variant.delete();

                    ttsCommit;

                }

        }

    }

     elapsed = timeConsumed(startTime, timeNow());

         info(strFmt("Time elapsed: %1 and number of records deleted %2", elapsed,counts));

}

Update All table id and Field Id on SQL dict based on AOT property

Dictionary      dictionary = new Dictionary();
    DictTable    dictTable;
    DictField       dictField;
    TableId         tableId,tableIdbuffer,NewtableId;
    FieldId         fieldId,fieldIdbuffer,newfieldId,fieldIdmin,fieldIdmax;
    SqlDictionary   sqlDictionaryTable;
    SqlDictionary   sqlDictionaryField;
    SqlDictionary   SqlDictionaryupdate,SqlDictionaryupdaterecordset;
    PS_Table        PS_Table;
    int             i;

    setPrefix("Update of data dictionary IDs");
    tableId = dictionary.tableNext(0);
    ttsbegin;
    select maxof(tabid) from SqlDictionaryupdate;
    tableIdbuffer = SqlDictionaryupdate.tabId + 1000;

    ttsBegin;
    delete_from PS_Table;
    ttsCommit;

    while (tableId > 0)
    {

        dictTable = new SysDictTable(tableId);
        setPrefix(dictTable.name());

        if (!dictTable.isSystemTable())
        {
            //Finds table in SqlDictionary by name in AOT, if ID was changed.
            //Empty field ID represents a table.
            select sqlDictionaryTable
                where sqlDictionaryTable.name == dictTable.name()
                && sqlDictionaryTable.fieldId == 0
                && sqlDictionaryTable.tabId != dictTable.id();

            if (sqlDictionaryTable)
            {
                //Updates table ID in SqlDictionary
                NewtableId = dictTable.id();
                select * from SqlDictionaryupdate
                where SqlDictionaryupdate.tabId == NewtableId;
                if(SqlDictionaryupdate)
                {

                    update_recordset SqlDictionaryupdaterecordset
                    setting TabId = tableIdbuffer
                    where SqlDictionaryupdaterecordset.TabId == SqlDictionaryupdate.tabId;
                    tableIdbuffer--;

                }

                if (ReleaseUpdateDB::changeTableId(
                    sqlDictionaryTable.tabId,
                    dictTable.id(),
                    dictTable.name()))
                {
                    info(strFmt("Table ID changed (%1 -> %2)", sqlDictionaryTable.tabId, dictTable.id()));
                }
            }

            fieldId = dictTable.fieldNext(0);
            select maxof(fieldId) from SqlDictionaryupdate;
            fieldIdbuffer = SqlDictionaryupdate.fieldId + 1000;
            select minOf(fieldId) from SqlDictionaryupdate;
            fieldIdmin = SqlDictionaryupdate.fieldId;
            //For all fields in table
            while (fieldId)
            {

                info(strFmt("%1",fieldId));
                dictField = dictTable.fieldObject(fieldId);
                //    info(dictField.name());
                if (dictField.isSql() && !dictField.isSystem())
                {
                    //Finds fields in SqlDictionary by name and compares IDs



                    select sqlDictionaryField
                        where sqlDictionaryField.tabId == dictTable.id()
                        && sqlDictionaryField.name == dictField.name()
                        && sqlDictionaryField.fieldId != 0
                        && sqlDictionaryField.fieldId != dictField.id();

                    if (sqlDictionaryField)
                    {
                        Newfieldid = dictField.id();
                        NewtableId = dictTable.id();

                        ttsBegin;
                        PS_Table.TabId = NewtableId;
                        PS_Table.Tablename = dictTable.name();
                        PS_Table.FieldName = dictField.name();
                        PS_Table.FieldId = Newfieldid;
                        PS_Table.insert();
                        ttsCommit;

                        select * from SqlDictionaryupdate
                        where SqlDictionaryupdate.tabId == NewtableId
                        && SqlDictionaryupdate.fieldId == Newfieldid;
                        if(SqlDictionaryupdate)
                        {

                             update_recordset SqlDictionaryupdaterecordset
                                setting FieldId = fieldIdbuffer
                                where SqlDictionaryupdaterecordset.TabId    == NewtableId
                                   && SqlDictionaryupdaterecordset.FieldId  == Newfieldid;
                            fieldIdbuffer--;

                        }

                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                            dictTable.id(),
                            sqlDictionaryField.fieldId,
                            dictField.id(),
                            dictTable.name(),
                            dictField.name()))
                        {
                            info(strFmt("Field %1 - ID changed (%2 -> %3)",
                            dictField.name(),
                            sqlDictionaryField.fieldId,
                            dictField.id()));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
        }
        tableId = dictionary.tableNext(tableId);
    }
    ttscommit;

Stock on hand from SQL as of date

select INVENTSUM.itemid, sum(postedqty) as Postedqty,
sum(Received) as Received,
sum(Deducted) as Deducted,
sum(Registered) as Registered,
sum(PICKED) as PICKED,
sum(RESERVPHYSICAL) as RESERVPHYSICAL,
sum(ORDERED) as ORDERED,
sum(ARRIVED) as ARRIVED,
sum(RESERVORDERED) as RESERVORDERED,
sum(ONORDER) as ONORDER,
INVENTDIM.INVENTLOCATIONID,
INVENTDIM.INVENTSITEID,
INVENTDIM.CONFIGID
from INVENTSUM
join INVENTDIM
on INVENTDIM.INVENTDIMID = INVENTSUM.INVENTDIMID
where LASTUPDDATEPHYSICAL <= '2016-04-01'
--and itemid = '200935'
group by INVENTSUM.itemid, INVENTDIM.INVENTLOCATIONID,INVENTDIM.INVENTSITEID,INVENTDIM.CONFIGID

--------------------------------------------------------------------------------------------------------
select * from (select INVENTSUM.itemid, sum(AvailPhysical) as AvailPhysical,
INVENTDIM.INVENTLOCATIONID,
INVENTDIM.INVENTSITEID,
INVENTDIM.CONFIGID,
INVENTDIM.INVENTSTYLEID,INVENTDIM.INVENTCOLORID
,INVENTDIM.INVENTBATCHID,INVENTDIM.INVENTSIZEID
from INVENTSUM as INVENTSUM
join INVENTDIM
on INVENTDIM.INVENTDIMID = INVENTSUM.INVENTDIMID
where /*LASTUPDDATEPHYSICAL <= '2016-04-01'
and */ INVENTSUM.itemid = 'A92991Y61553' and  INVENTSUM.DATAAREAID = 'USMF'
and INVENTDIM.INVENTSTYLEID != ''
group by INVENTSUM.itemid, INVENTDIM.INVENTLOCATIONID,INVENTDIM.INVENTSITEID,INVENTDIM.CONFIGID,INVENTDIM.INVENTSTYLEID,INVENTDIM.INVENTCOLORID,INVENTDIM.INVENTBATCHID,INVENTDIM.INVENTSIZEID)
 Stock
where stock.AvailPhysical >0 

Main Account Dimension update

static void MainAccountDimension(Args _args)
{
LSVVendInvStageTmp LSVVendInvStageTmp;
    MainAccountLegalEntity  MainAccountLegalEntity;
    MainAccount             mainaccount;
    CompanyInfo                         companyInfoList;
    Ledger                              ledgerList;
    MainAccountLegalEntity              mainAccountLegalEntityList;
    ;
    while select *from mainaccount
    {
   
         while select DataArea from companyInfoList
            join RecId from ledgerList
                where ledgerList.PrimaryForLegalEntity == companyInfoList.RecId &&
                    ledgerList.ChartOfAccounts == mainaccount.LedgerChartOfAccounts
            notexists join RecId from mainAccountLegalEntityList
                where mainAccountLegalEntityList.LegalEntity == companyInfoList.RecId &&
                    mainAccountLegalEntityList.MainAccount == mainaccount.RecId
        {
            MainAccountLegalEntity.clear();
            MainAccountLegalEntity.MainAccount          = mainaccount.RecId;
            MainAccountLegalEntity.LegalEntity          = companyInfoList.RecId;
            MainAccountLegalEntity.DefaultDimension     = 5637144576;
            MainAccountLegalEntity.FixedDimensions      = 0;

            MainAccountLegalEntity.insert();
        }
    }
}

Connect two DB

select sl4.salesid,sl4.itemid,sl4.LINENUM,sl4.CREATEDDATE from I5Production.dbo.SALESLINE as SL4
where NOT EXISTS

( select SL12.salesid,SL12.ITEMID,SL12.LINENUM From AxDev.dbo.SALESLINE SL12
where  sl4.salesid = SL12.salesid
and sl4.itemid = SL12.ITEMID
and sl4.LINENUM = SL12.LINENUM
and sl4.DATAAREAID = SL12.DATAAREAID
and  sl4.DATAAREAID = 'i5d'
--and  sl4.CREATEDDATE > '2015-12-31'
--and  sl4.SALESID ='S10099614'
);

Get SID

C:\Windows\system32>wmic useraccount get name,sid

Upgrade windows os from standard edition to server data center

DISM /online /Set-Edition:ServerDatacenter /AcceptEula /productkey:HXH7V

get default dimension with Attribute name and Value

 container conAttr = ["ICO"];
    DimensionAttribute                  dimensionAttribute;
    DimensionAttributeValue             dimensionAttributeValue;
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                    result;
    str dimValue = "APOL";
   
    dimensionAttribute = dimensionAttribute::findByName("ICO");
    dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,true);
    valueSetStorage.addItem(dimensionAttributeValue);
    result = valueSetStorage.save();
    print result;
    pause;

Get Voucher balance in AX 2012

ledgerJournalTrans ledgerJournalTrans;
    ledgerJournalEngine ledgerJournalEngine = new ledgerJournalEngine();
    ledgerJournalTable ledgerJournalTable;
    tmpVoucherSum       tmpVoucherSum;
    ;

    ledgerJournalTrans = ledgerJournalTrans::findRecId(5638439934,false);
    ledgerJournalTable = ledgerJournalTable::find(ledgerJournalTrans.JournalNum);
    ledgerJournalEngine.newJournalActive(ledgerJournalTable,true);
    print ledgerJournalEngine.voucherDebit(ledgerJournalTrans);




    tmpVoucherSum   = LedgerJournalEngine_Server::createVoucherBalance(ledgerJournalTrans.Voucher,
                                                                        ledgerJournalTable.JournalNum);
    print   tmpVoucherSum.debit;
    print   tmpVoucherSum.Credit;
    pause;

Hot fix installation error

An error occurred in writing to the event log on machine DBSERVER: The source was not found, but some or all event logs could not be searched.  Inaccessible logs: Security.
Error updating model database: Microsoft.Dynamics.Setup.AxSetupException: AxUtil call returned errors:The model contains a customization of a configuration key. The model cannot be imported because you can overlayer a configuration key from the patching layer only.

Solution:

Uninstall DMF for CU6 from Control panel / axutil

axutil delete /model:"Data Import Export Framework" from CMD .

Reinstall CU8

If you Need a Filter In a Form

//Add this to Class Declaration of a Form
public class FormRun extends ObjectRun
{
       QueryBuildRange rangeTenant;
       QueryBuildRange rangeAgreementId;
       QueryBuildRange rangeAgreementJournalId;
       QueryBuildRange rangeBuildingId;
       QueryBuildRange rangeRentTransId;
       QueryBuildRange rangePaymentDate;
       QueryBuildRange qbr,qbr1,qbr2,qbr3;

}
//Add this to Datasource Execute Query(Eg :- NetPaymentHistory_ds)
public void executeQuery()
{

      rangeTenant.value(TenantId.text() ? queryValue(TenantId.text()) 
              :SysQuery::valueUnlimited());
      rangeAgreementId.value(AgreementId.text() ? queryValue(AgreementId.text())
              : SysQuery::valueUnlimited());
      rangeAgreementJournalId.value(AgreementJournalId.text() ? queryValue(AgreementJournalId.text()) 
             : SysQuery::valueUnlimited());
      rangeBuildingId.value(BuildingId.text() ? queryValue(BuildingId.text())
            : SysQuery::valueUnlimited());
      rangeRentTransId.value(RentTransId.text() ? queryValue(RentTransId.text()) 
            : SysQuery::valueUnlimited());
      rangePaymentDate.value(PaymentDate.valueStr() ? queryValue(PaymentDate.valueStr())
            : SysQuery::valueUnlimited());

       super();
}
//Add this to Datasource init method (Eg :- NetPaymentHistory_ds)

public void init()
{

       super();

      this.query().dataSourceTable(tablenum(NetPaymentHistory)).clearDynalinks();

      rangeTenant = this.query().dataSourceTable(tablenum(NetPaymentHistory))
               .addRange(fieldnum(NetPaymentHistory, TenantId));
      rangeAgreementId = this.query().dataSourceTable(tablenum(NetPaymentHistory))
               .addRange(fieldnum(NetPaymentHistory, AgreementId));
      rangeAgreementJournalId = this.query().dataSourceTable(tablenum(NetPaymentHistory))
              .addRange(fieldnum(NetPaymentHistory, AgreementJournalID));
      rangeBuildingId = this.query().dataSourceTable(tablenum(NetPaymentHistory))
              .addRange(fieldnum(NetPaymentHistory, BuildingId));
      rangeRentTransId = this.query().dataSourceTable(tablenum(NetPaymentHistory))
             .addRange(fieldnum(NetPaymentHistory, RentTransId));
      rangePaymentDate = this.query().dataSourceTable(tablenum(NetPaymentHistory))
            .addRange(fieldnum(NetPaymentHistory, PaymentDate));
     // Enable caching of the document handling and note it display fields
    //purchRFQTable_ds.cacheAddMethod(tablemethodstr(PurchRFQTable, showDocHandIcon));

}
// Add this to Each of the String Control Field
public boolean modified()
{
     boolean ret;

    ret = super();

    NetPaymentHistory_ds.executeQuery();

    return ret;
}

Parent Form Refresh using x++

//Add this Method in Parent Form

void Form_doRefresh()
{
;

NetBuildings_ds.reread(); // Parent Form and Child Form Datasource Name
NetBuildings_ds.executeQuery();
NetBuildings_ds.refresh();

NetUnitDetails_ds.reread();
NetUnitDetails_ds.executeQuery();
NetUnitDetails_ds.refresh();
}

//Add this Method in Child Form where you need to Refresh the Parent Form

public void close()

{

    if(formHasMethod(element.args().caller(), identifierStr(Form_doRefresh)))//Form_doRefresh is Parent Method                                                                                                          Name
   {
        element.args().caller().Form_doRefresh();
   }

   super();
}
.................................................................................................................................................................

void bookrefresh()
{
   

.reread();

   

.refresh();

}

In the child form, you can do this –

void bookRefresh()
{
   FormRun callerForm = element.args().caller();

   if (callerForm)
   {
      if (callerForm.name() == formstr(

))

     {
         callerForm.bookRefresh();
     }
   }
}

Backup SQL DB using code

DECLARE @name VARCHAR(100) — database name 
DECLARE @path VARCHAR(256) — path for backup files 
DECLARE @fileName VARCHAR(256) — filename for backup 
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘G:\BACKUP\11-09-2012_2300Hrs\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’ 
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name 
END

CLOSE db_cursor
DEALLOCATE db_cursor

MANAGEMENT REPORTER ERROR

ERROR – UNABLE TO CONTACT THE SERVER, DO YOU WANT TO CHANGE THE SERVER SETTINGS (TEST CONNECTION)

SOLUTION –

Go to SQL SERVER MANAGEMENT STUDIO> SECURITY > LOGINS> NT AUTHORITY(CHECK WHICH LOGIN YOU HAVE GIVEN WHILE INSTALLING MR) > PROPERTIES>SERVER ROLES> CHECK BOX SHOULD BE ENABLED FOR Management reporter for that particular login – ASSIGN PUBLIC + SERVER ADMIN + SYSTEM ADMIN.

Convert WorkerId to UserId

userId = DirPersonUser::worker2UserId(MyHCMWorkerRecId);

How to Publish AX Reports for a specific AOS Instance

If you are running multiple instances of AX on a single environment, you have more than likely gone through the instructions as per this URL http://technet.microsoft.com/en-us/library/hh389760.aspx.
One of the steps identified in this URL talks about publishing reports using the following AX Powershell command: publish-axreports.
What isn’t perhaps obvious from the examples shown on the Deploy reports to the new Reporting Services Instance [AX 2012] page is – How do I publish reports for a specific AOS instance? (The examples shown is for the default instance)

When you look at the full syntax of the Publish-AxReports powershell command:


You will notice two optional parameters that are of interest, namely:

• ServicesAOSName
• ServicesAOSWSDLPort

These two parameters are used to “tell” the Publish-AXReports powershell command, which AOS to connect to when publishing reports.
The syntax used for these two parameters are as follow:

• ServicesAOSName

This parameter contains the name of the server that runs the AOS instance that you want to connect to e.g. AXServer

• ServicesAOSWSDLPort

This parameter contains the WSDL Port number that has been setup for your AOS instance e.g. 8102

Now that we understand the syntax, the Publish-AXReports command would look like this:

Publish-AXReport –ReportName * -id AXSSRS –servicesAOSname AXServer –servicesAOSWSDLPort 8102
Note – If you are running the Publish-AxReport command using the –servicesAOSName parameter you’ll also need to specify the –servicesAOSWSDLPort. They are both required to ensure that command executes correctly.