Create APP Developer Site

Get-SPServiceInstance | where{$_.GetType().Name -eq "AppManagementServiceInstance" -or $_.GetType().Name -eq "SPSubscriptionSettingsServiceInstance"}


  $account = Get-SPManagedAccount "Eli\spsvc"


  $appPoolSubSvc = New-SPServiceApplicationPool -Name SettingsServiceAppPool -Account $account
  $appPoolAppSvc = New-SPServiceApplicationPool -Name AppServiceAppPool -Account $account


  $appSubSvc = New-SPSubscriptionSettingsServiceApplication –ApplicationPool $appPoolSubSvc –Name SettingsServiceApp –DatabaseName SettingsServiceDB
  $proxySubSvc = New-SPSubscriptionSettingsServiceApplicationProxy –ServiceApplication $appSubSvc

  $appAppSvc = New-SPAppManagementServiceApplication –ApplicationPool $appPoolAppSvc –Name AppServiceApp –DatabaseName AppServiceDB
  $proxyAppSvc = New-SPAppManagementServiceApplicationProxy –ServiceApplication $appAppSvc



Set-SPAppSiteSubscriptionName -Name "app" -Confirm:$false

Add New User

Find SID from regedit, under HKEY_USERS. Copy the SID as your own machine's SID.

Update userinfo table as the following:
update userinfo set SID='', Networkdomain = '<Network_domain_name>', networkalias = '<Network_alias>' where id = 'admin'


Excerpt from other blog:
Prepare the Database-Create a new database in SQL and restore the backup from the customer into it.
-In SQL in the newly restored database go to Security > Users.  Right-click on Users and choose new user.  Add the user account that runs the AOS service.  For me, this is my domain account. 
-In the middle of this user form, under Owned Schemas mark db_owner.
-Still in the same form where you added the user, under Database role membership mark db_datareader, db_datawriter, and db_owner.
-In SQL in the newly restored database go to Programmability > Stored Procedures and right-click on CreateServerSessions and choose Properties.
-Click Permissions and click the Search button.  Find the account you just added to the database as a user, for me this is my domain account.
-In the bottom of this form under the Grant column mark all of the choices.
-In SQL in the newly restored database go to Programmability > Stored Procedures and right-click on CreateUserSessions and choose Properties.
-Click Permissions and click the Search button.  Find the account you just added to the database as a user, for me this is my domain account.
-In the bottom of this form under the Grant column mark all of the choices.
-In SQL click the New Query button.
-Choose your EXISTING AX database - so not the customer's database you just restored but the AX database you have with demo data in it, run the following query:
 select SID, NETWORKDOMAIN, NETWORKALIAS, * from UserInfo where Id = 'Admin'
-Click the New Query button and choose the CUSTOMER's database.  Leave your existing query against your own database open.  We are going to use the results in our next query.
-In the New Query against the customer's database run the following:
update USERINFO set SID = 'SID field from previous query', NETWORKDOMAIN = 'NETWORKDOMAIN field from previous query', NETWORKALIAS = 'NETWORKALIAS field from previous query'
 where ID = 'Admin' --The idea here is you want to grab the SID, NETWORKDOMAIN and NETWORKALIAS values from your own UserInfo table and update the customer's UserInfo table with your user information so you can log into their database.

Restore DB

1.Shutdown the AOS in services
2.Open SQL Management Studio
3.Connect to the SQL Database
4.Right Click MicrosoftDynamicsAx => Tasks => Restore => Database
5.Select From Device and then find the downloaded .bak File.
6.On the top left click on "Options" and check the first box: Overwrite the existing database (With REPLACE)
7.Click OK and repeat for the other DB.
Repeat same for the Model database.

UserSessionService

Steps to remove fix it
1. Go to directory: C:\Program Files\Microsoft Dynamics AX\60\Server\OE_US_AX_TST\bin
2. Stop AX Service
3. Make a copy of XPPIL folder
4. Delete XPPIL folder
5. Start AX Service
6. Generate Full CIL

Mailer Program

    SysMailer                           mailer;  
    SysEmailParameters                  params;
   
    ;
   
    // Set up the email header
    mailer = new SysMailer();
    mailer.subject("TEST EQ");
    mailer.fromAddress("abc@abc.com");
   
    params = SysEmailParameters::find();
    mailer.SMTPRelayServer(params.SMTPRelayServerName,
                           params.SMTPPortNumber,
                           params.SMTPUserName,
                           sysEmailParameters::password());

    mailer.tos().appendAddress("abc@abc.com");
    mailer.body("Some body message");
   
    // Send the email
    startlengthyoperation();

    mailer.sendMail();

    endlengthyoperation();

Compare Datetime

    mbsWMSPODDeliveryTable mbsWMSPODDeliveryTable;
    utcdatetime xppDttm;
    System.DateTime    nppDttm;
    TransDate   d;
    ;
    xppDttm = 2013-10-05T23:22:21; // ISO standard format.
    nppDttm =  Global::utcDateTime2SystemDateTime( xppDttm);
    while select crosscompany SignatureName
        from mbsWMSPODDeliveryTable
            where mbsWMSPODDeliveryTable.DeliveryDateTime > xppDttm
               
    {
    if(strScan(mbsWMSPODDeliveryTable.SignatureName, "Laing", 1, strLen(mbsWMSPODDeliveryTable.SignatureName)))
        info(mbsWMSPODDeliveryTable.SignatureName);
    }

CommaIo object not initialized

"CommaIo object not initialized" was indeed file-related but in a more broad
sense.

The code was correct. The file path and name were correct. Backslashes were
escaped. The calls, the setup, everything was correct, except...

When a new Class is created the RunOn property is set to "Called from" by
default. Changing that to "Client" fixes the problem.

Check Orphan SalesLine

static void checkSalesLineSalesTable(Args _args)
{
    #AviFiles
    SysOperationProgress    p;
    int64                   numRecs;
    SalesLine               salesLine;
    Counter                 totalLines;
    Counter                 totalGood;
    Counter                 totalBad;
    boolean                 goodFound;
    ;
    select count(RecId) from salesLine
         group by SalesId;
    numRecs = salesLine.RecId;
    if (numRecs)
    {
        p = SysOperationProgress::NewGeneral(#AviUpdate, "Check sales line sales qty", numRecs);
        while select * from salesLine
            group by SalesId
        {
            totalLines++;
            p.setText(strFmt('Record %1 : %2; Bad: %3', totalLines, numRecs, totalBad));
            p.incCount();
            if (!SalesTable::exist(salesLine.SalesId))
            {
                totalBad++;
                info(strfmt('Order %1 Type: %2 Status: %3 Item: %4 Qty: %5 Created by: %6, Created on: %7',
                            salesLine.SalesId,
                            salesLine.SalesType,
                            salesLine.SalesStatus,
                            salesLine.ItemId,
                            salesLine.SalesQty,
                            salesLine.createdBy,
                            salesLine.createdDateTime));
            }
            else
            {
                totalGood++;
            }
        }
    }
    info('Total lines: '+int2str(totalLines));
    info('Total good: '+int2str(totalGood));
    info('Total bad: '+int2str(totalBad));
}

Menu to Call Report

               salesInvoiceMenu = new MenuFunction(menuItemName, MenuItemType::Output);   
                if (mbsReprint && mbsConfigSetting::string("BD-191"))
                {
                    salesInvoiceMenu.objectType(UtilElementType::Report);
                    salesInvoiceMenu.object(mbsConfigSetting::string("BD-191"));
                }
                else
                {
                    if (reportContainer)
                    {
                        reportName             = conpeek(reportContainer,1);
                        reportCopies           = conpeek(reportContainer,2);

                        if (webInvoice)
                        {
                            reportName = strFmt("%1_web", reportName);
                        }

                        salesInvoiceMenu.enumTypeParameter(PrintCopyOriginal);
                        salesInvoiceMenu.enumParameter(args.parmEnum());
                        salesInvoiceMenu.objectType(UtilElementType::Report);
                        salesInvoiceMenu.object(reportName);
                    }
                    else
                    {
                        salesInvoiceMenu.objectType(UtilElementType::Report);
                        salesInvoiceMenu.object(menuItemOutputStr(SalesInvoice));
                    }
                }

Backup and Restore AX 2009

Pre-requisites
In my existing environment I have a running instance of AX 2009 that is on sp1.  If the customer is on AX 2009 I assume the customer is on sp1 since most are.  The AOS, SQL server and AX client are all on the same machine.  The AOS service under Start > Control Panel > Administrative Tools > Services is started and runs under my domain account.  In the AX application my domain account is the Admin account under Administration > Users.
Prepare the Database
-Create a new database in SQL and restore the backup from the customer into it.
-In SQL in the newly restored database go to Security > Users.  Right-click on Users and choose new user.  Add the user account that runs the AOS service.  For me, this is my domain account. 
-In the middle of this user form, under Owned Schemas mark db_owner.
-Still in the same form where you added the user, under Database role membership mark db_datareader, db_datawriter, and db_owner.
-In SQL in the newly restored database go to Programmability > Stored Procedures and right-click on CreateServerSessions and choose Properties.
-Click Permissions and click the Search button.  Find the account you just added to the database as a user, for me this is my domain account.
-In the bottom of this form under the Grant column mark all of the choices.
-In SQL in the newly restored database go to Programmability > Stored Procedures and right-click on CreateUserSessions and choose Properties.
-Click Permissions and click the Search button.  Find the account you just added to the database as a user, for me this is my domain account.
-In the bottom of this form under the Grant column mark all of the choices.
-In SQL click the New Query button.
-Choose your EXISTING AX database - so not the customer's database you just restored but the AX database you have with demo data in it, run the following query:
 select SID, NETWORKDOMAIN, NETWORKALIAS, * from UserInfo where Id = 'Admin'
-Click the New Query button and choose the CUSTOMER's database.  Leave your existing query against your own database open.  We are going to use the results in our next query.
-In the New Query against the customer's database run the following:
update USERINFO set SID = 'SID field from previous query', NETWORKDOMAIN = 'NETWORKDOMAIN field from previous query', NETWORKALIAS = 'NETWORKALIAS field from previous query'
 where ID = 'Admin' --The idea here is you want to grab the SID, NETWORKDOMAIN and NETWORKALIAS values from your own UserInfo table and update the customer's UserInfo table with your user information so you can log into their database.
Prepare the Application Files
-When you install if you choose the default paths the Application files are stored at this location "C:\Program Files\Microsoft Dynamics AX\50\Application\Appl".  Take the copy of the customer's application folder and copy it to this location.   When it finishes copying you should have your existing application folder and the customer's application folder sitting side-by-side inside the Appl directory.
-Go into the copied application folder and find the file named axapd.aoi and rename it.
Create a New Server Configuration
-Go to Start > Control Panel > Administrative Tools > Microsoft Dynamics AX 2009 Server Configuration and open the server configuration utility.
-In this utility click Manage > Create configuration and give the new configuration a name.
-In the new configuration under the Application Object Server tab find the Application instance dropdown and click it.  If the customer's application files are restored correctly you should see the customer's folder in this list, pick it.
-Under the Database Connection tab click the Database name dropdown.  If the customer's database has been restored you should see the name of that database in the list, pick it.
-Click OK and it should prompt you to restart the AOS, choose Yes.  This will restart the AOS with your AX install pointing to the customer's database and application files.  Since we renamed the axapd.aoi file the AOS service will take a while to start.  You can go into Control Panel > Administrative Tools > Services and find the Dynamics AX AOS service.  It will likely be in a Starting state as it rebuilds the axapd.aoi file.  Wait for it to change to a Started state.
Test the Environment
-Launch AX to verify you can get in.  Be aware of the company you are logged into when trying to recreate an issue as customer's tend to have a lot more companies than the demo data.

Runtime FormLookup

Query query = new Query();
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildRange queryBuildRange;
    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(TableTaskCreationRules), this);
    TableId tableId;
    FieldId fieldId;
    ;
    tableId = tablename2id("TableTaskCreationRules");
    fieldId = fieldname2id(tableId, "CreationRule");
    sysTableLookup.addLookupfield(fieldId, true);
    fieldId = fieldname2id(tableId, "CreationDesc");
    sysTableLookup.addLookupfield(fieldId);
    fieldId = fieldname2id(tableId, "CreationMode");
    sysTableLookup.addLookupfield(fieldId);
    queryBuildDataSource = query.addDataSource(tableId);
    queryBuildRange      = queryBuildDataSource.addRange(fieldNum(TableTaskCreationRules, TaskType));
    queryBuildRange.value(enum2str(TaskType::Picking));
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

Create Run-Time Lookup Forms

public void lookup()
{
   
    Query                   query          = new Query();
    QueryBuildDataSource    queryBuildDataSource;
    QueryBuildRange         queryBuildRange;

    // Create an instance of SysTableLookup where 'this'
    // is the current form control.
    SysTableLookup sysTableLookup =
        SysTableLookup::newParameters(tableNum(custTable), this);
    ;

    // Add fields to be shown in the lookup form.
    sysTableLookup.addLookupField(fieldNum(CustTable, AccountNum));
    sysTableLookup.addLookupField(fieldNum(CustTable, Name));

    // Limit and arrange the data selection.
    queryBuildDataSource = query.addDataSource(tableNum(CustTable));
    queryBuildRange = queryBuildDataSource.addRange(
        fieldNum(CustTable, AccountNum));
    queryBuildRange.value('100..2000');
    sysTableLookup.parmQuery(query);

    // Perform the lookup.
    sysTableLookup.performFormLookup();

    //Don't call super() (commented out in the code).
    //   super()
}

ReadXML

static void RealXML(Args _args)
{
    XMLDocument     retXmlDoc;
    XMLElement      xmlOutput;
    XMLElement      creditCardNode, invoiceSumNode, invoiceDtlNode;
    boolean         requestPayment = true;
    str             requestContactId, requestCCSubId, requestRecId, requestPayMode, requestLast4;
    str             requestCCnum, requestNameOnCard, requestCCExpMonth, requestCCExpYear, requestCCType;
    str             requestNumInv, requestTtlAmt, retMessageCode, retMessageText;
    str             invoiceId, xRefRecId, openAmount;
    ;
    retXmlDoc = new XMLDocument();
    xmlOutput = retXmlDoc.createElement("ProcessInvoiceCCPayment");
    xmlOutput.setAttribute("processingFullPayment", strfmt("%1", requestPayment));
    xmlOutput.setAttribute("contactId", strfmt("%1", requestContactId));
    creditCardNode = retXmlDoc.createElement("CreditCard");
    xmlOutput.appendChild(CreditCardNode);
    creditCardNode.setAttribute("CCSubscriptionId", strfmt("%1", requestCCSubId));
    creditCardNode.setAttribute("CCRecID", strfmt("%1", requestRecId));
    creditCardNode.setAttribute("payMode", strfmt("%1", requestPayMode));
    creditCardNode.setAttribute("CCLast4Digits", strfmt("%1", requestLast4));
    creditCardNode.setAttribute("mbsCCNumber", strfmt("%1", requestCCnum));
    creditCardNode.setAttribute("nameOnCard", strfmt("%1", requestNameOnCard));
    creditCardNode.setAttribute("CCExpMonth", strfmt("%1", requestCCExpMonth));
    creditCardNode.setAttribute("CCExpYear", strfmt("%1", requestCCExpYear));
    creditCardNode.setAttribute("CCType", strfmt("%1", requestCCType));
    invoiceSumNode = retXmlDoc.createElement("InvoiceSummary");
    xmlOutput.appendChild(invoiceSumNode);
    invoiceSumNode.setAttribute("totalNumberOfInvoices", strfmt("%1", requestNumInv));
    invoiceSumNode.setAttribute("totalOpenAmount", strfmt("%1", requestTtlAmt));
    invoiceSumNode.setAttribute("messageCode", strfmt("%1", retMessageCode));
    invoiceSumNode.setAttribute("messageText", strfmt("%1", retMessageText));
    invoiceDtlNode = retXmlDoc.createElement("InvoiceDetail");
    invoiceSumNode.appendChild(invoiceDtlNode);
    invoiceDtlNode.setAttribute("InvoiceId", strfmt("%1", invoiceId));
    invoiceDtlNode.setAttribute("RefRecId", strfmt("%1", xRefRecId));
    invoiceDtlNode.setAttribute("OpenAmount", strfmt("%1", openAmount));
    info(xmlOutput.xml());

    /*
    <ProcessInvoiceCCPayment processingFullPayment="true" contactId="">
        <CreditCard CCSubscriptionId="" CCRecID="" payMode="" CCLast4Digits="" mbsCCNumber="" nameOnCard="" CCExpMonth="" CCExpYear="" CCType="" />
        <InvoiceSummary totalNumberOfInvoices="" totalOpenAmount="" messageCode="" messageText="">
            <InvoiceDetail InvoiceId="" RefRecId="" OpenAmount="" />
        </InvoiceSummary>
    </ProcessInvoiceCCPayment>
    */
}

