overwriteSystemfields, modifiedDateTime, and more!

Gosh.  Such a simple task that takes such a long time.
The simple requisit is this.  At the moment that a user updates a referenced field to a sales order we are required to update the modifiedDateTime field on the sales order and associated invoices.  Nothing more.  The reason we are doing this, in a similar scenario to one stated in this DUG forum, is that we have an integration with SalesForce that requires all sales orders and associated invoices to be updated and we are using the modifiedDateTime field as our integration control for these entities.

Activating the ModifiedDateTime field on the table properties

Update the table properties for the ModifiedDateTime and in theory it should work...  But today we found an example in CustInvoiceJour table where it wasn't updating and we weren't the only ones to notice this.  Our suspicion is something to do with the layer that the change has been executed on, CUS, and that we work from VAR.  It's the only reason we can think of.

Therefore we opted for the overwriteSystemfields method on the buffer to be able to overwrite the value we required...  But this only works if executed on the server, requires us to set permissions, and the killer is that it only works on insert operations.

static server void ZZZupdateSalesTableRefs(SalesExportReason _newReason, SalesExportReason _oldReason)
{
    SalesTable              salesTable;
    CustInvoiceJour         custInvoiceJour;
    CustInvoiceSalesLink    custInvoiceLink;
    ;
    if (_newReason != _oldReason)
    {
        select count(RecId) from salesTable where salesTable.ExportReason == _oldReason;
        if (salesTable.RecId)
        {
            ttsBegin;
            new OverwriteSystemfieldsPermission().assert();

            while select forUpdate salesTable where salesTable.ExportReason == _oldReason
            {
                salesTable.ExportReason = _newReason;
                salesTable.doUpdate();              // Automagically updates modifiedDateTime...

                while select forUpdate custInvoiceJour
                    exists join custInvoiceLink
                    where custInvoiceLink.origSalesId == salesTable.SalesId
                        && custInvoiceLink.salesId == custInvoiceJour.SalesId
                        && custInvoiceLink.invoiceId == custInvoiceJour.InvoiceId
                        && custInvoiceLink.invoiceDate == custInvoiceJour.InvoiceDate
                {
                    //custInvoiceJour.modifiedDateTime = DateTimeUtil::utcNow(); //Compiler error!
                    custInvoiceJour.overwriteSystemfields(true);
                    custInvoiceJour.(fieldNum(CustInvoiceJour,modifiedDateTime)) = DateTimeUtil::utcNow();
                    custInvoiceJour.doUpdate();     // Only works on Insert operations
                }
            }
            CodeAccessPermission::revertAssert();
            ttsCommit;
        }
    }
}

We tried creating a UserConnection but updating the CustInvoiceJour record still would not fire the update to the modifiedDateTime value.

It's time to jump back about 10 years and use direct SQL! This time we have added a new utcDateTime field to the CustInvoiceJour table, which should be sufficient for most use cases, and then we launch a SQL script overwriting the modifiedDateTime value. It's horrible!

static server void ZZZupdateSalesTableRefs(SalesExportReason _newReason, SalesExportReason _oldReason)
{
    SalesTable              salesTable;
    CustInvoiceJour         custInvoiceJour;
    CustInvoiceSalesLink    custInvoiceLink;
    Connection              connection;
    Statement               statement;
    str                     query;
    boolean                 updateInvoice;
    ;

    if (_newReason != _oldReason)
    {
        select count(RecId) from salesTable where salesTable.ExportReason == _oldReason;
        if (salesTable.RecId)
        {
            ttsBegin;

            while select forUpdate salesTable where salesTable.ExportReason == _oldReason
            {
                salesTable.ExportReason = _newReason;
                salesTable.doUpdate();

                updateInvoice = false;
                while select forUpdate custInvoiceJour
                    exists join custInvoiceLink
                    where custInvoiceLink.origSalesId == salesTable.SalesId
                        && custInvoiceLink.salesId == custInvoiceJour.SalesId
                        && custInvoiceLink.invoiceId == custInvoiceJour.InvoiceId
                        && custInvoiceLink.invoiceDate == custInvoiceJour.InvoiceDate
                {
                    custInvoiceJour.ZZZModifiedDateTime = DateTimeUtil::utcNow(); // New field
                    custInvoiceJour.doUpdate();     // Does not update modifiedDateTime field
                    updateInvoice = true;
                }

                if (updateInvoice)
                {
                    query = strFmt(@"
                    UPDATE CustInvoiceJour
                    SET CustInvoiceJour.ModifiedDateTime = CustInvoiceJour.ZZZModifiedDateTime
                    FROM CustInvoiceJour
                    INNER JOIN CustInvoiceSalesLink
                        ON CustInvoiceSalesLink.origSalesId = '%2'
                        AND CustInvoiceSalesLink.DataAreaId = '%1'
                        AND CustInvoiceSalesLink.salesId = CustInvoiceJour.SalesId
                        AND CustInvoiceSalesLink.invoiceId = CustInvoiceJour.InvoiceId
                        AND CustInvoiceSalesLink.invoiceDate = CustInvoiceJour.InvoiceDate
                        AND CustInvoiceSalesLink.DataAreaId = CustInvoiceJour.DataAreaId",
                        salesTable.dataAreaId, salesTable.SalesId);
                    new SqlStatementExecutePermission(query).assert();
                    connection = new Connection();
                    statement = connection.createStatement();
                    statement.executeUpdate(query);
                    CodeAccessPermission::revertAssert();
                }
            }
            ttsCommit;
        }
    }
}

Now all we need to do is to justify to the project manager the time spent with this simple operation...