I have a very tricky Friday riddle on my desk this morning - I need to summarize data to a junction object.
Here are the factors in play:
- The are three main objects
- Transactions
- Campaigns
- Vendors
- There is a junction object called "Vendor Addendum" that links Vendors and Campaigns
I currently have (by default) the ability to create Summary Reports, where I can detail the total value of Transactions submitted by Vendors over a certain time period and group it by Campaign.
Vendor Delivery Last Week
Acme Co
- Widgets: $1,000
- Weebles: $974
- Wobbles: $869
Smith Inc
- Widgets: $929
- Weebles: $1,743
- Wobbles: $1,211
But I now have the need for the Junction Object "Vendor Addendum" to carry a value (using Rollup Helper, summarize Transaction data on Vendor Addendum record).
In desparation, I have already tried a few things:
- I created a flow, that did a lookup to the Vendor Addendum object, found the Vendor Addendum record that had the same relationships to Campaign and Vendor, and popilated a lookup relationship field
The problem here is that at any given time I have upwards of 250,000 transaction records in my system, and new transaction records are being added every second of every day and the flow was just firing way to often and I was hitting governer limits (tried this as a process as well and ran into the same problem)
- So then I came up with the cockamamie idea of putting a large case statement on the Transaction object that would return the Vendor Addendum ID statically based on the Vendor and Campaign associated with that transaction, and then setting up a daily Analytic Snapshot and mapping the Case Statement Generated ID to a lookup field on a new custom object.... and then using a rollup summary to get my desired result. As anyone can guess, two problems exist here:
- I am essentially doubleing the record count of my Transaction records, which would furhter strain my storage and archiving concerns
- My data would only refresh once per day
With all of that being said, are there any other "Code Light" options that I could use to get my data from point A to point B.
I am fully aware that the best, most reliable, and most responsible option is to hire a developer to create a scheduled apex job and just link the Transaction to the Vendor Addendum based on criteria... but I have a very tiny quarterly budget and just wanted to explore all other options first.
Is anyone up for a riddle?
Thanks,
John
I agree Jackie!
However, my hands are tied by the data model, because of an integration with a third party system :-(