FormRun 3

    salesTable              salesTable = SalesTable::find("S104872");
    FormRun                 formRun;
    Args                    args;
    str                     parmGuid;
    ;
    args = new Args(MenuItemDisplayStr(BDCCOneTime));
    args.record(salesTable);
    formRun = New Menufunction(MenuItemDisplayStr(BDCCOneTime), MenuItemType::Display).create(args);
    if (formRun)
    {
        formRun.run();
    }

FormRun 2

    salesTable              salesTable = SalesTable::find("S104872");
    FormRun                 fr;
    Args                    args;
    str                     parmGuid;
    ;
    args = new Args("BDCCOneTime");
    args.record(salesTable);
    fr = new FormRun(args);
    if (fr)
    {
        fr.init();
        fr.run();
        fr.wait();
    }

Where BDCCOneTime is a form. The short program passes a record into the Form.

FormRun

Form                    form;
    FormRun                 formRun;
    Args                    args;
    FormBuildDesign         formBuildDesign;
    FormBuildControl        formBuildControl;
    FormBuildTabControl     formBuildTabControl;
    FormBuildTabPageControl formBuildTabPageControl;
    FormBuildGridControl    formBuildGridControl;
    FormBuildDatasource     formBuildDatasource;
    FormBuildStringControl  formString;
    ;
    form = new Form();
    formBuildDatasource = form.addDataSource(tableStr(PurchTable));
    formBuildDesign = form.addDesign('design');
    formBuildTabControl = formBuildDesign.addControl(FormControlType::Tab, 'Tab');
    formBuildTabPageControl = formBuildTabControl.addControl(FormControlType::TabPage, 'TabPage');
    formBuildGridControl = formBuildTabPageControl.addControl(FormControlType::Grid, 'Grid');
    formString = formBuildGridControl.addDataField(formBuildDatasource.id(), fieldNum(PurchTable, PurchId));
    formString.label("PurchId");
    args    = new Args();
    args.object(form);
    formRun = classFactory.formRunClass(args);
    formRun.init();
    formRun.run();
    formRun.wait();

Clean Cart

Delete Table instance is different from select table instance. When do a loop find or assign to a new object, best practice is to use that object's find method.
static void clearDeliveryItems(cusWBWebCartId _cartId)
{
    cusWebCartLine  cusWebCartLine;
    InventTable     inventTable;
    cusWebCartLine  deletecusWebCartLine;
    str             test;
    ;
      _cartId = 'CART_ID_019937';
       while
        select * from cusWebCartLine where cusWebCartLine.cartId == _cartId
        {//test="reset";
            inventTable=inventTable::find(cusWebCartLine.ItemId,false);
                if (inventTable.cusbdDeliveryOption == NoYes::Yes)
                    {
                    ttsbegin;
                    deletecusWebCartLine = cusWebCartLine::findRecId(cusWebCartLine.RecId, true);
                    if (deletecusWebCartLine)
                    {
                        deletecusWebCartLine.delete();
                    }
                    ttscommit;
                   // test="deleted";
                    }
                // else test="safe";
        }
}
 

CreateClass

    int                         id;
    boolean                     ret;
    Array                       errArray = new Array(Types::String);
    str                         className;
    className   = "mbsEBErrChk" + enum2str(mDocType);
    id          = dict.className2Id(className);
    obj         = classFactory.createClass(id);
    if (obj)
    {
        obj.setmDocDir(mDocDir);
        obj.setmDocType(mDocType);
        obj.setTPId(TPId);
        obj.setUpdate(updateRec);
        if (partner)
        {
            obj.SetPartner(partner);
        }
        if (partnerType)
        {
            obj.SetPartnerType(partnerType);
        }
       numErrors   = obj.checkError(mDoc, writeErrRec, _showTiming);
    }

Lookup and Query Range

public void lookupHCMtable(FormStringControl _ctrl)
{
    SysTableLookup              sysTableLookup = SysTableLookup::newParameters(tablenum(hcmtable), _ctrl);
    Query                       query = new Query();
    QueryBuildDataSource        queryBuildDataSource;
    QueryBuildRange             queryBuildRange;
    ;
    sysTableLookup.addLookupfield(fieldnum(hcmtable, EmpId));
    sysTableLookup.addLookupfield(fieldnum(hcmtable, EmpName));
    queryBuildDataSource = query.addDataSource(tablenum(hcmtable));
    queryBuildRange = queryBuildDataSource.addRange(fieldnum(hcmtable, Lastdateworked));
    queryBuildRange.value(strFmt('(Lastdateworked > %1)', today()));
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}
 

Run Query -- Pass in Param

    query = new Query();
    queryRun = new QueryRun(queryStr(mbs3PHPShipTo));
    qbds = queryRun.query().dataSourceTable(tableNum(CustTable));
    qbr = qbds.addRange(fieldNum(CustTable, AccountNum));
    qbr.value(SysQuery::value(custAccount));
    while(queryRun.next())
    {
        custTable = queryRun.get(tableNum(CustTable));
        address = queryRun.get(tableNum(Address));
        customerElemShipTo = this.addElement("ShipToValue", customerShipToNode);
        customerElemSUD = this.addElement("DisplayName", customerElemShipTo);
        customerElemSUD.text(address.Name);
   }

Timestate Table

     DEV_ValidTimeState table;
     ;
     delete_from table;

     table.clear();
     table.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

     table.ValidFrom = DateTimeUtil::newDateTime(1\1\2012, 0);
     table.ValidTo = DateTimeUtil::maxValue();
     table.ItemId = '1000';

     table.insert();

To Update:
ttsBegin;
    while select forUpdate validTimeState(fromDateTime) table
    {
        table.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
        table.ItemId = '1002';
        table.update();
    }
    ttsCommit;

Table and Class

Table variables are declared on the basis of the data dictionary in MorphX; that is, on the basis of the tables, which are declared in the AOT. In most respects, table variables can be considered objects. Contrary to objects, they are not allocated explicitly. Only a variable declaration is required.
All tables are compatible with the Common table in the same way that all objects are compatible with the Object class. Table variables, which are declared as common buffers, can be used to hold data from any table. You cannot access tables without table variables.

public void myMethod()
{
    // Declares and allocates space for one CustTable record
    CustTable custTable;
}

Event Sequence

Sequence of Methods calls while opening the Form

Form --- init ()

Form --- Datasource --- init ()

Form --- run ()

Form --- Datasource --- execute Query ()

Form --- Datasource --- active ()

Sequence of Methods calls while closing the Form

Form --- canClose ()

Form --- close ()

Sequence of Methods calls while creating the record in the Form

Form --- Datasource --- create ()

Form --- Datasource --- initValue ()

Table --- initValue ()

Form --- Datasource --- active ()

Sequence of Method calls while saving the record in the Form

Form --- Datasource --- ValidateWrite ()

Table --- ValidateWrite ()

Form --- Datasource --- write ()

Table --- insert ()

Sequence of Method calls while deleting the record in the Form

Form --- Datasource --- validatedelete ()

Table --- validatedelete ()

Table --- delete ()

Form --- Datasource --- active ()

Sequence of Methods calls while modifying the fields in the Form

Table --- validateField ()

Table --- modifiedField ()

 

http://msdn.microsoft.com/en-us/library/aa655101.aspx

