Update_recordset and arrays from joint tables

The database modification command update_recordset gives you nice possibility to modify data in a fast way.

Unfortunately this kernel command a some little bug if you are using an array element of a joint table for value assignment. The follow update_recordset will be partial ignored, specific from the assignement from the array element custTableRead.Dimension[2]. This assignment and any following field value assignment will be left out (so the assignment of NameAlias with the value "any Alias" will just fail too):

CustTable    custTableUpdate;
CustTable    custTableRead;
;

ttsbegin;

update_recordset custTableUpdate 
    setting Street = 
        custTableRead.Street, 
    Name = custTableRead
        .Dimension[2], 
    NameAlias = "any Alias"
        where custTableUpdate
            .AccountNum 
            == "00000001"
        join custTableRead
            where custTableRead
                .AccountNum 
                == "00000002";

info(strfmt("%1", custTableUpdate
    .RowCount()));

select firstonly custTableUpdate 
    where custTableUpdate.AccountNum
        == "00000001";

// works fine so far
info(custTableUpdate.Street); 


// you didn't get what you expect
info(custTableUpdate.Name);

// you didn't get what you expect
info(custTableUpdate.NameAlias); 
ttscommit;

But all works fine as long as you code it without any array field:

update_recordset custTableUpdate 
    setting Name = custTableRead.Name, 
        NameAlias = "any Alias"
        where custTableUpdate.AccountNum 
            == "00000001"
        join custTableRead
            where custTableRead.AccountNum 
                == "00000002";

Also works fine too, if the array field is hosted by the same table as the table which is selected for update.

update_recordset custTableUpdate 
    setting Name = custTableUpdate
        .Dimension[2], 
        Street = custTableRead
        .Street
        where 
            custTableUpdate
                .AccountNum
            == "00000001"
        join custTableRead
            where custTableRead
                .AccountNum
            == "00000002";

