How to update the field value of the Auxiliary table by Modscript

I designed a Modscript to change the Balance field value of the Auxiliary table. After deployed the application, the value of Balance in Auxiliary table wasn't updated, but a new record was added to the Auxiliary table. Following shows where the Modscript is called, tables, and Modscript.

Primary table: Leave Requests

Auxiliary Table: Employee Vaction Balance

def changeBalance(userId) {

var fldValue = int();
var timeTaken = int();
var user = Ext.CreateAppRecord (Ext.TableId("USR_LEAVE_REQUESTS"));
user.ReadWithWhere("TS_EMPLOYEE=${userId}");
user.GetFieldValue("TS_ACTUAL_TIME_TAKEN", timeTaken);

var oldBalance =int();
var newBalance =int();

var balanceSelect = Ext.CreateAppRecord (Ext.TableId("USR_EMPLOYEE_VACTION_BALANCE"));
balanceSelect.ReadWithWhere("TS_EMPLOYEE=${userId}");
balanceSelect.GetFieldValue("TS_BALANCE", oldBalance);

newBalance = oldBalance - timeTaken;

balanceSelect.SetFieldValue("TS_BALANCE", newBalance);
var submit = balanceSelect.QuickSubmitToAux();

Ext.LogErrorMsg("Balance is changed!!!");
}

global u = int();
u = Shell.Item().GetFieldValue("Employee");

changeBalance(u);

  • Maybe the ReadWithWhere is not finding the record. ReadWithWhere will return true if it finds the record. Maybe capture the return value and see what is being returned.

  • Use Update() instead of QuickSubmitToAux(). You'll also need to set a lock on the item.

    def changeBalance(userId) {

        var fldValue = int();
        var timeTaken = int();
        var user = Ext.CreateAppRecord (Ext.TableId("USR_LEAVE_REQUESTS"));
        user.ReadWithWhere("TS_EMPLOYEE=${userId}");
        user.GetFieldValue("TS_ACTUAL_TIME_TAKEN", timeTaken);

        var oldBalance =int();
        var newBalance =int();

        var balanceSelect = Ext.CreateAppRecord (Ext.TableId("USR_EMPLOYEE_VACTION_BALANCE"));
        if(balanceSelect.ReadWithWhere("TS_EMPLOYEE=${userId}")){
            balanceSelect.Lock(true);
            balanceSelect.GetFieldValue("TS_BALANCE", oldBalance);

            newBalance = oldBalance - timeTaken;

            balanceSelect.SetFieldValue("TS_BALANCE", newBalance);
            var update = balanceSelect.Update();
            balanceSelect.Unlock();
            if(update)  {
                Ext.LogErrorMsg("Balance is changed!!!");
            } else {
                Ext.LogErrorMsg("Update failed!!!");
            }
        }
    }

    global u = int();
    u = Shell.Item().GetFieldValue("Employee");

    changeBalance(u);
  • Thank you,  

    I have tested the code. When I used the number value, the following statement worked without issues, but when I used the variable, the result wasn't correct.

    balanceSelect.SetFieldValue("TS_BALANCE", 100);                           the value of BALANCE is changed to 100

    balanceSelect.SetFieldValue("TS_BALANCE", timeTaken);                 the value of BALANCE isn't changed

    The variable "timeTaken" was gotten from the field of primary table.

    user.GetFieldValue("TS_ACTUAL_TIME_TAKEN", timeTaken);

    The code I run:

    def changeBalance(userId) {

    var fldValue = int();
    var timeTaken = int();
    var user = Ext.CreateAppRecord (Ext.TableId("USR_LEAVE_REQUESTS"));
    user.ReadWithWhere("TS_EMPLOYEE=${userId}");
    user.GetFieldValue("TS_ACTUAL_TIME_TAKEN", timeTaken);
    //Shell.Item().SetFieldValue("TEST", timeTaken);

    var oldBalance =int();
    var newBalance =int();
    var balanceSelect = Ext.CreateAppRecord (Ext.TableId("USR_EMPLOYEE_VACTION_BALANCE"));

    if(balanceSelect.ReadWithWhere("TS_EMPLOYEE=${userId}")){
    balanceSelect.Lock(true);
    balanceSelect.GetFieldValue("TS_BALANCE", oldBalance);
    newBalance = oldBalance - timeTaken;
    balanceSelect.SetFieldValue("TS_BALANCE", timeTaken);

    var update = balanceSelect.Update();
    balanceSelect.Unlock();

    if(update) {
    Ext.LogErrorMsg("Balance is changed!!!");
    } else {
    Ext.LogErrorMsg("Update failed!!!");
    }
    }
    }

    global u = int();
    u = Shell.Item().GetFieldValue("Employee");
    changeBalance(u);

  • Verified Answer

    Before I forget, this last line should be setting the value to "newBalance" instead of "timeTaken".

    balanceSelect.GetFieldValue("TS_BALANCE", oldBalance);
    newBalance = oldBalance - timeTaken;
    balanceSelect.SetFieldValue("TS_BALANCE", timeTaken);

    I don't know why using the number works and using the variable does not. To narrow down the issue, I would add a bunch of LogErrorMsg lines to find out what is happening. Obviously, you will not keep these after everything is running, but for now, that's the only way to troubleshoot. Maybe add something like:

    balanceSelect.GetFieldValue("TS_BALANCE", oldBalance);
    newBalance = oldBalance - timeTaken;

    Ext.LogErrorMsg("newBalance is " & newBalance & "  oldBalance is " & oldBalance & "  newBalance is " & newBalance);
    balanceSelect.SetFieldValue("TS_BALANCE", newBalance);

    Don't be shy about using the LogErrorMsg. When I troubleshoot code, I sometimes put them after almost every line.

  • Good morning, Vickie.

    I have debug the ModSript by adding Ext.LogErrorMsg() you mentioned. It worked. 

    Ext.LogErrorMsg("newBalance = " &&& newBalance &&& "  oldBalance = " &&& oldBalance &&& "  newBalance = " &&& newBalance);

    The reason caused the issue is I didn't close the previous item completely and opened a new one.

    BTW, The Orchestration still didn't work for dealing with this process.

    Thank you for your harding working!