User-Friendly Excel Exports, part 3

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

So far in this series we’ve been looking at exporting data to Excel from a single table (Contacts). Today we’re going to extend the technique to encompass a second related table, Donations, and without too much trouble we can leverage existing work from earlier entries in this series (part 1, part 1.1 and part 2).

To avoid unnecessary repetition, this article assumes familiarity with the aforementioned predecessors, and you can follow along in today’s accompanying demo file, excel exports, part 3, if you are so inclined.

What kind of changes do we need to make to extend the User-Friendly Excel Export technique so that it will work with Donations as well as Contacts? Well, a big one is to acknowledge that we won’t be exporting the same fields… or to be more precise, we won’t be exporting all of the same fields; depending on which table we start from, the field list will vary with some overlap.

Here are the fields, or more accurately, pseudo fields, that we might want to export from Contacts:

…and here is the corresponding set for Donations:

And to accommodate these two differing sets of pseudo fields, the Alias table will need a couple new “flag” fields, marked “C” for Contact and “D” for Donations below… these are check box-formatted fields that will contain a 1 if checked.

So each alias record can be independently assigned to Contacts, Donations, or both. The relationships graph has been expanded also, with two occurences of the Alias table added, joined like this…

…which facilitates constructing the two filtered value lists, like so:

The demo has some other changes I think are worth mentioning…

1. Bruce Robertson’s error evaluation testing routine has been extended to test both Contacts and Donations here…

…and also here:

2. I had a client request to be able to see the “Most Recent” donation date and amount from the perspective of Contacts. This entailed a) adding a separate sorted relationship from Contact to Donations…

…and b) adding these two entries to the Alias table.

3. Finally, since all the data exported to Excel comes out of calculated text fields, the donation amounts were looking a bit unprofessional, with their lack of proper cent notation or commas to indicate thousands, e.g.,

…as opposed to:

So I wrote a custom function to take care of those two problems…

…and debated whether or not the ability to specify a currency symbol should be part of the CF or not… ultimately I decided to leave it out, since it’s easy enough to specify it in the Alias “output” field like so…

"$" & CurrencyFormat ( DTN::amount )

…and more often than not, I’d prefer not to see the currency symbol in exported data.

Well, I actually have a few more things to say on the endlessly-fascinating subject of User-Friendly Excel Exports, but those will need to wait for part 4.