One piece of code to update a field in multiple tables

  • Article

I got inspired by Max Belugins blog post about "Abstract Macros" and I got to think about how I would have solved one of the tasks he once set out to solve with this concept.

Basically Max wanted to update a field, of same type, in several tables without writing code for each table.

My approach to this would be to create a table map for the tables I want to update. For each table I make a mapping to the field I want to update.

In the following example with my approach, I update all "City" fields of tables included in the AddressMap table map.

 static void UpdateFromTableMap(Args _args)
{
    #AOT
    // Used for finding the tables to update -->
    TreeNode        treeNodeMap;
    TreeNode              treeNodeMappedTable;
    Set                  setMappedTables = new Set(TypeId2Type(TypeId(TableId)));
    SetEnumerator       se;
    // Used for updating the fiels -->
    SysOperationProgress       progress;
    Common                     common;
    DictTable                   dictTable;
    // The update function
    void updateTable(common    _common)
    {
        AddressMap  addressMap = _common;
        ;
        while select forUpdate addressMap
        {
            addressMap.City = 'CITY';
            addressMap.doUpdate();
        }
    }
    ;
    // Find the base tree node for the tables mapped to the map
    treeNodeMap = TreeNode::findNode(#TableMapsPath+'\\'+tableid2name(tableNum(AddressMap))+'\\mappings');
    // Find tree node for each mapped table
    treeNodeMappedTable = treeNodeMap.AOTfirstChild();
    while (treeNodeMappedTable)
    {
        setMappedTables.add(tableName2Id(treeNodeMappedTable.AOTgetProperty('MappingTable')));
        info (treeNodeMappedTable.AOTgetProperty('MappingTable'));
        treeNodeMappedTable = treeNodeMappedTable.AOTnextSibling();
    }
    if (Box::okCancel("You are now about to update database records", DialogButton::Cancel) == DialogButton::Ok)
    {
        // Update the fields on all mapped tables
        progress = new SysOperationProgress(1,true);
        progress.setTotal(setMappedTables.elements());
        ttsBegin;
        se = setMappedTables.getEnumerator();
        while (se.moveNext())
        {
            DictTable = new dictTable(se.current());
            progress.setText(dictTable.name());
            progress.incCount();
            common = dictTable.makeRecord();
            updateTable(common);
        }
        ttsCommit;
    }
}

The example is split on two major parts. One part figuring out what tables to update and one part to perform the actual update. These parts could of course be merged, but here I want to emphasize on the steps involved.

Both Max's and my approach has the problem that they hide the usage of these tables from the cross reference tool though.

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm