Microsoft Dynamics AX (Archived)

Update table using only variables

(0) Share

Report

Posted on by MYGz

1,858

Hi,

I have a table EmpTable like so:

If I want to update the salary of John I can do it like so:

static void UpdateSal(Args _args)
{
    EmpTable EmpTable;
    real sal=110000;
    int RowId = 1;
    
    ttsBegin;
    select forUpdate EmpTable where EmpTable.Id==RowId;
    EmpTable.Salary=sal;
    EmpTable.update();
    ttsCommit;
    
    
}

I want help in implementing the above code with using only variables:

static void UpdateSal_WithStrValues(Args _args)
{

    str table = 'EmpTable'
    str field = 'Salary'
    int RowId = 1;
    real sal=110000;

    .....??
    .....??

}

*This post is locked for comments

All responses (12)

Answers (1)

MYGz 1,858 on at

Like (0)

Report
RE: Update table using only variables

Awesome. This is much better. I'll go with this.

A little typo above:

Common common = dictTable.makeRecord();
should be
Common common = dt.makeRecord();

Thank you Martin for your valuable inputs.
Verified answer

Martin Dráb 230,149 Most Valuable Professional on at

Like (1)

Report
RE: Update table using only variables

Here is how you can use newTable() and utilize the SysDictTable object to get field IDs in an easier way:

TableName table = 'EmpTable'; FieldName fieldToUpdate= 'Salary'; FieldName fieldToSelect= 'Id'; int rowId = 1; real sal = 6546456; SysDictTable dt = SysDictTable::newName(table); Common common = dictTable.makeRecord(); ttsbegin; while select forUpdate common where common.(dt.fieldName2Id(fieldToSelect)) == rowId; { common.(dt.fieldName2Id(fieldToUpdate)) = sal; if (!common.validateWrite()) { throw error("Nope"); } common.update(); } ttscommit;
MYGz 1,858 on at

Like (0)

Report
RE: Update table using only variables

Thanks Martin. You were right. New to X++ so don't have much clue about various errors.

Got a tip from here: https://stackoverflow.com/questions/53513457/update-table-using-only-variables

This code works now: (Made the string bounded to 50 length)

static void Job1(Args _args) { str 50 table = 'EmpTable'; str 50 fieldToUpdate= 'Salary'; str 50 fieldToSelect= 'Id'; int RowId = 1; real sal=12213; SysDictTable dictTable = new SysDictTable(tablename2id(table)); Common common = dictTable.makeRecord(); ttsbegin; while select forupdate common where common.(fieldName2id(tableName2Id(table),fieldToSelect)) == RowId { common.(fieldName2id(tableName2Id(table),fieldToUpdate)) = sal; common.update(); } ttscommit; }
Martin Dráb 230,149 Most Valuable Professional on at

Like (1)

Report

RE: Update table using only variables

The error does say that you can't use variables in tableName2Id() - it says "[...] unbounded string fields are not allowed in a WHERE expression".

The code I gave you is a replacement for new SysDictTable(tablename2id(table)). Both create a SysDictTable table object based on a table name. You're using it at a place where you need a string, not a SysDictTable object.
MYGz 1,858 on at

Like (0)

Report

RE: Update table using only variables

Thanks Martin.

You are right actually. It is only throwing error in the Where clause, Attaching screenshots

1. No Error if I don't use variable in Where clause

2. Error if I use variable in where clause:

3. Don't know how to implement the static function that you provided, this is what I tried:

Can the where clause be fixed somehow?

Thanks.
Martin Dráb 230,149 Most Valuable Professional on at

Like (0)

Report

RE: Update table using only variables

I don't think you're right; the parameter of tablename2id() can be a variable.

Anyway, you can simply use the right factory method: SysDictTable::newName(table).
MYGz 1,858 on at

Like (0)

Report
RE: Update table using only variables

Oh wait. TableName2Id requires a compile time string. Won't work with a string variable. Any hacks for this? The below code doesn't works. But the above does.

static void Job1(Args _args) { str table = 'EmpTable'; str fieldToUpdate= 'Salary'; str fieldToSelect= 'Id'; int RowId = 1; real sal=34536; SysDictTable dictTable = new SysDictTable(tablename2id(table)); Common common = dictTable.makeRecord(); ttsbegin; while select forupdate common where common.(fieldName2id(tableName2Id(table),fieldToSelect)) == RowId { common.(fieldName2id(tableName2Id(table),fieldToUpdate)) = sal; common.update(); } ttscommit; }
MYGz 1,858 on at

Like (0)

Report
RE: Update table using only variables

Thank you Nikolaos. I was able to implement it with your valuable tips.

Here's how I did it:

static void Job1(Args _args) { SysDictTable dictTable = new SysDictTable(tablename2id('EmpTable')); Common common = dictTable.makeRecord(); ttsbegin; while select forupdate common where common.(fieldName2id(tableName2Id("EmpTable"),'Id')) == 1 { common.(fieldName2id(tableName2Id("EmpTable"),'Salary')) = 110100; common.update(); } ttscommit; }
Suggested answer

nmaenpaa 101,156 on at

Like (1)

Report
RE: Update table using only variables

You can use DictTable class to instantiate the Common buffer with your table id:

DictTable dictTable = new DictTable(tableId); Common myTable = dictTable.makeRecord();

And you can use tablename2id function to get a table id if you know the table name.
MYGz 1,858 on at

Like (0)

Report
RE: Update table using only variables

I wanted to implement a common function where either:

1. I can supply 4 arguments, tablename, row to select, field to update, value to be updated.

(This case might not be possible as hinted by you since I need a table buffer object first, can't it be created dynamically?)

or

2. I can supply a 3 arguments, single selected buffer table record, field to update, value to update.

I'm Trying something here but not getting it:

static void UpdateSal_WithStrValues(Args _args) { Common common; EmpTable Emptable; str table = 'EmpTable'; str fieldToUpdate= 'Salary'; str fieldToSelect= 'Id'; int RowId = 1; real sal=110000; select EmpTable where EmpTable.Id==RowId; Common = EmpTable; ttsbegin; select forupdate common where common.(FieldNum(table, fieldToSelect)) == RowId; common.(FieldNum(table, fieldToUpdate)) = sal; common.update(); ttscommit; }