+ Start a Discussion
Matthew HamptonMatthew Hampton 

Apex/Relationship Question



I have the following scenario (and I believe the only way to get what I need is APEX but if there is a better way, please let me know).


I have four custom objects - Qualified Addresses, Billed Accounts, Cable Accounts, Internet Accounts and Voice Accounts.


Cable Accounts is related to Billed Accounts, Internet Accounts is related to Billed Accounts and Voice Accounts is related to Billed Accounts.


Billed Accounts is related to Qualified Addresses.


I want to be able to report on Qualified Addresses with Cable Accounts, Internet Accounts and Voice Accounts. I tried related each of those directly to Qualified Addresses but still had the same issues. Is the only way to correct this to write code updating Billed Accounts with informaion from the three related objects?


Please let me know if you need any more clarity on the question.


Sorry, I couldn't fully understand your use-case/question from the description above. Can you please expand om exactly what information you need updated on what Object from what Object and when that info needs to be updated? 


Per my understanding I think you want to create a report on the mentioned structure, i dont think salesforce supports this kind of structure in the report type object hierarchy.


For these kind of situation we genrally used a flat object concept that consists of all the objects details , this can be achieved using an batch apex class or daily load into the flat object using an integration service

Matthew HamptonMatthew Hampton

Thank you for the replies. I figured APEX was the only way to go.


To simplify things, I have a parent object (Qualified Addresses) with multiple parent-child relationships (Cable Accounts, Internet Accounts, Voice Accounts) and I need to be able to report on Qualified Addresses that have any, all, or no relationship records - however I need to be able to report on all three in a single report.


I did not think this was possible with out of the box reporting so I created the junction object (Billed Accounts) and need to get data from the three child objects above onto a single record.


On Billed Accounts, I want to have on the record two fields from Cable Accounts, two fields from Internet Accounts and two field from Voice Accounts. I presume code is the only way to do this?


If I set it up with the junction object, I would have Qualified Addresses as a Parent and Billed Accounts as the child and would not need to have Cable Accounts, Internet Accounts or Voice Accounts in any sort of relationship, correct?


How about using formula fields on the Junction Object (Billed Accounts) to pull in certain field values from the child (Cable Accounts, Internet Accounts, Voice Accounts) objects? Then you can create a Qualified Addresses-->Billed Accounts report that included those critical field values. No Apex coding required.

Matthew HamptonMatthew Hampton

That was my original thought, to just do the formula but unless I am having a brain cramp (which is entirely possible, I am on SF overload right now), it is not giving me the option to create a new custom field that is a formula and pull any fields off of any of the child objects. Am I just overlooking something here or have I forgotten a step in create the Parent-Child?


I am only able to create cross-object formulas where Billed Accounts is the child in the relationship, correct?




You're absolutely correct (no brain cramping that I know of :) ). You can't pull in Child fields into a Parent record using Formula Fields (since its a 1-Many relationship). You can only do the reverse. My bad - I had gotten confused between which end was the Parent.

Let me try and redeem myself. Another possible solution involves Master-Detail relationships and Workflow field updates. How about you make the relationship between the child objects (Cable Accounts, Internet Accounts, Voice Accounts) and the junction object (Billed Accounts) as Master-Details (instead of a lookup). Then you could create workflow rules on each of the child objects that update fields on the parent junction object every time a child record is created/updated. Obviously this would only work if the relationship between Cable Accounts/Internet Accounts/Voice Accounts and Billed Accounts is 1-1, but I believe that is infact your use case.

Matthew HamptonMatthew Hampton

I think that will work out. Thank you very much for your help. I will get working on that in the next day or two. The Master-Detail will work because if there is no Billed Account, there is no Video Account (or Internet Account or Voice Account).


Thanks again!

Matthew HamptonMatthew Hampton

I was able to get what I needed out of the master-detail relationship on the previous post, but now I have one other small issue.


I have a situation where I do need to write a code. Here is the code itself with a description that I can hopefully get some help with:


trigger GPONUpdate on CBT_Accounts__c (before insert, before update){
list<GPON_Address__c> gpon = [select id, name, cut_street__c, loc__c, zip_code__c from GPON_Address__c where cut_street__c=:Trigger.new[0].cut_street__c
and loc__c =:trigger.new[0].loc__c
and zip_code__c =:trigger.new[0].zip_code__c ];
trigger.new[0].gpon_address__c = gpon[0].id;
if(gpon.size() <=0)
trigger.new[0].gpon_address__c =null;


Essentially what I have is this:


The GPON Address object houses all address records that are qualified for a new service we are offering. CBT accounts houses all accounts that currently have some sort of service with us. I wrote the code so that as I add more addresses to the GPON Address object, the Account record is updated as being qualified for the new service.


The issue I have is the GPON Addresses become available by the hundred, so when I create the new GPON Address records, I do it through the Insert feature using the APEX Data Loader. How can I edit this code so that it works with a batch insert?


Thanks in advance!