Complex hierarchy

[juulschobbers]

We want to calculate the number of all contacts in a hierarchy with help of this rollup setting: http://i.imgur.com/y8eOke8.png When I press the calculate button it will count some part of the hierarchy, but in complex hierarchy structures the total is not correct.

So I've tried to change the Relationship field to a custom formula field based on this formula: http://salesforce.harmkorten.nl/2010/salesforce-com-dynamic-reporting-on-all-accounts-within-the-same-hierarchy/ but the field stays empty after pressing the calculate button.

In the new version I can only choose lookup fields. Is it possible to use a formula field for the API name of the child object?

[afawcett]

Yes, as a workaround, you can edit the Child Field to Aggregate field using the Standard Salesforce UI (this new UI is still only Pilot) and put any field you like in it as its a free text field not a drop down. I'll assign this issue to bug status, as it sounds like the Visualforce page is doing some filtering on field types permitted in the drop down?

[juulschobbers]

Thanks for your reply. Do you have any documentation on how to edit the child field to aggregate field using the standard Salesforce UI..... ?

[afawcett]

This should be the default behaviour when you install the package? Click tab, click Go, click Edit next to a record. Have you overridden the Edit action to point to the Visualforce page perhaps?

[juulschobbers]

I see you mean this edit page: http://i.imgur.com/CHWTlRx.png

I've changed the relationship field from ParentID to Hierarchy_group__c (this is the ID of the top level account in a hierarchie), but then nothing happens.

[afawcett]

Yes thats the one! :+1:

I'm seeing this exception being routed back to me as pacakge owner...

System.UnexpectedException: field 'Hierarchy_group__c' can not be grouped in a query call

I assume your clicking the Calculate button, if so i suspect the background job is failing, can you check Apex Jobs page under the Setup menu (under Jobs) for this message? What kind of field is Hierarchy_group__c btw, can you share a screenshot of how this and other related fields are defined on your rollup?

[afawcett]

@wes1278 I think this issue probably should be split at somepoint, though i think we need to know more before we do. At first it seems to be some issue in being able to pick certain fields from the new UI, but now there seems to be something else going on. Lets discuss some more to figure if this is one or two issues before splitting.

[juulschobbers]

Yes I've clicked the calculate button.

16-4-2014 22:31 Batch-Apex Voltooid First error: field 'Hierarchy_group_account__c' can not be grouped in a query call 47 47 47 Schobbers, Juul 16-4-2014 22:33 RollupCalculateJob 707w000000cLsu8

The Hierarchy_group__c is a formule field: http://i.imgur.com/L57F9XN.png

Screenshot how it's defined: http://i.imgur.com/gzAwK5f.png

[afawcett]

Sorry about the slow response on this, it's been a full week both work and community time wise (with the Apex Metadata API Spring'14 release). I'll hopefully be able to get some sight of this next week, where it might be best to arrange between us Support Login access via the package and i can see directly the issue if thats acceptable. Anyway, sorry leaving this hanging for so long, i hope to give this more focus towards end of next week and next weekend.

[juulschobbers]

Hi Andrew,

No problem at all. You've created a free and great app!! So I'm very happy that you are able to look into this 'issue'. So take all the time you need :)

Thanks and have a nice weekend!!

Juul

Verstuurd vanaf mijn iPhone

Op 25 apr. 2014 om 19:48 heeft Andrew Fawcett notifications@github.com het volgende geschreven:

Sorry about the slow response on this, it's been a full week both work and community time wise (with the Apex Metadata API Spring'14 release). I'll hopefully be able to get some sight of this next week, where it might be best to arrange between us Support Login access via the package and i can see directly the issue if thats acceptable. Anyway, sorry leaving this hanging for so long, i hope to give this more focus towards end of next week and next weekend.

Reply to this email directly or view it on GitHubhttps://github.com/afawcett/declarative-lookup-rollup-summaries/issues/27#issuecomment-41420364 .

De informatie in deze e-mail is vertrouwelijk en uitsluitend bestemd voor de geadresseerde. Indien de lezer van deze mededeling niet de geadresseerde is, wordt u er hierbij op gewezen, dat u geen recht hebt kennis te nemen van de resterende inhoud van deze e-mail, het te kopieren of te verstrekken aan andere personen dan de geadresseerde. Indien u deze e-mail abusievelijk hebt ontvangen, brengt u dan de afzender op de hoogte waarbij u gevraagd zal worden het originele bericht te vernietigen. Tempo-Team is niet verantwoordelijk en wijst iedere aansprakelijkheid af voor en/of in verband met alle gevolgen en/of schade van een onjuiste en/of onvolledige verzending en ontvangst van deze e-mail. Voor de op onze dienstverlening van toepassing zijnde algemene voorwaarden en informatie over onze juridische structuur, zie http://www.tempo-team.nl/uitzenden/algemeen/algemene-voorwaarden

[afawcett]

I've got a block of person time coming up next week (otherwise known as holiday) where i can focus on this for you. Are you able to arrange support access or we can perhaps arrange a good time to do a GTM next week?

[juulschobbers]

