On-The-Fly Data Transformation using Virtual Data Access Class

Hi Everyone,

Sometimes you want to show existing data in the different format (for report, inquire or corporation purpose).
In my example I want to show you how to transform transform database table into something different. I have a table with years, groups and values, I want to group data by year and compare it between selected years.

Acumatica Data Transformation

To do this we will use data view delegates and virtual data access classes.
First of all lets define 3 data access classes:

  • YearGroup – DAC for selecting data from database table.
  • YearFilter – virtual DAC for storing year what we want to compare.
  • VirtualComparasion – virtual DAC for showing grouped data in UI.
Now we need to define logic. The main idea here, that we can use data view delegate to select and process the data. But also we can store prepared data in the PXCache, so we don’t need to process it on every callback. It will make performance of our screen faster.
Also please notice, that we can clear cached data on filter update, so system will recalculate compared data on next callback.


Acumatica custom form based on virtual DAC
Have a nice development!

2 Replies to “On-The-Fly Data Transformation using Virtual Data Access Class”

  1. May I ask why in code sample attribute PXVirtualDAC is commented:

    public PXFilter Filter;
    public PXSelect Comparasions;
    Should it be commented in production code as well, or it is just typo?

    1. Hi Yuri,
      Actually I left here on purpose to show some options that may be important depend on your business logic.
      In my example it is not needed, as we don’t update anything into the cache. But in some cases you may need to have saving logic. [PXVirtualDAC] is a special attribute that identifies that PXCache won’t go to the database if you update the record. Without it, if you update anything in the cache for the first time, Acuamtica will go and select the latest version of the record from DB. In case of virtual dac, it will bring a problem, as there is no such table in DB.
      You can solve in 2 ways – [PXVirtualDAC] or insert all virtual fields in the PXCache from delegate. Than as it is inserted, Acumatica won’t go to DB anymore.
      Hope it helps!

Leave a Reply

Your email address will not be published. Required fields are marked *