Is it possible to roll-up a child record Id into a parent look-up field?

[cdevelin]

I have a parent 'asset' object, and I have a child 'valuation' object with potentially many valuation records per asset.

I have a look-up field on the parent where I put the most recent valuation child record Id (called 'valuation_link__c') to enable a user to easily jump from the parent record to the most recent, relevant valuation child record, and to enable formula fields on the parent to show values from the relevant child.

I was wondering whether Apex roll-up can do this for me? i.e. when I am loading thousands of new valuation records into the child object, and each one is related to its parent, rather than running a flow on each child record to update the parent link field if it is newer than the current occupant, can Apex roll-up evaluate the most recent valuation child record per parent, and update the top valuation record id into the lookup field on the parent object?

I tried setting it up, but the full recalculation fails, and the log files shows an error 'list index out of bounds: 4'

[jamessimone]

Yes, you should be able to do this and while there are a couple of different ways, the easiest might be:

• set up a FIRST-based rollup
• set up a Rollup Order By record for that rollup ordering by whatever date field (CreatedDate?) makes your valuation record the most recent

That ought to handle things nicely!

[cdevelin]

[jamessimone]

@cdevelin I think because your response was authored via email that it failed to attach images and as a result it's pretty hard to read through. Is there any chance you can re-submit your response with whatever documentation and attachments you were looking to provide? Thanks!

[cdevelin]

Hi James,

Thanks for responding so quickly!

I did that, my sort order is by a valuation sequence reference number, and I have a FIRST-oriented rollup, but it doesn’t seem to like the ID field usage:

As an illustration of what I am wanting to achieve, the parent record has this field:

That Valuation Link field is a filtered lookup for all the valuation records linked to this parent, but they have been loaded in a random sequence in this test, so the most recent record which should actually be linked here is VAL-0000000492:

I am wanting to use Valuation Reference 2, sorted in descending order, to pick the most recent valuation ID, since that should always be unique, even if multiple valuations exist on the same date:

I also attached the zipped log.

Thanks

Chris
apex-07L9D00000FpDB9UAN.zip

[jamessimone]

@cdevelin this is a bug that I can fix with an updated version, but I think you'll be able to get it working on your end if you remove the Limit Amount argument on your rollup custom metadata record. Let me know if that works!

[cdevelin]

Thanks James, that did the trick, works fine now when I removed the limit.

Can I ask another couple of questions? Can you roll-up multiple different child amounts and focus them on a single parent? e.g. if I wanted to add up a series of transactions being invoices raised and payments received, each held in two separate objects, and the net result is shown as a balance field, i.e. roll-up #1 invoices into balance owing, separately #2 roll-up payments into balance owing, and have the final balance be the sum of both aggregates? (in this case payments would need to be a negative total obviously).

If that didn't work, I could obviously roll-up into two separate fields and combine with a formula on the parent, but it would be cool if it can combine two or more roll-ups targeting the same field and produce the combined sum. I haven't tried it yet, and I suspect that I will just get the result of one or the other, depending which is the last to run.

But then a second question, if I have child amounts which are positive, and I want to change the sign of the aggregated parent when rolling up, is there a way of mathematically manipulating the result, e.g. add up payments which are positive amounts in an object, but show them as a negative sum on the parent object? (Again, solvable with a formula field after the fact, but if I can avoid one, all the better).

Many thanks.

[jamessimone]

Let's take these one at a time:

1. "Can you roll-up multiple different child amounts and focus them on a single parent?"
   i. This is technically feasible at present (for SUM and COUNT rollups) and I can introduce more functionality in the next release to allow this functionality within the full recalc application as well. I've created Allow multiple different child fields to rollup to a single parent field #459 to track this.
   ii. If you set up two rollups from different children or from two fields on the same child to the same parent field right now, for SUM and COUNT this will (again) already work, with the caveat being that the full recalc application won't calculate the total across the two fields correctly. You'd need to have Is Full Record Set unchecked on each record
2. "if I have child amounts which are positive, and I want to change the sign of the aggregated parent when rolling up, is there a way of mathematically manipulating the result, e.g. add up payments which are positive amounts in an object, but show them as a negative sum on the parent object?"
   i. what an interesting question! 😅 At present, such a transformation isn't directly supported. I'd like to think about how to best give you an option to achieve this before answering definitively.

[cdevelin]

Thanks James, I'll give the combined rollup a try! With the mathematical manipulation of the rollup, I could imagine other purposes other than multiplying by -1, e.g. perhaps you wanted a summary field showing a commission calc or a sales tax calc being, for example, 10% of the aggregated children, so just being able to activate a multiplier parameter and supplying a numeric factor of 0.1 could be handy for the numeric rollups, or rolling up opportunities, and having a sliding scale of probability multipliers for opportunity value at each stage etc etc, and then focussing the end value on a single summed figure.