+ Start a Discussion
Matthew HamptonMatthew Hampton 

Lookup Summary Help

Hello:

 

I am still fairly new to Apex and was wondering if what I asking below is even possible.

 

I have two custom obejcts created, one that houses all available addresses and one that I want to calculate a market penetration on, based off of the address object.

 

In GPON_Addresses_c, I have three custom fields labeled as Phone_c, Internet_c, and Video_c. Those fields either contain a 1 if the addresses has the service or is blank if the address does not have the service. The GPON_Address_c object also contains a field labeled Complex_c that identifies what commiunity/building the address resides in.

 

In Rev_Share_Calculation_c, I want each Complex_c (not in a relationship with Complex_c on the GPON_Addresses_c object) to do:

 

(a) a Record Count of the number of times the Complex_c shows up in GPON_Addresses_c

 

(b) sum the number of Phone_c, Internet_c, and Video_c from the GPON_Addresses_C

 

(c) divide the number by the record count

 

I realize this is rather complex and probably backwards way of doing this. If there is a trigger that allows me to do this or a better way to acomplish what I need, please feel free to offer suggestions. I am still a novice on this type of item.

 

Thanks,

 

Matt

 

mikefmikef

I am able to follow your question until the "In Rev_Share_Calculation__c..." part. What is Rev_Share_Calculation__c? Is that a custom field on your market penetration object, or is that the name of your market penetration object?

Also is the Complex__c field on Address a text field? Which I think it is by your explanation. If Complex__c is a text field then your calculations will be not be very accurate, as the user will be able to misspell the complex. And you will false calculations on a complex because of this. ie. is Acme comes up 5 times and Accme comes up 6 times but in reality Acme should have been counted 11 times.

 

So with not really knowing what you really want to do this is what I suggest.

 

  1. Create a Record Type or just a new type picklist value on your account object called "Complex". This will let you use the Account object to store all the different complex locations your company deals with.
  2. Change the Complex__c field on your address object to be a lookup to accounts and filter that look up to only Complex type accounts.
  3. Use reports to figure out your market penetration and not an object in the database. This report will be summarized by Complex and you can use report formula field to do all your calculations.
Let me know if I way off here.

 

Matthew HamptonMatthew Hampton

Mike:

 

Rev_Share_Calculation_C is a custom object. The Complex_C is a custom field on that custom object and is a text field. Essentially what I need is to do a sort of a VLOOKUP with the value of Complex_C what I am looking to match on the two objects and then return another field if there is a match.

 

For example, on Rev_Share_Calculation_c, if Joe Smith Homes is in custom field Complex_C, I would like to be able to query the custom object GPON_Address_c, and count how many times Joe Smith Realty appears in a custom field (call it Name_c) on GPON_Address_c. Is that possible or is there a better way to do this?

 

Thanks in advance for your help.

 

Matt

mikefmikef

Perfect thanks for the further explanation.

 

So the best solution is to make the complex field on address a lookup to Rev_Share_Cal... and make the Name field on Rev_Share_Cal... the name of the complex. That way you can use a roll-up summary field on Rev_Share_Cal to count all the address records. BUT if you can't change the object's configuration because of user issues or other database dependencies then you have to write a trigger.

 

The trigger would be on address and put it in the after part of the trigger. Every time an address is created you will need to query for the Rev_Share_Cal's Complex. When you find that record you increment a new field, Num_Of_Addresses__c by one. Only on insert. Do the same for delete but decrement.

 

In the Trigger you have to make sure you code for:

 

  • not finding a Rev_Share_Cal with that complex
  • finding more then one Rev_Share_Cal with that complex
You can see that changing the object's structure is a way easier task then writing and maintaining these triggers.
Let me know if this helps.

 

Matthew HamptonMatthew Hampton

Awesome, I will give that a try. I think I actually might be able to work it with the roll-up summary. I will give that a try and let you know if i need anything else. Thanks again for your time!