To make a assignment from custTableRead.Dimension[2] to custTableUpdate.Name to work, you have to do it the old fashion way: select first, then update. Well, the execution of this code is slower, but it works (and that's prior in my eyes :).

ttsbegin;

select firstonly Dimension 
    from custTableRead
    where custTableRead.AccountNum
    == "00000002"
    join forupdate custTableUpdate
        where custTableUpdate
        .AccountNum == "00000001";
        
custTableUpdate.Name = custTableRead
    .Dimension[2];
custTableUpdate.update();
        
ttscommit;

This behaviour relates to AX 2009 only and (if not fixed in the meantime) later versions, since the ability to use joins in update_recordset was invented in version AX 2009.

Select Group by and Join Order By

Take care if you mix Group By and Order By in select statements. Queries like that will bring you data from the Data Base, but you loose data; Data retrieved form tables in Order By mode will not be available.

The code example below retrieves the desired due dates from table CustTransOpen, but the vouchers from table CustTrans are missing:

CustTable custTable;
CustTrans custTrans;
CustTransOpen custTransOpen;
;

while select CustGroup from custtable 
    group by CustGroup // group by
    join Voucher 
    from custTrans // order by
         where custTrans.AccountNum 
             == custtable.AccountNum
         join DueDate from custTransOpen 
             group by DueDate // group by
             where custTransOpen.RefRecId 
                 == custTrans.RecId
{
    info(custTable.CustGroup); // works
    info(custTrans.Voucher); // works not
    info(Date2StrUsr(custTransOpen
        .DueDate)); // works
}

Add a 'group by' to CustTrans, and you get CustTrans data too:

CustTable custTable;
CustTrans custTrans;
CustTransOpen custTransOpen;
;

while select CustGroup from custtable 
    group by CustGroup // group by
    join Voucher, RecId from custTrans 
        group by Voucher, RecId // group by
        where custTrans.AccountNum 
            == custtable.AccountNum
        join DueDate from custTransOpen 
            group by DueDate // group by
            where custTransOpen.RefRecId 
                == custTrans.RecId
{
    info(custTable.CustGroup); // works
    info(custTrans.Voucher); // works now
    info(Date2StrUsr(custTransOpen
        .DueDate)); // works
}

This issue applies to queries proceeded with a QueryRun object too.

Still not clear, if the source of this behaviour is driven by AX or by the SQL engine. But fact is, the mix of Group By and Order By raises hard finding bugs.

ChangeCompany: common.company() vs. common.dataAreaId

When you use the changeCompany(...) functionality and the company should be retrieved by a record's company you should always use

changeCompany(common.company())
{
    // do something in the specific company
}

instead of

changeCompany(common.dataAreaId)
{
    // do something in the specific company
}

Why? Because the dataAreaId can contain a virtual company aswell as a normal company, and if you try to do changeCompany(virtualCompany), this will interrupt with a runtime error. But the company() property returns always the selectable company which can properly used in a changeCompany(...) statement.

This works also with table views and crosscompany selections:

while select crosscompany:['Company1', 'Company2'] common
{
    changeCompany(common.company())
    {
        // do something in the specific company       
    }
}

By the way, probably unknown to much people, this company(...) property can be used before data modification to dictate operation company:

    // current company is Company1
    common.company('Company2');
    select firstonly common; // this will return the first row in Company2 even you are in Company1!

I think, this can minimize the use of changeCompany(...) if used wisely.

Update_recordset und Arrays von gejointen Tabellen

Die Datenbankmodifikationsansweisung update_recordset bietet hübsche Möglichkeiten zum schnellen Ändern von Daten.

Leider hat sich hier im Kernel ein kleiner Fehler eingeschlichen, wenn man als Wertzuweisung ein Array-Element einer gejointen Tabelle verwendet. Folgendes update_recordset wird teilweise ignoriert, konkret ab der Zuweisung des Array-Element custTableRead.Dimension[2] wird diese sowie jede weitere Feldwertzuweisung ausgelassen (also auch die Zuweisung von NameAlias mit dem Wert "any Alias" wird schlichtweg nicht durchgeführt):

    CustTable    custTableUpdate;
    CustTable    custTableRead;
    ;

    ttsbegin;

    update_recordset custTableUpdate setting Street = custTableRead.Street, Name = custTableRead.Dimension[2], NameAlias = "any Alias"
        where custTableUpdate.AccountNum == "00000001"
        join custTableRead
            where custTableRead.AccountNum == "00000002";

    info(strfmt("%1", custTableUpdate.RowCount()));

    select firstonly custTableUpdate where custTableUpdate.AccountNum == "00000001";

    info(custTableUpdate.Street); // works fine so far
    info(custTableUpdate.Name); // you didn't get what you expect
    info(custTableUpdate.NameAlias); // you didn't get what you expect

    ttscommit;

Allerdings funktioniert alles prima, wenn kein Array-Feld im Spiel ist:

update_recordset custTableUpdate setting Name = custTableRead.Name, NameAlias = "any Alias"
        where custTableUpdate.AccountNum == "00000001"
        join custTableRead
            where custTableRead.AccountNum == "00000002";

Es funktioniert auch wenn das Array-Feld von der gleichen Tabelle stammt wie die zu aktualisierende Tabelle.

update_recordset custTableUpdate setting Name = custTableUpdate.Dimension[2], Street = custTableRead.Street
        where custTableUpdate.AccountNum == "00000001"
        join custTableRead
            where custTableRead.AccountNum == "00000002";

Damit eine Zuweisung von custTableRead.Dimension[2] in custTableUpdate.Name korrekt funktioniert muss man leider den altmodischen Weg gehen: Die Datensätze erst auswählen, dann die Zuweisung vornehmen und danach aktualisieren. Dies kostet zwar mehr Zeit, funktioniert dafür aber korrekt (was meiner Meinung nach auch mehr Priorität geniesst :).

ttsbegin;

    select firstonly Dimension from custTableRead
        where custTableRead.AccountNum == "00000002"
        join forupdate custTableUpdate
            where custTableUpdate.AccountNum == "00000001";
        
    custTableUpdate.Name = custTableRead.Dimension[2];
    custTableUpdate.update();
        
    ttscommit;

Dieses Verhalten bezieht sich nur auf AX 2009 und (falls nicht korrigiert) spätere Versionen. Die Möglichkeit zur Verwendung von Joins in update_recordset steht nämlich erst ab AX 2009 zur Verfügung.

Select Group By und Join Order By

Vorsicht ist geboten bei Select-Statements, welche Group By und Order By mischen. Zwar funktioniert die Abfrage auf der Datenbank korrekt, aber mit der Einbusse von Daten. Daten für Order By-Tabellen sind dann nämlich nicht verfügbar.

Unten stehendes Beispiel bringt die gewünschten Fälligkeitsdaten der Tabelle CustTransOpen, aber die Belege der Tabelle CustTrans fehlen:

CustTable custTable;
CustTrans custTrans;
CustTransOpen custTransOpen;
;

while select CustGroup from custtable group by CustGroup // group by
    join Voucher from custTrans // order by
         where custTrans.AccountNum == custtable.AccountNum
         join DueDate from custTransOpen group by DueDate // group by
              where custTransOpen.RefRecId == custTrans.RecId
{
    info(custTable.CustGroup); // works
    info(custTrans.Voucher); // works not
    info(Date2StrUsr(custTransOpen.DueDate)); // works
}

Wenn man der Tabelle CustTrans ein 'group by' hinzufügt, erhält man dann auch Daten:

CustTable custTable;
CustTrans custTrans;
CustTransOpen custTransOpen;
;

while select CustGroup from custtable group by CustGroup // group by
    join Voucher, RecId from custTrans group by Voucher, RecId // group by
         where custTrans.AccountNum == custtable.AccountNum
         join DueDate from custTransOpen group by DueDate // group by
              where custTransOpen.RefRecId == custTrans.RecId
{
    info(custTable.CustGroup); // works
    info(custTrans.Voucher); // works now
    info(Date2StrUsr(custTransOpen.DueDate)); works
}

Dieselbe Problematik besteht auch mit Datenabfragen die mit dem QueryRun-Objekt verarbeitet werden.

Es bleibt offen, ob die Ursache für dieses Problem bei AX oder an der SQL-Engine liegt; feststeht, dass man mit dem Mischen von Group By und Order By Bugs erzeugt, die man leider nicht so schnell herausfindet.

ForUpdate record after record insert

Every time, if you save a record into database using method insert() or doInsert(), you're record handle is selected forUpdate. That means, that you can do data manipulation with update() or doUpdate() without reread the current record.

But to do this you can run into trouble. If you have not a database transaction outside the insertation and manipulation, other users can modify your record as well. And as soon you want to do your update() or doUpdate() you get an exception:

Cannot edit a record in Table (Tablename).
An update conflict occurred due to another user process deleting the record or changing one or more fields in the record.

Therefore avoid such code:

Table table;
;
table.KeyField = 'a';
table.insert();

table.AdditionalField = 'halli galli';
table.update();

Do it that way instead:

Table table;
;
ttsbegin;
table.KeyField = 'a';
table.insert();

table.AdditionalField = 'halli galli';
table.update();
ttscommit;

Or even this way:

Table table;
;
table.KeyField = 'a';
table.insert();

// time consuming other code here

ttsbegin;
table.selectForUpdate(true);
table.reread();
table.AdditionalField = 'halli galli';
table.update();
ttscommit;

Depending on requirements you may need a database transaction over all code or not.

ForUpdate-Datensatz nach dem Einfügen eines Datensatzes

Jedes mal, wenn ein Datensatz mit der insert()- oder der doInsert()-Methode auf der Datenbank erzeugt wird, erhält man den neu erzeugten Datensatz mit dem Prädikat forupdate. Das heisst im Prinzip, dass ich den Datensatz beliebig mit update() oder doUpdate() ändern kann ohne ich vorher von der Datenbank neu lesen zu müssen.

Allerdings gibt es oft Probleme, wenn das ganze nicht in einer Datenbanktransaktion gehalten wird: U.u. wird der Datensatz nämlich in der Zwischenzeit verändert und das führt zu einer Ausnahme sobald update() oder doUpdate() die Änderung in die Datenbank schreiben soll:

Ein Datensatz in Debitoren (Tabellenname) kann nicht bearbeitet werden.
Aktualisierungskonflikt, weil ein anderer Benutzerprozess den Datensatz gelöscht oder mindestens ein Feld im Datensatz geändert hat.

Deshalb sollte man solchen Code vermeiden:

Table table;
;
table.KeyField = 'a';
table.insert();

table.AdditionalField = 'halli galli';
table.update();

Stattdessen sollte man ihn entweder so schreiben:

Table table;
;
ttsbegin;
table.KeyField = 'a';
table.insert();

table.AdditionalField = 'halli galli';
table.update();
ttscommit;

Oder so:

Table table;
;
table.KeyField = 'a';
table.insert();

// time consuming other code here

ttsbegin;
table.selectForUpdate(true);
table.reread();
table.AdditionalField = 'halli galli';
table.update();
ttscommit;

Je nach Anforderung ob der Datensatz in der Zwischenzeit verändert werden darf braucht es eine alles umfassende Datenbanktransaktion oder eben nicht.