Reading an Excel file

1. In the AOT, create a new job named ReadExcelFile with the following code
(replace the file name with your own):

static void ReadExcelFile (Args _args)
{
    SysExcelApplication excel;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;

    SysExcelCells cells;
    COMVariantType type;
    int row;
    CustAccount account;
    CustName name;
    #define.filename(@'C:\temp\customers.xlsx')
    excel = SysExcelApplication::construct();
    workbooks = excel.workbooks();
    try
    {
        workbooks.open(#filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    type = cells.item(row+1, 1).value().variantType();
    while (type != COMVariantType::VT_EMPTY)
    {
        row++;
        account = cells.item(row, 1).value().bStr();
        name = cells.item(row, 2).value().bStr();
        info(strFmt('%1 - %2', account, name));
        type = cells.item(row+1, 1).value().variantType();
    }
    excel.quit();
}
   

2. Run the job to display the contents of the file in the Infolog, as shown in the
following screenshot:

Creating an Excel file

1. In the AOT, create a new job named CreateExcelFile with the following code:

static void CreateExcelFile(Args _args)
{
    CustTable custTable;
    SysExcelApplication excel;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    SysExcelCell cell;
    int row;
    excel = SysExcelApplication::construct();
    workbooks = excel.workbooks();
    workbook = workbooks.add();
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();

    cells.range('A:A').numberFormat('@');
    while select custTable
    {
        row++;
        cell = cells.item(row, 1);
        cell.value(custTable.AccountNum);
        cell = cells.item(row, 2);
        cell.value(custTable.name());
    }
    excel.visible(true);
}

2. Run the job and check the list of customers on the screen:

3. Save the list as a file for further use in the next recipe, say C:\temp\customers.xlsx.

Processing a project journal

1. In the AOT, create a new job named ProjJournalCreate with the following code:

static void ProjJournalCreate(Args _args)
{
    ProjJournalTable jourTable;
    ProjJournalTrans jourTrans;
    ProjJournalTableData jourTableData;
    ProjJournalTransData jourTransData;
    ProjJournalStatic jourStatic;
    ttsBegin;
    jourTableData = JournalTableData::newTable(jourTable);
    jourTable.JournalId = jourTableData.nextJournalId();
    jourTable.JournalType = ProjJournalType::Hour;
    jourTable.JournalNameId = 'Hours';
    jourTableData.initFromJournalName(ProjJournalName::find(jourTable.JournalNameId)); 
    jourStatic = jourTableData.journalStatic();
    jourTransData = jourStatic.newJournalTransData(jourTrans, jourTableData);
    jourTransData.initFromJournalTable();
    jourTrans.initValue();
    jourTrans.ProjId = '10001';
    jourTrans.initFromProjTable(ProjTable::find(jourTrans.ProjId));

    jourTrans.TransDate = systemDateGet();
    jourTrans.ProjTransDate = jourTrans.TransDate;
    jourTrans.CategoryId = 'Design';
    jourTrans.setHourCostPrice();
    jourTrans.setHourSalesPrice();
    jourTrans.TaxItemGroupId = ProjCategory::find(jourTrans.CategoryId).TaxItemGroupId;
    jourTrans.Worker = HcmWorker::findByPersonnelNumber('000062').RecId;
    jourTrans.Txt = 'Design documentation';
    jourTrans.Qty = 8;
    jourTransData.create();
    jourTable.insert();
    ttsCommit;
    info(strFmt("Journal '%1' has been created", jourTable.JournalId));
}

2. Run the job and check the results by going to Project management and accounting | Journals | Hour:

3. Click on the Lines button to open the journal lines, and notice the newly created record:

Posting a general journal

1. Open General ledger | Journals | General journal, and find previously created
journal or manually create a new one. Note the journal's number.

2. In the AOT, create a new job named LedgerJournalPost with the following code
(replace the text 000420_010 with the journal's number from the previous step):

static void LedgerJournalPost(Args _args)
{
    LedgerJournalCheckPost jourPost;
    LedgerJournalTable jourTable;
    jourTable = LedgerJournalTable::find('000420_010');
    jourPost = LedgerJournalCheckPost::newLedgerJournalTable( jourTable, NoYes::Yes);
    jourPost.run();
}
3. Run the job, and notice the Infolog, confirming that the journal was
successfully posted:

4. Open General ledger | Journals | General journal and locate the journal to make
sure that it was posted:

Creating a general journal

1. In the AOT, create a new class named LedgerJournalTransData with the
following code:

class LedgerJournalTransData extends JournalTransData
{
}

public void create(boolean _doInsert = false, boolean _initVoucherList = true)
{
    lastLineNum++;
    journalTrans.LineNum = lastLineNum;
    if (journalTableData.journalVoucherNum())
    {
        this.initVoucher(lastVoucher, false, _initVoucherList);
    }
   
    this.addTotal(false, false);
    if (_doInsert)
    {
        journalTrans.doInsert();
    }
    else
    {
        journalTrans.insert();
    }

    if (journalTableData.journalVoucherNum())
    {
        lastVoucher = journalTrans.Voucher;
    }
}


2. Open the LedgerJournalStatic class, and replace its
newJournalTransData() method with the following code:

JournalTransData newJournalTransData(JournalTransMap _journalTrans,
JournalTableData _journalTableData)
{
    return new LedgerJournalTransData(_journalTrans, _journalTableData);
}

3. Double check that the getLedgerDimension() method exists on the
DimensionAttributeValueCombination table. If not, create it as described in the first
recipe in this chapter.

4. Create a new job named LedgerJournalCreate, with the following code:
static void LedgerJournalCreate(Args _args)
{
    LedgerJournalTable jourTable;
    LedgerJournalTrans jourTrans;
    LedgerJournalTableData jourTableData;
    LedgerJournalTransData jourTransData;
    LedgerJournalStatic jourStatic;
    DimensionDynamicAccount ledgerDim;
    DimensionDynamicAccount offsetLedgerDim;
    ttsBegin;
    ledgerDim = DimensionAttributeValueCombination::getLedgerDimension(
    '110180', ['Department', 'CostCenter', 'ExpensePurpose'], ['OU_2311', 'OU_3568', 'Training']);
    offsetLedgerDim = DimensionAttributeValueCombination::getLedgerDimension(
    '170150', ['Department', 'CostCenter', 'ExpensePurpose'], ['OU_2311', 'OU_3568', 'Training']);
    jourTableData = JournalTableData::newTable(jourTable);
    jourTable.JournalNum = jourTableData.nextJournalId();
    jourTable.JournalType = LedgerJournalType::Daily;
    jourTable.JournalName = 'GenJrn';
    jourTableData.initFromJournalName(LedgerJournalName::find(jourTable.JournalName));
    jourStatic = jourTableData.journalStatic();
    jourTransData = jourStatic.newJournalTransData(jourTrans, jourTableData);
    jourTransData.initFromJournalTable();
    jourTrans.CurrencyCode = 'USD';
    jourTrans.initValue();
    jourTrans.TransDate = systemDateGet();
    jourTrans.LedgerDimension = ledgerDim;
    jourTrans.Txt = 'General journal demo';
    jourTrans.OffsetLedgerDimension = offsetLedgerDim;
    jourTrans.AmountCurDebit = 1000;
    jourTransData.create();
    jourTable.insert();
    ttsCommit;
    info(strFmt("Journal '%1' has been created", jourTable.JournalNum));
}

5. Run the job and check the results by opening General ledger | Journals |
General journal:

6. Click on the Lines button to open journal lines and notice the newly created line:

Building a lookup for selecting a file

1. In the AOT, open the VendFormLetterParameters table and create a new field with
the following properties:

Property                 Value
Type                       String
Name                     TermsAndConditions
Label                      Terms & conditions
ExtendedDataType  FilenameOpen

2. Then add the field to the bottom of the table's PurchaseOrder field group.

3. Next, open the PurchFormLetterParameters form, and create the following
four methods:

public str fileNameLookupTitle()
{
    return "Select Terms & conditions document";
}

public str fileNameLookupInitialPath()
{
    container file;
    file = fileNameSplit(VendFormletterParameters.TermsAndConditions);
    return conPeek(file ,1);
}

public str fileNameLookupFilename()
{
    Filename path;
    Filename name;
    Filename type;
    [path, name, type] = fileNameSplit(VendFormletterParameters.TermsAndConditions);
    return name + type;
}

public container fileNameLookupFilter()
{
    #File
    return [WinAPI::fileType(#txt), #AllFilesName+#txt];
}

4. As a result, we should be able to select and store a text file in the Procurement and
sourcing | Setup | Forms | Form setup form in the Terms & conditions field under
the Purchase order tab of the page:

Adding a Make New Folder button

The mentioned WinAPI class has one more method named browseForFolderDialog().
Besides folder browsing, it also allows creating a new one. The method accepts three
optional arguments:
1. The lookup description.
2. The folder path selected initially.
3. The boolean value, where true shows and false hides the Make New Folder
button. The button is shown by default if this argument is omitted.


Let's replace the lookup() method of the DocumentPath field in the LedgerParameters
form data source with the following code:
public void lookup(FormControl _formControl, str _filterStr)
{
    FilePath path;
    path = WinAPI::browseForFolderDialog(
    "Select document folder extended", LedgerParameters.DocumentPath, true);
    LedgerParameters.DocumentPath = path;
    LedgerParameters_ds.refresh();
}

Now, the folder browsing lookup has a new Make New Folder button, which allows the user to
create a new folder straight away without leaving the lookup:

Building the Browse for Folder lookup

1. In the AOT, open the LedgerParameters table, and create a new field with the
following properties:

Property   Value
Type         String
Name       DocumentPath
Label        Documents
ExtendedDataType FilePath
2. Add the newly created field to the bottom of the table's General field group.

3. In the AOT, open the LedgerParameters form, and create a new method with the
following code:

public str filePathLookupTitle()
{
    return "Select document folder";
}
4. To test the results, open General ledger | Setup | General ledger parameters, and notice the newly created Documents control, which allows you to select a folder:

Another way of displaying custom options

1. In the AOT, create a new job named SysListSelectSingle:
static void SysListSelectSingle(Args _args)
{
    container choices;
    container headers;
    container selection;
    container selected;
    boolean ok;
    choices = [ ["3.0\nAxapta 3.0", 1, false], ["4.0\nDynamics AX 4.0", 2, false],
    ["2009\nDynamics AX 2009", 3, false], ["2012\nDynamics AX 2012", 4, true]];
    headers = ["Version", "Description"];
    selection = selectSingle("Choose version", "Please select Dynamics AX version", choices, headers);
    [ok, selected] = selection;

    if (ok && conLen(selected))
    {
        info(strFmt("You've selected option No. %1", conPeek(selected,1)));
    }
}

2. Run the job to display the options:

3. Select any of the options, click the OK button, and notice your choice displayed in
the Infolog:

Notice that in this case, the returned value is a container holding the selected options.

Displaying a list of custom options

1. In the AOT, create a new job named PickList with the following code:
static void PickList(Args _args)
{
    Map choices;
    str ret;
    choices = new Map(Types::Integer, Types::String);
    choices.insert(1, "Axapta 3.0");
    choices.insert(2, "Dynamics AX 4.0");
    choices.insert(3, "Dynamics AX 2009");
    choices.insert(4, "Dynamics AX 2012");
    ret = pickList(choices, "", "Choose version");
   
    if (ret)
    {
        info(strFmt("You've selected option No. %1", ret));
    }
}


2. Run the job to view the results:

3. Double-click on one of the options to show the selected option in the Infolog:

Building a tree lookup

1. In the AOT, create a new form named BudgetModelLookup. Set its design properties
as follows:

Property         Value
Frame            Border
WindowType   Popup

2. Add a new Tree control to the design, with the following properties:

Property Value
Name     ModelTree

3. Add the following line to the form's class declaration:
BudgetModelTree budgetModelTree;


4. Override the form's init() method with the following code:
public void init()
{
    FormStringControl callingControl;
    callingControl = SysTableLookup::getCallerStringControl (this.args());
    super();
    budgetModelTree = BudgetModelTree::construct(ModelTree, callingControl.text());
    budgetModelTree.buildTree();
}

5. Override the mouseDblClick() and mouseUp() methods of the ModelTree control
with the following code, respectively:
public int mouseDblClick(int _x, int _y, int _button, boolean _ctrl, boolean _shift)
{
    int ret;
    FormTreeItem formTreeItem;
    BudgetModel budgetModel;
    ret = super(_x, _y, _button, _ctrl, _shift);
    formTreeItem = this.getItem(this.getSelection());
    select firstOnly SubModelId from budgetModel
    where budgetModel.RecId == formTreeItem.data();
    element.closeSelect(budgetModel.SubModelId);
    return ret;
}
public int mouseUp(int _x, int _y, int _button, boolean _ctrl, boolean _shift)
{
    int ret;
    ret = super(_x, _y, _button, _ctrl, _shift);
    return 1;

6. The form should look similar to the following screenshot:

7. In the AOT, open the BudgetModel table, and change its lookupBudgetModel()
method with the following code:

public static void lookupBudgetModel(FormStringControl _ctrl, boolean _showStopped = false)
{
    Args args;
    Object formRun;
    args = new Args();
    args.name(formStr(BudgetModelLookup));
    args.caller(_ctrl);
    formRun = classfactory.formRunClass(args);
    formRun.init();
    _ctrl.performFormLookup(formRun);
}


8. To see the results, open Budgeting | Common | Budget register entries. Start
creating a new entry by clicking on the Budget register entry button in the action
pane, and expand the Budget model lookup:

Using a form for building a lookup

1. In the AOT, create a new form named CustLookup. Add a new data source with the
following properties:
Property              Value
Name                  CustTable
Table                   CustTable
Index                   AccountIdx
AllowCheck          No
AllowEdit             No
AllowCreate         No
AllowDelete         No
OnlyFetchActive  Yes

2. Change the properties of the form's design as follows:
Property             Value
Frame                Border
WindowType       Popup

3. Add a new Grid control to the form's design, with the following properties:
Property             Value
Name                 Customers
ShowRowLabels No
DataSource        CustTable

4. Add a new StringEdit control to the grid, with the following properties:
Property             Value
Name                 AccountNum
AutoDeclaration Yes
DataSource        CustTable
DataField           AccountNum


5. Add a new ReferenceGroup control to the grid with the following properties, right
after the AccountNum:
Property             Value
Name                 Name
DataSource        CustTable
ReferenceField   Party

6. Add one more StringEdit control to the grid with the following properties, right
after the Name:
Property             Value
Name                 Phone
DataSource        CustTable
DataMethod       phone

7. Add a new ComboBox control with the following properties to the end of the
Customers grid:
Property             Value
Name                 Blocked
DataSource        CustTable
DataField            Blocked

8. Override the form's init() method with the following code:
public void init()
{
    super();
    element.selectMode(AccountNum);
}

9. Override the form's run() method with the following code:
public void run()
{
    FormStringControl callingControl;
    boolean filterLookup;
    callingControl = SysTableLookup::getCallerStringControl(
    element.args());

    filterLookup = SysTableLookup::filterLookupPreRun(callingControl, AccountNum, CustTable_ds);
    super();
    SysTableLookup::filterLookupPostRun(filterLookup, callingControl.text(), AccountNum, CustTable_ds);
}

10. Finally, override the init() method of the CustTable data source with the
following code:
public void init()
{
    Query query;
    QueryBuildDataSource qbds;
    QueryBuildRange qbr;
    query = new Query();
    qbds = query.addDataSource(tableNum(CustTable));
    qbr = qbds.addRange(fieldNum(CustTable,Blocked));
    qbr.value(queryvalue(CustVendorBlocked::No));
    this.query(query);
}

11. The form in the AOT should look similar to the following screenshot:

12. Locate the CustAccount extended data type in the AOT, and change its property
as follows:

Property     Value
FormHelp   CustLookup

13. To test the results, open Sales and marketing | Common | Sales orders | All sales
orders, and start creating a new sales order. Notice that now the Customer account
lookup is different, and it includes only active customers:

Create a New Table and Field by using X++ code

static void Job36(Args _args)
{
    SysDictTable sysdictTable;

    treenode trv;

    AOTTableFieldList fieldnode;

    #AOT

    trv = treenode::findNode(#TablesPath);

    // adding table

    trv.AOTadd('MS_IT_TestCompanyAddress');

    trv = trv.AOTfindChild('MS_IT_TestCompanyAddress');

    trv.AOTcompile(1);

    trv.AOTsave();

    trv.AOTfindChild('MS_IT_TestCompanyAddress');

    fieldnode = trv.AOTfirstChild();

    // adding fields

    fieldnode.addString('GLCompanyAddressNbr');

    fieldnode.addString('AddressOneText');

    fieldnode.addString('StateProvinceCode');

    fieldnode.addString('PostalCode');

    fieldnode.addString('CityName');

    fieldnode.addString('DistrictName');

    fieldnode.addString('AddressPhoneNbr');

    fieldnode.addString('FaxNbr');

    trv.AOTcompile(1);
}

Building a selected/available list

1. In the AOT, create a new table named InventBuyerGroupList. Do not change any of
the properties as this table is for demonstration only.


2. Add a new field to the table with the following properties (click on Yes if asked to add
a new relation to the table):

Property                   Value
Type                         String
Name                       GroupId
ExtendedDataType   ItemBuyerGroupId


3. Add another field to the table with the following properties:

Property                  Value
Type                        String
Name                      CustAccount
ExtendedDataType  CustAccount

4. In the AOT, open the InventBuyerGroup form and change the design properties
as follows:

Property     Value
Style           Auto

5. Add a new Tab control with the following properties, to the bottom of the design:

Property     Value
Width         Column width
Height        Column height

6. Add a new TabPage control with the following properties to the newly created tab:

Property     Value
Name         BuyerGroups
Caption      Buyer groups

7. Add another TabPage control with the following properties to the newly created tab:

Property     Value
Name         Customers
Caption      Customers


8. Move the existing Grid control to the first tab page and hide the existing Body group
by setting the property:

Property     Value
Visible        No

9. The form should look similar to the following screenshot:

10. Add the following line to the form's class declaration:
SysListPanelRelationTable sysListPanel;

11. Override the form's init() method with the following code:

public void init()
{
    container columns;
    #ResAppl
    columns = [fieldNum(CustTable, AccountNum)];
    sysListPanel = SysListPanelRelationTable::newForm(element, element.controlId(
    formControlStr(InventBuyerGroup,Customers)), "Selected", "Available", #ImageCustomer,


    tableNum(InventBuyerGroupList), fieldNum(InventBuyerGroupList,CustAccount),
    fieldNum(InventBuyerGroupList,GroupId), tableNum(CustTable),
    fieldNum(CustTable,AccountNum), columns);
    super();
    sysListPanel.init();
}


12. Override the pageActivated() method on the newly created Customers tab page
with the following code:

public void pageActivated()
{
    sysListPanel.parmRelationRangeValue(InventBuyerGroup.Group);
    sysListPanel.parmRelationRangeRecId(InventBuyerGroup.RecId);
    sysListPanel.fill();
    super();
}

13. In order to test the list, open Inventory and warehouse management | Setup |
Inventory | Buyer groups, select any group, go to the Customers tab page and use
the buttons provided to move records from one side to the other. You could also do a
double-click or drag-and-drop with your mouse:

Creating a custom instant search filter

1. In the AOT, open the MainAccountListPage form and add a new StringEdit
control with the following properties to the existing Filter group:

Property                     Value
Name                         FilterName
AutoDeclaration         Yes
ExtendedDataType     AccountName
2. Override its textChange() method with the following code:
public void textChange()
{
    super();
    MainAccount_ds.executeQuery();
}
3. Override the control's enter() method with the following code:

public void enter()
{
    super();
    this.setSelection(strLen(this.text()), strLen(this.text()));
}
4. Override the executeQuery() method of the MainAccount data source with the
following code:

public void executeQuery()
{
    QueryBuildRange qbrName;
    qbrName = SysQuery::findOrCreateRange(this.queryBuildDataSource(),
    fieldNum(MainAccount,Name));
   
    qbrName.value(FilterName.text() ? '*'+queryValue(FilterName.text())+'*' : SysQuery::valueUnlimited());

    super();
}


5. In order to test the search, open General ledger | Common | Main accounts
and start typing in the Account name filter. Notice how the account list is being
filtered automatically:

Adding the View details link

1. Open the LedgerJournalTable form in the AOT, expand its data sources, and override jumpRef() of the Name field on the LedgerJournalTable data source with the following code:

public void jumpRef()
{
    LedgerJournalName name;
    Args args;
    MenuFunction mf;
    name = LedgerJournalName::find(
    LedgerJournalTable.JournalName);
 
    if (!name)
    {
        return;
    }
    args = new Args();
    args.caller(element);
    args.record(name);
    mf = new MenuFunction(menuitemDisplayStr(LedgerJournalSetup), MenuItemType::Display);
    mf.run(args);
}

2. Go to General ledger | Journals | General journal, select any of the existing records,
and right-click on the Description column. Notice that the View details option, which
will open the Journal names form, is now available:

Using a tree control

1. In the AOT, create a new class named BudgetModelTree with the following code:
class BudgetModelTree
{
    FormTreeControl tree;
    BudgetModelId modelId;
}


public void new(FormTreeControl _formTreeControl, BudgetModelId _budgetModelId)
{
    tree = _formTreeControl;
    modelId = _budgetModelId;
}


public static BudgetModelTree construct(FormTreeControl _formTreeControl, BudgetModelId_budgetModelId = '')
{
    return new BudgetModelTree(_formTreeControl, _budgetModelId);
}


private TreeItemIdx createNode(TreeItemIdx _parentIdx, BudgetModelId _modelId, RecId _recId)
{
    TreeItemIdx itemIdx;
    BudgetModel model;
    BudgetModel submodel;
    model = BudgetModel::find(HeadingSub::Heading, _modelId);
    itemIdx = SysFormTreeControl::addTreeItem(tree, _modelId + ' : ' + model.Txt, _parentIdx, _recId, 0,true);

   
    if (modelId == _modelId)
    {
        tree.select(itemIdx);
    }

    while select submodel
    where submodel.ModelId == _modelId && submodel.Type == HeadingSub::SubModel
    {
        this.createNode(itemIdx, submodel.SubModelId, submodel.RecId);
    }
    return itemIdx;
}


public void buildTree()
{
    BudgetModel model;
    BudgetModel submodel;
    TreeItemIdx itemIdx;
    tree.deleteAll();
    tree.lock();
    while select RecId, ModelId from model
    where model.Type == HeadingSub::Heading
    notExists join submodel
    where submodel.SubModelId == model.ModelId &&
    submodel.Type == HeadingSub::SubModel
    {
        itemIdx = this.createNode(FormTreeAdd::Root, model.ModelId, model.RecId);
        SysFormTreeControl::expandTree(tree, itemIdx);
    }
    tree.unLock(true);
}



2. In the AOT, open the BudgetModel form's design, expand the Body group, then
expand the GridContainer group, and change the following property of the
BudgetModel grid control:

Property  Value
Visible     No

3. Create a new Tree control right below the BudgetModel grid with the
following properties:

Property    Value
Name        Tree
Width        Column width
Height        Column height
Border       Single line
RowSelect Yes

4. Add the following code to the bottom of the form's class declaration:
BudgetModelTree modelTree;

5. Add the following code to the bottom of form's init():
modelTree = BudgetModelTree::construct(Tree);
modelTree.buildTree();

6. Override selectionChanged() on the Tree control with the following code:
public void selectionChanged(FormTreeItem _oldItem, FormTreeItem _newItem, FormTreeSelect _how)
{
    BudgetModel model;
    BudgetModelId modelId;
    super(_oldItem, _newItem, _how);
    if (_newItem.data())
    {
        select firstOnly model
        where model.RecId == _newItem.data();
  
        if (model.Type == HeadingSub::SubModel)
        {
            modelId = model.SubModelId;

            select firstOnly model
            where model.ModelId == modelId && model.Type == HeadingSub::Heading;
        }
    BudgetModel_ds.findRecord(model);
    BudgetModel_ds.refresh();
    }
}

7. Override the delete() method on the BudgetModel data source with the
following code:

public void delete()
{
    super();
    if (BudgetModel.RecId)
    {
        modelTree.buildTree();
    }
}

8. Override the delete() method on the SubModel data source with the
following code:

public void delete()
{
    super();
    if (SubModel.RecId)
    {
        modelTree.buildTree();
    }
}

9. Add the following code to the bottom of the write() method on the BudgetModel
data source:
modelTree.buildTree();

10. Override the write() method on the SubModel data source and add the following
code to its bottom:
modelTree.buildTree();

11. In the AOT, the BudgetModel form should look like the following screenshot:

12. To test the tree control, open Budgeting | Setup | Budget models. Notice how the
ledger budget models are presented as a hierarchy:

Storing last form values

1. In the AOT, find the LedgerJournalTable form, and add the following code to the bottom of its class declaration:

AllOpenPosted showStatus;
NoYes showCurrentUser;
#define.CurrentVersion(1)
#localmacro.CurrentList
showStatus,
showCurrentUser
#endmacro

2. Create the following additional form methods:
public void initParmDefault()
{
    showStatus = AllOpenPosted::Open;
    showCurrentUser = true;
}


public container pack()
{
    return [#CurrentVersion, #CurrentList];
}


public boolean unpack(container _packedClass)
{
    int version = RunBase::getVersion(_packedClass);


    switch (version)
    {
        case #CurrentVersion:
        [version, #CurrentList] = _packedClass;
        return true;
        default:
        return false;
    }
    return false;
}


public IdentifierName lastValueDesignName()
{
    return element.args().menuItemName();
}


public IdentifierName lastValueElementName()
{
    return this.name();
}


public UtilElementType lastValueType()
{
    return UtilElementType::Form;
}


public UserId lastValueUserId()
{
    return curUserId();
}


public DataAreaId lastValueDataAreaId()
{
    return curext();
}


3. Add the following code to the form's run() method right before its super():

xSysLastValue::getLast(this);
AllOpenPostedField.selection(showStatus);
ShowUserCreatedOnly.value(showCurrentUser);

4. Add the following code to the bottom of the form's close() method:

showStatus = AllOpenPostedField.selection();
showCurrentUser = ShowUserCreatedOnly.value();
xSysLastValue::saveLast(this);

Modifying multiple forms dynamically

1. In the AOT, open SysSetupFormRun class, and create a new method with the
following code:

private void addAboutButton()
{
    FormActionPaneControl actionPane;
    FormActionPaneTabControl actionPaneTab;
    FormCommandButtonControl cmdAbout;
    FormButtonGroupControl btngrp;
    #define.taskAbout(259)
    actionPane = this.design().controlNum(1);
    if (!actionPane || !(actionPane is FormActionPaneControl) || actionPane.style() == ActionPaneStyle::Strip)
    {
        return;
    }
    actionPaneTab = actionPane.controlNum(1);
    if (!actionPaneTab || !(actionPaneTab is FormActionPaneTabControl))
    {
        return;
    }
    btngrp = actionPaneTab.addControl(
    FormControlType::ButtonGroup, 'ButtonGroup');
    btngrp.caption("About");
    cmdAbout = btngrp.addControl(
    FormControlType::CommandButton, 'About');
    cmdAbout.command(#taskAbout);
    cmdAbout.imageLocation(SysImageLocation::EmbeddedResource);
    cmdAbout.normalImage('412');
    cmdAbout.big(NoYes::Yes);
    cmdAbout.saveRecord(NoYes::No);
}




2. In the same class, override the run() method with the following code:

public void run()
{
    this.addAboutButton();
    super();
}

Building a dynamic form

class CustGroupDynamic
{
}


public static void main(Args _args)
{
    DictTable dictTable;
    Form form;
    FormBuildDesign design;
    FormBuildDataSource ds;
    FormBuildActionPaneControl actionPane;
    FormBuildActionPaneTabControl actionPaneTab;
    FormBuildButtonGroupControl btngrp1;
    FormBuildButtonGroupControl btngrp2;
    FormBuildCommandButtonControl cmdNew;
    FormBuildCommandButtonControl cmdDel;
    FormBuildMenuButtonControl mbPosting;
    FormBuildFunctionButtonControl mibPosting;        
    FormBuildFunctionButtonControl mibForecast;
    FormBuildGridControl grid;
    FormBuildGroupControl grpBody;
    Args args;
    FormRun formRun;
    #Task
    dictTable = new DictTable(tableNum(CustGroup));
    form = new Form();
    form.name("CustGroupDynamic");
    ds = form.addDataSource(dictTable.name());
    ds.table(dictTable.id());
    design = form.addDesign('Design');
    design.caption("Customer groups");
    design.style(FormStyle::SimpleList);
    design.titleDatasource(ds.id());
    actionPane = design.addControl(
    FormControlType::ActionPane, 'ActionPane');
    actionPane.style(ActionPaneStyle::Strip);  
    actionPaneTab = actionPane.addControl(FormControlType::ActionPaneTab, 'ActionPaneTab');
    btngrp1 = actionPaneTab.addControl(FormControlType::ButtonGroup, 'NewDeleteGroup');  
    btngrp2 = actionPaneTab.addControl(FormControlType::ButtonGroup, 'ButtonGroup');
    cmdNew = btngrp1.addControl(FormControlType::CommandButton, 'NewButton');
    cmdNew.buttonDisplay(FormButtonDisplay::TextAndImageLeft);
    cmdNew.normalImage('11045');
    cmdNew.imageLocation(SysImageLocation::EmbeddedResource);
    cmdNew.primary(NoYes::Yes);
    cmdNew.command(#taskNew);
    cmdDel = btngrp1.addControl(FormControlType::CommandButton, 'NewButton');
    cmdDel.text("Delete");
    cmdDel.buttonDisplay(FormButtonDisplay::TextAndImageLeft);
    cmdDel.normalImage('10121');
    cmdDel.imageLocation(SysImageLocation::EmbeddedResource);
    cmdDel.saveRecord(NoYes::Yes);
    cmdDel.primary(NoYes::Yes);
    cmdDel.command(#taskDeleteRecord);
    mbPosting = btngrp2.addControl(FormControlType::MenuButton, 'MenuButtonPosting');
    mbPosting.helpText("Set up related data for the group.");
    mbPosting.text("Setup");
    mibPosting = mbPosting.addControl(FormControlType::MenuFunctionButton, 'Posting');
    mibPosting.text('Item posting');
    mibPosting.saveRecord(NoYes::No);
    mibPosting.dataSource(ds.id());
    mibPosting.menuItemName(menuitemDisplayStr(InventPosting));
    mibForecast = btngrp2.addControl(FormControlType::MenuFunctionButton, 'SalesForecast');
    mibForecast.text('Forecast');
    mibForecast.saveRecord(NoYes::No);
    mibForecast.menuItemName(menuitemDisplayStr(ForecastSalesGroup));
    grpBody = design.addControl(FormControlType::Group, 'Body');
    grpBody.heightMode(FormHeight::ColumnHeight);
    grpBody.columnspace(0);
    grpBody.style(GroupStyle::BorderlessGridContainer);
    grid = grpBody.addControl(FormControlType::Grid, "Grid");
    grid.dataSource(ds.name());
    grid.widthMode(FormWidth::ColumnWidth);
    grid.heightMode(FormHeight::ColumnHeight);
    grid.addDataField(ds.id(), fieldNum(CustGroup,CustGroup));
    grid.addDataField(ds.id(), fieldNum(CustGroup,Name));
    grid.addDataField(ds.id(), fieldNum(CustGroup,PaymTermId));
    grid.addDataField(ds.id(), fieldnum(CustGroup,ClearingPeriod));
    grid.addDataField(ds.id(), fieldNum(CustGroup,BankCustPaymIdTable));
    grid.addDataField(ds.id(), fieldNum(CustGroup,TaxGroupId));
    args = new Args();
    args.object(form);
    formRun = classFactory.formRunClass(args);
    formRun.init();
    formRun.run();
    formRun.detach();
}

Creating a dialog

class CustCreate extends RunBase
{
    DialogField fieldAccount;
    DialogField fieldName;
    DialogField fieldGroup;
    DialogField fieldCurrency;
    DialogField fieldPaymTermId;
    DialogField fieldPaymMode;
    CustAccount custAccount;
    CustName custName;
    CustGroupId custGroupId;
    CurrencyCode currencyCode;

    CustPaymTermId paymTermId;
    CustPaymMode paymMode;
}

public container pack()
{
    return conNull();
}

public boolean unpack(container _packedClass)
{
    return true;
}

protected Object dialog()
{
    Dialog dialog;
    DialogGroup groupCustomer;
    DialogGroup groupPayment;
    dialog = super();
    dialog.caption("Customer information");
    fieldAccount = dialog.addField(extendedTypeStr(CustVendAC), "Customer account");
    fieldName = dialog.addField(extendedTypeStr(CustName));
    dialog.addTabPage("Details");
    groupCustomer = dialog.addGroup("Setup");
    fieldGroup = dialog.addField(extendedTypeStr(CustGroupId));
    fieldCurrency = dialog.addField(extendedTypeStr(CurrencyCode));
    groupPayment = dialog.addGroup("Payment");
    fieldPaymTermId = dialog.addField(extendedTypeStr(CustPaymTermId));
    fieldPaymMode = dialog.addField(extendedTypeStr(CustPaymMode));
    return dialog;
}

public boolean getFromDialog()
{
    custAccount = fieldAccount.value();
    custName = fieldName.value();

    custGroupId = fieldGroup.value();
    currencyCode = fieldCurrency.value();
    paymTermId = fieldPaymTermId.value();
    paymMode = fieldPaymMode.value();
    return super();
}

public void run()
{
    info("You have entered customer information:");
    info(strFmt("Account: %1", custAccount));
    info(strFmt("Name: %1", custName));
    info(strFmt("Group: %1", custGroupId));
    info(strFmt("Currency: %1", currencyCode));
    info(strFmt("Terms of payment: %1", paymTermId));
    info(strFmt("Method of payment: %1", paymMode));
}

public static void main(Args _args)
{
    CustCreate custCreate = new CustCreate();
    if (custCreate.prompt())
    {
        custCreate.run();
    }
}

Reading a comma-separated value file

class ReadCommaFile
{
}


public static client void main(Args _args)
{
    CommaTextIo file;
    container line;
    ;
    #define.filename(@'C:\Temp\accounts.csv')
    #File
    file = new CommaTextIo(#filename, #io_read);
   
    if (!file || file.status() != IO_Status::Ok)
    {
        throw error("File cannot be opened.");
    }
    line = file.read();
   
    while (file.status() == IO_Status::Ok)
    {
        info(con2Str(line, ' - '));
        line = file.read();
    }
}

Creating a comma-separated value file

class CreateCommaFile
{
}


public static client void main(Args _args)
{
    CommaTextIo file;
    container line;
    MainAccount mainAccount;
    ;
    #define.filename(@'C:\Temp\accounts.csv')
    #File
    file = new CommaTextIo(#filename, #io_write);
   
    if (!file || file.status() != IO_Status::Ok)
    {
        throw error("File cannot be opened.");
    }
   
    while select MainAccountId, Name from mainAccount
    {
        line = [mainAccount.MainAccountId, mainAccount.Name];
        file.writeExp(line);
    }
   
    info(strFmt("File %1 created.", #filename));
}  

Importing data from an XML file

class ReadXmlFile
{
}


public static void main(Args _args)
{
    XmlDocument doc;
    XmlNodeList data;
    XmlElement nodeTable;
    XmlElement nodeAccount;
    XmlElement nodeName;
    ;
    #define.filename(@'C:\Temp\accounts.xml')
    doc = XmlDocument::newFile(#filename);
    data = doc.selectNodes('//'+tableStr(MainAccount));
    nodeTable = data.nextNode();

    while (nodeTable)
    {
        nodeAccount = nodeTable.selectSingleNode(fieldStr(MainAccount, MainAccountId));
        nodeName = nodeTable.selectSingleNode(fieldStr(MainAccount, Name));
        info(strFmt("%1 - %2", nodeAccount.text(), nodeName.text()));
        nodeTable = data.nextNode();
    }
}

Exporting data to an XML file

class CreateXmlFile
{
}


public static void main(Args _args)
{
    XmlDocument doc;
    XmlElement nodeXml;
    XmlElement nodeTable;
    XmlElement nodeAccount;
    XmlElement nodeName;
    MainAccount mainAccount;
    #define.filename(@'C:\Temp\accounts.xml')
    doc = XmlDocument::newBlank();
    nodeXml = doc.createElement('xml');
    doc.appendChild(nodeXml);
    while select RecId, MainAccountId, Name from mainAccount
    {
        nodeTable = doc.createElement(tableStr(MainAccount));
        nodeTable.setAttribute(fieldStr(MainAccount, RecId), int642str(mainAccount.RecId));
        nodeXml.appendChild(nodeTable);
        nodeAccount = doc.createElement(fieldStr(MainAccount, MainAccountId));
        nodeAccount.appendChild(doc.createTextNode(mainAccount.MainAccountId));
        nodeTable.appendChild(nodeAccount);
        nodeName = doc.createElement(fieldStr(MainAccount, Name));
        nodeName.appendChild(doc.createTextNode(mainAccount.Name));
        nodeTable.appendChild(nodeName);
    }
    doc.save(#filename);
    info(strFmt("File %1 created.", #filename));
}

Building a query object

class ProjTableQuery
{
}


static void main(Args _args)
{
    Query query;
    QueryBuildDataSource qbds1;
    QueryBuildDataSource qbds2;
    QueryBuildRange qbr1;
    QueryBuildRange qbr2;
    QueryRun queryRun;
    ProjTable projTable;
    ;
    query = new Query();
    qbds1 = query.addDataSource(tableNum(ProjTable));
    qbds1.addSortField(fieldNum(ProjTable, Name), SortOrder::Ascending);
    qbr1 = qbds1.addRange(fieldNum(ProjTable,Type));
    qbr1.value(queryValue(ProjType::FixedPrice));
    qbr2 = qbds1.addRange(fieldNum(ProjTable,ProjId));
    qbr2.value(queryValue('2') + '*');
    qbds2 = qbds1.addDataSource(tableNum(ProjEmplTrans));
    qbds2.relations(true);
    qbds2.joinMode(JoinMode::ExistsJoin);
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        projTable = queryRun.get(tableNum(ProjTable));
        info(strFmt( "%1, %2, %3", projTable.ProjId, projTable.Name, projTable.Type));
    }
}

Copying a record across companies

static void buf2Buf(Common _from, Common _to)
{
    DictTable dictTable = new DictTable(_from.TableId);
    FieldId fieldId = dictTable.fieldNext(0);


    while (fieldId && ! isSysId(fieldId))
    {
        _to.(fieldId) = _from.(fieldId);
        fieldId = dictTable.fieldNext(fieldId);
    }
}



static void MainAccountCopy(Args _args)
{
    MainAccount mainAccount1;
    MainAccount mainAccount2;
    mainAccount1 = MainAccount::findByMainAccountId('211100');
    ttsBegin;
    buf2Buf(mainAccount1, mainAccount2);
    mainAccount2.MainAccountId = '211101';
    if (!mainAccount2.validateWrite())
    {
        throw Exception::Error;
    }
    mainAccount2.insert();
    ttsCommit;
}

Copying a record

class MainAccountCopy
{

}


static void main(Args _args)
{
    MainAccount mainAccount1;
    MainAccount mainAccount2;
 
;
    mainAccount1 = MainAccount::findByMainAccountId('211100');
    ttsBegin;
    mainAccount2.data(mainAccount1);
    mainAccount2.MainAccountId = '211101';
    if (!mainAccount2.validateWrite())
    {
        throw Exception::Error;
    }
    mainAccount2.insert();
    ttsCommit;
}

Using a normal table as a temporary table

class VendTableTmp
{
}


server static void main(Args _args)
{
    VendTable vendTable;
    vendTable.setTmp();
    vendTable.AccountNum = '1000';
    vendTable.Blocked = CustVendorBlocked::No;
    vendTable.Party = 1;
    vendTable.doInsert();


    vendTable.clear();
    vendTable.AccountNum = '1002';
    vendTable.Blocked = CustVendorBlocked::All;
    vendTable.Party = 2;
    vendTable.doInsert();

    while select vendTable
    {
        info(strFmt( "%1 - %2", vendTable.AccountNum, vendTable.Blocked));
    }
}

Adding a document handling note

static void VendAccountDocu(Args _args)
{
DocuRef docuRef;
VendTable vendTable;
vendTable = VendTable::find('3001');
docuRef.RefCompanyId = vendTable.dataAreaId;
docuRef.RefTableId = vendTable.TableId;
docuRef.RefRecId = vendTable.RecId;
docuRef.TypeId = 'Note';
docuRef.Name = 'Imported';
docuRef.Notes = 'This vendor was imported.';
docuRef.insert();
}

Merging two records

//Open the AOT, create a new job named LedgerReasonMerge with the
//following code:

static void LedgerReasonMerge(Args _args)
{
ReasonTable reasonTableDelete;
ReasonTable reasonTable;
ttsBegin;
select firstOnly forUpdate reasonTableDelete
where reasonTableDelete.Reason == 'COUNTER';
select firstOnly forUpdate reasonTable
where reasonTable.Reason == 'AUCTION';
reasonTableDelete.merge(reasonTable);
reasonTable.doUpdate();
reasonTableDelete.doDelete();
ttsCommit;
}



//The key method in this recipe is the merge()method. It will ensure that all data from one
//record will be copied into the second one and all related transactions will be updated to
//reflect the change.

Renaming the primary key

//Open the AOT, create a new job named CustAccountRename, and enter the
//following code.


static void CustAccountRename(Args _args)
{
CustTable custTable;
select firstOnly custTable
where custTable.AccountNum == '1103';
if (custTable.RecId)
{
custTable.AccountNum = '1103_';
custTable.renamePrimaryKey();
}
}