Sub Select For XML Attribute

Hi All,

Today want to share with you one complex requirement that can be archived with Acumatica Cloud xRP platform.
Let me describe scenario first. Lets assume we have customers certification system and we want to track their achievements. For each customer we have several persons (contacts) that want to do certification. Each person can choose one or several courses, depend on his knowledge, position and interest. Depend on each course person needs to complete one or more exams. For some coerces you may choose which of available exams do you want to complete (group 1 or group 2 of exams). And some of these groups are required and some are not.

Here I will provide diagram that illustrate this:

Acumatica Cloud xRP platform

So the problem here that you have multiple dimensions – multiple Contacts, multiple Courses, multiple groups. If you want to get information about how many guys was certified in each company you should do several selects for each contact, course and group. This may significantly degrees performance of any report or inquiry screen.

To keep performance great, it is better to get all data in one select. Luckily Microsoft SQL Server supports very nice feature – Select for XML. This can help us to selects all dimensions and analyze it on server side.

Select for XML

A SELECT query returns results as a rowset, but you can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. Read more here.

Lets check one example

SELECT BAccountID,AcctCD, AcctName,
(SELECT * FROM Contact Contact 
WHERE BAccount.CompanyID = Contact.CompanyID 
AND BAccount.BAccountID = Contact.BAccountID 
FROM BAccount BAccount
WHERE BAccount.Type = 'PR'

This Example will return data like this:

But how to use it in Acumatica?
First of all we need to develop new attribute that will generate sub select with FOR XML parameter a and put it into the DACs field.

New attribute should:

  • Be inherited form PXDBFieldAttribute, as we will read this field form DB.
  • Have RowSelecting event handler to read data from data reader.
  • Have CommandPreparing event handler to change query and append FOR XML parameter.
    public class PXDBSubSelectForXMLAttribute : PXDBFieldAttribute, 
        IPXRowSelectingSubscriber, IPXCommandPreparingSubscriber
    { }

Also attribute should accept sub-select query and  restrict fields that are will be selected with sub-select to male XML simpler.

public PXDBSubSelectForXMLAttribute(Type select, params Type[] columns)
{ }

Example or attribute definition:

On<UNContactInUniversity.contactID, Equal<Contact.contactID>>,
On<UNContactTest.universityID, Equal<UNContactInUniversity.universityID>>>>,
       Where<Contact.bAccountID, Equal<BAccount.bAccountID>,
               And<UNContactTest.passed, IsNotNull>>>),

public String Certification { get; set; }

This example will read all certification information from associated with current BAccount table. Attribute will select form database only specified as parameters fields (Contact.contactID, UNContactTest.testCD, UNContactTest.passed), other fields will be skipped form selection.

And as addition it should provide functionality to read XML data as PXResult with all DACs specified as tables in sub-select command. This behavior is standard for Acumatica.

Attribute will create a custom PXDataRecord (PXDataRecordXML) that will read all specified in attribute fields form XML. Other (Not specified in definition) fields will be filled with null. After reading data attribute will created DAC instance and rise RowSelecting events.

List<PXResult> data = PXDBSubSelectForXMLAttribute
.GetValues<PartnerStatistics, PartnerStatistics.certification>(sender, row);
foreach (Contact contact in data.Select(r => r.GetItem<Contact>()))
       //do something

As in addition attribute also can display XML data in specified format:
DisplayFormat = “Contact ID = {Contact.contactID}, Test CD = {UNContactTest.testCD}, Passed = {UNContactTest.passed}”
Please not that names of tables and fields are case sensitive, so be careful with names.
Several addition parameters can help you to control displaying of values better:
DisplayAppendColumns – parameter will add columns that are not in Display format automatically.
DisplayInsertNulls – will display value even if it is null.

Update from 18/01/2018. Please note SQL generation on Command Preparing had been changed in version 2018R2 to the object model. This example has been updated to meet requiranments of Acumatica 2018R2. If you need to see previous version, please refer to Revision 2 of GitHub Gist file

Full attribute code snippet:

Just as a funny thing:
Here you can see a SQL query that was generated for inquiry screen that I had developed. This includes select for XML.

Looks simple! 🙂

Have a nice development!

Leave a Reply

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