How to write a method in computed column for data entity?

(0) Share

Report

Posted on by junior AX

1,552

I added a string unmapped field (computed: yes) and i created a new method

    private static server str Method1()   
    {
    }

I want to say if(one of the fields of data entity = sth) return "String1" else return "string2". How can i do that in method 1 that i created?

or can i use a method that is already on the table (data entity datasource method)?

One more thing, from what i read, there is two types, one is computed column and the other is virtual field

1. When should i use computed and when virtual? i read that computed is better but in what case i should say i should use computed and in which case i should choose virtual? what is the difference?

2. I think i can't skip staging using virtual field, is it the same using computed column?

I have the same question (0)

All responses (27)

Answers (1)

Suggested answer

Sergei Minozhenko 23,093 on at

Like (1)

Report

Hi junior AX ,

Check the usage of method SysComputedColumn::if , there are a lot of examples in different entities in the standard application

1. Use computed if it's possible, if not - use virtual fields.

2. Yes, you can't skip staging for entities with virtualfields and with computed you still can skip it

docs.microsoft.com/.../data-entity-computed-columns-virtual-fields

Was this reply helpful? Yes No
Suggested answer

vinitgoyal2005 6,332 on at

Like (1)

Report

Hi,

There are many examples available in AOT as well as on the internet. Check hellodax.com/.../.

Check SalesOrderHeaderV2Entity:

1. Always try to use the Computed column. You can use computed column when the data(columns) exist as a part of data entity but in some scenarios, it is not possible.

example. You have first name and last name in data entity and you want full name as one column then you can use computed column.

Suppose you want to export the on hand inventory for the Product on SalesLine for some integration then you need to use Virtual comuns.

The problem with virtual columns is performance. It is much slower compared to computed column.

2. You have to use staging when using a virtual column.

check https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/data-entity-computed-columns-virtual-fields

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Sergie,

I created an extension of the entity and added the method, then added this method to new unmapped field but i get this error: Method does not exist on view 'ComputedFiledMethod'. What am i missing?

also, which is better to create new method in the entity or use a static method on the table? or it doesn't make any difference?

And can u give me a case where i can't use computed and i have to switch to virtual?

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi Junior_AX,

If you created a computed method in another object (like extension or class) you need to use object_name::method syntax in method name (MyEntityExtension::MyNewMethod)

>> also, which is better to create a new method in the entity or use a static method on the table? or it doesn't make any difference?

Doesn't make any difference

>> And can u give me a case where i can't use computed and i have to switch to virtual?

Let's say you have complex logic in x++ class to calculate sum from different sources. In this case, it probably will be impossible to translate it to SQL and you have only option to use virtual field.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Sergie,

Thanks alot.

what abot the error i mentioned in my previous reply? "Method does not exist on view 'ComputedFiledMethod"

also for the sum, i can put all the logic for calculation the sum in the method and use computed column or cant i?

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi Junior AX,

How did you specify method on entity field? Have you used MyEntityExtension::MyNewMethod syntax?

You can use computed column if you can translate logic to SQL query, if you need to write stored procedure in DB to do calulculation, you can't do it in computed column. If you need to do a sub query to calc sum - it can be done with computed column.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
Hi Sergie,

I used the syntax as u mentioned the error went but this is what i'm getting now (i tried full sync but still, but if i delete the computed field the error goes):

Here's the logic in my Extension method:

private static server str Type() { DataEntityName dataEntityName= tablestr(MyEntity); Table1 table1; Table2 table2; Table3 table3; Table4 table4; String10 ret = "string1"; str Id = SysComputedColumn::comparisonField(tableStr(TableX), identifierStr(TableX), fieldStr(TableX, Id)); if(Id != "") { select firstonly table2 where table2.Status < Status::Closed join table3 where table3.DId == table2.DId exists join table4 where table4.DId == table3.DId && table4.DDId == Id; if (table2) { return "string2"; } select firstonly table1 where table1.DDId == Id && table1.Posted == NoYes::No; if (table1) { return "string3"; } } return ret; }

As for the virtual field, so if my method says select sum(field) from table.... this is considered simple so i can use computed field but if it was sth more complex, then i should use virtual field? but can u give me an example of a select statement this is considered complex and can't be used as computed column?

Was this reply helpful? Yes No
Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi.

Unfortunately computed columns don't work in that way. You are mixing x++ code with methods for computed columns. But you should use only SysComputedColumn methods or return raw sql code (there are some examples in standard code, maybe in DirPartyEntity).

I don't have examples of queries for computed columns and queries.

By the way, have you estimated amount of records to be exported? Maybe you don't need waste your time on computed columns and implement virtual column?

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Sergie,

So the logic i wrote can be translated to raw sql code, or it's not doable at all?

As for the virtual column, i didn't want to use it because of bad performance and it won't skip staging, so if i want o export 2000 records for example, this virtual field will make it slower by how much?

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

Like (0)

Report

You should start by designing T-SQL code snippet for your computed column. Only then write X++ code generating this T-SQL code.

Was this reply helpful? Yes No