In this video/post we are going to go through the requirement of exporting all the CustInvoiceTrans with the related header and lines charges. This is a real requirement example that I chose because I find it very didactic, since given the table structure I was forced to use and understand the functions AllItemsQuery, ListDistinct and ListJoin. I also take advantage of this example to explain again how to export data to excel, generating myself the Excel template with the ranges and everything (See a more detailed explanation about how Excel templates work here). Check it out, it is a good opportunity to continue strengthening your ER abilities!

Requirement

We need to export the CustInvoiceTrans (and filter them by date, Customer account etc.) and also the related Header and lines charges. The charges are stored in the MarkupTrans, and we have charges related to the CustInvoiceTrans (Line charges) and to the CustInvoiceJour (Header charges). This is the detail that will make our Model mapping not trivial, we need to get the Filtered CustInvoiceTrans, and retrieve also the related MarkupTrans as well as the CustInvoiceJour related MarkupTrans and, in addition, we need to make it in a way that we can have two different record lists, with the CustInvoiceTrans records in one hand, and all the markupTrans records in the other. (since the requirement is to have 2 different lists in Excel). How would you do that?

Model

We first create a simple ER model with the 2 record lists and some fields in it:

Model Mapping

In this section is where the complexity is added. (In this case I map the model directly clicking in the map model to datasource, instead of creating a new ER Object). We start by Adding the CustInvoiceTrans Table records, making sure to mark the property Ask for query, that will allow the user to filter the CustInvoiceTrans.

ALLItemsQuery function

Now, we need to get all the MarkUpTrans records related to our filtered lines, and we need to have at root level. Luckily we have the function AllItemsQuery that will bring to a new record list all the related records for a given related DS, That’s exactly what we need! We create a new Calculated Field, that we can call $MarkUpTransCustInvoiceTrans, and its formula will be:

ALLITEMSQUERY(CustInvoiceTrans.'<Relations'.MarkupTrans)

This way, we get all the CustInvoiceTrans related MarkupTrans after the filter was applied. We now have to do something similar for the CustInvoiceJour related charges, but in this case we will need to make 2 steps, first retrieving all the CustInvoiceJour related with the filtered CustInvoiceTrans records, and then getting all the MarkupTrans related to those CustInvoiceJour. To get the Related CustInvoiceJour, we use the same function, ALLItemsQuery again:

ALLITEMSQUERY(CustInvoiceTrans.'>Relations'.CustInvoice)

And before we go and get the Related charges, we have to take into account one important detail, now the cardinality of the relation is exactly the opposite, many CustInvoiceTrans can be related to the same CustInvoiceJour, so we need to “remove the duplicates” of the $CustInvoiceJourRelated.

ListDistinct function

The ListDistinct function basically receives a RecordList and a field to be used as uniqueness criteria, so we create a new Calculated field called $CustInvoiceJourRelatedDistinct, using the formula:

LISTDISTINCT('$CustInvoiceJourRelated', '$CustInvoiceJourRelated'.RecId)

Once we know our CustInvoiceJour record list is clean, we just grab the related markuptrans and save them in a new Calculated Field, $MarkUpTransCustInvoiceJour:

ALLITEMSQUERY('$CustInvoiceJourRelatedDistinct'.'<Relations'.MarkupTrans)

ListJoin function

Now, we have:

So, we have the CustInvoiceTrans records and also we have the related MarkupTrans records for header and lines. We just need to do a “Union” of the 2 MarkupTrans record list we have. We can use the ListJoin function to combine 2 record lists of the same type. In this case both of them are MarkUpTrans records. But before doing that, in our excel we need to at least track the InvoiceId to which that charges is related, no matter if it’s a header charge or a line charge. So what we are going to do is to add the exact same calculated field to both Record list, with the same name, so it will also be included in the ListJoin like if it was a normal field in the MarkupTrans table. So we add a Calculated field named $InvoiceId, to both record lists, like this:

And now, we are ready to use the ListJoin function:

LISTJOIN('$MarkUpTransCustInvoiceJour', '$MarkUpTransCustInvoiceTrans')

It will magically get all the MarkupTrans and our created Calculated field!

Format to export to Excel

Explaining this part into details in the blog post makes little sense since making the Excel template is very graphic and you can just go and watch how I set the template up. I will just add here the overview:

• After completing the model (with the model mapping), create a new format based on that model in excel format.
• Create an excel template where you set the names to the important ranges (you will need to add also ranges for the record lists, taking into account that those ranges are going to be N-plicated)
• Import the excel template and map the template named ranges to the model that we filled in the model mapping:

Conclusion

We can use functions like ALLItemsQuery, ListDistinct, ListJoin… as well as playing with the table relations and methods to get more or less complex data structured the way we need, to make it compatible with the format we want to export to, or import from. That’s the key of Electronic Reporting, and the most difficult part of it. I hope this post and video helped you to gain confidence (and that showed you a few things you didn’t know) to help you in your way to be a better ER developer.