Question Status

Verified
dwcwork asked a question on 17 Sep 2013 9:25 AM

Not sure how to do this - something I used Virtual Fields quite a bit in another Database system.  I have two tables: InventTable and InventTableModule.   InventTableModule has pricing information for Inventory, Purchase order and Sales Order (3 records for one Item).  I want to flatten the data into the InventTable, have 3 virtual fields InventoryPrice, SOPrice and POPrice.  That is the way I would do it in my old database for use in reporting, but not sure how to do it in this database.  Do I create a table from a query?  Can anybody point me to a document to read that would explain how to do this.  I am not new to MS-SQL, just new to how you need to use it with AX 2012R2.  I have a report that I want to only print the part once with the 3 fields following (with other data too, but these 3 fields are what are slowing me down).  The only way I can see to do it right now is with 3 lines - not an option.

Reply
Verified Answer
gl00mie responded on 18 Sep 2013 12:59 AM

AX supports display methods on tables, and AX Windows/EP client can show their return values like read-only table fields. A display method can fetch data from sources other than the table it's defined on; you can find a sample of using display methods in a SSRS report in this blog post.

Reply
Verified Answer
Heinz Schweda responded on 18 Sep 2013 6:04 AM

To get your needed values you can use existing methods like this:

   InventTable inventTable = InventTable::find('1000');

   info(strFmt("Sales price: %1 Purch price: %2 Inventory Price: %3",

                                                  inventTable.inventTableModuleSales().pcsPrice(),

                                                  inventTable.inventTableModulePurch().pcsPrice(),

                                                  inventTable.inventTableModuleInvent().pcsPrice()));  

Also you can use computed columns in views, see msdn.microsoft.com/.../gg845841.aspx for example

Reply
Suggested Answer
Joris de Gruyter responded on 17 Sep 2013 9:56 AM

You'll have to explain what you mean by virtual fields, or what your definition is.

AX can have fields on tables that don't store their actual values in the database (sometimes called virtual fields in other software), but when you ask about creating table from a query and flattening data i'm wondering if you're looking to use views.

Reply
Suggested Answer
Joris de Gruyter responded on 17 Sep 2013 1:05 PM

Well, there's also something called a "reference datasource" which is used for exactly that, on forms at least. You include part of another table as a datasource to your form. You would bind to that referenced datasource tho, not the inventtable. But it's "optimized" as far as queries go.

I'm assuming this is on a form?

Reply
Suggested Answer
Joris de Gruyter responded on 17 Sep 2013 9:56 AM

You'll have to explain what you mean by virtual fields, or what your definition is.

AX can have fields on tables that don't store their actual values in the database (sometimes called virtual fields in other software), but when you ask about creating table from a query and flattening data i'm wondering if you're looking to use views.

Reply
dwcwork responded on 17 Sep 2013 10:48 AM

Too new to know what I need.  As I am finding as I jump into the AX 2012 world - I don't know what I don't know.  Yep - accessing the data from another table that is not actually stored in the table I am looking at. So instead of looking directly at table InventTableModule to get my 3 prices, I am pulling them all from InventTable.  Usually a little code involves - where it uses the common index between the files, but adds another variable - like the Module type of 'Sales Order', to only pull that price.  That way I do not have to do the logic of reading the InventTableModule 3 times since the links to the proper instance of those 3 records are found in the one record of InventTable.  I know it still has to pull the data from the 3 records, but that is in the background - so easier to create the report.  Whichever way is best/easiest Views or virtual fields (or whatever they are called in AX)  I am game to learn all I can.  There is a lot of new stuff I am trying to cram into my brain.  I know what I want to do but don't necessarily know how to get there from here.  Thanks.

Reply
Suggested Answer
Joris de Gruyter responded on 17 Sep 2013 1:05 PM

Well, there's also something called a "reference datasource" which is used for exactly that, on forms at least. You include part of another table as a datasource to your form. You would bind to that referenced datasource tho, not the inventtable. But it's "optimized" as far as queries go.

I'm assuming this is on a form?

Reply
dwcwork responded on 17 Sep 2013 2:22 PM

Sorry.  This is for a report - but something else I will research, since I never know when it will come in handy.  So I am going to start looking up Views and see if that is what I need.  What are the "virtual fields" called in AX - so I can research those too?

Reply
Verified Answer
gl00mie responded on 18 Sep 2013 12:59 AM

AX supports display methods on tables, and AX Windows/EP client can show their return values like read-only table fields. A display method can fetch data from sources other than the table it's defined on; you can find a sample of using display methods in a SSRS report in this blog post.

Reply
Verified Answer
Heinz Schweda responded on 18 Sep 2013 6:04 AM

To get your needed values you can use existing methods like this:

   InventTable inventTable = InventTable::find('1000');

   info(strFmt("Sales price: %1 Purch price: %2 Inventory Price: %3",

                                                  inventTable.inventTableModuleSales().pcsPrice(),

                                                  inventTable.inventTableModulePurch().pcsPrice(),

                                                  inventTable.inventTableModuleInvent().pcsPrice()));  

Also you can use computed columns in views, see msdn.microsoft.com/.../gg845841.aspx for example

Reply