In some situations you may want to show different unconnected documents inside one grid.
Just for example you want to create Customer Overview screen and show all Invoices, Payments and Sales Orders together in one place.
Join tables together is not the best approach, as you will have to add different columns with same names for fields from different tables.
Better Approach is select them independently in Data View delegate and merge them into one virtual DAC.
So plan is the following:
- Create a new Virtual DAC that will represent any document in the system. Please note that this DAC should have a Key that should unique even if there a several documents with the same number in different modules. Usually you can just add a Module to DAC key.
In my example this DAC is AnyDocument
- You declare a DataView based on this DAC with no parameters.
In my Case it is PXSelect Details;
- Now you use DataView delegate to select other documents and create instances of your merged DAC on the fly.
- Finally you can bind the grid to the new DAC and show all required details.
- If you want to have a navigation, you need to implement a special navigation action and there depend on Module decide what Graph should you use for redirection purpose.
Here you can see the full example of the CustomerOverview graph:
Couple of important things in this example:
– PXVirtualDAC – Is a special PXCache extension that forbid cache to read the database in any case. we need it as this DAC is virtual.
– CustomerFamilyHelper – is needed to get parent child relationship to select all the documents from child customers also.
– IsDirty – we override this method to no not notify users that they need to save changes before they leave the screen.
– PXRedirectRequiredException – does actual navigation.
Have a nice development!
9 Replies to “Select Multiple Documents into One Grid”
Can we update some field in this grid view?
Hi Firman, you can update fields from the delegate if you need.
If you give me more specific question, I can try to help more.
Thanks for your suggest.
In my custom grid, I just want updating the field (PIC Purchasing) which one these field exist in all tables that i use on my virtual dac. I have tried to update the field, but the other fields are lost in my grid view.
very helpful, if you can help. Thanks
If all fields are lost, that probably mean that you haven’t properly specified the record key. Each DAC must have a proper IsKey defined on it. It is used to find which records should be updated.
Not all fields are lost, only fields that haven’t a proper isKey. How we save after updated the fields?
I have tried create the button save, using PXGraph.CreateInstance and save it. But the record not updated.
Hi Firman, PXGraph.CreateInstance is definitely wrong approach. So i think we need to review your code.
I suggest you to either create a support case for Acumatica dev team or find me in linked in so we can arrange the review out of blog.
Great post. This can be very useful in certain situations.
My instinct would have been to create some database view but this is definitely better. I was not aware of the Virtual DAC.
If I understood correctly, it might be difficult to support database paging using above approach. When retrieving invoices, payments, etc. But this should not be an issue in most cases.
You still can do paging, but it will require a some manual calculations as you select data from different tables and there is different number of records.
Please check here the approach how you can pass required number of records to the select: http://asiablog.acumatica.com/2016/06/using-pxview-in-dataview-delegate.html Note StartRow and TotalRows
Perfect, thanks Sergey