Call Form from code

   if (mbsConfigSetting::Boolean(#BD71))
   {
        args = new Args();
        args.name(formstr('mbsbdcustFiscalTable'));
        args.record(custtable);
        formrun = ClassFactory.formRunClass(args);
        if (formRun)
            {
            formRun.init();
            formRun.run();
            formrun.wait();
            }
        }
   }

 Args                args;
    FormRun             formRun;
   args = new Args();
   args.parmEnumType(eNumNum(InventJournalType));
   args.parmEnum(InventJournalType::Count);
   args.record( InventJournalTable::find(this.TaskSourceId));
   formRun = new MenuFunction(menuitemdisplaystr(InventJournalTableCount), MenuItemType::Display).create(args);
   if (formRun)
    {
            formRun.run();
            formRun.detach();
    }

 

Surrogate Key

Surrogate Key

When a primary index is not specified, Microsoft Dynamics AX uses a Surrogate Key as the primary index. This key is the RecId field and, if the table is saved per company, the DataAreaId. The surrogate key is used on many relations between tables.

Form Query

Use the query object to modify the query of a form data source. The query object can be retrieved using either <name of data source>_Q or FormDataSource.Query()

To make a permanent modification of the query, this is typically implemented in FormDataSource.Init() after the call to super().

To filter records in a form perform the following steps.

1.      In the ClassDeclaration, declare the relevant QueryBuildRange or QueryFilter objects.

2.      In FormDataSource.Init, initialize the range object.

3.      In FormDataSource.ExecuteQuery, assign the actual values to the ranges before call of super().

Combine the sorting with some aggregated fields, which makes the data source display aggregate information from the table instead of transactions. Perform the following steps to show the sum of quantity of inventory transactions shown per Item Id:

1.      Group the data by item id using the addGroupByField on the datasource.

Add Sum(Qty) as a SelectionField

public void init()
{
    QueryBuildDataSource dataSource;

    super();

    dataSource = this.query().dataSourceTable(tableNum(InventTrans));
    dataSource.addGroupByField(fieldNum(InventTrans, ItemId));
    dataSource.addSelectionField(fieldNum(InventTrans, Qty), SelectionField::Sum);
}

Args

If the caller is activating the called object by a menu item the Args object is automatically initialized and sent as a parameter to the object called. AOT properties of the menu item will be used.

If the called object is a form or a report, it can automatically apply to the information send by the Args object. Forms will automatically try to make a delayed synchronize with the args.record().

Query -- Exists Join

Combine records from one table whenever a value exists in a common field in another table.

Display inventory items the providing vendor who has been blocked.
    InventTable inventTable;
    VendTable vendTable;
    ;

    ttsbegin;

    while select forupdate inventTable
     exists join vendTable
      where vendTable.AccountNum == inventTable.PrimaryVendorId &&
vendTable.Blocked == CustVendorBlocked::All
    {
        inventTable.PrimaryVendorId ="";
        inventTable.update();
    }
    ttscommit;

Display only those customers which have at least one sales order in sales table. 

Query                   query;

QueryBuildDatasource    datasource,SalesDataSource,CustomerDataSource;

QueryRun   queryrun;

CustTable custtable;

SalesTable salesTable; 

query = new Query();

CustomerDataSource  = query.addDataSource(tableNum(CustTable ));

SalesDataSource  = CustomerDataSource.addDataSource(tableNum(SalesTable ));

SalesDataSource.joinMode(JoinMode::ExistsJoin   );

SalesDataSource.relations(false);

SalesDataSource.addLink(fieldNum(CustTable, AccountNum),

fieldNum(SalesTable  , CustAccount ));

queryrun = new QueryRun(query);

while(queryRun.next())

{

custtable = queryRun.get(tablenum(CustTable ));

Info(custtable.AccountNum);

}

Query

The queryBuildRange object contains a limitation of the query on a single field.

The queryFilter object is used to filter the result set of an outer join. It filters the data at a later stage than 

the queryBuildRange object and filters the parent table based on the child table results.

The queryBuildDynaLink objects can only exist on the outer data source of a query. The objects contain information about a relation to an external record. When the query is run, this information is converted to additional entries in the "where" section of the SQL statement. The function is used by forms when two data sources are synchronized. The subordinate data source contains DynaLink(s) to the master data source. The function is used even if the two data sources are placed in two different forms, but are synchronized.

The queryBuildLink objects can only exist on inner data sources. The objects specify the relation between the two tables in the join.

2 Data Region with Query Datasource

Query Based Reporting with 2 tables

1.       Generate Query – Add CustTable first, and CustTrans to the datasource of CustTable

2.       Create a Visual Report Model Project, add a new dataset, in the dataset property, selected the query created.

3.       Create Design, drag and drop the dataset to the design,

4.       Create Group, first drag and drop the CustGroup field, then AccountNum, in the header node of AccountNum group, add currency, party to the row. This grouping is first grouped by CustGroup, then by AccountNum, in the AccountNum row, the report lists Currency, Party …

5.       Add a second data region, right click on the “TransActionDetail” design, add Pie.

6.       In the Pie – Data, add Field AmountMST, add CustGroup to the Series

7.       Deploy the report.

SSRS Report from Query

Query based Reporting

1.       Generate Query – Add table to the query, add fields

2.       Create a JOB to run the query to test if the data was displayed correctly

3.       Create a Visual Studio Project, select “Report Model” as project type

4.       Add a new dataset to the “Datasets”, set dataset property to Query

5.       Right click on the design, select auto-design. Fields are automatically created.

6.       Add a table to the auto-design (Dataset)

7.       Preview the report by right clicking on the auto-design – preview.

8.       To group the report by a certain field, drag and drop that field from the dataset to the “Groupings” under the table.

9.       Add Range, From AOT, drag and drop a field to the “Range”, refresh the dataset on the Visual Studio, the parameter Products_DynamicParameter adds the select button to the report dialog.

10.   Deploy the report, in AOT, right click the report “Deploy Element”.

11.   Create a menu for the report, create a menu item in the output folder. The menu item links to the newly created report.