Microsoft Dynamics AX (Archived)

Update table using only variables

(0) Share

Report

Posted on by MYGz

2,162

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

I have the same question (0)

All responses (12)

Answers (1)

AXTechie2120 568 on at

Like (0)

Report

Hi,

what do you mean by variables.

You can updates the details by the code given by you.

But why do you want the other way?

Can you share more details on that.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

You can't update the data in the table without having a variable that points to the table buffer. This is what you already have in your first code example. So it already works with variables.

If you want to create a method that can be used to update data in many different tables, you can use a variable of type Common instead of EmplTable.

And you can reference fields with Common.FieldNum(TableName, FieldName).

Is this what you are looking for? What is your actual functional requirement?

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report
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; }

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report
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.

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report
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; }

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report
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; }

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

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).

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (1)

Report

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.

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report
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; }

Was this reply helpful? Yes No