Workaround for subqueries for a Form ListPage AX2012

(0) Share

Report

Posted on by CBNestor

Hi all,

I need a hand with this please,

I am trying to show 10 productions' Route Operations per each started production in a form listpage. So my plan is to convert this Form List Page that I have created in AOT. (which is showing all prodRoute vertically)

into this: one row per started production, with 10 first operations in columns (Horizontally)

In my head thinking in SQL I would make this using a sub query per each Group of fields but I cannot translate that into AX in AOT.

The cool part of using the ax listpage is that I can use the prebuilt Display Method from each table.

What would be a efficient way to build this??

All responses (12)

Answers (1)

Suggested answer

André Arnaud de Cal... 291,735 Super User 2024 Season 2 on at

Like (0)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

Hi Nestor,

A temporary table is possible, however, it would take computing and database read times to prepare all data. It will be also slower compared to your Started Productions ListPage. If the current load time of the form with computed columns is too slow, you can try to fetch only used fields from the ProdTable or indeed have a look at using a temporary table.

There are multiple examples in the application where a temporary table is used. One example is the Organization hierarchy purposes form which build a temp table and shows it on the list grid.
CBNestor 44 on at

Like (0)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

Interesting Never worked with tempDB for a form just a simple business logic for a report only.

You think it will Make it faster with TempDB?.

I just wonder how will you Populate it with . Only with ProdId? and then the rest with Display Methods Fields?... wonder if that worth the try

I think you were right. I have finally made by hand the whole 10 operations groups (50 computedColumns total)

And I can see that after the 6th group the performance is decreasing as well as filtering and sorting inside form.

I am pulling only 231 records . In time Comparison the Started Productions ListPage is 30% faster than mine. I really wonder is worth the effort to start looking on the Temporary table solution Or is just the amount of columns displayed that is forcing the Client to be slow...
ergun sahin 8,812 Super User 2024 Season 1 on at

Like (0)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

But as I said before, I never build nearly the entire form(view) with computedColumns, although I used it occasionally. I will use more if there is no performance issue. (They are pretty easy and useful)
ergun sahin 8,812 Super User 2024 Season 1 on at

Like (0)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

I don't know about the demand, but I would probably do it with tempTable. It seems that something can be done with queries and views as well. Finally, Display fields, dynamic fields etc.

If they don't accept those, I would say let's make a different tab :)

If they did not accept this suggestion too, I think I would direct them to the reports.

I think the corruption is caused by the Column width
CBNestor 44 on at

Like (0)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

Hi Ergün,

even though I am still interested in a different solution. To be honest I don't know other

by the way you know how to hide the pane that is behind the FactBoxes ( is not exactly the Parts Pane)

edit: got it. Grid properties -> width: Column width
André Arnaud de Cal... 291,735 Super User 2024 Season 2 on at

Like (0)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

The advantage of using T-SQL in computed columns is that the execution is done on the SQL server.
ergun sahin 8,812 Super User 2024 Season 1 on at

Like (0)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

A finished form is always best. (There are easier ways, but it is difficult to say that they are better.)

I would avoid using ComputedColumn if possible but it's a bit of a reflexive move. It has no fundamental basis. (I feel like it's going to slow down.)

I have never used it this way, but if there is no slowness due to data, I think it is ok
Suggested answer

CBNestor 44 on at

Like (1)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

Hi Ergün,

The Speed is the same as a regular ListPage form (prodTableListPage for example). So Is good

Every Column (except for the ProdTable) are ComputedColumn once I did it for the first group I just need to duplicated the methods and the Fields and changing the name and I got it.

here the structure if you are interested.

If you know a better solution please let me know
ergun sahin 8,812 Super User 2024 Season 1 on at

Like (0)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

I understood what you were trying to do when I saw the result. (In fact, if you look carefully, you can understand it in the first picture. I probably didn't have the time when I first saw it)

How is the speed of the final version of the form?(It seemed a little slow but not sure)

I think there are easier ways but I'd be lying if I said I don't curious about the structure or your codes.
CBNestor 44 on at

Like (1)

Report

RE: Workaround for subqueries for a Form ListPage AX2012

Hi André,

It is almost done know. It really helped the explanation of subqueries from Martin It was exactly what I needed!

Just a liitle bit tedious to built all by hand I have to find a way to generate the objects by x++