Following on from @Tomazâs question here I spoke to Tomaz and got a little more detail and then built a solution for him I have tried to write it up here as it is relatively complex (in fact my example is a little simpler than the real thing)and involved pulling data from a ârelated chainâ which I think @dirk_s was also asking about here.
The Setup
We have an app chain:
It should be said in Tomazâs setup App C was related to App B in the opposite direction, so it doesnât matter which direction the relationship is.
App C has a number on each record and what we want to get is a report in App A which shows all of App Bâs related App C records split by the related App D. Now if you are anything like me you are confused already, but stick with it and as we build the report it should become more clear.
The Automation
The first thing we want to do is get the related records:
Now we want to build something that we can search in a loop against to split out records related to APP D, to make it easier we can use a hidden calculation field on the App C record:
@All of Name[0]
This just gives us the name of the related APP D record and makes the following steps easier.
Build an Array of names
First up we create an empty Array which we are going to push our related APP D names into via a loop of our collected APP C records:
This leaves us with something that looks like this:
The issue with this is that âD Oneâ and âD Twoâ are related more than once so are in the list multiple times and we donât want that, so we need to de dupe it, I do that by converting to a set and then back to an array:
This gives us a nice clean array to work with:
Getting the Averages
We need to do a couple of preparation steps before we move on to getting the Averages and the first of those is to clear the App C collection.
Then we need a couple more arrays to put things in so we will set those up:
Now we can start getting the data we actually want:
The first part of our loop we get the related records the same as we did all the way back at the start however this time we filter a App C records to only the ones that are related to our D List.
We can then use the rollup block to get the average of the number in the relevant App C records.
The final part of the Loop is to add the information to our arrays leaving us with two arrays:
The Table
All that is left is to get the data into something useful we can do that by using our arrays to build an HTML table ready to add to a PDF:
Conclusion
We have a report which pulls data from indirectly related apps and displays data from a subset of a collection based on specific criteria.