I'm off for a short leave on thursday till sunday. But before and after I'm available for a GTM. Otherwise I can grant you access.

[afawcett]

Ah ok, no worries, grant me access for a week or so and I'll probably take a look next week. Also can you confirm the steps to reproduce here as well please? Thanks!

[juulschobbers]

I've granted you access for a week.

[afawcett]

Just been looking at this, basically the related to field has to be a lookup and not a formula is the cause of the error. I'll add validation for this in a future release. In the meantime, i've been reading about what your trying to do via the blog you linked above, very interesting btw. I am going to try and reproduce this in my own org now that I see more what your doing.

But i do have one question why have you chosen to use the AccountID18digc rather than ParentId (as used in the blog). Also how is the AccountID18digc populated?

[afawcett]

I ment to say above i'm going to see if I can get it working using the ParentId field as you first tried. Then perhaps a formula field to rollup the contact totals to the parent/group account (based on the formula idea in the blog). Can you also give any further details as to what you said in the first post about "When I press the calculate button it will count some part of the hierarchy, but in complex hierarchy structures the total is not correct."

[afawcett]

Don't worry about how AccountID18Dig is populated I've found your Workflow Field update, still curious as to why you need it though. Working on something i think might do what you want another way....

[juulschobbers]

Hi Andrew,

Thanks for looking into it.

I'm using the AccountID18Dig because of this: http://salesforcevision.blogspot.nl/2011/08/salesforce-18-digit-id.html

I've created the Hierarchy_group__c field because the parentId is not showing the ultimate parentId in complex hierarchies. So I can't rollup the total of the hierarchie.

Example 1 (with parentId)

HQ Division A (ParentId = HQ) Division A1 (ParentId = Division A) Division A2 (ParentId = Division A) Division A2.1 (ParentId = Division A2)

Example 2 (with Hierarchy group)

HQ Division A (Hierarchie_groupc = HQ) Division A1 (Hierarchie_group__c = HQ) Division A2 (Hierarchie_groupc = HQ) Division A2.1 (Hierarchie_group__c = HQ)

Do you understand my problem?

[afawcett]

Ok, but your requirement is to show the total number of contacts for all child accounts at the parent level yes?

[juulschobbers]

Yes I need to show it on the ultimate parent level

[afawcett]

I think I've done it! Does this make sense to you?

[juulschobbers]

Yes this looks good!

[afawcett]

Ok I'll grab some screenshots of my setup, this is realtime btw! :+1:

[afawcett]

Ok first the two rollup summary definitions...

Now the three fields I created on Account to support this.

Finally the formula field...

I've done some tests adding and removing contacts and it does seem to work just fine!

Let me know how you get on!

[juulschobbers]

This one is not giving the correct number of contacts in one of the complex account hierarchies....

The total is 199 and this field is showing 96. It looks like it's only counting the contacts under the highest level and one layer below.

[afawcett]

This is my setup...

[afawcett]

I'm also seeing this error now in your system, i'll login and have a look...

Failed to process batch for class 'dlrs.RollupCalculateJob' for job id '707w000000d4tZ3'
caused by: System.QueryException: unexpected token: JTYq

[juulschobbers]

I've changed that already. It was a Relationship Criteria error. I've deleted that field.

[afawcett]

Your rollup summaries don't appear to be setup the same as mine above, for example your not referencing the Number_of_Contacts_inclusive__c field you created, as far as i can see, this will probably be a factor, please try this...

1. Can you adjust your Contactpersonen to exactly match my Rollup Account Contacts (as above, though field names don't need to be exact)
2. And your Number of Child Contacts to exactly match my Rollup Number of Child Records (as above, though field names don't need to be exact).
3. Then press Calculate on your Contactpersonen and wait for the Calculate Job Id field to clear (the job to finish)
4. Then press Calculate on your Number of Child Contacts and again wait.
5. The above steps should setup the data correctly initially incase there is anything effecting it currently (it can be helpful if you could setup a ListView like mine shown above).
6. Then add or remove Contacts should realtime (providing you have enabled this) update the totals.

[afawcett]

Note that I am using realtime mode, i've not tested with Schedule mode, i suspect there maybe some dependency ordering needed between processing the two rollups, but have not confirmed.

[juulschobbers]

The total of column "Number of Contacts" = 199, but the "number of contacts (inclusive)" on the highest level is only 128

[afawcett]

I've just logged in and ran both the Calculates (waiting for one to finish before the next) and the total is 199! :+1:

Have a play with it, as i said i don't think the Schedule mode will be 100% successful without further testing, as it depends on the order the context and account rollups are processed. I can take a deeper look at this when i have more time.

So in the meantime i recommend that you switch both these rollups to Realtime mode if that's OK.

[juulschobbers]

Thanks!!! I will try these in realtime mode, but in the beginning some users received critical errors.

[afawcett]

Great, let me know the errors and steps if they do and now that I have replication and can more easily reproduce.

[afawcett]

I'm finding this to be pretty stable, do let me know if you find any issues with it and thanks for sticking with me while I figured this out! :+1: